Postgresql de

PostgreSQL for Data Engineering

When to Use

Load this skill when the task involves:

• Designing or maintaining large PostgreSQL tables with declarative partitioning
• Choosing or creating indexes (B-Tree, BRIN, GIN, GIST, partial, covering)
• Bulk-loading data with COPY, unlogged tables, or pg_bulkload
• Diagnosing slow queries with EXPLAIN / EXPLAIN ANALYZE
• Tuning autovacuum to prevent table bloat
• Writing advanced SQL: window functions, JSONB, CTEs, LATERAL joins
• Any DE pipeline that reads/writes PostgreSQL as a source or sink

---

1. Declarative Partitioning

Strategy Selection

Strategy	Use case	Partition key
RANGE	Time-series, sequential IDs	event_date, created_at, id
LIST	Low-cardinality categorical	region, status, tenant_id
HASH	Even spread, no natural range	user_id, order_id

RANGE — time-series events table

-- Parent table (holds no data itself)
CREATE TABLE events (
    event_id    BIGINT       NOT NULL,
    event_date  DATE         NOT NULL,
    source      TEXT         NOT NULL,
    payload     JSONB,
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT now()
) PARTITION BY RANGE (event_date);

-- Monthly child partitions
CREATE TABLE events_2024_01
    PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02
    PARTITION OF events
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Default partition catches out-of-range rows (prevents insert errors)
CREATE TABLE events_default
    PARTITION OF events DEFAULT;

-- Index on every partition (inherit automatically in PG 11+)
CREATE INDEX ON events (event_date);
CREATE INDEX ON events (source, event_date);

LIST — multi-tenant or regional sharding

CREATE TABLE orders (
    order_id   BIGINT       NOT NULL,
    region     TEXT         NOT NULL,
    amount     NUMERIC(12,2),
    created_at TIMESTAMPTZ  NOT NULL DEFAULT now()
) PARTITION BY LIST (region);

CREATE TABLE orders_eu   PARTITION OF orders FOR VALUES IN ('EU', 'DE', 'FR', 'NL');
CREATE TABLE orders_us   PARTITION OF orders FOR VALUES IN ('US', 'CA', 'MX');
CREATE TABLE orders_apac PARTITION OF orders FOR VALUES IN ('AU', 'SG', 'JP', 'IN');
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

HASH — even distribution when no natural range exists

