adk-postgres Session Table, in depth: the adk_sessions row, its identity, lifecycle and update behaviour in PostgreSQL

What the session row is responsible for

Start from the interface, not from the table. The session service calls map onto the row like this:

• createSession inserts it, with an id either supplied by the caller or generated.
• getSession reads it, merges user and app state from their own tables, and loads events.
• appendEvent locks it with SELECT ... FOR UPDATE, takes the next sequence number from last_seq, merges the state delta and updates updated_at.
• listSessions reads all rows for one app and user, without events.
• deleteSession deletes it, and the foreign key cascades to the events.

So the row has four jobs: identity, session-scoped state, a per-session mutex, and listing metadata. The design decisions below each serve one of them. For what state scopes mean to the agent itself, read session context in ADK Java first.

The table, column by column

-- Base table, exactly as in the event log article:
CREATE TABLE adk_sessions (
  app_name   text NOT NULL,
  user_id    text NOT NULL,
  session_id text NOT NULL,
  state      jsonb NOT NULL DEFAULT '{}',
  last_seq   bigint NOT NULL DEFAULT 0,
  updated_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (app_name, user_id, session_id)
);

-- Additions this article argues for:
ALTER TABLE adk_sessions
  ADD COLUMN created_at timestamptz NOT NULL DEFAULT now(),
  ADD COLUMN expires_at timestamptz,                       -- NULL = never expires
  ADD CONSTRAINT session_id_sane CHECK (length(session_id) BETWEEN 1 AND 128),
  ADD CONSTRAINT state_is_object CHECK (jsonb_typeof(state) = 'object');

ALTER TABLE adk_sessions SET (fillfactor = 80);             -- room for HOT updates
CREATE INDEX adk_sessions_expiry ON adk_sessions (expires_at) WHERE expires_at IS NOT NULL;

The base definition is the one the event log uses, repeated so this page stands alone. The additions are deliberate and modest:

• Composite primary key. ADK addresses a session by the triple of app name, user id and session id, and every call carries all three. Keying on the triple means a lookup can never return another user's session by accident, and the key's prefix (app, user) is exactly the listSessions filter, so no second index is needed for it.
• session_id as text, not uuid. The interface accepts caller-supplied ids, and clients sometimes use their own formats. A uuid column would reject them. The check constraint bounds length so that an attacker cannot store megabyte keys.
• state as a jsonb object. It holds only keys without a prefix; user: and app: keys live in their own tables and temp: keys are never persisted. The constraint rejects arrays or scalars written by a buggy migration.
• last_seq is both the event counter and a version number: a client that remembers the value it loaded can detect that someone else appended in between.
• created_at and expires_at support retention without reading events. updated_at alone cannot express "keep for 30 days after creation" or per-session retention promised to a customer.

The stub this page replaces proposed message_count and last_message columns. Resist those: they duplicate what the event log already knows, they must be updated on every append, and the second one copies user text into another place you must secure and delete.

Identity: createSession semantics

The in-memory service generates UUID.randomUUID().toString() when the id is null or blank, and silently overwrites an existing session that has the same id. A database service should not copy the overwrite. Replacing a live conversation because a client retried a create call destroys data, and with the event log it would also orphan or mix events. Insert with ON CONFLICT DO NOTHING RETURNING and treat "no row returned" as a conflict:

// Excerpt from PostgresSessionService. Check which createSession overload your ADK
// version leaves abstract; the others have default implementations on the interface.
@Override
public Single<Session> createSession(String appName, String userId,
        @Nullable ConcurrentMap<String, Object> state, @Nullable String sessionId) {
    String id = (sessionId == null || sessionId.isBlank())
        ? UUID.randomUUID().toString() : sessionId.trim();
    return Single.fromCallable(() -> {
        Map<String, Object> sessionState = sessionScoped(state);  // drops temp:, routes user:/app:
        try (Connection c = ds.getConnection();
             PreparedStatement ps = c.prepareStatement(
                 "INSERT INTO adk_sessions (app_name, user_id, session_id, state, expires_at) "
               + "VALUES (?, ?, ?, ?::jsonb, now() + ?::interval) "
               + "ON CONFLICT DO NOTHING RETURNING updated_at")) {
            ps.setString(1, appName);
            ps.setString(2, userId);
            ps.setString(3, id);
            ps.setString(4, json.writeValueAsString(sessionState));
            ps.setString(5, ttl);                                   // e.g. "30 days"
            try (ResultSet rs = ps.executeQuery()) {
                if (!rs.next()) {
                    throw new SessionException("session already exists: " + id);
                }
                return Session.builder(id).appName(appName).userId(userId)
                    .state(new ConcurrentHashMap<>(sessionState))
                    .events(new ArrayList<>())
                    .lastUpdateTime(rs.getTimestamp(1).toInstant())
                    .build();
            }
        }
    }).subscribeOn(Schedulers.io());
}

The blocking JDBC work runs on Schedulers.io(), as the interface returns RxJava types. Initial state goes through the same prefix routing as later deltas, so a user: key passed at creation lands in the user table rather than in the session row. If your clients legitimately retry creates with a fixed id, make the retry idempotent instead: on conflict, read the existing row and return it when app and user match. Either choice is defensible; overwriting is not.

Listing, deleting and expiring

-- listSessions(appName, userId): metadata only, never events.
SELECT session_id, state, updated_at
FROM adk_sessions
WHERE app_name = $1 AND user_id = $2
  AND (expires_at IS NULL OR expires_at > now())
