Migrations against the interactions table
The interactions table is special: it is the largest table in the platform and
is on the LLM proxy's hot write path — every proxied LLM call inserts a row. A
migration that takes a strong lock on it, even briefly, blocks those inserts, so
the proxy cannot record interactions until the migration finishes. On a large
table a "quick" CREATE INDEX can hold that lock for minutes.
Treat any schema change to interactions as production-risk work. The same rules
apply to any other very large, write-hot table.
Safe vs risky operations
Fast once the required lock is acquired (metadata-only, no table rewrite in PostgreSQL 11+):
ADD COLUMN ... DEFAULT <constant> NOT NULL — the default is stored as
metadata; existing rows are not rewritten. This avoids a table-size-dependent
rewrite. (The billing_mode column was added this way.)
ADD COLUMN nullable, with no default.
- Nonvolatile defaults such as
now() also avoid the rewrite; now() is stable, not volatile.
DROP DEFAULT, SET DEFAULT <constant>, renaming a column.
These operations still acquire table locks: ordinary ADD COLUMN requires
ACCESS EXCLUSIVE, even without a rewrite. Check long-running transactions and
use a bounded lock_timeout rather than assuming metadata-only means lock-free.
See the PostgreSQL ALTER TABLE reference.
Operations with potentially table-size-dependent work:
ADD COLUMN ... DEFAULT <volatile expr> (e.g. clock_timestamp(), gen_random_uuid()) —
rewrites every row.
ALTER COLUMN ... TYPE ... — usually rewrites the table.
SET NOT NULL on an existing column — full scan to validate.
CREATE INDEX / DROP INDEX (non-concurrent) — this is the most common
trap. A plain CREATE INDEX takes a SHARE lock that blocks writes for the
entire build; adding a column to an existing covering index means a
DROP INDEX + CREATE INDEX rebuild.
The index rule
Never add, drop, or rebuild an index on interactions inside a Drizzle
migration. Drizzle runs each migration in a single transaction, and
CREATE INDEX CONCURRENTLY / DROP INDEX CONCURRENTLY cannot run inside a
transaction — so the only thing a generated migration can emit is the blocking,
non-concurrent form.
Instead:
- Keep the Drizzle schema's index definition matching what is actually deployed,
so
pnpm db:generate does not emit an index change. If you need a new index
for a query, decide whether the query can tolerate a heap fetch instead — for
an analytics query (not the hot path) it usually can.
- If the index is genuinely needed, apply it out of band as an ops step with
CREATE INDEX CONCURRENTLY (and DROP INDEX CONCURRENTLY for the old one)
during a maintenance window, then update the schema to match. CONCURRENTLY
builds without blocking writes, at the cost of a slower build and a second
table scan.
The migration linter (pnpm --dir backend check:migrations) flags DROP INDEX
as an error and non-concurrent CREATE INDEX as a warning for exactly this
reason. If it fires on an interactions migration, stop and rework the change —
do not just add the allow-breaking marker.
Audit the table on staging before you ship
Before merging a migration that touches interactions, size the real table on
the GKE staging database so you know the blast radius. This is read-only —
never run the migration DDL by hand against staging or production; migrations
deploy through the normal pipeline.
Access is via GCP/GKE IAM (managed separately from this repo), so the commands
below grant nothing on their own.
Switch kubectl to the GKE staging context:
kubectl config get-contexts -o name | grep archestra-staging
# e.g. gke_<project>_us-central1-a_archestra-staging
kubectl config use-context <that-context>
Find the Postgres pod (namespace archestra, container postgresql):
kubectl get pods -n archestra | grep postgresql # archestra-platform-postgresql-0
Open a read-only psql session (use the app credentials already in the pod's
environment; do not export secrets):
kubectl exec -it -n archestra archestra-platform-postgresql-0 -c postgresql \
-- bash -lc 'PGPASSWORD="$POSTGRES_PASSWORD" psql -U "$POSTGRES_USER" -d "$POSTGRES_USER"'
Run the audit queries (all read-only):
-- PostgreSQL version. Metadata-only ADD COLUMN ... DEFAULT needs 11+.
SELECT version();
-- Fast row estimate. NEVER run count(*) on this table — it scans everything.
SELECT reltuples::bigint AS est_rows, relpages
FROM pg_class WHERE relname = 'interactions';
-- Heap / TOAST / index sizes.
SELECT pg_size_pretty(pg_total_relation_size('interactions')) AS total,
pg_size_pretty(pg_relation_size('interactions')) AS heap,
pg_size_pretty(pg_indexes_size('interactions')) AS indexes;
-- Per-index size — a non-concurrent rebuild is at least this expensive.
SELECT indexrelname,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'interactions'
ORDER BY pg_relation_size(indexrelid) DESC;
-- Long-running transactions. A CREATE INDEX waits behind these AND, once it
-- starts, blocks writes until it finishes — so know what's open first.
SELECT pid, now() - xact_start AS xact_age, state, left(query, 80) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL AND pid <> pg_backend_pid()
ORDER BY xact_start
LIMIT 10;
Read the numbers:
- Metadata-only changes avoid a table rewrite, but can still wait for a
conflicting transaction. Assess lock waits as well as table size.
- A table rewrite or a non-concurrent index build scales with heap/index size.
As a rough order of magnitude, an index build reads the whole table, sorts,
and writes the index — expect it to be at least as slow as a full scan of
the heap, and it holds the write lock the entire time. If that is more than
a couple of seconds of estimated build time, do not do it in a transactional
migration (see "The index rule").
When you switch away, restore your previous kubectl context
(kubectl config use-context <previous>).
See also
archestra-dev-migrations — the general migration flow (pnpm db:generate,
drizzle-kit check, check:migrations, data migrations, conflict resolution).
1---2name: archestra-dev-interactions-migrations3description: Use BEFORE writing or running any Drizzle migration that touches the `interactions` table (or any other very large, write-hot table). The interactions table is the platform's biggest, append-heavy table — every LLM proxy call writes a row — so a careless migration can take a write-blocking lock and stall the proxy. Covers which operations are safe vs table-rewriting/lock-taking, the "never rebuild an index in a transactional migration" rule, and a read-only audit procedure against the GKE staging database to size the risk first.4metadata:5 source: https://skillmd.com/skills/archestra-ai/archestra-dev-interactions-migrations6---78# Migrations against the `interactions` table910The `interactions` table is special: it is the largest table in the platform and11is on the LLM proxy's hot write path — every proxied LLM call inserts a row. A12migration that takes a strong lock on it, even briefly, blocks those inserts, so13the proxy cannot record interactions until the migration finishes. On a large14table a "quick" `CREATE INDEX` can hold that lock for minutes.1516Treat any schema change to `interactions` as production-risk work. The same rules17apply to any other very large, write-hot table.1819## Safe vs risky operations2021Fast once the required lock is acquired (metadata-only, no table rewrite in PostgreSQL 11+):2223- `ADD COLUMN ... DEFAULT <constant> NOT NULL` — the default is stored as24 metadata; existing rows are not rewritten. This avoids a table-size-dependent25 rewrite. (The billing_mode column was added this way.)26- `ADD COLUMN` nullable, with no default.27- Nonvolatile defaults such as `now()` also avoid the rewrite; `now()` is stable, not volatile.28- `DROP DEFAULT`, `SET DEFAULT <constant>`, renaming a column.2930These operations still acquire table locks: ordinary `ADD COLUMN` requires31`ACCESS EXCLUSIVE`, even without a rewrite. Check long-running transactions and32use a bounded `lock_timeout` rather than assuming metadata-only means lock-free.33See the [PostgreSQL ALTER TABLE reference](https://www.postgresql.org/docs/17/sql-altertable.html).3435Operations with potentially table-size-dependent work:3637- `ADD COLUMN ... DEFAULT <volatile expr>` (e.g. `clock_timestamp()`, `gen_random_uuid()`) —38 rewrites every row.39- `ALTER COLUMN ... TYPE ...` — usually rewrites the table.40- `SET NOT NULL` on an existing column — full scan to validate.41- **`CREATE INDEX` / `DROP INDEX` (non-concurrent)** — this is the most common42 trap. A plain `CREATE INDEX` takes a `SHARE` lock that blocks writes for the43 entire build; adding a column to an existing covering index means a44 `DROP INDEX` + `CREATE INDEX` rebuild.4546## The index rule4748**Never add, drop, or rebuild an index on `interactions` inside a Drizzle49migration.** Drizzle runs each migration in a single transaction, and50`CREATE INDEX CONCURRENTLY` / `DROP INDEX CONCURRENTLY` cannot run inside a51transaction — so the only thing a generated migration can emit is the blocking,52non-concurrent form.5354Instead:55561. Keep the Drizzle schema's index definition matching what is actually deployed,57 so `pnpm db:generate` does not emit an index change. If you need a new index58 for a query, decide whether the query can tolerate a heap fetch instead — for59 an analytics query (not the hot path) it usually can.602. If the index is genuinely needed, apply it out of band as an ops step with61 `CREATE INDEX CONCURRENTLY` (and `DROP INDEX CONCURRENTLY` for the old one)62 during a maintenance window, then update the schema to match. `CONCURRENTLY`63 builds without blocking writes, at the cost of a slower build and a second64 table scan.6566The migration linter (`pnpm --dir backend check:migrations`) flags `DROP INDEX`67as an error and non-concurrent `CREATE INDEX` as a warning for exactly this68reason. If it fires on an `interactions` migration, stop and rework the change —69do not just add the `allow-breaking` marker.7071## Audit the table on staging before you ship7273Before merging a migration that touches `interactions`, size the real table on74the GKE staging database so you know the blast radius. This is **read-only** —75never run the migration DDL by hand against staging or production; migrations76deploy through the normal pipeline.7778Access is via GCP/GKE IAM (managed separately from this repo), so the commands79below grant nothing on their own.80811. Switch kubectl to the GKE staging context:8283 ```bash84 kubectl config get-contexts -o name | grep archestra-staging85 # e.g. gke_<project>_us-central1-a_archestra-staging86 kubectl config use-context <that-context>87 ```88892. Find the Postgres pod (namespace `archestra`, container `postgresql`):9091 ```bash92 kubectl get pods -n archestra | grep postgresql # archestra-platform-postgresql-093 ```94953. Open a read-only psql session (use the app credentials already in the pod's96 environment; do not export secrets):9798 ```bash99 kubectl exec -it -n archestra archestra-platform-postgresql-0 -c postgresql \100 -- bash -lc 'PGPASSWORD="$POSTGRES_PASSWORD" psql -U "$POSTGRES_USER" -d "$POSTGRES_USER"'101 ```1021034. Run the audit queries (all read-only):104105 ```sql106 -- PostgreSQL version. Metadata-only ADD COLUMN ... DEFAULT needs 11+.107 SELECT version();108109 -- Fast row estimate. NEVER run count(*) on this table — it scans everything.110 SELECT reltuples::bigint AS est_rows, relpages111 FROM pg_class WHERE relname = 'interactions';112113 -- Heap / TOAST / index sizes.114 SELECT pg_size_pretty(pg_total_relation_size('interactions')) AS total,115 pg_size_pretty(pg_relation_size('interactions')) AS heap,116 pg_size_pretty(pg_indexes_size('interactions')) AS indexes;117118 -- Per-index size — a non-concurrent rebuild is at least this expensive.119 SELECT indexrelname,120 pg_size_pretty(pg_relation_size(indexrelid)) AS size121 FROM pg_stat_user_indexes122 WHERE relname = 'interactions'123 ORDER BY pg_relation_size(indexrelid) DESC;124125 -- Long-running transactions. A CREATE INDEX waits behind these AND, once it126 -- starts, blocks writes until it finishes — so know what's open first.127 SELECT pid, now() - xact_start AS xact_age, state, left(query, 80) AS query128 FROM pg_stat_activity129 WHERE xact_start IS NOT NULL AND pid <> pg_backend_pid()130 ORDER BY xact_start131 LIMIT 10;132 ```1331345. Read the numbers:135 - Metadata-only changes avoid a table rewrite, but can still wait for a136 conflicting transaction. Assess lock waits as well as table size.137 - A table rewrite or a non-concurrent index build scales with heap/index size.138 As a rough order of magnitude, an index build reads the whole table, sorts,139 and writes the index — expect it to be at least as slow as a full scan of140 the heap, and it holds the write lock the entire time. If that is more than141 a couple of seconds of estimated build time, do not do it in a transactional142 migration (see "The index rule").143144When you switch away, restore your previous kubectl context145(`kubectl config use-context <previous>`).146147## See also148149- `archestra-dev-migrations` — the general migration flow (`pnpm db:generate`,150 `drizzle-kit check`, `check:migrations`, data migrations, conflict resolution).