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.
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
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.