Single Table Inheritance — 4-Minute ALTER TABLE Lock

-- io.thecodeforge — Database tutorial

-- This is the STI table a typical payments service ends up with.
-- Notice how many columns are NULL for specific payment types.
-- This isn't hypothetical — I've seen this exact shape in three production codebases.

CREATE TABLE payments (
    id                      BIGSERIAL PRIMARY KEY,

    -- The discriminator column: the only thing STI *requires*
    -- Your ORM writes this automatically. Don't forget to index it.
    payment_type            VARCHAR(50)     NOT NULL,

    -- Columns shared by ALL payment types — this is STI's sweet spot.
    -- High overlap here is the green flag for using STI.
    amount_cents            INTEGER         NOT NULL,
    currency_code           CHAR(3)         NOT NULL,
    status                  VARCHAR(20)     NOT NULL DEFAULT 'pending',
    created_at              TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    updated_at              TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    customer_id             BIGINT          NOT NULL REFERENCES customers(id),

    -- CreditCardPayment-only columns.
    -- NULL for every BankTransfer, CryptoPayment, and WireTransfer row.
    card_last_four          CHAR(4),
    card_network            VARCHAR(20),        -- 'visa', 'mastercard', etc.
    card_expiry_month       SMALLINT,
    card_expiry_year        SMALLINT,
    card_fingerprint        VARCHAR(64),        -- tokenised card identifier
    stripe_charge_id        VARCHAR(64),

    -- BankTransferPayment-only columns.
    -- NULL for every CreditCard, Crypto, and Wire row.
    bank_account_last_four  CHAR(4),
    bank_routing_number     VARCHAR(20),
    ach_trace_number        VARCHAR(15),
    bank_name               VARCHAR(100),

    -- CryptoPayment-only columns.
    -- NULL for everything else.
    blockchain_network       VARCHAR(30),        -- 'ethereum', 'bitcoin', etc.
    wallet_address          VARCHAR(100),
    transaction_hash        VARCHAR(128),
    confirmation_count      INTEGER,

    -- WireTransfer-only columns.
    swift_code              CHAR(11),
    iban                    VARCHAR(34),
    beneficiary_bank_name   VARCHAR(100),
    correspondent_bank_bic  CHAR(11)
);

-- The discriminator index is non-negotiable.
-- Without it, every polymorphic query scans the full table.
CREATE INDEX idx_payments_type
    ON payments (payment_type);

-- Composite index for the most common query pattern:
-- "show me all pending credit card payments for this customer"
CREATE INDEX idx_payments_customer_type_status
    ON payments (customer_id, payment_type, status);

-- Partial index — this is the move that saves you when columns are sparse.
-- Standard index on card_fingerprint would be ~60% NULL, near-useless.
-- This index ONLY indexes rows where the column is actually populated.
CREATE INDEX idx_payments_card_fingerprint
    ON payments (card_fingerprint)
    WHERE payment_type = 'credit_card' AND card_fingerprint IS NOT NULL;

-- Same pattern for crypto transaction lookups.
CREATE INDEX idx_payments_txn_hash
    ON payments (transaction_hash)
    WHERE payment_type = 'crypto' AND transaction_hash IS NOT NULL;


-- Now let's see what the data actually looks like at rest.
-- Run this after inserting a mix of payment types and you'll
-- immediately see the NULL sprawl that STI produces.

INSERT INTO payments (
    payment_type, amount_cents, currency_code, status, customer_id,
    card_last_four, card_network, card_expiry_month, card_expiry_year,
    card_fingerprint, stripe_charge_id
) VALUES (
    'credit_card', 4999, 'USD', 'captured', 1001,
    '4242', 'visa', 12, 2027,
    'fp_abc123xyz', 'ch_3OqExample'
);

INSERT INTO payments (
    payment_type, amount_cents, currency_code, status, customer_id,
    bank_account_last_four, bank_routing_number, ach_trace_number, bank_name
) VALUES (
    'bank_transfer', 150000, 'USD', 'pending', 1002,
    '6789', '021000021', '20240115123456789', 'JPMorgan Chase'
);

