Postgres for Data Engineers cheat sheet

COPY, upserts, partitioning, EXPLAIN, and the catalog queries you need to run Postgres as a serious pipeline source or sink.

SQL & DatabasesIntermediate6 sections

Bulk loading and unloading

\copy staging.events from 'events.csv' with (format csv, header true): Client-side COPY through psql: no server file access needed. Orders of magnitude faster than INSERT loops.
copy (select * from fct_orders where d >= '2026-01-01') to stdout with (format csv, header true): Stream query results out for extraction. Pair with \copy on the client to land a file.
insert into t select * from staging on conflict do nothing;: Idempotent append when reloading a batch that may partially exist.
truncate staging.events; -- then reload: TRUNCATE is transactional in Postgres — you can roll it back, unlike most databases.

insert into dim_user (id, email, updated_at) values ($1, $2, now()) on conflict (id) do update set email = excluded.email, updated_at = excluded.updated_at: The canonical Postgres upsert. `excluded` refers to the row that failed to insert.
on conflict (id) do update set ... where dim_user.updated_at < excluded.updated_at: Guard clause makes the upsert idempotent under out-of-order replays.
merge into tgt using src on tgt.id = src.id when matched then update set ... when not matched then insert ...;: MERGE landed in Postgres 15. Prefer ON CONFLICT for single-table upserts; MERGE for multi-action logic.

create table events (ts timestamptz, payload jsonb) partition by range (ts);: Declarative range partitioning. The parent holds no data itself.
create table events_2026_08 partition of events for values from ('2026-08-01') to ('2026-09-01');: One child per month is the common pattern. Automate creation ahead of time (pg_partman or a cron job).
alter table events detach partition events_2025_01 concurrently;: Cheap retention: detach then drop old partitions instead of DELETE. CONCURRENTLY avoids blocking queries.
select relname, pg_size_pretty(pg_total_relation_size(oid)) from pg_class where relname like 'events%';: Check partition sizes to verify pruning and spot skew.

explain (analyze, buffers) select ...;: ANALYZE runs the query for real timings; BUFFERS shows cache hits vs disk reads. Never trust plain EXPLAIN estimates alone.
select * from pg_stat_activity where state <> 'idle' order by query_start;: What is running right now. Look for long query_start and wait_event to find blockers.
select pg_terminate_backend(pid);: Kill a runaway query. pg_cancel_backend(pid) is the gentler option — cancels the query, keeps the session.
select * from pg_stat_statements order by total_exec_time desc limit 10;: Cumulative worst offenders. Requires the pg_stat_statements extension — enable it everywhere.

vacuum (analyze, verbose) fct_orders;: Reclaims dead tuples and refreshes stats. Autovacuum usually handles it — but heavy UPDATE/DELETE tables need tuning.
select relname, n_dead_tup, last_autovacuum from pg_stat_user_tables order by n_dead_tup desc;: Find bloat candidates. Millions of dead tuples means autovacuum is falling behind.
create index concurrently idx_orders_customer on fct_orders (customer_id);: CONCURRENTLY avoids locking writes. Mandatory on production tables; cannot run inside a transaction.
reindex index concurrently idx_orders_customer;: Rebuild a bloated index online (Postgres 12+).

select column_name, data_type from information_schema.columns where table_name = 'fct_orders';: Portable schema introspection — works on nearly every SQL database.
select pg_size_pretty(pg_database_size(current_database()));: Total database size.
select conname, pg_get_constraintdef(oid) from pg_constraint where conrelid = 'fct_orders'::regclass;: Every constraint on a table, with its full definition.
select * from pg_stat_replication;: Replication lag per replica — critical when a read replica feeds your extracts.

From DataLane — tutorials at/blog, practice SQL live in theplayground.