CREATE TABLE user_events (
    user_id    BIGINT NOT NULL,
    event_type TEXT   NOT NULL,
    ts         TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY HASH (user_id);

-- 8 buckets
CREATE TABLE user_events_0 PARTITION OF user_events FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE user_events_1 PARTITION OF user_events FOR VALUES WITH (MODULUS 8, REMAINDER 1);
-- ... repeat through REMAINDER 7

Attach / Detach

-- Detach a partition for archival (non-blocking in PG 14+ with CONCURRENTLY)
ALTER TABLE events DETACH PARTITION events_2022_01 CONCURRENTLY;

-- Re-attach an existing table as a partition
ALTER TABLE events ATTACH PARTITION events_2024_03
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

Partition Pruning

-- Confirm pruning is enabled
SHOW enable_partition_pruning;  -- must be 'on'

-- Pruning happens at planning time for literals:
EXPLAIN SELECT * FROM events WHERE event_date = '2024-01-15';
-- Output should show only events_2024_01, not all partitions

-- For runtime parameters, enable_runtime_partition_pruning must be on (default on PG 13+)

pg_partman — automated partition management

-- Install extension
CREATE EXTENSION pg_partman SCHEMA partman;

-- Create a time-based partition set (monthly, pre-create 3 future partitions)
SELECT partman.create_parent(
    p_parent_table  => 'public.events',
    p_control       => 'event_date',
    p_type          => 'range',
    p_interval      => 'monthly',
    p_premake       => 3,
    p_start_partition => '2024-01-01'
);

-- Run maintenance (create new partitions, drop expired ones)
-- Typically called from pg_cron every hour:
SELECT partman.run_maintenance('public.events');

-- Retention: drop partitions older than 12 months
UPDATE partman.part_config
SET    retention = '12 months',
       retention_keep_table = FALSE
WHERE  parent_table = 'public.events';

---

2. Index Types and Strategy

B-Tree — the default, works for equality and range queries

-- Simple column
CREATE INDEX CONCURRENTLY idx_orders_created_at
    ON orders (created_at DESC);

-- Composite: put equality columns first, range column last
CREATE INDEX CONCURRENTLY idx_orders_region_created
    ON orders (region, created_at DESC);

-- Covering index (INCLUDE) — enables index-only scans for wide SELECTs
CREATE INDEX CONCURRENTLY idx_orders_region_amount
    ON orders (region, created_at DESC)
    INCLUDE (order_id, amount);
-- SELECT order_id, amount FROM orders WHERE region='EU' ORDER BY created_at DESC
-- now never touches the heap

BRIN — Block Range INdex, ideal for naturally-ordered time-series

-- BRIN is tiny (~100x smaller than B-Tree) but requires physical correlation
-- Works well when rows are appended in timestamp order (logs, events, IoT)
CREATE INDEX CONCURRENTLY idx_events_created_brin
    ON events USING BRIN (created_at)
    WITH (pages_per_range = 32);   -- default 128; smaller = more precise, larger index

-- Verify correlation before choosing BRIN:
SELECT attname, correlation
FROM   pg_stats
WHERE  tablename = 'events' AND attname = 'created_at';
-- correlation close to 1.0 or -1.0 → BRIN is appropriate

GIN — inverted index for JSONB, arrays, and full-text search

-- Default jsonb_ops: supports @>, ?, ?|, ?&, @?  (flexible, larger index)
CREATE INDEX CONCURRENTLY idx_events_payload_gin
    ON events USING GIN (payload);

-- jsonb_path_ops: supports only @> and @@  (faster, 30–50% smaller)
-- Use when queries only check containment and you have a stable schema
CREATE INDEX CONCURRENTLY idx_events_payload_path_gin
    ON events USING GIN (payload jsonb_path_ops);

-- GIN on array column
CREATE INDEX CONCURRENTLY idx_product_tags_gin
    ON products USING GIN (tags);
-- Supports: WHERE tags @> ARRAY['electronics','sale']

Partial index — index only the rows you query

-- Index only active orders — dramatically smaller, faster to maintain
CREATE INDEX CONCURRENTLY idx_orders_active_created
    ON orders (created_at DESC)
    WHERE status = 'ACTIVE';

-- Index only non-null values
CREATE INDEX CONCURRENTLY idx_events_source_notnull
    ON events (source)
    WHERE source IS NOT NULL;

GIST — geometry, ranges, nearest-neighbor

-- Range overlap queries
CREATE INDEX CONCURRENTLY idx_bookings_period_gist
    ON bookings USING GIST (tstzrange(start_at, end_at));

-- Find overlapping bookings
SELECT * FROM bookings
WHERE  tstzrange(start_at, end_at) && tstzrange('2024-06-01','2024-06-30');

Index maintenance best practices

-- Always use CONCURRENTLY to avoid table lock in production
CREATE INDEX CONCURRENTLY ...;

-- Check index bloat
SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS idx_size,
       idx_scan,
       idx_tup_read,
       idx_tup_fetch
FROM   pg_stat_user_indexes
WHERE  relname = 'events'
ORDER  BY pg_relation_size(indexrelid) DESC;

-- Rebuild a bloated index online
REINDEX INDEX CONCURRENTLY idx_events_payload_gin;

---

3. COPY Command — Bulk Load and Export

COPY FROM — load data into PostgreSQL

-- CSV with header
COPY events (event_id, event_date, source, payload)
FROM '/data/events_2024_01.csv'
WITH (
    FORMAT CSV,
    HEADER TRUE,
    DELIMITER ',',
    QUOTE '"',
    NULL ''
);

-- Compressed file via shell (server must be able to read the pipe)
COPY events FROM PROGRAM 'gunzip -c /data/events.csv.gz'
WITH (FORMAT CSV, HEADER TRUE);

-- Binary mode — fastest, no encoding overhead; not human-readable
COPY events TO '/data/events_binary.bin' WITH (FORMAT BINARY);
COPY events FROM '/data/events_binary.bin' WITH (FORMAT BINARY);

COPY TO — export from PostgreSQL

-- Export a query result
COPY (
    SELECT event_id, event_date, source, payload->>'user_id' AS user_id
    FROM   events
    WHERE  event_date >= '2024-01-01'
      AND  event_date <  '2024-02-01'
) TO '/data/export_jan.csv'
WITH (FORMAT CSV, HEADER TRUE, DELIMITER '\t');

Client-side \copy (psql meta-command)

-- Runs on the client, no superuser needed, reads local files
\copy events (event_id, event_date, source) FROM 'local_file.csv' CSV HEADER
\copy (SELECT * FROM events LIMIT 1000) TO 'sample.csv' CSV HEADER

COPY vs INSERT performance

Method	~rows/sec	Notes
Single-row INSERT	~5 K	Max overhead, one WAL record per row
Multi-row INSERT (1000/batch)	~50–200 K	Good for application code
COPY (logged)	~200–800 K	Best general-purpose bulk load
COPY + UNLOGGED table	~1–3 M	No WAL writes; data lost on crash
pg_bulkload	>3 M	Bypass shared_buffers; requires extension

---

4. EXPLAIN / EXPLAIN ANALYZE

Reading a query plan

-- Always use ANALYZE BUFFERS for real diagnostics; VERBOSE adds column detail
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT  o.order_id, o.amount, c.name
FROM    orders o
JOIN    customers c ON c.customer_id = o.customer_id
WHERE   o.region = 'EU'
  AND   o.created_at >= now() - INTERVAL '7 days';

Sample output interpretation:

Hash Join  (cost=1234.56..5678.90 rows=1500 width=48)
           (actual time=12.345..67.890 rows=1423 loops=1)
  Buffers: shared hit=1200 read=340
  ->  Seq Scan on orders_eu  (cost=0..3000 rows=5000 ...)
        Filter: (created_at >= ...)
        Rows Removed by Filter: 3577
  ->  Hash  (cost=800..800 rows=34456 ...)
        Buckets: 65536  Batches: 1  Memory Usage: 2048kB
        ->  Seq Scan on customers  (cost=0..800 ...)

Key fields:

Field	Meaning
cost=X..Y	Planner estimate: startup cost .. total cost (arbitrary units)
rows=N (plan)	Planner estimate of output rows
rows=N (actual)	Actual rows returned
loops=N	Node executed N times (multiply actual by loops for total work)
shared hit	Pages served from shared_buffers (fast)
shared read	Pages fetched from disk or OS cache (slow)
Rows Removed by Filter	Rows read but discarded — suggests a missing index

Scan type guide

Scan type	When chosen	What to check
Seq Scan	No usable index, or low selectivity	Add index if Rows Removed by Filter is high
Index Scan	High selectivity, random access	Normal; watch shared read for I/O cost
Index Only Scan	All columns in INCLUDE or index	Ideal; verify Heap Fetches=0
Bitmap Heap Scan	Medium selectivity	Normal; Recheck Cond rows indicate bloat

Common slow-query patterns

-- Pattern 1: Stale statistics → huge row estimate mismatch
-- Fix: ANALYZE table; or lower autovacuum_analyze_scale_factor per table

-- Pattern 2: Function on indexed column disables index
-- BAD:
WHERE DATE_TRUNC('day', created_at) = '2024-01-15'
-- GOOD (use range instead):
WHERE created_at >= '2024-01-15' AND created_at < '2024-01-16'
-- Or create an expression index:
CREATE INDEX ON events (DATE_TRUNC('day', created_at));

-- Pattern 3: Implicit type cast prevents index use
-- BAD (event_id is BIGINT, literal is TEXT):
WHERE event_id = '12345'
-- GOOD:
WHERE event_id = 12345

-- Pattern 4: JIT overhead on short queries
-- If JIT Time is large fraction of execution time, disable per session:
SET jit = off;
-- Or raise the threshold globally:
-- jit_above_cost = 500000  (default 100000)

Diagnosing with pg_stat_statements

-- Top 10 slowest queries by total time
SELECT query,
       calls,
       total_exec_time::NUMERIC(12,2) AS total_ms,
       mean_exec_time::NUMERIC(10,2)  AS mean_ms,
       rows
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  10;

---

5. Autovacuum Tuning

How bloat happens

Every UPDATE or DELETE leaves dead tuples. PostgreSQL uses MVCC, meaning old row versions are not immediately removed. Autovacuum reclaims them. Without proper tuning, large write-heavy tables accumulate bloat that slows scans and wastes disk.

Key parameters

-- Global defaults (postgresql.conf)
autovacuum_vacuum_scale_factor    = 0.2    -- trigger at 20% dead tuples (too high for large tables)
autovacuum_vacuum_threshold       = 50     -- minimum dead tuples to trigger vacuum
autovacuum_analyze_scale_factor   = 0.1
autovacuum_analyze_threshold      = 50

autovacuum_vacuum_cost_limit      = 200    -- total I/O cost budget per pass
autovacuum_vacuum_cost_delay      = 2ms    -- pause after exhausting budget
autovacuum_max_workers            = 3      -- parallel autovacuum workers

Per-table tuning for high-write tables

-- A 10M-row orders table: default fires at 2M dead tuples — far too late
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor   = 0.01,   -- trigger at 1%  (100K rows)
    autovacuum_vacuum_threshold      = 1000,
    autovacuum_analyze_scale_factor  = 0.005,  -- analyze at 0.5%
    autovacuum_vacuum_cost_limit     = 1000,   -- more aggressive I/O budget
    autovacuum_vacuum_cost_delay     = 2        -- ms; 0 = unlimited (risky on HDD)
);