INSERT INTO payments (
    payment_type, amount_cents, currency_code, status, customer_id,
    blockchain_network, wallet_address, transaction_hash, confirmation_count
) VALUES (
    'crypto', 89900, 'USD', 'confirming', 1003,
    'ethereum',
    '0xAbC123dEf456GhI789jKl012MnO345PqR678StU',
    '0x9f86d081884c7d659a2feaa0c55ad015a3bf4f1b2b0b822cd15d6c15b0f00a08',
    3
);


-- This query exposes the NULL sprawl visually.
-- In a real schema audit, this is the first thing I run.
SELECT
    payment_type,
    COUNT(*)                                                AS total_rows,
    COUNT(card_last_four)                                   AS credit_card_col_populated,
    COUNT(bank_account_last_four)                           AS bank_col_populated,
    COUNT(transaction_hash)                                 AS crypto_col_populated,
    COUNT(swift_code)                                       AS wire_col_populated,

    -- This ratio is your STI health metric.
    -- If it drops below 0.4 for any subtype, you should reconsider STI.
    ROUND(
        COUNT(card_last_four)::NUMERIC / NULLIF(COUNT(*), 0),
        2
    ) AS credit_card_col_fill_ratio
FROM payments
GROUP BY payment_type
ORDER BY total_rows DESC;

-- io.thecodeforge — Database tutorial

-- Here's a schema where STI EARNS its place.
-- A CMS content system: Articles, Videos, and Podcasts.
-- The overlap is high (~75%), the polymorphic query pattern is constant,
-- and the subtype-specific columns are few.

CREATE TABLE content_items (
    id              BIGSERIAL PRIMARY KEY,

    -- Discriminator. Always VARCHAR, never an integer enum --
    -- string values make the data self-documenting in the DB.
    content_type    VARCHAR(30)     NOT NULL,

    -- ~~~ SHARED COLUMNS: 8 out of 11 total = 73% overlap ~~~
    -- This is the green-flag ratio. STI makes sense here.
    title           VARCHAR(255)    NOT NULL,
    slug            VARCHAR(255)    NOT NULL UNIQUE,
    author_id       BIGINT          NOT NULL REFERENCES authors(id),
    published_at    TIMESTAMPTZ,
    status          VARCHAR(20)     NOT NULL DEFAULT 'draft',
    excerpt         TEXT,
    thumbnail_url   VARCHAR(512),
    view_count      INTEGER         NOT NULL DEFAULT 0,

    -- ~~~ SUBTYPE-SPECIFIC: only 3 columns, all sparse ~~~

    -- Article-only: NULL for videos and podcasts.
    reading_time_minutes    SMALLINT,

    -- Video-only: NULL for articles and podcasts.
    video_duration_seconds  INTEGER,

    -- Podcast-only: NULL for articles and videos.
    audio_duration_seconds  INTEGER
);

-- Composite index for the editorial dashboard query:
-- "show me all published content, newest first, for this author"
CREATE INDEX idx_content_author_status_published
    ON content_items (author_id, status, published_at DESC)
    WHERE status = 'published';

-- This is the polymorphic query STI was born for.
-- Try doing this cleanly with three separate tables — you need a UNION.
-- With STI it's a single fast index scan.
SELECT
    id,
    content_type,
    title,
    slug,
    published_at,
    view_count
FROM content_items
WHERE
    author_id   = 42
    AND status  = 'published'
ORDER BY published_at DESC
LIMIT 20;


-- Now here's why the polymorphic query advantage is real.
-- Compare the STI approach above to what you'd need with separate tables:

-- WITH SEPARATE TABLES (Class Table Inheritance) — same query:
SELECT id, 'article' AS content_type, title, slug, published_at, view_count
FROM   articles
WHERE  author_id = 42 AND status = 'published'

UNION ALL