ORDER BY updated_at DESC
LIMIT 200;                           -- a hard cap; page in the API layer if you need more

-- deleteSession: events go with the row through ON DELETE CASCADE.
DELETE FROM adk_sessions WHERE app_name = $1 AND user_id = $2 AND session_id = $3;

-- Expiry job: small batches, so locks and WAL bursts stay short.
DELETE FROM adk_sessions
WHERE ctid = ANY (ARRAY(
  SELECT ctid FROM adk_sessions
  WHERE expires_at < now()
  LIMIT 500
  FOR UPDATE SKIP LOCKED));

listSessions returns Session objects, and the in-memory implementation clears their events before returning them. Do the same in SQL: select no events at all. A "recent conversations" sidebar that loads full histories is the most common way this service gets slow.

The ORDER BY updated_at sorts the rows of a single user, which the primary key has already narrowed to a handful, so it needs no index. This matters more than it looks, as the next section shows. The in-memory service treats deleting a missing session as a no-op; keep that, so that delete is safe to retry.

The expiry job deletes in batches of a few hundred, skipping rows that are locked by an in-flight append. Each deleted session cascades to its events, so a batch of 500 sessions may delete tens of thousands of event rows; tune the batch by watching the job's duration, not the session count. At high volume, partition the events table by time as the event log article describes, so that most expiry becomes dropping partitions.

The hot row: MVCC, HOT updates and fillfactor

PostgreSQL never updates a row in place. Under MVCC, an UPDATE writes a new version of the whole row and marks the old one dead; vacuum reclaims it later. Every appended event updates last_seq and updated_at, and often state, so a 40-event conversation produces 40 row versions.

Whether that is cheap depends on a heap-only tuple (HOT) update. If no indexed column changes and the new version fits on the same 8 kB page, PostgreSQL chains the new version from the old one and does not touch any index. Otherwise every index on the table gets a new entry too, and index bloat grows with the event rate rather than with the number of sessions.

Two design choices keep appends HOT. First, do not index updated_at, even though listing sorts by it; the primary key prefix narrows the rows first. An index on updated_at turns every append into an index write. Second, set fillfactor to about 80, so each page keeps free space for the next versions of its rows. The expiry index is on expires_at, which appends do not change, so it does not break HOT. Monitor the ratio of n_tup_hot_upd to n_tup_upd in pg_stat_user_tables; if it falls well below most updates, find the indexed column that is changing. The vacuum and bloat article explains how to tune autovacuum for a table like this, which is small but updated constantly.

State size and TOAST

When a row grows beyond about 2 kB, PostgreSQL compresses large values and may move them out of line into a TOAST table. That is transparent on read, but a jsonb value is stored as one unit: changing a single key rewrites the entire value. The merge state = (state - $1::text[]) || $2::jsonb from the event log's append transaction is therefore O(size of state), on every event.

A session with 200 bytes of state costs nothing. A session where a tool stored a 300 kB search result in state rewrites 300 kB, plus its WAL, on every subsequent event, even events that do not touch state at all when your merge writes the column unconditionally. Two rules follow: skip the state update entirely when the delta is empty, and keep large values out of session state. Put documents and tool outputs in artifacts or in the event payload, and keep only a reference in state. A check constraint such as pg_column_size(state) < 65536 turns a runaway into an error you can see instead of a slow table.

Row-level security for multi-tenant deployments

If several agent applications share one database, a bug in a query's WHERE clause can expose another app's sessions. Row-level security makes the database enforce the app boundary:

ALTER TABLE adk_sessions ENABLE ROW LEVEL SECURITY;
CREATE POLICY per_app ON adk_sessions
  USING (app_name = current_setting('adk.app_name'))
  WITH CHECK (app_name = current_setting('adk.app_name'));
-- The service sets it per transaction: SET LOCAL adk.app_name = 'support-bot';

The service's connection pool sets the app name with SET LOCAL inside each transaction, so the setting cannot leak across pooled connections. Apply the same policy to the event and user-state tables. Row-level security does not apply to table owners by default, so run the service as a separate, non-owner role.

Worked example: sizing a support agent

A support agent creates 50,000 sessions a day, averages 30 events each, and keeps sessions for 30 days. That is 1.5 million rows at steady state and 1.5 million row updates a day, about 17 a second on average and several times that at peak.

With a typical row of about 400 bytes, the live table is under a gigabyte. Without HOT, each update also inserts one entry into the primary key index and one into any other index, and vacuum must clean both. With fillfactor 80 and no index on changing columns, the same load touches only heap pages, and the table stays near its live size.

Now suppose one tool writes a 50 kB customer record into session state. Thirty events rewrite about 1.5 MB per session, so the table writes up to 75 GB of TOAST data a day before compression, plus WAL, for data that never changed. Moving the record to a reference makes that cost disappear, and no functional test would have noticed it.

Failure modes

What to do next

1. Compare your adk_sessions table with the DDL above and add created_at, expires_at and the two check constraints.
2. Make createSession fail or return idempotently on a duplicate id; never overwrite.
3. List every index on the table and drop any on a column that appendEvent changes; set fillfactor to 80.
4. Query pg_stat_user_tables for the HOT update ratio and record it as a dashboard metric.
5. Measure pg_column_size(state) percentiles, skip empty state updates, and move large values to artifacts.
6. Add the batched expiry job and, if apps share the database, row-level security under a non-owner role.