The Hidden Cost of Postgres Constraints at Scale

Your ingest workers are queuing. pg_stat_activity shows lock waits. The blocked query is not a slow SELECT. It's your bulk INSERT, waiting on the devices table.

You added a FOREIGN KEY there months ago. You added a UNIQUE constraint on the readings table to catch duplicates. Both were the right call. At 100 devices and 10,000 rows, you never felt them. At 50K inserts per second, they've become the ceiling.

What you will learn

• What Postgres actually executes on every insert to enforce FOREIGN KEY and UNIQUE constraints
• Why this overhead is invisible during a PoC and destructive at production ingest rates
• Four concrete approaches that preserve data integrity without paying the full constraint cost on every row

Why it matters

The Postgres Optimization Treadmill describes how high-frequency time-series workloads hit architectural ceilings despite correct tuning. MVCC overhead, index write amplification, and WAL volume all compound as data grows. Constraint enforcement layers on top of all of that, and it compounds in the same direction.

Every FOREIGN KEY fires an index lookup against the referenced table on every insert. At 50K inserts/sec, that's 50K random reads per second competing directly with your write path. Every UNIQUE constraint fires an index scan before every insert, on an append-only table where duplicates shouldn't ever occur. Both generate additional WAL records and hold row-level locks during execution. Together, they quietly consume the ingest safety margin between "running well" and "falling behind."

Tracing a single constrained insert

A vanilla insert into a plain table performs two operations: a heap tuple write and a WAL commit record.

A constrained insert does five:

1. Heap write. The row is written to the 8KB heap page.
2. B-tree insertion. Every index on the table receives a new entry, traversing from root to leaf and splitting pages as needed.
3. FK shared-lock acquisition. Postgres acquires a FOR KEY SHARE on the referenced row in the parent table (devices) to verify it exists.
4. UNIQUE index scan. Postgres scans the unique index to confirm no matching entry already exists before writing.
5. WAL commit record. The constraint checks generate WAL in addition to the row write itself.

Under concurrent write load at 50K inserts/sec, step 3 is where it breaks down — not through mutual blocking, since FOR KEY SHARE locks are compatible with each other, but through MultiXacts. With only a handful of device rows referenced by thousands of concurrent transactions, each parent row is locked FOR KEY SHARE by many transactions at once, and Postgres must track that shared ownership with a MultiXactID. At this concurrency the churn saturates the MultiXact SLRU caches. pg_stat_activity surfaces this as MultiXact LWLock wait events (only for PostgreSQL 16-18; event names may differ on earlier versions), not as a query performance problem.

Not all constraints carry the same cost. NOT NULL and CHECK constraints evaluate against the row being inserted with no external lookups. They're near-free. FOREIGN KEY and UNIQUE are where the overhead lives, because both require reads against external state on every single insert.

Identifying the problem

Run this query during your next peak ingest window:

SELECT
    pid,
    wait_event_type,
    wait_event,
    query,
    state,
    now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
  AND (
        (wait_event_type = 'LWLock' AND wait_event LIKE 'MultiXact%') -- PostgreSQL 16–18: SLRU wait-event names verified on these versions
     OR (wait_event_type = 'Lock' AND wait_event IN ('transactionid', 'tuple'))
      )
ORDER BY duration DESC;

All MultiXact wait events share the MultiXact prefix on PostgreSQL 16-18, so the LIKE 'MultiXact%' filter captures all SLRU contention events regardless of minor version. Look for rows where wait_event starts with MultiXact. That indicates MultiXact SLRU contention from FK checks on hot parent rows - the mechanism described above. Rows where wait_event is transactionid or tuple indicate true row-lock waits from a concurrent update or delete on the parent, a less common but related failure mode.

Four approaches to reduce constraint overhead

These options are ordered by risk and invasiveness. Start with option 1 if you have existing constraints and need a minimal-change fix. Move to option 2 if your duplication window is bounded to recent data. Use options 3 or 4 only if you own the full write path end to end and can enforce integrity outside the database.

1. Defer FK checks to commit time

Postgres supports deferring constraint checks to commit time rather than row time. Inside a bulk-insert transaction, the FK lookup runs once per batch instead of once per row.

First, declare the constraint as deferrable. This is backward-compatible: the constraint still enforces row-by-row in any transaction that does not explicitly defer it.

ALTER TABLE sensor_readings
  DROP CONSTRAINT IF EXISTS sensor_readings_device_id_fkey;

ALTER TABLE sensor_readings
  ADD CONSTRAINT sensor_readings_device_id_fkey
  FOREIGN KEY (device_id) REFERENCES devices(id)
  DEFERRABLE INITIALLY IMMEDIATE;

Then defer it inside each bulk-insert transaction:

BEGIN;

SET CONSTRAINTS sensor_readings_device_id_fkey DEFERRED;

INSERT INTO sensor_readings (ts, device_id, value)
SELECT ts, device_id, value FROM staging_data;

COMMIT;

For a batch of 1,000 rows, this turns 1,000 FK lookups into one check at commit. The integrity guarantee is unchanged: if any device_id in the batch does not exist in devices, the commit fails and the batch rolls back. Transactions that do not call SET CONSTRAINTS ... DEFERRED continue to enforce row-by-row, so this change does not affect other callers.

2. Scope the UNIQUE check to your live data window

If your primary concern is duplicate prevention during a historical bulk load or replay, a partial index eliminates the UNIQUE check for any row whose timestamp falls outside the live window.

ALTER TABLE sensor_readings
  DROP CONSTRAINT IF EXISTS sensor_readings_unique_reading;