SELECT id, 'video', title, slug, published_at, view_count
FROM   videos
WHERE  author_id = 42 AND status = 'published'

UNION ALL

SELECT id, 'podcast', title, slug, published_at, view_count
FROM   podcasts
WHERE  author_id = 42 AND status = 'published'

ORDER BY published_at DESC
LIMIT 20;
-- Three index scans, a sort across merged results, and a LIMIT applied last.
-- At 5M rows across three tables, this hurts. The STI version does not.


-- Querying a specific subtype is equally clean with STI.
-- The ORM does this automatically — here's the raw SQL it generates:
SELECT
    id,
    title,
    slug,
    video_duration_seconds
FROM content_items
WHERE
    content_type = 'video'
    AND status   = 'published'
ORDER BY published_at DESC;

-- io.thecodeforge — Database tutorial

-- Diagnosing a degrading STI table in PostgreSQL.
-- Run this against any STI table you've inherited to get a full health report.

-- Step 1: Measure actual NULL density per column.
-- This tells you exactly which columns are poisoning your index selectivity.
SELECT
    a.attname                                           AS column_name,
    s.null_frac                                         AS null_fraction,
    s.n_distinct,
    -- A null_frac above 0.35 on an indexed column is a red flag.
    -- The planner may skip the index entirely.
    CASE
        WHEN s.null_frac > 0.35 THEN 'HIGH NULL DENSITY — check for partial index'
        WHEN s.null_frac > 0.15 THEN 'MODERATE — monitor'
        ELSE 'OK'
    END                                                 AS index_risk_assessment
FROM
    pg_attribute           a
    JOIN pg_stats          s ON s.attname = a.attname
                             AND s.tablename = 'content_items'
    JOIN pg_class          c ON c.oid = a.attrelid
WHERE
    c.relname   = 'content_items'
    AND a.attnum > 0          -- exclude system columns
    AND NOT a.attisdropped    -- exclude dropped columns
ORDER BY s.null_frac DESC;


-- Step 2: Measure table bloat from NULL storage.
-- Postgres stores NULLs efficiently via a null bitmap, but this tells you
-- your ACTUAL average row width vs what it would be with separate tables.
SELECT
    relname                                 AS table_name,
    pg_size_pretty(pg_total_relation_size(oid)) AS total_size,
    pg_size_pretty(pg_relation_size(oid))   AS table_size,
    pg_size_pretty(
        pg_total_relation_size(oid)
        - pg_relation_size(oid)
    )                                       AS index_size,
    n_live_tup                              AS live_rows,
    -- Bytes per row: high value on a sparse STI table = storage waste signal
    pg_relation_size(oid) / NULLIF(n_live_tup, 0) AS bytes_per_row
FROM
    pg_stat_user_tables
WHERE
    relname = 'content_items';


-- Step 3: The migration risk check.
-- Before running ANY alter on a large STI table, estimate the blast radius.
-- This shows you row count and current table size so you can calculate
-- whether to use pg_repack, a shadow table migration, or a timed maintenance window.
SELECT
    content_type,
    COUNT(*)                                AS row_count,
    -- Proportion of the table this subtype owns.
    -- Useful for estimating migration lock duration.
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS pct_of_table
FROM content_items
GROUP BY content_type
ORDER BY row_count DESC;


-- Step 4: The escape hatch — migrating one subtype OUT of STI
-- to its own table without downtime.
-- Pattern: create new table, backfill, switch writes, drop STI columns.

-- 4a. Create the extracted table — clean, no NULLs, no dead columns.
CREATE TABLE video_content (
    id                      BIGINT PRIMARY KEY REFERENCES content_items(id),
    video_duration_seconds  INTEGER NOT NULL,
    -- Add video-specific columns here freely now that they're isolated.
    resolution              VARCHAR(10),
    codec                   VARCHAR(20),
    cdn_asset_key           VARCHAR(256)
);

