adk-postgres Migrations and Schema Versioning, in depth: Flyway baselines, lock-safe PostgreSQL DDL, expand/contract, and versioning the event payload

Three things carry a version, and they ship separately

The first is the DDL: tables, columns, constraints and indexes. The second is the payload format. Each event row stores Event.toJson() as jsonb, so its shape is set by whichever ADK version wrote it, not by your migrations. The third is the service code that reads both. They change at different times. DDL changes when you run a migration, payloads change when you upgrade the ADK dependency, and code changes on every deploy. During a rolling deploy, old and new code run side by side against a single database.

One rule follows from this: every schema state must work with two code versions, the one being replaced and the one replacing it. A migration that only the new code can use turns a rolling deploy into a race, and a rollback into an outage. Everything below is a way to keep that N and N+1 overlap intact.

Tool choice and repository layout

Use a migration tool with an ordered, checksummed history rather than hand-run scripts. Flyway with plain SQL files is a good fit for four tables: the SQL is exactly what runs, and reviewers read PostgreSQL rather than an abstraction. Liquibase works too if your organisation already uses its changelogs. Versioned files are named V<version>__<description>.sql and run once, in order. Repeatable files named R__<description>.sql rerun whenever their content changes, which suits views and functions. Flyway records each applied migration in flyway_schema_history with its version, checksum and success flag, and by default validates checksums before migrating. So editing an already-applied file fails the next run.

src/main/resources/db/migration/
  V1__baseline_event_log.sql         -- the four tables, as first shipped
  V2__session_lifecycle_columns.sql  -- created_at, expires_at
  V3__session_checks_not_valid.sql   -- constraints added NOT VALID (instant)
  V4__validate_and_index.sql         -- VALIDATE, CREATE INDEX CONCURRENTLY (non-transactional)

If the tables already exist in production because someone created them by hand, don't recreate them. Make V1 match exactly what is deployed, and use Flyway's baseline feature on that existing database so history starts at V1 without running it. Before trusting the baseline, compare a schema dump from production with one produced by running V1 on an empty database.

Run migrations as a job, not from the agent pods

Flyway can migrate from application startup, but for a service with many replicas a separate step is safer: an init container, a CI/CD stage or a Kubernetes Job that must finish before the rollout starts. Replicas then start against a finished schema, a failed migration stops the deploy before any new code runs, and DDL runs with a role the application does not hold. Give the migration role ownership of the tables. Give the application role only SELECT, INSERT, UPDATE and DELETE (plus SELECT on flyway_schema_history for the readiness check below), so a bug or injected SQL in the service cannot drop a table.

// Migration entry point, run as its own process before the rollout.
public final class Migrate {
    public static void main(String[] args) {
        Flyway flyway = Flyway.configure()
            .dataSource(System.getenv("MIGRATION_JDBC_URL"),
                        System.getenv("MIGRATION_USER"),
                        System.getenv("MIGRATION_PASSWORD"))
            .locations("classpath:db/migration")
            .load();
        flyway.migrate();     // validates checksums of applied files first
    }
}

The deployment article covers the container and rollout mechanics. What matters here is ordering: expand migrations before the deploy that needs them, and contract migrations only after the old code is fully gone.

PostgreSQL locks decide whether a migration is safe

Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock, which conflicts with everything, including plain SELECT. A metadata-only change holds it for milliseconds. The danger is in waiting for it. If a long transaction is reading adk_sessions, the ALTER queues behind it, and every later query on the table queues behind the ALTER. The agent service stalls even though the change itself would have taken no time. appendEvent takes a row lock on the session and holds it for the whole transaction, so a slow model call inside a transaction makes this worse. Keep transactions short.

Guard every DDL migration with a lock timeout so it fails fast instead of blocking traffic, and retry it at a quieter moment.

SET lock_timeout = '3s';          -- give up rather than stall the queue behind us
SET statement_timeout = '60s';

Some facts that follow from the PostgreSQL documentation. Since version 11, ADD COLUMN with a non-volatile default is metadata-only and does not rewrite the table. A volatile default such as clock_timestamp() or gen_random_uuid() forces a full rewrite. ADD CONSTRAINT ... NOT VALID skips the scan of existing rows but still enforces the constraint on new writes. A later VALIDATE CONSTRAINT does the scan under a weaker lock that allows reads and writes. CREATE INDEX CONCURRENTLY builds without blocking writes but cannot run inside a transaction block. That matters because Flyway normally runs each migration in a transaction. Redgate documents postgresql.transactional.lock=false for such migrations, so Flyway uses a session-level advisory lock rather than a transactional one. Check your Flyway version's docs for how to mark a single script as non-transactional.

Worked example: shipping the session table changes as V2 to V4

The session table article adds created_at, expires_at, a length check on session_id, a check that state is a JSON object, fillfactor = 80, and a partial index on expiry. Written as a single ALTER, that takes a strong lock and scans the whole table while holding it. Split, each step is either instant or runs under a weak lock.

-- V2__session_lifecycle_columns.sql   (metadata-only on PostgreSQL 11+)
SET lock_timeout = '3s';
ALTER TABLE adk_sessions
  ADD COLUMN created_at timestamptz NOT NULL DEFAULT now(),
  ADD COLUMN expires_at timestamptz;              -- NULL = never expires
ALTER TABLE adk_sessions SET (fillfactor = 80);   -- affects pages written from now on

-- V3__session_checks_not_valid.sql   (instant: no scan of existing rows)
SET lock_timeout = '3s';
ALTER TABLE adk_sessions
  ADD CONSTRAINT session_id_sane CHECK (length(session_id) BETWEEN 1 AND 128) NOT VALID,
  ADD CONSTRAINT state_is_object CHECK (jsonb_typeof(state) = 'object') NOT VALID;