-- Small hot lookup table: keep almost always fresh
ALTER TABLE sessions SET (
    autovacuum_vacuum_scale_factor  = 0.0,
    autovacuum_vacuum_threshold     = 500,
    autovacuum_vacuum_cost_limit    = 800
);

Monitor bloat and vacuum activity

-- Dead tuple ratio per table
SELECT relname,
       n_dead_tup,
       n_live_tup,
       ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
       last_autovacuum,
       last_autoanalyze
FROM   pg_stat_user_tables
ORDER  BY n_dead_tup DESC
LIMIT  20;

-- Physical bloat estimate
SELECT relname,
       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
FROM   pg_class
WHERE  relkind = 'r'
ORDER  BY pg_total_relation_size(oid) DESC
LIMIT  20;

Manual vacuum / analyze

-- Reclaim space and update statistics (safe in production, no table lock)
VACUUM ANALYZE orders;

-- FULL reclaims disk space but holds AccessExclusiveLock — avoid on live tables
VACUUM FULL orders;  -- only during maintenance windows

-- Freeze old XIDs to prevent transaction ID wraparound
VACUUM FREEZE ANALYZE events_2022_01;

---

6. Window Functions — Advanced Patterns

Running totals, lag/lead, rank

SELECT
    order_id,
    region,
    amount,
    -- Running sum within region, ordered by date
    SUM(amount) OVER (
        PARTITION BY region
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    -- Previous row value
    LAG(amount, 1, 0) OVER (PARTITION BY region ORDER BY created_at) AS prev_amount,
    -- Dense rank (no gaps on ties)
    DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region
FROM orders;

ROWS vs RANGE vs GROUPS frame modes

-- ROWS: physical row count — precise, deterministic
SUM(amount) OVER (
    PARTITION BY region ORDER BY created_at
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW   -- rolling 7-row window
)

-- RANGE: logical value range — includes all ties at boundaries
SUM(amount) OVER (
    PARTITION BY region ORDER BY created_at
    RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW  -- rolling 7-day window
)

-- GROUPS (PostgreSQL 11+): peer-group count — useful for ranked groupings
RANK() OVER (
    ORDER BY amount
    GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)

FILTER clause inside window aggregates

-- Count only orders where amount > 1000, alongside total count
SELECT
    region,
    created_at::DATE,
    COUNT(*)                          AS total_orders,
    COUNT(*) FILTER (WHERE amount > 1000) AS large_orders,
    SUM(amount) FILTER (WHERE status = 'COMPLETED') AS completed_revenue
FROM   orders
GROUP  BY region, created_at::DATE;

Named WINDOW clause — avoid duplication

SELECT
    region, order_id, amount,
    SUM(amount)  OVER w   AS running_total,
    AVG(amount)  OVER w   AS running_avg,
    COUNT(*)     OVER w   AS running_count,
    FIRST_VALUE(amount) OVER w AS first_in_window
FROM   orders
WINDOW w AS (PARTITION BY region ORDER BY created_at ROWS UNBOUNDED PRECEDING);

Percentile and nth_value patterns

-- Continuous percentile (interpolated)
SELECT region,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount,
       PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY amount) AS p95_amount
