Adding tenant_id to a Shared Table Without Downtime

The events table predates multi-tenancy. Every row belongs to a tenant, but the only way to know which is to join through projects.tenant_id. Now the product needs row-level security, per-tenant data export, and eventually partitioning by tenant — all of which need tenant_id on the table itself. It has 1.8 billion rows and takes thousands of writes a second from every tenant. Adding the column is instant; everything that follows — populating it, keeping new rows populated, making it NOT NULL, indexing it, and finally enforcing tenant isolation — must happen without blocking writes or changing behaviour for tenants mid-migration. This guide sequences the whole change. It is the shared-table counterpart to fleet rollouts in Migrating Multi-Tenant Databases.

Each step is online; enforcement (NOT NULL, RLS) comes only after the data is complete.

Symptom / Error Signatures

The need usually surfaces as:

Tenant-scoped queries that join through parent tables just to filter by tenant, with plans that scan far more rows than one tenant owns.
A requirement for row-level security or per-tenant partitioning that cannot be met without the column on the table.
Data-export or deletion requests (“delete all data for tenant X”) that are slow and error-prone.

Doing it naively fails with the familiar large-table symptoms: ALTER TABLE events ADD COLUMN tenant_id bigint NOT NULL DEFAULT ... cannot express a per-row value; an UPDATE events SET tenant_id = ... FROM projects runs for hours as one transaction; SET NOT NULL scans under an exclusive lock; a non-concurrent index blocks writes.

Root Cause Analysis

The change combines four large-table operations, each with an established online technique:

Operation	Naive form	Online form
add the column	fine (metadata-only when nullable)	same
populate existing rows	one huge `UPDATE ... FROM`	keyset batches with throttling
populate new rows	application change in every writer	`BEFORE INSERT` trigger deriving from the parent
require it	`SET NOT NULL` (full scan, exclusive lock)	`CHECK (tenant_id IS NOT NULL) NOT VALID`, `VALIDATE`, `SET NOT NULL`
index it	`CREATE INDEX` (blocks writes)	`CREATE INDEX CONCURRENTLY`

The ordering matters because each step depends on the previous being complete: the trigger must exist before the backfill finishes, or rows inserted during the backfill are missed; NOT NULL must wait until no NULLs remain; row-level security must wait until every row has the right tenant, or it hides rows from their owners.

Choosing the derivation source matters for correctness. The trigger should derive tenant_id from the authoritative parent (projects.tenant_id via NEW.project_id), not trust a value the application passes — at least until every writer has been updated to pass it correctly, at which point the trigger can verify instead of derive.

Enforcement steps start only after the backfill has provably finished.

Immediate Mitigation

1. Add the column and the derivation trigger together.

-- PostgreSQL · migration role · brief locks only
SET lock_timeout = '3s';
ALTER TABLE events ADD COLUMN IF NOT EXISTS tenant_id bigint;
CREATE OR REPLACE FUNCTION events_set_tenant() RETURNS trigger AS $$
BEGIN
  IF NEW.tenant_id IS NULL THEN
    SELECT p.tenant_id INTO NEW.tenant_id FROM projects p WHERE p.id = NEW.project_id;
  END IF;
  RETURN NEW;
END $$ LANGUAGE plpgsql;
CREATE TRIGGER events_set_tenant BEFORE INSERT OR UPDATE OF project_id ON events
  FOR EACH ROW EXECUTE FUNCTION events_set_tenant();
-- ROLLBACK PATH: DROP TRIGGER events_set_tenant ON events; DROP FUNCTION events_set_tenant(); ALTER TABLE events DROP COLUMN tenant_id;

The lookup uses projects’ primary key, so it costs one index probe per insert.

2. Backfill in keyset batches, throttled on replica lag, and resumable from a checkpoint, per resuming an interrupted backfill from a checkpoint.

-- PostgreSQL · one batch · repeated by a worker advancing :lo; each call its own transaction
UPDATE events e SET tenant_id = p.tenant_id
FROM projects p
WHERE e.project_id = p.id
  AND e.id >= :lo AND e.id < :lo + 10000
  AND e.tenant_id IS NULL;

3. Verify completeness before enforcing anything.

-- PostgreSQL · read-only · must be 0 before the NOT NULL step (run per id range on very large tables)
SELECT count(*) FROM events WHERE tenant_id IS NULL;

Permanent Fix / Long-Term Pattern

4. Enforce NOT NULL online. Add the check NOT VALID, validate it, then SET NOT NULL, which PostgreSQL 12+ proves from the validated check — the sequence in adding NOT NULL via a CHECK constraint in Postgres.

5. Index for tenant-scoped access. Tenant-scoped queries want tenant_id as the leading column of their indexes; build them concurrently, one at a time, and drop superseded indexes afterwards.

-- PostgreSQL · outside a transaction · writes continue during the build
SET lock_timeout = '3s';
CREATE INDEX CONCURRENTLY IF NOT EXISTS events_tenant_created_idx ON events (tenant_id, created_at);

6. Enable row-level security only when every row is populated and the application sets the tenant context for its sessions. Test with a role that is subject to RLS before enabling it for the application role, because a missing tenant context makes queries return nothing rather than failing.

-- PostgreSQL · migration role · policy relies on a session setting the app sets per request
ALTER TABLE events ENABLE ROW LEVEL SECURITY;
CREATE POLICY events_tenant_isolation ON events
  USING (tenant_id = current_setting('app.tenant_id')::bigint);
-- ROLLBACK PATH: ALTER TABLE events DISABLE ROW LEVEL SECURITY; DROP POLICY events_tenant_isolation ON events;

Once tenant_id is populated and indexed, partitioning by tenant (or by time within tenant) becomes possible, following Partitioning Live Tables Without Downtime. Keep the trigger until every writer supplies tenant_id itself, then convert it to a check that the supplied value matches the parent, or remove it.

Enforcement is where a shared-table migration becomes visible to tenants — each step waits for proof.

Verification Checklist

The derivation trigger existed before the backfill started, and new rows always receive tenant_id.
The backfill was batched, throttled and resumable, and count(*) WHERE tenant_id IS NULL reached zero.
NOT NULL was added via a validated check without a blocking scan.
Tenant-leading indexes were built concurrently, and critical queries use them.
Row-level security was enabled only after staging tests with an RLS-subject role passed for every application path.

Frequently Asked Questions

Why derive tenant_id in a trigger instead of the application? Because every writer — services, jobs, imports, admin tools — would otherwise need updating before the backfill could finish. The trigger covers them all immediately and derives the value from the authoritative parent row.

Can the backfill run while tenants are writing? Yes. Each batch is a short transaction on a range of rows, and new rows are handled by the trigger. Throttle on replica lag so the extra write volume does not affect reads served by replicas.

What happens if row-level security is enabled too early? Rows without a tenant_id, or sessions without a tenant context, are filtered out silently, so tenants see missing data rather than errors. Enable it only after the column is complete and every code path sets the context.

Does adding tenant_id help partitioning? It is a prerequisite for partitioning by tenant, and a tenant-leading key often improves locality even without partitioning. Partitioning itself is a separate, larger migration.