-- 4b. Backfill from the STI table — run this in batches to avoid lock pressure.
-- Don't do INSERT ... SELECT on 10M rows in one transaction. Use a loop.
INSERT INTO video_content (id, video_duration_seconds)
SELECT
    id,
    COALESCE(video_duration_seconds, 0) -- handle any unexpected NULLs cleanly
FROM content_items
WHERE
    content_type            = 'video'
    AND video_duration_seconds IS NOT NULL;

-- 4c. Verify the backfill before you cut over.
SELECT
    (
        SELECT COUNT(*) FROM content_items  WHERE content_type = 'video'
    ) AS sti_video_count,
    (
        SELECT COUNT(*) FROM video_content
    ) AS extracted_video_count;
-- These two numbers must match before you touch application code.

-- io.thecodeforge — Database tutorial

-- Class Table Inheritance (CTI) — the right pattern when STI's
-- NULL density is too high but you still need polymorphic queries.
--
-- Same CMS domain as earlier, but let's say the video subtype has grown
-- to 25 unique columns (transcoding metadata, CDN configs, chapters, etc.)
-- That kills STI's overlap ratio. CTI is the correct move.

-- Base table: ONLY the truly shared columns.
-- Every subtype has a corresponding row here.
CREATE TABLE content_items_base (
    id              BIGSERIAL PRIMARY KEY,
    content_type    VARCHAR(30)     NOT NULL,
    title           VARCHAR(255)    NOT NULL,
    slug            VARCHAR(255)    NOT NULL UNIQUE,
    author_id       BIGINT          NOT NULL REFERENCES authors(id),
    published_at    TIMESTAMPTZ,
    status          VARCHAR(20)     NOT NULL DEFAULT 'draft',
    excerpt         TEXT,
    thumbnail_url   VARCHAR(512),
    view_count      INTEGER         NOT NULL DEFAULT 0,
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()
);

-- Article subtype table: only article-specific columns.
-- PK = FK to base table — one-to-one relationship enforced at DB level.
CREATE TABLE articles (
    id                      BIGINT PRIMARY KEY REFERENCES content_items_base(id)
                            ON DELETE CASCADE,
    body_html               TEXT            NOT NULL,
    reading_time_minutes    SMALLINT,
    word_count              INTEGER,
    seo_meta_description    VARCHAR(160)
);

-- Video subtype: now we can have 25 columns here without polluting the base table.
CREATE TABLE videos (
    id                      BIGINT PRIMARY KEY REFERENCES content_items_base(id)
                            ON DELETE CASCADE,
    video_duration_seconds  INTEGER         NOT NULL,
    resolution              VARCHAR(10),
    codec                   VARCHAR(20),
    cdn_asset_key           VARCHAR(256)    NOT NULL,
    transcoding_status      VARCHAR(20)     NOT NULL DEFAULT 'pending',
    chapter_count           SMALLINT,
    has_captions            BOOLEAN         NOT NULL DEFAULT FALSE,
    -- ... 17 more columns that would have DESTROYED an STI table's NULL ratio
    storage_bytes           BIGINT
);

-- Podcast subtype.
CREATE TABLE podcasts (
    id                      BIGINT PRIMARY KEY REFERENCES content_items_base(id)
                            ON DELETE CASCADE,
    audio_duration_seconds  INTEGER         NOT NULL,
    episode_number          SMALLINT,
    season_number           SMALLINT,
    audio_cdn_key           VARCHAR(256)    NOT NULL,
    transcript_url          VARCHAR(512)
);

-- Index on base table for polymorphic queries.
CREATE INDEX idx_content_base_author_status
    ON content_items_base (author_id, status, published_at DESC)
    WHERE status = 'published';


-- Polymorphic query in CTI — note the LEFT JOINs.
-- This is the trade-off: it's two lines longer than the STI version
-- and touches 4 tables instead of 1. But zero NULLs, clean schema.
SELECT
    b.id,
    b.content_type,
    b.title,
    b.slug,
    b.published_at,
    b.view_count,
    -- Coalesce across subtypes to get duration regardless of type.
    COALESCE(
        v.video_duration_seconds,
        p.audio_duration_seconds
    )                                   AS duration_seconds,
    a.reading_time_minutes
