search

tembo-io / pgmq

A lightweight message queue. Like AWS SQS and RSMQ but on Postgres.
PostgreSQL License
2.68k stars 71 forks source link

Add retention period configuration #132

Open v0idpwn opened 1 year ago

v0idpwn commented 1 year ago

For SQS feature parity. Messages too old should be automatically deleted.

caffeinatedgaze commented 9 months ago

Is this currently implemented (e.g. pg_partman takes care of it) or library users have to sort it out on their own? This also looks like something that should be on the README. :thinking:

I could probably contribute with your help.

caffeinatedgaze commented 9 months ago

Ah, it is already there. One has to create the table as partitioned from the very beginning. I wish I knew this earlier.

For everybody else, see this: https://github.com/tembo-io/pgmq?tab=readme-ov-file#partitioned-queues

ChuckHend commented 9 months ago

We don't have an API for it, but a queue should be able to change from regular to a partitioned queue through manual steps. All of the read/write/delete operations are compatible with both.

That would be a really cool contribution.

caffeinatedgaze commented 8 months ago

I am thinking of doing this in two steps:

  1. Create a new table partitioned by key.
  2. Move data from the old table to the new one that happens to be inside the retention interval.

The downside is that any operations on the queue must be paused.

I am really new to partition management. Please, share your thoughts :smile: @ChuckHend

ChuckHend commented 8 months ago

pg_partman's background worker handles the partition management, such as creating new partitions and dropping old partitions.

The pgmq pgmq_create_partitioned() api handles configuring pg_partman for the queue. retention_interval can be either the number of messages to retain, or the number of days of messages to retain. Anything beyond that is retention_interval is dropped by pg_partman. Our docs could be more clear about this for sure.

https://github.com/tembo-io/pgmq/blob/e00e716b1a79ec330de2060720cee8425c5c4355/src/api.rs#L126-L130

What I was thinking is that we could create some new function: alter_queue() and this would handle taking a partitioned queue, and turning it into non-partitioned. Or non-partitioned into a partitioned queue.

In case of non-partitioned -> partitioned, we'd probably need it to execute the migration_to_partman outlined in pg_partman's 4.7.1 docs