FROM   orders
GROUP  BY region;

-- nth_value with frame boundary
SELECT order_id, region, amount,
       NTH_VALUE(amount, 2) OVER (
           PARTITION BY region
           ORDER BY amount DESC
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS second_highest
FROM   orders;

---

7. JSONB Operations

Core operators

-- -> returns JSONB child  (object key or array index)
SELECT payload -> 'user' -> 'id'  FROM events;          -- nested JSONB

-- ->> returns TEXT (use for comparisons and output)
SELECT payload ->> 'event_type'  FROM events;

-- #>  path as array → JSONB
SELECT payload #> '{user,address,city}'  FROM events;

-- #>> path as array → TEXT
SELECT payload #>> '{user,address,city}'  FROM events;

-- @> containment — uses GIN index
SELECT * FROM events WHERE payload @> '{"event_type": "purchase"}';
SELECT * FROM events WHERE payload @> '{"user": {"tier": "premium"}}';

-- ? key existence
SELECT * FROM events WHERE payload ? 'error_code';

-- ?| any key
SELECT * FROM events WHERE payload ?| ARRAY['error_code', 'warning_code'];

-- ?& all keys
SELECT * FROM events WHERE payload ?& ARRAY['user', 'event_type', 'ts'];

jsonb_each, jsonb_each_text — expand keys into rows

-- Flatten top-level keys into (key, value) pairs
SELECT e.event_id, kv.key, kv.value
FROM   events e,
       jsonb_each(e.payload) AS kv
WHERE  e.event_date = '2024-01-15';

jsonb_agg — aggregate rows back into JSON

-- Build a JSON array of objects grouped by region
SELECT region,
       jsonb_agg(
           jsonb_build_object(
               'order_id', order_id,
               'amount',   amount,
               'status',   status
           ) ORDER BY created_at DESC
       ) AS orders_json
FROM   orders
WHERE  created_at >= now() - INTERVAL '1 day'
GROUP  BY region;

jsonb_set and update patterns

-- Update a single nested key without replacing the whole document
UPDATE events
SET    payload = jsonb_set(payload, '{user, tier}', '"gold"', TRUE)
WHERE  event_id = 12345;

-- Remove a key
UPDATE events
SET    payload = payload - 'debug_info'
WHERE  event_date < '2024-01-01';

-- Concatenate / merge two JSONB objects (right wins on key conflict)
UPDATE events
SET    payload = payload || '{"processed": true, "version": 2}'
WHERE  event_type = 'import';

JSONB path queries (PostgreSQL 12+)

-- jsonpath: find events where any item in a cart array costs > 100
SELECT event_id
FROM   events
WHERE  payload @? '$.cart[*] ? (@.price > 100)';

-- jsonb_path_query — returns matching elements
SELECT event_id,
       jsonb_path_query_array(payload, '$.cart[*].sku') AS skus
FROM   events
WHERE  payload @? '$.cart[*]';

---

8. CTEs, WITH RECURSIVE, and LATERAL Joins

Basic CTE — readable decomposition

-- CTEs are inlined by default in PG 12+ (same as subquery)
WITH recent_orders AS (
    SELECT order_id, customer_id, amount, region
    FROM   orders
    WHERE  created_at >= now() - INTERVAL '30 days'
),
customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_30d
    FROM   recent_orders
    GROUP  BY customer_id
)
SELECT c.name, ct.total_30d
FROM   customers c
JOIN   customer_totals ct USING (customer_id)
WHERE  ct.total_30d > 10000
ORDER  BY ct.total_30d DESC;

