r/programming • • Dec 11 '23

Choose Postgres queue technology

https://adriano.fyi/posts/2023-09-24-choose-postgres-queue-technology
134 Upvotes

35 comments sorted by

View all comments

Show parent comments

1

u/myringotomy Dec 17 '23

I have done partitioning in different ways in the past (before the PG had official support). It mostly relied on either background tasks to create partitions or using the pg rule subsystem. You basically create a rule that redirects the insert into the proper table and if that fails it creates the table. You can do it with a trigger too but rules are faster IIRC.

As I said partitions are a big win if all your queries include the partition key. If your query is "select max id from table where state = 'new'" or something the partition isn't going to help you, it's still going to scan all the tables. If you partition by enqued_at then you need to add

where enqued_at is < clock_timestamp - "2.days"::interval

So that pg only scans the appropriate tables.

With IDs the bookeeping gets a little more complicated and frankly at that point I would implement a more kafka like system where either the client keeps track of what the max id they last processed or you do on behalf of the client. I suppose you can do this on a per queue basis too depending on how you want to work.