Postgres: no retention or partition management for events/attempts, causing unbounded growth

[miekassu]

Summary

Outpost's Postgres events and attempts tables are declared as range-partitioned on time, but only DEFAULT partitions are created.

We could not find any code that creates, rotates, or drops time-based Postgres partitions, nor any built-in Postgres retention mechanism. This leaves operators implementing retention externally with row-by-row DELETE, with the associated dead-tuple, vacuum, and index-maintenance overhead.

This appears to be an incomplete part of the intended Postgres design rather than a new feature request.

Evidence in the source

The Postgres schema already uses time-based partitioning:

PRIMARY KEY (time, id)
) PARTITION BY RANGE (time);

CREATE TABLE events_default PARTITION OF events DEFAULT;

The same pattern is used for attempts.

More importantly, internal/logstore/pglogstore/README.md:367-373 explicitly lists:

Easy data retention (drop old partitions)

as a benefit of the time-based partitioning design.

However, we could not find runtime or migration code that creates, attaches, detaches, or drops those partitions.

The retention documentation also appears unfinished: docs/content/self-hosting/guides/event-delivery-log.mdoc:8 currently contains:

Need to talk about data retention and retention policies.

Request

It would be great to complete the Postgres retention design by adding:

1. Automatic time-range partition management — create future partitions and drop expired ones.
2. A Postgres retention setting implemented on top of those partitions.
3. A supported migration path for existing installations where data is already in the DEFAULT partitions.
4. Documentation describing the intended Postgres retention model.

Questions

1. Is Postgres partition lifecycle management already planned or tracked?
2. Is there a recommended migration path for existing DEFAULT partitions?
3. Until native retention exists, what retention approach do you recommend for Postgres?

Small unrelated finding

The primary-key index on attempts_default still appears to be named deliveries_default_pkey.

000005_denormalize_attempts.up.sql:59-60 renames the table and partition but apparently not the constraint-backing index. Harmless, but slightly confusing when inspecting pg_stat_user_indexes.

[alexluong]

Hi @miekassu, thanks for sharing a great writeup. I can confirm that everything you called out here is correct.

To briefly answer your questions first:

1. Is Postgres partition lifecycle management already planned or tracked?
   Yes, we have Postgres partition management #249 to track this. That said, it's not on the roadmap currently.

2. Is there a recommended migration path for existing DEFAULT partitions?
   No, that's not something we have considered but can definitely think through and improve our documentation.

3. Until native retention exists, what retention approach do you recommend for Postgres?
   It depends on your comfort with Postgres -- either running pg_partman/pg_cron yourself, or just a simple CRON batch DELETE ... WHERE time < now() - interval of some sort.

As for the index name, great catch, I have opened a PR #1031 for it.

Now, regarding the partitioning management, it's not something we have prioritized for the time being. What I can do now is invest some time documenting the team's position on this topic and fill in the stub event-delivery-log page with recommendation on how you can manage it yourself.

One potential option is if you're open to contributing, I'd be happy to discuss further and see if we can address the issue together. I definitely do understand that it could be a bit more work than you're looking for, so all good either way.

Hope that helps give you a good idea of where we are on this topic. Let me know what you think and if there's anything I can support further. In the meantime, I'll work on the documentation and hopefully have a draft ready to go shortly.