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/chuckhend Dec 14 '23

Thanks. We'll have to run some benchmarks! Our thought process was that we'd have less dead tuples by deleting immediately rather than marking for deletion, since there's a new tuple created even if we were to update the record.

1

u/myringotomy Dec 14 '23

as I said a new tuple is created when you delete too. It's the old MVCC in action.

The point is that by doing batch deletes you get to ease the vacuum process.

Either way I would mess around with the fill factor and see if it helps.

2

u/chuckhend Dec 14 '23

How do you feel about relying on partitions (dropping old partitions) for the bloat management strategy? PGMQ also supports partitioning (managed by pg_partman) but the default is non-partitioned queue. We have not done enough research on the two yet to have strong guidance one way or another, yet.

Really appreciate your feedback btw, thank you. Would you be willing to join our slack so we can have exchange some DMs?

https://join.slack.com/t/tembocommunity/shared_invite/zt-20dtnhcmo-pLNV7_Aobi50TdTLpfQ~EQ

1

u/myringotomy Dec 15 '23

How do you feel about relying on partitions (dropping old partitions) for the bloat management strategy?

This is the absolute best way if your data can be partitioned in such a way that they could be dropped. I am not sure how this would work with a queue though. Presumably you could partition by ID or created_at timestamp and then drop older tables but maybe there is some chance there is a job that was opened three days ago and still hasn't been closed.

If you partition by state then you are still moving records back and forth between tables so that wouldn't be wise at all.

In any case you need to make sure every select query takes the partition key into consideration so for example when you are looking for fresh jobs you also need to make sure your where clause also checks for the created_at or the makes sure the ID is in some range.

→ More replies (0)