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

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:

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

Questions

Is Postgres partition lifecycle management already planned or tracked?
Is there a recommended migration path for existing DEFAULT partitions?
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.

@miekassu

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:

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

docs: document event and delivery log storage and retention

@miekassu

Hi @miekassu, here's the docs PR #1032 if you'd like to review it.

I think it would make sense to convert this GH issue to a discussion as well since there's no further action item from it. Happy to chat more and discuss the migration part to start a proper partitioning solution for you. We can chat here or on our Slack Community, whichever works better for you.

docs: document event and delivery log storage and retention (#1032)

@alexluong

Disclosure up front: I maintain pg-partsmith, a PostgreSQL partition-lifecycle tool, and it comes up below. I found this thread while researching how production systems manage partition lifecycles, and ended up running Outpost's schema for real. Two things came out of that which are useful here regardless of what anyone adopts.

1. On this schema, dropping an expired partition takes ACCESS EXCLUSIVE on the parent

docs/content/self-hosting/guides/event-delivery-log.mdoc now suggests managing partitions yourself with something like pg_partman, which is sound advice. Worth knowing before anyone plans a maintenance window: events and attempts ship a permanent DEFAULT partition, and PostgreSQL refuses the concurrent detach outright while one exists.

ALTER TABLE events DETACH PARTITION events__2026_01 CONCURRENTLY;
ERROR:  cannot detach partitions concurrently when a default partition exists

So the detach falls back to the blocking form, which holds ACCESS EXCLUSIVE on events for its duration — ingestion waits. This is a PostgreSQL restriction, not a property of any particular tool: pg_partman, a hand-written ALTER TABLE, and my own tool all hit it identically on this schema. The lock is short (a catalog update, not a data rewrite), but it is not the "cheaper than deleting rows, and concurrent" story the partition route usually promises, and it's worth a sentence in that docs section.

The way out, if you ever want it, is for Outpost to create partitions ahead of time and not keep a DEFAULT permanently — at which point DETACH ... CONCURRENTLY becomes available and retention stops blocking writes entirely. That's a change on your side, not something an external tool can do for you, so I mention it as information rather than a request.

2. The step that isn't in the docs: the rows already in DEFAULT

@alexluong, on the migration question you said this hadn't been considered yet. That's the part I'd flag as the real gap, because it sits before any retention scheme works: today every row an existing install has ever written is in events_default / attempts_default. Creating partitions from now on doesn't move them, and dropping expired partitions doesn't reach them, so an operator who follows the partition advice gets a growing DEFAULT partition alongside their tidy new ones.

Draining it means creating each historical window's partition and moving its rows in, in bounded batches, before it can be attached — PostgreSQL won't attach a partition while DEFAULT still holds rows for its range. Both pg_partman (partition_data_time) and my tool can do this; it just needs to be a deliberate step in whatever guidance you give.

What I actually ran

Your ten internal/migrator/migrations/postgres/*.up.sql applied verbatim to PostgreSQL 17 at main (f67ae011), seeded with 965 events and 965 attempts spanning eight months, all in DEFAULT — an existing install, in other words. Ten lines of config:

version: 1
defaults:
  schema: public
  granularity: month
  create_ahead_count: 3
  retention_count: 6
tables:
  - { table_name: events, partition_column: time }
  - { table_name: attempts, partition_column: time }

and three container runs, no Python installed anywhere, no change to Outpost:

$ docker run ... ghcr.io/bedrock-python/pg-partsmith:1.5.0 backfill -c partitions.yaml
public.events   — moved 965 rows in 9 batches into 9 partitions; DEFAULT is drained
public.attempts — moved 965 rows in 9 batches into 9 partitions; DEFAULT is drained

$ ... apply --allow-destructive
public.events   — created 2, repaired 0, attached 0, detached 3, dropped 3
public.attempts — created 2, repaired 0, attached 0, detached 3, dropped 3

965 → 621 rows, exactly the three expired months. plan prints what it would do, and what each operation locks, before anything runs.

Caveats, since they matter more than the output: the backfill takes the table's lock for its duration and a window's rows are invisible through the parent between leaving DEFAULT and the partition being attached (batch size bounds that); retention detaches take the ACCESS EXCLUSIVE described above; and it needs no server-side extension, which is the main practical difference from pg_partman if the extension isn't available on your PostgreSQL.

Offer, not a request

I'm not proposing Outpost depend on anything — an external maintenance job is the right shape here and the docs already frame it that way. If a paragraph in the retention section would be useful — the DEFAULT-partition lock caveat, and the drain-DEFAULT-first step, written tool-neutrally with pg_partman and this as the two examples — I'm happy to open a PR against event-delivery-log.mdoc. Say the word and I'll write it; if you'd rather keep vendor tooling out of the docs entirely, that's a perfectly good answer and the first section still stands on its own.

@miekassu — if you're still looking at this, the two caveats above are the ones I'd want to know about before choosing between batched deletes and partitions.

@AlexeyShalaev

Hi @AlexeyShalaev, thank you for your detailed writeup. Super helpful! There is certainly some blindspot on our end as we don't operate Outpost on PG ourselves, so support from the community is very appreciated here.

Yes, please feel free to open a PR on the docs and we'll review it accordingly, and let me know if you need anything else on our end! Thanks.

docs: how to adopt partitioning on an existing PostgreSQL install (#1058

Navigation Menu

Repository navigation

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

Summary

Evidence in the source

Request

Questions

Small unrelated finding

1. On this schema, dropping an expired partition takes ACCESS EXCLUSIVE on the parent

2. The step that isn't in the docs: the rows already in DEFAULT

What I actually ran

Offer, not a request

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

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

Description

Summary

Evidence in the source

Request

Questions

Small unrelated finding

Activity

alexluong commented on Aug 10, 2026

alexluong commented on Aug 14, 2026

AlexeyShalaev commented on Sep 4, 2026

1. On this schema, dropping an expired partition takes ACCESS EXCLUSIVE on the parent

2. The step that isn't in the docs: the rows already in DEFAULT

What I actually ran

Offer, not a request

alexluong commented on Sep 4, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions