adk-postgres Partitioning for High-Volume Workloads, in depth: choosing the key for the ADK event log, keeping per-session order, retention by DROP and converting a live table

When partitioning pays

Take a support agent handling 50,000 sessions a day, averaging 40 events per session and about 4 KB of JSON per event after compression. That is two million rows and roughly 8 GB of new data a day, about 240 GB held under a 30-day retention policy. These are illustrative figures; substitute your own.

Without partitioning, retention is a DELETE of two million rows a day. Each deleted row stays on disk as a dead tuple until vacuum reclaims it, every delete writes WAL, the primary key index keeps its bloat, and the delete competes with the live append traffic for I/O. With daily partitions, retention is dropping a table: a catalog change that frees 8 GB at once, writes almost no WAL and leaves no dead tuples.

That retention win is the main reason to partition an event log. Query speed is a weaker argument, because the dominant read, loading one session in order, is already an index range scan on the primary key and does not get much faster with partitions. If you keep everything forever and never delete, partitioning adds operational work for little gain. The general trade-offs are covered in database partitioning; the rest of this page is specific to the ADK event log.

Choosing the key: event time, session day or hash

PostgreSQL requires every primary key and unique constraint on a partitioned table to include the partition key columns, because each constraint is enforced only inside each partition. That rule decides the key. The event log depends on two uniqueness guarantees: seq is unique within a session, so history cannot fork, and event_id is unique within a session, so a retried append fails instead of duplicating an event.

The recommendation is session_day: the date the session was created, stored on the session row and copied onto every event. Because it is constant for a session, adding it to the primary key costs nothing logically; the key is still unique per session, and the database still enforces both guarantees. The session table guide suggests partitioning events by time; this is the refinement of that advice that keeps the constraints intact.

The price is long-lived sessions. A session that started 40 days ago and is still active keeps writing into a 40-day-old partition, which therefore cannot be dropped. Handle this with an explicit maximum session lifetime: after, say, seven days, the application closes the session and starts a new one seeded with a summary. The drop rule then becomes simple: a partition for day D may be dropped once D plus the maximum lifetime plus the retention period has passed.

The schema

The table keeps the columns from the event log guide and adds the key. The session table gains a session_day column; it is one row per session, small and hot, so it stays unpartitioned.

-- Fresh install. For a live table, follow the conversion steps further down instead.
-- Sessions keep their own day; one row per session, small, not partitioned.
ALTER TABLE adk_sessions ADD COLUMN session_day date NOT NULL DEFAULT current_date;

CREATE TABLE adk_events (
    app_name      text        NOT NULL,
    user_id       text        NOT NULL,
    session_id    text        NOT NULL,
    session_day   date        NOT NULL,  -- copied from adk_sessions under the row lock
    seq           bigint      NOT NULL,
    event_id      text        NOT NULL,
    invocation_id text,
    author        text        NOT NULL,
    event_ts      bigint      NOT NULL,
    created_at    timestamptz NOT NULL DEFAULT now(),
    payload       jsonb       NOT NULL,
    PRIMARY KEY (app_name, user_id, session_id, session_day, seq),
    UNIQUE      (app_name, user_id, session_id, session_day, event_id),
    FOREIGN KEY (app_name, user_id, session_id) REFERENCES adk_sessions ON DELETE CASCADE
) PARTITION BY RANGE (session_day);

CREATE TABLE adk_events_p20260930 PARTITION OF adk_events
    FOR VALUES FROM ('2026-09-30') TO ('2026-10-01');

A foreign key from a partitioned table to an ordinary one has been supported since PostgreSQL 11, so the cascade from sessions still works. Note that there is no default partition. An insert whose day has no partition fails with an error instead of landing in a catch-all table. That failure is loud and immediate, which is what you want; a default partition quietly accumulates rows, and creating a partition that overlaps them later forces a scan of the default partition and fails if matching rows are there.

The append and read paths

The application change is small because the event log already locks the session row to assign seq. Read session_day in the same statement and bind it into the insert. There is no extra round trip.

// Inside persist(): the lock that assigns seq also returns the partition key.
private static final String LOCK_SQL =
    "SELECT last_seq, session_day FROM adk_sessions " +
    "WHERE app_name = ? AND user_id = ? AND session_id = ? FOR UPDATE";

private static final String READ_SQL =
    "SELECT payload FROM adk_events " +
    "WHERE app_name = ? AND user_id = ? AND session_id = ? AND session_day = ? " +
    "ORDER BY seq";                     // session_day lets the planner prune

// insertEvent(c, s, e, seq, day) binds day into the session_day column.

Reads must carry the key too. getSession already reads the session row for its state, so it has session_day in hand before it loads events. With the key in the WHERE clause, the planner prunes to one partition. Without it, the query still returns correct results, but it probes the primary key index of every partition, and with 45 partitions that is 45 index lookups per session load. Check with EXPLAIN that only one partition appears in the plan. The same rule applies to the message history projection if you partition it as well.

The maintenance job

Partitions have to exist before the first write of their day and have to be removed after their retention. A small scheduled job does both. Run it several times a day, not once, so a single failed run never leaves tomorrow without a partition.

public final class PartitionMaintenance {
    private final DataSource ds;
    private final int premakeDays = 7;
    private final Duration maxSessionLife = Duration.ofDays(7);
    private final Duration retention = Duration.ofDays(30);

    public void run(LocalDate today) throws SQLException {
        try (Connection c = ds.getConnection()) {
            c.setAutoCommit(true);   // DETACH ... CONCURRENTLY refuses a transaction block
            for (int i = 0; i <= premakeDays; i++) {
                LocalDate d = today.plusDays(i);
                exec(c, "CREATE TABLE IF NOT EXISTS " + name(d) + " PARTITION OF adk_events "
                      + "FOR VALUES FROM ('" + d + "') TO ('" + d.plusDays(1) + "')");
            }
            // A day is droppable only once its longest-lived session has aged out.
            LocalDate cutoff = today.minusDays(maxSessionLife.toDays() + retention.toDays());
            for (String part : partitionsBefore(c, cutoff)) {   // reads pg_inherits
                exec(c, "ALTER TABLE adk_events DETACH PARTITION " + part + " CONCURRENTLY");
                exec(c, "DROP TABLE " + part);
            }
            // Session rows for those days: batched, and their events are already gone.
            int deleted;
            do {
                deleted = update(c, "DELETE FROM adk_sessions WHERE ctid IN (SELECT ctid FROM adk_sessions "
                        + "WHERE session_day < '" + cutoff + "' LIMIT 5000)");
            } while (deleted > 0);
        }
    }

    private static String name(LocalDate d) {
        return "adk_events_p" + d.format(DateTimeFormatter.BASIC_ISO_DATE);
    }
}

DETACH PARTITION ... CONCURRENTLY, added in PostgreSQL 14, detaches without blocking queries on the parent for the whole operation; it cannot run inside a transaction block, and it cannot be used while the table has a default partition, which is one more reason not to have one. Detach first, then drop, then delete the old session rows in small batches. Deleting session rows first would fire the cascade and delete every event row one at a time, which is the exact cost partitioning was meant to avoid. The date strings in the job come from LocalDate, never from user input, but still keep the job's database role limited to these tables. Tools such as the pg_partman extension automate premaking and retention; the job here shows what any of them must do.

Converting a live table without a rewrite

An existing table cannot be altered into a partitioned one. The fastest safe conversion attaches the old table, unchanged in its data, as a single legacy partition covering every day before the cutover.

1. Add session_day to the old events table and to adk_sessions with a constant default equal to the day before cutover. Since PostgreSQL 11, adding a column with a constant default does not rewrite the table.
2. Deploy the application change that reads session_day under the row lock and binds it into every insert, so no writer omits the new NOT NULL key after cutover.
3. On the old table, build unique indexes matching both the new primary key and the new event_id constraint with CREATE UNIQUE INDEX CONCURRENTLY; a missing one would be built under lock during the attach. Then add a CHECK constraint on session_day matching the legacy range, first as NOT VALID and then with VALIDATE CONSTRAINT, which takes only a light lock.
4. In one short transaction: rename the old table, create the partitioned parent under the original name, attach the old table as the legacy partition and create today's partition. The valid CHECK constraint lets the attach skip its validation scan.
5. Change the column default on adk_sessions to current_date so new sessions land in daily partitions. Sessions that existed before cutover keep the legacy day and keep writing into the legacy partition until they age out.
6. Drop the legacy partition once the maximum session lifetime plus retention has passed since cutover.

Rehearse the whole sequence on a restored copy of production, as the migrations guide recommends for any locking change, and measure how long step 4 holds its lock.

Indexes and statistics

An index created on the partitioned parent is created on every partition, and on every partition made later. CREATE INDEX CONCURRENTLY is not supported on the parent. The workaround is to create the index on the parent only, with CREATE INDEX ... ON ONLY adk_events, which leaves it invalid, then build the matching index concurrently on each partition and attach each one with ALTER INDEX ... ATTACH PARTITION; once every partition's index is attached, the parent index becomes valid.

Autovacuum processes each partition but does not analyze the partitioned parent. Queries that depend on statistics for the whole table can therefore plan on stale estimates. Run ANALYZE adk_events on a schedule, for example from the maintenance job after new partitions are created. Keep the partition count in the hundreds rather than the thousands; each additional partition adds planning and locking overhead for queries that cannot prune.

Failure modes

• No partition for today. The job failed for days and appends start failing. Premake a week ahead and alert when fewer than three future partitions exist.
• Partitioning by event time. A session crossing midnight splits in two, and the primary key can no longer prevent a duplicate sequence number.
• Reads without the key. Correct results, but every partition is probed. It shows up as session loads that slow down as retention grows.
• Cascade instead of drop. Deleting old sessions before dropping their partitions deletes events row by row.
• Unbounded sessions. One session kept alive for months pins its partition forever. Enforce a maximum lifetime.
• A default partition. It hides missing-partition errors, blocks concurrent detach and turns later partition creation into a scan.

What to do next

1. Measure daily event rows and bytes, and decide whether retention by DROP is worth the added operations.
2. Pick the maximum session lifetime with the product team and implement the close-and-summarise rollover.
3. Add session_day to the lock query, the insert and every event read, and check with EXPLAIN that one partition is scanned.
4. Deploy the maintenance job with premaking, alerts on future partition count and a scheduled ANALYZE.
5. Rehearse the legacy-partition conversion on a production copy and time its locks.
6. Watch append latency and lock waits in your agent observability dashboards through the cutover.