FROM content_items_base b
    LEFT JOIN articles  a ON a.id = b.id
    LEFT JOIN videos    v ON v.id = b.id
    LEFT JOIN podcasts  p ON p.id = b.id
WHERE
    b.author_id   = 42
    AND b.status  = 'published'
ORDER BY b.published_at DESC
LIMIT 20;


-- Subtype-specific query in CTI — INNER JOIN, not LEFT JOIN.
-- Slightly more explicit than STI but the video table is CLEAN.
-- No NULLs from article or podcast columns.
SELECT
    b.id,
    b.title,
    v.video_duration_seconds,
    v.transcoding_status,
    v.has_captions,
    v.storage_bytes
FROM content_items_base b
    INNER JOIN videos v ON v.id = b.id
WHERE
    b.status                = 'published'
    AND v.transcoding_status = 'complete'
ORDER BY b.published_at DESC;

-- io.thecodeforge — Database tutorial

-- PostgreSQL function to assess STI table health.
-- Returns warning messages if thresholds are exceeded.
-- Call this in a CI step after migrations.

CREATE OR REPLACE FUNCTION io.thecodeforge.sti_health_check(table_name TEXT)
RETURNS TABLE(metric TEXT, value NUMERIC, threshold NUMERIC, status TEXT, recommendation TEXT) AS $$
DECLARE
    total_columns INT;
    shared_columns INT;
    overlap_ratio NUMERIC;
    row_count BIGINT;
    subtype_count INT;
    rec RECORD;
BEGIN
    -- Get total columns (excluding system columns and the type discriminator)
    SELECT COUNT(*) INTO total_columns
    FROM information_schema.columns
    WHERE table_name = $1
      AND column_name NOT IN ('id', 'type', 'created_at', 'updated_at');

    -- Count shared columns (columns where null_frac < 0.1 across all subtypes?)
    -- Simplified: count columns that are NOT NULL for the most common subtype
    SELECT COUNT(*) INTO shared_columns
    FROM pg_stats
    WHERE tablename = $1
      AND null_frac < 0.05; -- shared columns are almost never NULL

    overlap_ratio := shared_columns::NUMERIC / NULLIF(total_columns, 0);

    -- Row count
    SELECT n_live_tup INTO row_count
    FROM pg_stat_user_tables
    WHERE relname = $1;

    -- Subtype count (from discriminator column)
    EXECUTE format('SELECT COUNT(DISTINCT %I) FROM %I',
                   (SELECT column_name FROM information_schema.columns
                    WHERE table_name = $1 AND column_name IN ('type', 'payment_type', 'content_type')
                    LIMIT 1),
                   $1) INTO subtype_count;

    -- Check overlap ratio
    IF overlap_ratio < 0.55 THEN
        metric := 'overlap_ratio';
        value := overlap_ratio;
        threshold := 0.55;
        status := 'FAIL';
        recommendation := 'Overlap ratio below 55%. Consider migrating to Class Table Inheritance.';
        RETURN NEXT;
    END IF;

    -- Check row count + subtype count
    IF row_count > 10000000 AND subtype_count > 5 THEN
        metric := 'table_size_and_subtypes';
        value := row_count::NUMERIC;
        threshold := 10000000;
        status := 'WARN';
        recommendation := 'Large STI table with many subtypes. Plan migration to CTI or Concrete Inheritance.';
        RETURN NEXT;
    END IF;

    -- Check indexed columns with high NULL density
    FOR rec IN
        SELECT attname, null_frac
        FROM pg_stats
        WHERE tablename = $1
          AND null_frac > 0.35
          AND attname IN (
              SELECT column_name
              FROM pg_indexes
              WHERE tablename = $1
                AND indexdef LIKE '%' || attname || '%'
          )
    LOOP
        metric := 'null_density_' || rec.attname;
        value := rec.null_frac;
        threshold := 0.35;
        status := 'FAIL';
        recommendation := 'Index on ' || rec.attname || ' has >35% NULL density. Replace with a partial index using WHERE type = ''x'' AND ' || rec.attname || ' IS NOT NULL.';
        RETURN NEXT;
    END LOOP;

    -- If no issues, return a single OK row
    IF NOT FOUND THEN
        metric := 'overall';
        value := 1;
        threshold := 0;
        status := 'PASS';
        recommendation := 'STI table appears healthy.';
        RETURN NEXT;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Usage example (add to CI after any schema change):