MATERIALIZED CTE — force separate execution

-- Force materialisation when CTE is expensive and referenced multiple times
WITH MATERIALIZED daily_stats AS (
    SELECT event_date,
           COUNT(*)          AS event_count,
           SUM(payload->>'amount')::NUMERIC AS revenue
    FROM   events
    WHERE  event_date >= CURRENT_DATE - 90
    GROUP  BY event_date
)
SELECT d.event_date,
       d.event_count,
       d.revenue,
       AVG(d.revenue) OVER (ORDER BY d.event_date ROWS 6 PRECEDING) AS revenue_7d_avg
FROM   daily_stats d
ORDER  BY event_date;
-- Without MATERIALIZED, the aggregate runs twice; with it, runs once.

WITH RECURSIVE — hierarchical / graph traversal

-- Org chart: find all reports under a given manager
WITH RECURSIVE org_tree AS (
    -- Anchor: direct reports of manager 42
    SELECT employee_id, manager_id, name, 1 AS depth
    FROM   employees
    WHERE  manager_id = 42

    UNION ALL

    -- Recursive: one level deeper
    SELECT e.employee_id, e.manager_id, e.name, ot.depth + 1
    FROM   employees e
    JOIN   org_tree ot ON e.manager_id = ot.employee_id
    WHERE  ot.depth < 10   -- depth guard prevents infinite loops
)
SELECT * FROM org_tree ORDER BY depth, name;

