Splitting a Wide Table into Two

The orders table has grown to 94 columns. Checkout reads and writes a dozen of them thousands of times per second; the other 80 — fulfilment notes, customs data, gift-wrap options, legacy integration fields — are touched a few times per order. Every update rewrites the whole wide row, every sequential scan drags the cold columns through the cache, and every schema change on the table is a high-stakes event because it is the hottest table in the system. Splitting the cold columns into order_details, one-to-one with orders, fixes all three. Unlike a rename, a split moves data, so it cannot be done with a catalog trick; it needs the full expand-and-contract treatment at table level: create, keep in sync, backfill, switch reads, contract. This guide walks through it. It belongs to Renaming and Splitting Tables Online.

Symptom / Error Signatures

A split is worth doing when:

• pg_stat_user_tables.n_tup_upd on the table is very high and pg_total_relation_size has grown mostly because of rarely read columns (large text or jsonb fields increase TOAST and row width).
• HOT updates (n_tup_hot_upd) are rare because rows no longer fit their pages after update.
• Schema changes on the table are frequent and each one is risky because of its traffic.

And a split in progress goes wrong when:

• Reads of moved columns return NULL for new rows — the sync was not in place before the backfill ended.
• Old code writes a moved column that the new code no longer reads, and the value is lost after reads switch.
• SELECT * consumers break when the old columns are finally dropped.

Root Cause Analysis

A split changes where data lives while code in several versions is still reading and writing it. The only way to keep every version correct is to have the data in both places for the whole transition, with one location clearly designated as the source of truth at each stage, and to switch readers before writers:

A trigger is the most reliable sync because it covers every writer. Its job is simple for a one-to-one split: on insert or update of orders, upsert the corresponding order_details row. In R3, when writes move to order_details, a reverse trigger keeps orders current for any code still on R2 until R4 removes the old columns.

Immediate Mitigation

1. Create the new table and the forward sync.

-- PostgreSQL · migration role · new objects plus a trigger; brief locks only
CREATE TABLE order_details (
  order_id      bigint PRIMARY KEY REFERENCES orders (id) ON DELETE CASCADE,
  customs_code  text,
  gift_wrap     boolean,
  fulfil_notes  text
);
CREATE OR REPLACE FUNCTION orders_details_sync() RETURNS trigger AS $$
BEGIN
  INSERT INTO order_details (order_id, customs_code, gift_wrap, fulfil_notes)
  VALUES (NEW.id, NEW.customs_code, NEW.gift_wrap, NEW.fulfil_notes)
  ON CONFLICT (order_id) DO UPDATE
    SET customs_code = EXCLUDED.customs_code, gift_wrap = EXCLUDED.gift_wrap, fulfil_notes = EXCLUDED.fulfil_notes;
  RETURN NEW;
END $$ LANGUAGE plpgsql;
SET lock_timeout = '3s';
CREATE TRIGGER orders_details_sync AFTER INSERT OR UPDATE OF customs_code, gift_wrap, fulfil_notes ON orders
  FOR EACH ROW EXECUTE FUNCTION orders_details_sync();
-- ROLLBACK PATH: DROP TRIGGER orders_details_sync ON orders; DROP FUNCTION orders_details_sync(); DROP TABLE order_details;

2. Backfill existing rows in batches, idempotently, walking the primary key and pausing on replica lag, as in optimizing backfill scripts for zero-downtime deploys:

-- PostgreSQL · one batch · repeated by a script advancing :lo
INSERT INTO order_details (order_id, customs_code, gift_wrap, fulfil_notes)
SELECT id, customs_code, gift_wrap, fulfil_notes FROM orders
WHERE id >= :lo AND id < :lo + 5000
ON CONFLICT (order_id) DO NOTHING;

ON CONFLICT DO NOTHING lets the trigger’s newer values win for rows changed during the backfill.

3. Reconcile before switching reads. Compare the two copies; any difference means a gap in the sync.

-- PostgreSQL · read-only · must return 0 before release 2
SELECT count(*) FROM orders o LEFT JOIN order_details d ON d.order_id = o.id
WHERE d.order_id IS NULL
   OR (o.customs_code, o.gift_wrap, o.fulfil_notes) IS DISTINCT FROM (d.customs_code, d.gift_wrap, d.fulfil_notes);

Permanent Fix / Long-Term Pattern

Proceed release by release. Release 2 switches reads of the cold columns to order_details (a join, or a separate query where the details are needed), keeping writes on orders. Release 3 moves writes to order_details and flips the trigger direction so orders stays current for any R2 code still running. Release 4, after verification that nothing reads or writes the cold columns on orders, drops them and the reverse trigger — the contract steps described in dropping constraints safely during the contract phase and safely removing a NOT NULL column with expand-contract.

Keep the reconciliation query running on a schedule throughout, and feature-flag the read switch so it can be reverted without a deploy, as in using feature flags to toggle schema changes safely. After the columns are dropped, the space in orders is reclaimed only as rows are updated and vacuumed; for an immediate reclaim on a large table, use pg_repack rather than VACUUM FULL.

Verification Checklist

• The forward sync trigger was in place before the backfill began.
• The reconciliation query returned zero before reads switched and stayed at zero while both copies existed.
• The read switch was behind a feature flag and could be reverted without a deploy.
• Writes moved to the new table only after all readers had switched, with a reverse sync for older code.
• Old columns were dropped only after query statistics showed no use for a full business cycle.

Frequently Asked Questions

Why not move the data in one migration? Because running code in several versions reads and writes the columns throughout the rollout. Keeping both copies in sync for the transition is what lets old and new code coexist and lets you reverse the switch instantly.

Is a trigger or application dual-write better for the sync? A trigger covers every writer, including other services and manual fixes, and is the safer default. Application dual-writes avoid trigger overhead but require every writer to be updated before the backfill.

Should order_details rows be created for every order? For a strict one-to-one split, yes — the sync and backfill create them. If most orders have no details at all, a sparse table where missing rows mean “no details” can be smaller; adjust readers to treat a missing row as nulls.

How do I reclaim space after dropping the old columns? Dropped columns are removed from the catalog immediately, but their data remains in existing row versions until rows are rewritten. Updates and vacuum reclaim it gradually; pg_repack rewrites the table online if you need the space back quickly.