-- SELECT * FROM io.thecodeforge.sti_health_check('payments');

-- io.thecodeforge — Database tutorial

-- Production migration script: extract 'video' subtype from STI to CTI.
-- Run in a transaction for each batch, commit frequently.

-- Step 0: Create base table if not exists (here we assume legacy STI table is the base)
-- For this migration, we'll keep the base table and create a separate video_detail table.

-- Step 1: Create child table
CREATE TABLE io.thecodeforge.video_details (
    id                      BIGINT PRIMARY KEY REFERENCES io.thecodeforge.content_items(id),
    video_duration_seconds  INTEGER         NOT NULL,
    resolution              VARCHAR(10),
    codec                   VARCHAR(20),
    cdn_asset_key           VARCHAR(256)    NOT NULL,
    transcoding_status      VARCHAR(20)     NOT NULL DEFAULT 'pending',
    has_captions            BOOLEAN         NOT NULL DEFAULT FALSE,
    storage_bytes           BIGINT
);

-- Step 2: Backfill in batches
DO $$
DECLARE
    batch_size INTEGER := 5000;
    offset_val INTEGER := 0;
    rows_inserted INTEGER;
BEGIN
    LOOP
        INSERT INTO io.thecodeforge.video_details (
            id, video_duration_seconds, resolution, codec, cdn_asset_key,
            transcoding_status, has_captions, storage_bytes
        )
        SELECT
            id,
            COALESCE(video_duration_seconds, 0),
            COALESCE(resolution, 'unknown'),
            COALESCE(codec, 'unknown'),
            COALESCE(cdn_asset_key, 'pending'),
            COALESCE(transcoding_status, 'pending'),
            COALESCE(has_captions, FALSE),
            COALESCE(storage_bytes, 0)
        FROM io.thecodeforge.content_items
        WHERE content_type = 'video'
        ORDER BY id
        LIMIT batch_size OFFSET offset_val;

        GET DIAGNOSTICS rows_inserted = ROW_COUNT;
        EXIT WHEN rows_inserted = 0;
        COMMIT;
        offset_val := offset_val + batch_size;
        RAISE NOTICE 'Inserted % rows, offset now %', rows_inserted, offset_val;
    END LOOP;
END $$;

-- Step 3: Verify parity
SELECT
    (SELECT COUNT(*) FROM io.thecodeforge.content_items WHERE content_type = 'video') AS sti_count,
    (SELECT COUNT(*) FROM io.thecodeforge.video_details) AS detail_count;

-- Step 4: Deploy code that reads from video_details for video content, writes to both.
-- After a day of dual-writes with monitoring, drop video columns from STI table.

-- Step 5: Drop columns (off-peak, with lock timeout set)
SET lock_timeout = '30s';
ALTER TABLE io.thecodeforge.content_items DROP COLUMN video_duration_seconds;
ALTER TABLE io.thecodeforge.content_items DROP COLUMN resolution;
ALTER TABLE io.thecodeforge.content_items DROP COLUMN codec;
ALTER TABLE io.thecodeforge.content_items DROP COLUMN cdn_asset_key;
ALTER TABLE io.thecodeforge.content_items DROP COLUMN transcoding_status;
ALTER TABLE io.thecodeforge.content_items DROP COLUMN has_captions;
ALTER TABLE io.thecodeforge.content_items DROP COLUMN storage_bytes;