-- Generate a calendar series (no recursive table needed with generate_series,
-- but shown for completeness)
WITH RECURSIVE date_series AS (
    SELECT '2024-01-01'::DATE AS dt
    UNION ALL
    SELECT dt + 1 FROM date_series WHERE dt < '2024-12-31'
)
SELECT dt FROM date_series;
-- Prefer generate_series() for dates; WITH RECURSIVE for graph/tree structures

LATERAL join — correlated subquery as table

-- Top-3 most recent orders per customer (efficient with index on customer_id, created_at)
SELECT c.customer_id, c.name, top_orders.*
FROM   customers c
CROSS  JOIN LATERAL (
    SELECT order_id, amount, created_at
    FROM   orders o
    WHERE  o.customer_id = c.customer_id
    ORDER  BY o.created_at DESC
    LIMIT  3
) AS top_orders;

-- LATERAL with function
SELECT u.user_id, stats.*
FROM   users u
CROSS  JOIN LATERAL compute_user_stats(u.user_id, now() - INTERVAL '30 days') AS stats;

---

9. Bulk Load Patterns

Pattern 1 — COPY into a permanent logged table (general purpose)

BEGIN;

-- Disable triggers if safe (e.g., no FK checks needed during load)
ALTER TABLE events DISABLE TRIGGER ALL;

COPY events (event_id, event_date, source, payload)
FROM '/data/events_batch.csv'
WITH (FORMAT CSV, HEADER TRUE);

ALTER TABLE events ENABLE TRIGGER ALL;

COMMIT;

Pattern 2 — UNLOGGED staging table trick

-- Step 1: create an unlogged staging copy (no WAL → ~3–5x faster writes)
CREATE UNLOGGED TABLE events_stage (LIKE events INCLUDING DEFAULTS);

-- Step 2: bulk-load into staging
COPY events_stage FROM '/data/events_batch.csv' WITH (FORMAT CSV, HEADER TRUE);

-- Step 3: validate / transform in staging
DELETE FROM events_stage WHERE event_id IS NULL OR event_date IS NULL;

-- Step 4: insert from staging into main table
INSERT INTO events SELECT * FROM events_stage
ON CONFLICT (event_id) DO NOTHING;

-- Step 5: clean up
DROP TABLE events_stage;
-- Note: events_stage is truncated to empty automatically if the server crashes

Pattern 3 — Disable indexes + COPY + rebuild

-- For initial historical loads of hundreds of millions of rows
-- Step 1: drop non-primary indexes (keep PK/UNIQUE if needed for uniqueness)
DROP INDEX CONCURRENTLY idx_events_created_brin;
DROP INDEX CONCURRENTLY idx_events_payload_gin;

