liminfoPostgreSQL Reference
Free reference guide: PostgreSQL Reference
About PostgreSQL Reference
The PostgreSQL Reference is a practical cheat sheet for database developers and administrators working with PostgreSQL. It covers the six most important areas of day-to-day PostgreSQL work: core DML queries (SELECT with JOIN and CTEs, INSERT with RETURNING, UPDATE, DELETE), index management (B-tree, GIN for JSONB, GiST for spatial data, unique indexes, and EXPLAIN ANALYZE), PL/pgSQL functions (CREATE FUNCTION, COALESCE, string_agg, generate_series, jsonb_build_object), trigger creation and management (BEFORE/AFTER triggers with NEW/OLD row access), logical replication (publications and subscriptions), and database configuration (SHOW, ALTER SYSTEM, VACUUM ANALYZE, pg_stat_activity).
This reference is particularly useful for backend developers writing complex queries, DBAs tuning PostgreSQL performance, and engineers setting up high-availability replication. PostgreSQL's rich feature set — including JSONB support, multiple index types, and native logical replication — makes a concise reference especially valuable when you need to recall the exact syntax for a GIN index or a PL/pgSQL function body without opening the full documentation.
Each entry includes a complete, copy-ready SQL example. The reference is organized to match real administrative tasks: building and indexing tables, writing stored procedures, setting up event-driven triggers, configuring streaming replication, and monitoring active connections and slow queries with system catalog views like pg_stat_activity and pg_stat_replication.
Key Features
- Core DML: SELECT with JOIN, CTE (WITH clause), INSERT RETURNING, UPDATE, and DELETE
- B-tree, GIN (JSONB/array), GiST (spatial/range), and unique index creation
- EXPLAIN ANALYZE for query execution plan analysis and index usage verification
- PL/pgSQL CREATE FUNCTION with dollar-quoting and return types
- Built-in functions: COALESCE, string_agg, generate_series, jsonb_build_object
- BEFORE/AFTER trigger creation with NEW/OLD row references and DROP TRIGGER
- Logical replication: CREATE PUBLICATION, CREATE SUBSCRIPTION, pg_stat_replication
- Configuration: SHOW, ALTER SYSTEM SET, VACUUM ANALYZE, pg_stat_activity monitoring
Frequently Asked Questions
What is a CTE (Common Table Expression) and when should I use it?
A CTE, written with the `WITH ... AS (...)` syntax, is a named temporary result set that you can reference in the main query. Use CTEs to break complex queries into readable steps, to reuse a subquery result multiple times, or for recursive queries. In PostgreSQL 12+, CTEs are inlined by default (the optimizer can push predicates into them), but you can use `WITH ... AS MATERIALIZED (...)` to force materialization.
What is the difference between GIN and GiST indexes in PostgreSQL?
GIN (Generalized Inverted Index) is optimized for values that contain multiple components, such as JSONB documents, arrays, and full-text search vectors. It is fast for queries that check element containment (`@>`, `?`, `@@`). GiST (Generalized Search Tree) is better for spatial data types (PostGIS geometries), range types, and nearest-neighbor searches. GIN indexes are faster to query but slower to build and update than GiST.
How do I use RETURNING to get the inserted row ID in PostgreSQL?
Add `RETURNING id` (or `RETURNING *`) to the end of an INSERT, UPDATE, or DELETE statement: `INSERT INTO users (name, email) VALUES ('John', 'john@example.com') RETURNING id;`. This is a PostgreSQL extension to standard SQL and eliminates the need for a separate SELECT query to retrieve auto-generated primary key values.
How do I create a trigger that automatically updates a timestamp column?
First create a PL/pgSQL function that returns TRIGGER and sets `NEW.updated_at = NOW()`. Then create a trigger with `CREATE TRIGGER update_timestamp BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_modified_column()`. The `NEW` keyword refers to the row being inserted or updated, while `OLD` refers to the previous row state in UPDATE and DELETE triggers.
How does logical replication differ from streaming replication in PostgreSQL?
Streaming replication (physical) copies the entire PostgreSQL cluster byte-by-byte and requires the replica to be on the same PostgreSQL major version. Logical replication (via CREATE PUBLICATION/SUBSCRIPTION) replicates individual tables and can replicate between different PostgreSQL versions or to external systems. Logical replication also allows selective table replication and is useful for zero-downtime major version upgrades.
How do I find and kill slow or blocking queries in PostgreSQL?
Use `SELECT pid, query, state, wait_event_type FROM pg_stat_activity WHERE state = 'active';` to see running queries. For blocking locks, join with `pg_locks`. To cancel a query gracefully use `SELECT pg_cancel_backend(pid)`, which sends SIGINT. To forcibly terminate use `SELECT pg_terminate_backend(pid)`, which sends SIGTERM. Always prefer cancel over terminate to allow the query to clean up properly.
What does VACUUM ANALYZE do and when should I run it?
VACUUM removes dead row versions left by UPDATE and DELETE operations to reclaim storage. ANALYZE updates the statistics used by the query planner to choose optimal execution plans. `VACUUM ANALYZE table_name` does both. PostgreSQL runs autovacuum automatically in the background, but you may want to run it manually after large bulk loads or before performance-critical operations. `VACUUM FULL` is more aggressive but locks the table exclusively.
How do I change PostgreSQL configuration settings without restarting?
Use `ALTER SYSTEM SET shared_buffers = '4GB';` to write the setting to `postgresql.auto.conf`. Then call `SELECT pg_reload_conf();` to apply settings that do not require a restart (like `work_mem`, `log_min_duration_statement`). Settings that require a restart (like `shared_buffers`, `max_connections`) will take effect on the next PostgreSQL restart. Use `SHOW setting_name` to verify the current active value.