PostgreSQL Cheatsheet — Complete Quick Reference (2026)

In this tutorial, you'll learn about PostgreSQL Cheatsheet. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

PostgreSQL is an advanced, open-source relational database with extensive support for complex queries, JSON, indexing strategies, and analytical functions.

psql Meta-Commands

Data Types

Index Types

CREATE INDEX idx_name ON table (column);
CREATE INDEX idx_gin ON table USING GIN (jsonb_col);
CREATE INDEX idx_brin ON table USING BRIN (created_at) WITH (pages_per_range = 32);

Window Functions

-- ROW_NUMBER, RANK, DENSE_RANK
SELECT name, dept, salary,
  ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank
FROM employees;

-- LAG / LEAD — access previous/next rows
SELECT date, amount,
  LAG(amount, 1) OVER (ORDER BY date) AS prev_amount,
  AVG(amount) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS ma_7d
FROM sales;

CTE (WITH Queries)

WITH regional_sales AS (
  SELECT region, SUM(amount) AS total
  FROM orders GROUP BY region
)
SELECT region, total FROM regional_sales WHERE total > 10000;

-- Recursive CTE
WITH RECURSIVE t(n) AS (
  VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 10
) SELECT n FROM t;

JSONB Operations

| Operator | Description | |----------|-------------| | -> 'key' | Get JSON field as JSON | | ->> 'key' | Get JSON field as text | | #> '{a,b}' | Path access as JSON | | #>> '{a,b}' | Path access as text | | @> '{"a":1}' | Contains (JSONB only) | | ? 'key' | Key exists | | ?| ARRAY['a','b'] | Any key exists | | ?& ARRAY['a','b'] | All keys exist | | || '{"c":3}' | Concatenate/merge | | - 'key' | Delete key |

SELECT data->>'name' AS name, data @> '{"active": true}' AS active
FROM users WHERE data ? 'email';
CREATE INDEX ON users USING GIN (data jsonb_path_ops);

Performance Tuning

EXPLAIN ANALYZE SELECT * FROM table;           -- see query plan
SET work_mem = '256MB';                        -- per-operation memory
SET effective_cache_size = '4GB';              -- OS cache estimate
VACUUM ANALYZE table;                          -- reclaim + stats
CLUSTER table USING idx_name;                  -- physical reorder
-- Key indexes: target WHERE, JOIN, ORDER BY columns

Must-Know Items

• VACUUM is automatic with autovacuum enabled (default on) — don't disable it
• Use JSONB not JSON — JSONB supports indexing, faster operators, no duplicate keys
• EXPLAIN ANALYZE reveals actual vs estimated row counts — look for sequential scans on large tables
• Partial indexes: CREATE INDEX ON t (col) WHERE condition — smaller, faster
• pg_stat_statements extension tracks query performance metrics
• Use COPY not INSERT for bulk data loading

{{< faq "What is the difference between JSON and JSONB in PostgreSQL?">}}JSON stores exact text copy; JSONB stores parsed binary format. JSONB supports indexing (GIN), has faster operators like @> and ?, removes duplicate keys, and normalizes whitespace. Always prefer JSONB unless you need to preserve key order or exact whitespace.{{< /faq >}}

See full PostgreSQL tutorials for advanced database patterns.