-- Step 2: load data (now much faster without index maintenance)
COPY events FROM '/data/historical_events.csv' WITH (FORMAT CSV, HEADER TRUE);

-- Step 3: rebuild indexes (CONCURRENTLY avoids table lock)
CREATE INDEX CONCURRENTLY idx_events_created_brin
    ON events USING BRIN (created_at) WITH (pages_per_range = 32);
CREATE INDEX CONCURRENTLY idx_events_payload_gin
    ON events USING GIN (payload jsonb_path_ops);

-- Step 4: update statistics
ANALYZE events;

Pattern 4 — Session-level settings for maximum load throughput

-- Set in the loading session (reverts when session closes)
SET maintenance_work_mem = '2GB';        -- faster index builds
SET max_wal_size = '8GB';               -- reduce checkpoint frequency
SET synchronous_commit = off;            -- async WAL flush (risk: lose ~1s of data on crash)
SET wal_buffers = '64MB';               -- write-ahead log buffer

-- Then run COPY ...

Pattern 5 — pg_bulkload (extreme throughput, extension required)

# pg_bulkload bypasses shared_buffers and WAL; fastest possible ingestion
# Install: apt-get install pg_bulkload  or  pgxn install pg_bulkload

pg_bulkload \
  -d mydb \
  -U postgres \
  -i /data/events_batch.csv \
  -O events \
  -o "TYPE=CSV" \
  -o "SKIP=1" \
  -o "DELIMITER=," \
  -o "FILTER=events_filter_func"

-- After pg_bulkload, always rebuild indexes and run ANALYZE
REINDEX TABLE events;
ANALYZE events;

Partition-aware bulk load

-- For partitioned tables: load directly into the specific child partition
-- to avoid partition routing overhead
COPY events_2024_01 FROM '/data/events_2024_01.csv' WITH (FORMAT CSV, HEADER TRUE);
COPY events_2024_02 FROM '/data/events_2024_02.csv' WITH (FORMAT CSV, HEADER TRUE);

---

Anti-Patterns

Anti-pattern	Problem	Fix
SELECT * on wide partitioned table	Reads all partitions, all columns	Explicit column list + WHERE on partition key
WHERE DATE_TRUNC('month', ts) = '2024-01-01'	Function on column disables index	Use range: ts >= '2024-01-01' AND ts < '2024-02-01'
Autovacuum with default scale_factor on 100M+ row tables	Vacuum fires at 20M dead tuples; severe bloat	ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.01)
VACUUM FULL in production	Holds AccessExclusiveLock for hours	Use pg_repack extension for online defragmentation
COPY without a transaction	Partial load on error leaves dirty state	Wrap COPY in BEGIN/COMMIT
GIN index on every JSONB column	GIN indexes are 60–80% of table size; slow writes	Index only frequently-queried paths; use jsonb_path_ops for containment-only
UNLOGGED table as permanent store	Data lost on server crash, not replicated	Use only for staging/temp workloads
Recursive CTE without depth guard	Infinite loop on cyclic graph data	Add WHERE depth < N guard
Ignoring shared read in BUFFERS output	Misses I/O bottleneck; relies on warm cache	Tune shared_buffers; check effective_cache_size
NOT MATERIALIZED hint on multi-reference CTE	Expensive subquery runs multiple times	Use MATERIALIZED when CTE is referenced 2+ times and is costly

---

References to Consult When Needed

• PostgreSQL 16 Documentation — Table Partitioning
• PostgreSQL 16 Documentation — EXPLAIN
• PostgreSQL 16 Documentation — Populating a Database (bulk load)
• PostgreSQL 16 Documentation — Index Types
• PostgreSQL 16 Documentation — WITH Queries
• PostgreSQL 16 Documentation — JSON Types
• PostgreSQL 16 Documentation — Autovacuum
• pg_partman GitHub — partition management extension
• pganalyze — Understanding GIN Indexes
• EDB — 7 Best Practice Tips for PostgreSQL Bulk Data Loading
• Cybertec — PostgreSQL Bulk Loading
• Percona — Tuning Autovacuum in PostgreSQL
• Crunchy Data — Indexing JSONB in Postgres