-- V4__validate_and_index.sql   (must run outside a transaction; each statement autocommits)
ALTER TABLE adk_sessions VALIDATE CONSTRAINT session_id_sane;
ALTER TABLE adk_sessions VALIDATE CONSTRAINT state_is_object;
CREATE INDEX CONCURRENTLY IF NOT EXISTS adk_sessions_expiry
  ON adk_sessions (expires_at) WHERE expires_at IS NOT NULL;

Never put VALIDATE in the same transaction as the ADD. PostgreSQL holds every lock until commit, so the ADD's ACCESS EXCLUSIVE lock would last for the whole scan. Three more details are easy to miss. First, now() is not volatile: it returns the transaction start time. So every existing row gets the same created_at, the moment V2 ran, not its real creation time. If you need real ages, backfill from min(created_at) in adk_events for each session as a separate batched step. Second, the fillfactor change does not repack existing pages. Only pages written afterwards leave room for HOT updates, unless you rewrite the table on purpose. Third, if a concurrent index build fails, it leaves an INVALID index behind. Drop it and rerun. IF NOT EXISTS would otherwise skip the rerun and leave a useless index.

Before V3, run the constraint predicates as a SELECT. If old rows violate them, VALIDATE fails. Fix the data in its own migration first, rather than learning about bad rows halfway through a deploy.

Breaking changes: expand, migrate, contract

Renames, type changes and splits cannot be applied in one step without breaking the old code. Say you want to replace the unit-ambiguous adk_events.event_ts with event_time timestamptz. Spread the change over several releases.

Only the last step is irreversible, and it happens after you have run on the new column for long enough to trust it. Backfills on a busy log table should work in small keyset batches, each in its own transaction, so no batch holds many row locks and the job can resume after a crash:

-- repeat until 0 rows; bind the largest key of the previous batch (use RETURNING)
UPDATE adk_events e SET event_time = to_timestamp(e.event_ts / 1000.0)  -- only if you verified millis
FROM (SELECT app_name, user_id, session_id, seq FROM adk_events
      WHERE event_time IS NULL AND (app_name, user_id, session_id, seq) > (?, ?, ?, ?)  -- last key
      ORDER BY app_name, user_id, session_id, seq LIMIT 5000) b
WHERE (e.app_name, e.user_id, e.session_id, e.seq) = (b.app_name, b.user_id, b.session_id, b.seq)
RETURNING e.app_name, e.user_id, e.session_id, e.seq;

The event log article warns not to assume a unit for Event.timestamp(), and that applies here. Confirm the unit your ADK version writes, from code and from sampled data, before choosing the conversion. If the log is partitioned, run the backfill one partition at a time.

The second schema: versioning Event payloads

Your migrations never touch the JSON inside payload, but an ADK upgrade can change what Event.toJson() emits and what the deserializer accepts. The failure is quiet: new sessions work, and resuming an old session fails because a months-old event no longer parses. The message history article relies on the same rows being readable indefinitely, which makes this a correctness issue, not a cosmetic one.

Record the writer version on each row, and put a tolerant upcasting step between the database and the deserializer:

-- V5__payload_version.sql  (metadata-only: constant default)
ALTER TABLE adk_events ADD COLUMN payload_version smallint NOT NULL DEFAULT 1;

// Java: upcast old shapes before handing JSON to ADK
static String upcast(int version, String json) throws IOException {
    ObjectNode node = (ObjectNode) MAPPER.readTree(json);
    switch (version) {
        case 1: /* e.g. rename or reshape a field changed in a later ADK release */
        case 2: break;                     // current shape
        default: throw new IllegalStateException("payload_version from the future: " + version);
    }
    return MAPPER.writeValueAsString(node);
}

The cases stay empty until an upgrade actually changes something. The point is that the hook exists, the version column tells you which rows need it, and an unknown future version fails loudly instead of being half-parsed by old code after a rollback. Make ADK upgrades a gated change: a CI test loads a fixed corpus of payloads captured from production through the new ADK version's parser, and a failure blocks the upgrade.

Startup compatibility check

Each build knows the minimum schema version it needs. Check it at startup and fail readiness if the database is behind, so a pod never serves traffic against a schema it cannot use:

SELECT version FROM flyway_schema_history
WHERE success AND version IS NOT NULL
ORDER BY installed_rank DESC LIMIT 1;

Compare versions numerically in Java, not as strings, because '10' < '9' as text. Don't fail when the database is ahead: under expand/contract, a newer additive schema is exactly what an old pod should tolerate during a rollback. Readiness gating pairs with graceful shutdown, so pods draining during a deploy finish their open appends before the next migration begins.

Testing migrations

• Run all migrations from empty against a real PostgreSQL of your production major version, for example with Testcontainers. H2 does not reproduce PostgreSQL's locking or jsonb behaviour.
• Run them against a restored, anonymised production snapshot and time each one. A metadata-only change that takes minutes means your assumption about the default or the lock is wrong.
• Run the previous release's integration tests against the new schema. That is the direct test of the N and N+1 rule.
• Diff pg_dump --schema-only of a migrated database against the expected schema to catch hand-applied drift in production.
• Replay the payload corpus through the upcaster and the current ADK parser on every dependency bump.

Failure modes

What to do next

1. Dump your current adk-postgres schema and turn it into V1, baselining existing databases rather than re-running it.
2. Move migrations into a separate job with a DDL role and give the service a DML-only role.
3. Add lock_timeout to every DDL file and split any change that scans a table into NOT VALID plus VALIDATE.
4. Ship the session table changes as V2 to V4 above and time them on a production snapshot.
5. Add payload_version and a no-op upcaster now, before the first ADK upgrade forces it.
6. Add a readiness check for the minimum schema version, and a CI job that runs N-1 tests against the new schema.