6 ms·
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 mor
by jashmatthews 2y 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.
- iTokio 2y agopartitions 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.
- jashmatthews 2y agoYeah the ULID/UUIDs which can be be partitioned by time in this way are AWESOME for these use cases.
- felixyz 2y agoWith a partitioned table you can painlessly remove old rows. Of course, you then have to maintain your partitions, but that's trivial.
- jashmatthews 2y agoIt'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.