Comment by jashmatthews
1 year ago
By the time you need append-only job statuses it's better to move to a dedicated queue. Append-only statuses help but they also make the polling query a lot more expensive.
Deleting older rows is a nightmare at scale. It leaves holes in the earlier parts of the table and nerfs half the advantage of using append-only in the first place. You end up paying 8kb page IO costs for a single job.
Dedicated queues have constant time operations for enqueue and dequeue which don't blow up at random times.
With a partitioned table you can painlessly remove old rows. Of course, you then have to maintain your partitions, but that's trivial.
It's far from trivial. Autoanalyze doesn't work on partitioned tables, only on the partitions themselves. Partitioning a busy job queue table is a nightmare in itself.
partitions are often used to drop old data in constant time.
They can also help to mitigate io issues if you use your insertion timestamp as the partition key and include it in your main queries.
Yeah the ULID/UUIDs which can be be partitioned by time in this way are AWESOME for these use cases.