CREATE UNIQUE INDEX idx_sensor_readings_recent_unique
  ON sensor_readings (device_id, ts)
  WHERE ts > now() - INTERVAL '7 days';

Postgres evaluates the partial index predicate at INSERT time. A row inserted with ts = now() satisfies ts > now() - 7 days and gets the UNIQUE check. A row inserted during a historical backfill with ts = '2024-01-15' does not satisfy the predicate in 2026 and skips the check entirely. That is the primary benefit: bulk loads of historical data avoid the UNIQUE scan completely.

For ongoing ingestion of fresh data, each new row is added to the partial index as it's inserted, so the index grows over time. To reclaim the size advantage, schedule a weekly rebuild:

REINDEX INDEX CONCURRENTLY idx_sensor_readings_recent_unique;

At 2 years of retention (730 days), a freshly rebuilt 7-day partial index covers roughly 1% of the dataset: 7 / 730 = 0.0096. The index is approximately 1/100th the size of a full-table UNIQUE index on the same columns, which reduces both scan time and per-insert write amplification by the same factor.

3. Validate FK references in the application layer

For workloads where the device set is stable and well-known, validating device_id in the application before inserting removes the per-row database lookup completely.

import psycopg2

# Cache valid device IDs at startup; refresh on a schedule
def load_valid_devices(conn):
    with conn.cursor() as cur:
        cur.execute("SELECT id FROM devices;")
        return {row[0] for row in cur.fetchall()}

# Initialize the cache
conn = psycopg2.connect("dbname=mydb user=postgres host=localhost")
valid_devices = load_valid_devices(conn)

def insert_readings(conn, batch):
    # Filter invalid device IDs before they reach the database
    valid_batch = [r for r in batch if r["device_id"] in valid_devices]
    invalid_count = len(batch) - len(valid_batch)
    if invalid_count > 0:
        # Log or alert; this signals an upstream data quality issue
        print(f"Dropped {invalid_count} rows with unknown device_id")

    with conn.cursor() as cur:
        cur.executemany(
            "INSERT INTO sensor_readings (ts, device_id, value) VALUES (%s, %s, %s)",
            [(r["ts"], r["device_id"], r["value"]) for r in valid_batch],
        )
    conn.commit()

This trades a database-level guarantee for an application-level guarantee. It works when the application owns the write path and invalid device_id values represent an upstream data quality problem rather than a concurrent-write race condition. Once this pattern is in place, the database-level FK is redundant, which makes option 4 available.

4. Drop the FK constraint

If your ingest pipeline already validates device_id before writing to Postgres (as shown in option 3), the database-level FK enforces a guarantee the pipeline already provides. Removing it cuts the per-insert lock acquisition entirely.

ALTER TABLE sensor_readings
  DROP CONSTRAINT IF EXISTS sensor_readings_device_id_fkey;

This eliminates the shared-lock acquisition on devices for every insert and reduces WAL records by removing the per-row constraint check entries. Combined with the partial UNIQUE index from option 2, this recovers measurable ingest headroom without changing hardware. The Postgres Optimization Treadmill article shows that a 50K inserts/sec workload with five indexes already generates 25-50MB/sec of WAL from heap and index writes. Dropping the FK removes additional per-row overhead sitting on top of that baseline.

The tradeoff is real: no FK means no database-level catch for data quality bugs that slip through the pipeline. Only drop the constraint if option 3 is in place and you have monitoring to detect upstream device ID mismatches before they reach the database.

Validating the fix

After applying option 1 or option 4, rerun the detection query during peak load:

SELECT
    pid,
    wait_event_type,
    wait_event,
    query,
    state,
    now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
  AND (
        (wait_event_type = 'LWLock' AND wait_event LIKE 'MultiXact%') -- PostgreSQL 16–18
     OR (wait_event_type = 'Lock' AND wait_event IN ('transactionid', 'tuple'))
      )
ORDER BY duration DESC;

MultiXact LWLock waits and any transactionid/tuple row-lock waits tied to the devices table should drop to near zero (MultiXact events only visible on PostgreSQL 16-18). If they persist after applying option 1, confirm the constraint was successfully altered to include DEFERRABLE before the transaction runs SET CONSTRAINTS ... DEFERRED.

After applying option 2, confirm the partial index exists and the full constraint is gone:

SELECT
    i.indexname,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    x.indpred
FROM pg_stat_user_indexes i
JOIN pg_index x USING (indexrelid)
WHERE i.relname = 'sensor_readings';

The indpred column contains the partial index predicate as text. A non-null value confirms the index is partial. The index size should reflect only the data that has been inserted since the last REINDEX. At 2 years of retention with a 7-day partial index freshly rebuilt, expect a size approximately 1/100th that of a full-table index on the same columns.

To verify WAL reduction after option 4, compare wal_bytes from pg_stat_wal before and after dropping the FK:

SELECT wal_records, wal_bytes, pg_size_pretty(wal_bytes) AS wal_size
FROM pg_stat_wal;

After dropping the FK, wal_bytes growth rate should decrease measurably within a few minutes of sustained ingest. On older Postgres versions, check pg_stat_bgwriter for write activity trends instead.

Next step

Run the pg_stat_activity lock detection query during your next peak ingest window. If you see lock waits pointing at your devices table, apply option 1 first: alter the FK to be deferrable and add SET CONSTRAINTS ... DEFERRED to your bulk-insert transactions. It's a two-statement change with no impact on the integrity guarantee and no risk to other callers.

If your ingest rate is still climbing and you're already on the optimization treadmill, the Tiger Data free trial lets you validate ingest headroom on your own data.