Top 85 PostgreSQL Interview Questions & Answers (2026)

PostgreSQL (often called Postgres) is a powerful, open-source object-relational database management system (ORDBMS) with over 35 years of active development. It is known for its standards compliance (SQL:2016), extensibility, and robustness. PostgreSQL supports advanced data types (JSON, arrays, hstore, UUID, geometric), full-text search, custom functions, stored procedures, triggers, and foreign data wrappers. It uses MVCC (Multi-Version Concurrency Control) to handle concurrent access without read locks. Popular in web applications, analytics, and geospatial systems (via the PostGIS extension), PostgreSQL is the default database for many frameworks including Rails and Django.

PostgreSQL is a fully ACID-compliant, object-relational database with stronger standards compliance, more advanced SQL features, better support for complex queries, and richer data types (arrays, JSON, hstore, custom types). MySQL is simpler, historically faster for read-heavy workloads with MyISAM, and more widely deployed in LAMP stacks. Key differences: PostgreSQL supports partial indexes, expression indexes, and advanced join algorithms; it treats NULL consistently per SQL standard; it has no implicit data type coercions that silently truncate data. MySQL has a larger market share in legacy web apps. PostgreSQL is generally preferred for data integrity, analytics, and applications requiring complex queries or custom extensions.

In PostgreSQL, a database is an isolated namespace that contains schemas, tables, views, functions, sequences, and other objects. A single PostgreSQL server instance (cluster) can host multiple databases. Each database is completely isolated — you cannot directly join tables from different databases. Databases are created with CREATE DATABASE dbname; and listed with \l in psql or SELECT datname FROM pg_database;. The default databases created during installation are postgres (admin default), template0 (pristine template), and template1 (customizable template used when creating new databases).

A schema is a named namespace within a database that contains tables, views, indexes, sequences, and functions. It provides a logical grouping and avoids naming conflicts. The default schema is public. Create with: CREATE SCHEMA myschema;. Reference objects with: myschema.tablename. The search_path variable determines which schemas are searched when a table is referenced without a schema prefix: SET search_path TO myschema, public;. Common uses: multi-tenant applications (one schema per tenant), separating application data from audit tables, or organizing microservice database objects. Schemas are listed with \dn in psql.

psql is the interactive terminal client for PostgreSQL. Connect with: psql -h hostname -p 5432 -U username -d dbname. If running locally as the postgres user: psql (connects to the default database). Key psql meta-commands: \l (list databases), \c dbname (connect to database), \dt (list tables), \d tablename (describe table), \dn (list schemas), \df (list functions), \du (list users/roles), \q (quit), \? (help for meta-commands), \h SELECT (SQL command help). The connection string format: postgresql://user:password@host:5432/dbname.

PostgreSQL has a rich type system. Numeric: integer (INT4), bigint (INT8), smallint, numeric(p,s)/decimal (exact), real (float4), double precision (float8), serial/bigserial (auto-increment). Text: varchar(n), char(n), text (unlimited). Boolean: boolean (true/false). Date/Time: date, time, timestamp, timestamptz (with timezone — recommended), interval. Unique to Postgres: uuid, json/jsonb, array, hstore, inet/cidr (network addresses), tsvector (full-text search). Use text over varchar — they perform identically in Postgres.

Use the CREATE TABLE statement. Example: CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email TEXT UNIQUE NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW());. Key constraints: PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT value, CHECK (condition), REFERENCES other_table(column) (foreign key). Use SERIAL or BIGSERIAL for auto-incrementing IDs, or the modern GENERATED ALWAYS AS IDENTITY syntax. IF NOT EXISTS prevents errors: CREATE TABLE IF NOT EXISTS users (...). Describe the created table with \d users in psql.

Use the INSERT INTO statement. Single row: INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');. Multiple rows: INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com'), ('Carol', 'carol@example.com');. Return the inserted row: INSERT INTO users (name) VALUES ('Dave') RETURNING id, name; — the RETURNING clause is a PostgreSQL extension very useful for getting generated IDs. Insert from a query: INSERT INTO archive SELECT * FROM users WHERE created_at < '2023-01-01';. Use ON CONFLICT for upsert behavior (covered under intermediate topics).

The SELECT statement retrieves data. Basic form: SELECT column1, column2 FROM tablename WHERE condition ORDER BY column DESC LIMIT 10;. SELECT * retrieves all columns (avoid in production for performance and clarity). Filter with WHERE: operators =, !=, <, >, BETWEEN, IN (list), LIKE, ILIKE (case-insensitive), IS NULL, IS NOT NULL. Combine conditions with AND, OR, NOT. Aliases: SELECT name AS full_name. Computed columns: SELECT price * quantity AS total. Distinct values: SELECT DISTINCT country FROM users.

A PRIMARY KEY is a column (or combination of columns) that uniquely identifies each row in a table. It automatically enforces NOT NULL and UNIQUE constraints. Every table should have a primary key for data integrity and efficient lookups. PostgreSQL automatically creates a unique B-tree index on the primary key column(s). Common patterns: id SERIAL PRIMARY KEY (auto-incrementing integer), id UUID PRIMARY KEY DEFAULT gen_random_uuid() (universally unique ID). Composite primary keys: PRIMARY KEY (user_id, role_id) at the table level. Choose BIGSERIAL (64-bit) over SERIAL (32-bit) for tables expected to have more than 2 billion rows.

A FOREIGN KEY enforces referential integrity between two tables — it ensures that a value in one table corresponds to an existing value in another. Syntax: REFERENCES parent_table(column). Example: CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id));. Actions on referenced row deletion/update: ON DELETE CASCADE (delete child rows), ON DELETE SET NULL (null the FK), ON DELETE RESTRICT (default — prevent deletion), ON DELETE NO ACTION (deferred check). Foreign keys can cause performance issues if the referencing column isn't indexed — always add an index on FK columns. Disable FK checks temporarily (per session) is not directly supported; you can use SET session_replication_role = replica;.

WHERE filters individual rows before grouping — it cannot reference aggregate functions. HAVING filters groups after GROUP BY is applied — it can reference aggregate functions. Example: SELECT department, COUNT(*) FROM employees WHERE salary > 50000 GROUP BY department HAVING COUNT(*) > 5; — WHERE filters individual employees with salary > 50k; HAVING then filters departments with more than 5 such employees. Using an aggregate in WHERE causes an error; use HAVING instead. For performance, always prefer WHERE to filter rows as early as possible (before grouping), and use HAVING only when filtering on aggregated results is necessary.

Aggregate functions compute a single result from a set of rows. Common ones: COUNT(*) (all rows), COUNT(col) (non-NULL values), COUNT(DISTINCT col), SUM(col), AVG(col), MIN(col), MAX(col). PostgreSQL-specific: STRING_AGG(col, ',') (concatenate strings), ARRAY_AGG(col) (collect into array), JSON_AGG(col) (collect into JSON array), BOOL_AND(col), BOOL_OR(col). Used with GROUP BY: SELECT dept, AVG(salary) FROM employees GROUP BY dept;. FILTER clause for conditional aggregation: COUNT(*) FILTER (WHERE status = 'active').

GROUP BY groups rows that have the same values in specified columns, typically used with aggregate functions to summarize data. SELECT department, COUNT(*), AVG(salary) FROM employees GROUP BY department;. All non-aggregate columns in SELECT must appear in GROUP BY. GROUP BY 1, 2 groups by the first and second SELECT columns (positional). GROUP BY ROLLUP(a, b) generates subtotals. GROUP BY CUBE(a, b) generates all combinations. GROUPING SETS allows specifying exactly which groupings to compute. Filter groups with HAVING. For counting distinct combinations: SELECT city, country, COUNT(*) FROM users GROUP BY city, country ORDER BY COUNT(*) DESC;.

Use ORDER BY at the end of a query. ORDER BY column ASC sorts ascending (default); ORDER BY column DESC sorts descending. Multiple columns: ORDER BY last_name ASC, first_name ASC. Sort by expression: ORDER BY LENGTH(name). Sort by alias: SELECT name AS n FROM users ORDER BY n. NULLS FIRST / NULLS LAST controls where NULLs appear (PostgreSQL-specific extension): ORDER BY score DESC NULLS LAST. Without ORDER BY, row order is not guaranteed — never rely on implicit ordering. For pagination, combine with LIMIT and OFFSET: ORDER BY id LIMIT 20 OFFSET 40.

JOINs combine rows from two or more tables based on a related column. Types: INNER JOIN (only matching rows in both tables), LEFT JOIN (all rows from left + matching from right; NULL for non-matches), RIGHT JOIN (all from right + matching from left), FULL OUTER JOIN (all rows from both, NULLs where no match), CROSS JOIN (Cartesian product — every combination). Example: SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id;. NATURAL JOIN joins on all identically-named columns (avoid — fragile). SELF JOIN: join a table to itself using aliases. Always use explicit ON conditions for clarity and correctness.

INNER JOIN returns only rows where the join condition matches in both tables. Rows without a match in either table are excluded. LEFT JOIN (also LEFT OUTER JOIN) returns all rows from the left (first) table, plus matching rows from the right table. Where there is no match, the right table's columns are filled with NULL. Use LEFT JOIN when you want to include records from the left table regardless of whether a related record exists in the right table. Example: finding all users even those with no orders: SELECT u.name, o.id FROM users u LEFT JOIN orders o ON u.id = o.user_id; — users with no orders appear with o.id = NULL.

Use the UPDATE statement. Basic form: UPDATE tablename SET column1 = value1, column2 = value2 WHERE condition;. Always include a WHERE clause — omitting it updates every row. Example: UPDATE users SET email = 'new@example.com' WHERE id = 42;. Update with expressions: UPDATE products SET price = price * 1.1 WHERE category = 'electronics';. Update from another table (JOIN): UPDATE orders SET status = 'shipped' FROM shipments WHERE orders.id = shipments.order_id;. Return updated rows: UPDATE users SET active = false WHERE last_login < NOW() - INTERVAL '1 year' RETURNING id, email;.

Use the DELETE FROM statement. DELETE FROM tablename WHERE condition;. Always include WHERE — DELETE FROM tablename; deletes all rows (use TRUNCATE for that instead, which is faster). Delete with RETURNING: DELETE FROM sessions WHERE expires_at < NOW() RETURNING session_id;. Delete using a join (DELETE...USING): DELETE FROM orders USING users WHERE orders.user_id = users.id AND users.is_deleted = true;. TRUNCATE: TRUNCATE tablename; — removes all rows much faster than DELETE (doesn't scan rows), resets sequences if RESTART IDENTITY is specified. TRUNCATE is not reversible without a transaction.

LIMIT restricts the number of rows returned; OFFSET skips a number of rows before starting to return rows. Used for pagination: SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40; returns rows 41-60 (page 3 if page size is 20). PostgreSQL also supports FETCH FIRST 20 ROWS ONLY (SQL standard). OFFSET pagination has a major performance problem: OFFSET 10000 still scans and discards 10,000 rows. For large datasets, use keyset/cursor pagination instead: WHERE id > last_seen_id ORDER BY id LIMIT 20 — this is O(1) using the index and does not degrade at high offsets.

A view is a named query stored in the database that can be referenced like a table. It does not store data itself (unless it's a materialized view). Views simplify complex queries, encapsulate business logic, and control data access (show only certain columns to certain users). Create: CREATE VIEW active_users AS SELECT id, name, email FROM users WHERE is_active = true;. Query: SELECT * FROM active_users;. Update a view: CREATE OR REPLACE VIEW active_users AS .... Simple views (single-table, no aggregation, no DISTINCT) are updatable — you can INSERT/UPDATE/DELETE through them. Complex views require a RULE or INSTEAD OF trigger for updates.

A sequence is a database object that generates unique numeric values, typically used for auto-incrementing primary keys. SERIAL creates an implicit sequence; you can also create one explicitly: CREATE SEQUENCE my_seq START 1 INCREMENT 1;. Get next value: SELECT nextval('my_seq');. Current value: SELECT currval('my_seq'); (only valid after calling nextval in the same session). Reset: ALTER SEQUENCE my_seq RESTART WITH 1;. Sequences are non-transactional — even if a transaction rolls back, the sequence value is not rolled back, which means gaps in IDs are normal and expected. This prevents bottlenecks from locking. The modern alternative to SERIAL is GENERATED ALWAYS AS IDENTITY.

In PostgreSQL, all three store character strings but differ in behavior: CHAR(n) (character) stores exactly n characters — shorter strings are padded with spaces. VARCHAR(n) stores up to n characters — no padding, but enforces a length limit. TEXT stores unlimited length strings with no length limit. Key PostgreSQL fact: VARCHAR and TEXT are stored identically internally — there is no performance difference between them. CHAR is slightly different due to blank-padding behavior. Best practice: use TEXT for most string data and add a CHECK (length(col) <= n) constraint if you need a length limit — it gives a cleaner error message than VARCHAR truncation errors in other DBs. PostgreSQL never silently truncates data.

NULL represents the absence of a value — it is not zero, not empty string, not false. NULL follows three-valued logic: any comparison with NULL yields NULL (unknown), not TRUE or FALSE. NULL = NULL is NULL (not TRUE) — use IS NULL or IS NOT NULL to check for NULLs. Aggregate functions ignore NULLs (e.g., AVG skips NULL values). COALESCE(a, b, c) returns the first non-NULL argument — essential for providing defaults. NULLIF(a, b) returns NULL if a equals b (useful to avoid division by zero: NULLIF(divisor, 0)). NOT IN with a list containing NULL can return no rows — a common gotcha. Constrain with NOT NULL at column level.

LIKE performs case-sensitive pattern matching. ILIKE is PostgreSQL's case-insensitive variant. Pattern characters: % matches any sequence of characters (including empty), _ matches exactly one character. Examples: WHERE name LIKE 'A%' (starts with A), WHERE email LIKE '%@gmail.com' (ends with), WHERE code LIKE 'B_3' (B, any char, 3). For literal % or _, escape them: LIKE '50\%' ESCAPE '\' . Performance: LIKE with a leading wildcard (LIKE '%text') cannot use a B-tree index and requires a full scan. For prefix searches (LIKE 'text%'), a standard index works. For arbitrary pattern matching, use pg_trgm extension with a GIN/GiST index.

Constraints enforce rules on data to maintain integrity. Types: NOT NULL (column cannot be NULL), UNIQUE (all values in column must be distinct; NULLs are not considered duplicates — multiple NULLs allowed), PRIMARY KEY (NOT NULL + UNIQUE — identifies each row), FOREIGN KEY (enforces referential integrity to another table), CHECK (arbitrary boolean condition: CHECK (age >= 18), CHECK (price > 0)), EXCLUSION (ensures no two rows conflict based on operators — used for time ranges, e.g., no overlapping reservations). Constraints can be column-level or table-level. Name constraints for clear error messages: CONSTRAINT chk_age CHECK (age >= 0).

Use ALTER TABLE. Add column: ALTER TABLE users ADD COLUMN phone TEXT;. Add with default and constraint: ALTER TABLE users ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;. In PostgreSQL 11+, adding a column with a constant DEFAULT is instant (no table rewrite). Drop column: ALTER TABLE users DROP COLUMN phone;. Drop and cascade to dependent objects: ALTER TABLE users DROP COLUMN phone CASCADE;. Rename column: ALTER TABLE users RENAME COLUMN old_name TO new_name;. Change type: ALTER TABLE users ALTER COLUMN age TYPE BIGINT; — may require a USING clause for non-trivial conversions. Dropping columns doesn't immediately reclaim disk space — run VACUUM FULL afterward if needed.

TRUNCATE removes all rows from a table instantly by deallocating the data pages, making it much faster than DELETE for large tables. Key differences: TRUNCATE does not scan individual rows — it's an O(1) operation regardless of table size. TRUNCATE resets sequences if RESTART IDENTITY is specified. TRUNCATE fires BEFORE/AFTER STATEMENT triggers (not row-level triggers). TRUNCATE is transactional in PostgreSQL (unlike MySQL) — it can be rolled back. DELETE is fully logged row-by-row, supports WHERE filtering, fires row-level triggers, and does not reset sequences. For removing all data from large tables in a script, prefer TRUNCATE. For removing specific rows or when you need RETURNING, use DELETE.

The RETURNING clause is a PostgreSQL extension to INSERT, UPDATE, and DELETE statements that returns data from the affected rows — avoiding a separate SELECT query. Example: INSERT INTO users (name, email) VALUES ('Alice', 'a@x.com') RETURNING id; returns the auto-generated ID immediately. UPDATE products SET price = price * 1.1 WHERE id = 5 RETURNING id, price; returns the new price. DELETE FROM sessions WHERE expires_at < NOW() RETURNING session_id;. RETURNING can return any column or expression from the affected rows. This is particularly useful in ORMs, application code, and CTEs (with data-modifying CTEs using WITH ... INSERT ... RETURNING).

A transaction is a sequence of operations treated as a single atomic unit — either all succeed or none do. PostgreSQL is fully ACID-compliant. Start a transaction: BEGIN;. Commit (make permanent): COMMIT;. Rollback (undo all changes): ROLLBACK;. Every individual SQL statement in PostgreSQL runs in an implicit single-statement transaction if not wrapped in an explicit BEGIN...COMMIT. Savepoints allow partial rollbacks within a transaction: SAVEPOINT sp1; then ROLLBACK TO sp1;. PostgreSQL never has dirty reads — even uncommitted transactions don't see each other's changes by default (READ COMMITTED isolation). Transactions are essential for maintaining data consistency across multiple related operations.

An index is a data structure that speeds up query lookups at the cost of additional storage and slower writes. PostgreSQL automatically creates indexes for PRIMARY KEY and UNIQUE constraints. Create manually: CREATE INDEX idx_users_email ON users(email);. The default index type is B-tree — suitable for equality and range queries, sorting, and most use cases. Other types: Hash (equality only), GIN (full-text search, arrays, JSONB), GiST (geometric, full-text), BRIN (very large tables with naturally ordered data). Indexes slow down INSERT/UPDATE/DELETE operations. Use EXPLAIN ANALYZE to verify an index is being used. List indexes: \di in psql or query pg_indexes.

Create with CREATE INDEX. Basic: CREATE INDEX idx_name ON tablename(column);. Multi-column: CREATE INDEX idx_name ON tablename(col1, col2);. Unique index: CREATE UNIQUE INDEX idx_name ON tablename(column);. Partial index: CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;. Expression index: CREATE INDEX idx_lower_email ON users(LOWER(email));. Concurrent creation (no table lock): CREATE INDEX CONCURRENTLY idx_name ON tablename(column); — takes longer but doesn't block reads/writes. Drop: DROP INDEX idx_name;. Rename: ALTER INDEX old_name RENAME TO new_name;. Always use CONCURRENTLY in production to avoid locking.

EXPLAIN displays the query execution plan that the PostgreSQL query planner will use for a query. EXPLAIN SELECT * FROM users WHERE email = 'a@b.com'; shows the plan without executing. EXPLAIN ANALYZE executes the query and shows actual timings alongside estimated ones — essential for diagnosing performance. Key plan nodes: Seq Scan (full table scan), Index Scan (uses index), Index Only Scan (data from index only — fastest), Bitmap Heap Scan (batch index+heap reads), Hash Join/Merge Join/Nested Loop (join algorithms). Look at cost, rows estimates vs actuals, and where estimates are far off (stale statistics). Run ANALYZE tablename to update statistics.

SELECT DISTINCT returns only unique rows, eliminating duplicates. SELECT DISTINCT country FROM users; returns each country once. SELECT DISTINCT col1, col2 deduplicates based on the combination of both columns. DISTINCT ON is a PostgreSQL-specific extension: SELECT DISTINCT ON (user_id) * FROM orders ORDER BY user_id, created_at DESC; — returns one row per user_id, specifically the most recent order. The ORDER BY must start with the DISTINCT ON column(s). DISTINCT requires sorting or hashing all rows, which can be expensive — a GROUP BY or a subquery with ROW_NUMBER() is sometimes faster. Use COUNT(DISTINCT col) to count unique values.

UNION combines results from two SELECT statements and removes duplicate rows (implicit DISTINCT). UNION ALL combines results and keeps all rows including duplicates. Both require the same number of columns and compatible data types. Example: SELECT name FROM current_employees UNION SELECT name FROM former_employees; — returns unique names. With UNION ALL: all names including people who were employees twice. Performance: UNION ALL is significantly faster because it doesn't need to sort and deduplicate. Always prefer UNION ALL unless you specifically need deduplication. INTERSECT returns rows in both; EXCEPT returns rows in the first but not the second.

A subquery (inner query or nested query) is a query nested inside another query. It can appear in SELECT, FROM, WHERE, or HAVING clauses. Scalar subquery: returns one value — SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count FROM users u;. Row subquery: returns one row — WHERE (col1, col2) = (SELECT a, b FROM ...). Table subquery (derived table): in FROM clause — SELECT * FROM (SELECT * FROM users WHERE active = true) AS active_users;. Correlated subquery: references outer query columns — re-executed for each row (often replaced by JOIN or CTE for performance). EXISTS, NOT EXISTS, IN, NOT IN, ANY, ALL operators work with subqueries.

COALESCE(val1, val2, ..., valN) returns the first non-NULL value in its argument list. It is the SQL standard equivalent of a null-safe "or default" operation. Example: SELECT COALESCE(nickname, first_name, 'Anonymous') FROM users; — uses nickname if not null, else first_name, else the literal 'Anonymous'. Useful for: providing default values, handling optional fields, avoiding NULL in output. COALESCE(SUM(col), 0) returns 0 instead of NULL when no rows match. It evaluates arguments left to right and stops at the first non-NULL. It's equivalent to CASE WHEN val1 IS NOT NULL THEN val1 WHEN val2 IS NOT NULL THEN val2 ... END but much more concise.

PostgreSQL has rich string functions: LENGTH(str) / CHAR_LENGTH(str) (length in characters), UPPER(str) / LOWER(str), TRIM(str) / LTRIM / RTRIM (remove whitespace), SUBSTRING(str FROM pos FOR len) or SUBSTR(str, pos, len), POSITION(substr IN str) (find position), REPLACE(str, from, to), CONCAT(str1, str2, ...) or the || operator, LPAD(str, len, fill) / RPAD (pad string), SPLIT_PART(str, delimiter, n) (split and pick part), REGEXP_REPLACE(str, pattern, replacement), REGEXP_MATCH(str, pattern), INITCAP(str) (title case), MD5(str) (hash), FORMAT('Hello %s, you are %s years old', name, age).

PostgreSQL's date/time capabilities are excellent. Current time: NOW() (transaction time, with timezone), CURRENT_TIMESTAMP (same as NOW), CLOCK_TIMESTAMP() (actual wall-clock time), CURRENT_DATE, CURRENT_TIME. Extract parts: EXTRACT(YEAR FROM timestamp), DATE_PART('month', timestamp). Arithmetic: NOW() - INTERVAL '7 days', date1 - date2 (returns interval). Truncate: DATE_TRUNC('month', timestamp) — truncates to start of month. Format: TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI'). Parse: TO_TIMESTAMP('2024-01-15', 'YYYY-MM-DD'). Age: AGE(timestamp) returns interval from date to now. Always store timestamps as TIMESTAMPTZ (with timezone) to avoid timezone confusion.

In PostgreSQL, NOW() and CURRENT_TIMESTAMP are effectively equivalent — both return the current date and time with timezone at the start of the current transaction. This means they return the same value throughout a transaction, even if the transaction takes minutes. This is important for consistency: all rows inserted in one transaction get the same timestamp. CLOCK_TIMESTAMP() returns the actual current wall-clock time at the moment it is called, changing throughout the transaction — use it when you need the true elapsed time. STATEMENT_TIMESTAMP() returns the time at the start of the current statement. TIMEOFDAY() returns wall-clock time as text.

CASE provides conditional logic in SQL queries. Searched CASE: CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default_result END. Example: SELECT name, CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'C' END AS grade FROM students;. Simple CASE: CASE column WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE default END. Use in ORDER BY: ORDER BY CASE status WHEN 'urgent' THEN 1 WHEN 'normal' THEN 2 ELSE 3 END. Use in aggregates: SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END). CASE always returns a single value and can be used anywhere an expression is valid.

A CTE (Common Table Expression), defined with the WITH clause, is a named temporary result set that exists only within the query. CTEs improve readability by naming intermediate results. Syntax: WITH active_users AS (SELECT * FROM users WHERE is_active = true) SELECT * FROM active_users WHERE age > 18;. Multiple CTEs: WITH cte1 AS (...), cte2 AS (...) SELECT .... Recursive CTEs: use WITH RECURSIVE to traverse hierarchical data (e.g., org charts, category trees). Data-modifying CTEs: WITH deleted AS (DELETE FROM sessions WHERE expired RETURNING id) INSERT INTO audit_log SELECT id, NOW() FROM deleted;. In PostgreSQL 12+, the planner can inline CTEs as subqueries (optimization fence was removed).

Window functions perform calculations across a set of rows related to the current row (a "window") without collapsing rows like GROUP BY. Syntax: function() OVER (PARTITION BY col ORDER BY col ROWS/RANGE frame). Common functions: ROW_NUMBER() (unique row number per partition), RANK() (rank with gaps for ties), DENSE_RANK() (rank without gaps), NTILE(n) (divide into n buckets), LAG(col, n)/LEAD(col, n) (access previous/next rows), FIRST_VALUE(col)/LAST_VALUE(col), SUM/AVG/COUNT() OVER (...) (running totals). Example: SELECT name, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as rank FROM employees;

A partial index is an index built on a subset of rows defined by a WHERE clause. This makes the index smaller, faster to update, and more selective. Example: CREATE INDEX idx_active_users ON users(email) WHERE is_active = true; — only indexes active users; queries with WHERE is_active = true AND email = 'x' use this small index. CREATE INDEX idx_unprocessed ON jobs(created_at) WHERE status = 'pending'; — useful when most rows are processed and only a small fraction are active. Partial indexes save storage and are often 10-100x smaller than full indexes for skewed data distributions. The planner uses a partial index only when the query's WHERE clause implies the index predicate.

An expression index (also called a functional index) is built on the result of a function or expression applied to a column, rather than the column value directly. Example: CREATE INDEX idx_lower_email ON users(LOWER(email)); allows case-insensitive lookups: WHERE LOWER(email) = LOWER('User@Example.COM') — PostgreSQL uses this index. CREATE INDEX idx_year ON orders(EXTRACT(YEAR FROM created_at)); for year-based queries. JSON field index: CREATE INDEX idx_city ON profiles((data->>'city'));. The query's WHERE clause must use the exact same expression as the index for the planner to use it. Expression indexes are invaluable for queries that transform data before comparing.

MVCC (Multi-Version Concurrency Control) is PostgreSQL's mechanism for handling concurrent access without read locks. Instead of locking rows when they are read, PostgreSQL keeps multiple versions of each row. Each transaction sees a consistent snapshot of the database from the time it started — readers don't block writers, writers don't block readers. Row versions are stored as heap tuples with xmin (transaction that created it) and xmax (transaction that deleted/updated it) system columns. Old row versions become "dead tuples" after the transactions that need them finish. VACUUM reclaims space from dead tuples. MVCC enables high concurrency but requires periodic vacuuming to prevent table bloat and transaction ID wraparound.

PostgreSQL supports four transaction isolation levels (SQL standard). READ UNCOMMITTED: PostgreSQL treats this as READ COMMITTED — dirty reads never occur. READ COMMITTED (default): a query sees only committed data at the moment the query started; different statements in the same transaction may see different data. REPEATABLE READ: all queries in a transaction see data as of the transaction start; prevents non-repeatable reads; may get serialization errors. SERIALIZABLE: strictest level — transactions appear to execute sequentially; uses SSI (Serializable Snapshot Isolation) in PostgreSQL; may get serialization failures requiring retry. Set per transaction: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;. Choose the lowest isolation level that satisfies correctness requirements.

VACUUM reclaims disk space occupied by dead tuples (rows deleted or updated under MVCC). VACUUM tablename; marks space as reusable (but doesn't return to OS). VACUUM FULL tablename; rewrites the entire table to reclaim disk space — requires an exclusive lock. VACUUM FREEZE prevents transaction ID wraparound — critical for long-lived databases. ANALYZE collects statistics about table data distribution and stores them in pg_statistic — the query planner uses these to choose optimal plans. ANALYZE tablename; updates statistics. VACUUM ANALYZE does both in one pass. autovacuum is the background daemon that runs VACUUM and ANALYZE automatically — never disable it in production.

Autovacuum is a background daemon that automatically runs VACUUM and ANALYZE on tables when needed. It monitors table activity via pg_stat_user_tables and triggers when dead tuple counts or stale statistics reach thresholds. Key configuration parameters: autovacuum_vacuum_threshold (50 dead tuples before considering vacuum), autovacuum_vacuum_scale_factor (0.2 = 20% of table size), autovacuum_analyze_threshold, autovacuum_analyze_scale_factor. For large, high-churn tables, reduce scale factors: ALTER TABLE big_table SET (autovacuum_vacuum_scale_factor = 0.01);. Monitor autovacuum: SELECT relname, last_vacuum, last_autovacuum, n_dead_tup FROM pg_stat_user_tables;. Never disable autovacuum — it prevents transaction ID wraparound (a catastrophic failure mode).

A materialized view stores the result of a query physically on disk, unlike a regular view which runs the query on every access. This makes reads very fast for expensive queries. Create: CREATE MATERIALIZED VIEW monthly_sales AS SELECT DATE_TRUNC('month', order_date) AS month, SUM(total) FROM orders GROUP BY 1;. Query it like a table: SELECT * FROM monthly_sales;. Refresh the data: REFRESH MATERIALIZED VIEW monthly_sales; — blocks reads during refresh. Non-blocking refresh: REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales; — requires a unique index on the view. Materialized views are ideal for complex analytical queries, dashboards, and pre-aggregated reports that can tolerate slightly stale data. Schedule refreshes with pg_cron or application-level jobs.

PostgreSQL supports two JSON storage types. JSON stores JSON as text, preserving the original format including whitespace and duplicate keys — it validates JSON syntax but doesn't parse deeply. JSONB stores JSON in a decomposed binary format — it parses and validates fully, removes whitespace, removes duplicate keys (last value wins), and reorders keys. Key advantages of JSONB: supports indexing (GIN indexes), has more operators, and is much faster for querying/filtering. Query operators: -> (get JSON object), ->> (get as text), #> (path access), @> (contains), <@ (contained by), ? (key exists). Always prefer JSONB over JSON unless you specifically need to preserve insertion order or duplicate keys.

JSONB columns can be indexed using GIN (Generalized Inverted Index) or expression indexes. Full GIN index: CREATE INDEX idx_gin ON table USING GIN (jsonb_column); — supports @>, ?, ?|, ?& operators. GIN with jsonb_path_ops: CREATE INDEX idx_gin ON table USING GIN (jsonb_col jsonb_path_ops); — smaller, faster for @> queries only. Expression index on a specific field: CREATE INDEX idx_city ON profiles((data->>'city')); — used when querying a specific key frequently. CREATE INDEX ON events((data->'user'->'id')); for nested access. Expression indexes are smaller and more selective than full GIN indexes when only one or few fields are queried.

ON CONFLICT (available since PostgreSQL 9.5) enables upsert behavior — insert a row, or take action if a conflict (UNIQUE/PRIMARY KEY violation) occurs. ON CONFLICT DO NOTHING: silently ignore duplicate inserts. ON CONFLICT (column) DO UPDATE SET col = EXCLUDED.col: update the existing row with the values that would have been inserted (accessed via the EXCLUDED pseudo-table). Example: INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name, updated_at = NOW();. Specify the conflict target (column or constraint name) when multiple unique constraints exist. This is atomic — no race condition between check and insert.

A trigger is a function that is automatically called when a specified event (INSERT, UPDATE, DELETE, TRUNCATE) occurs on a table or view. Triggers are created in two steps: (1) create the trigger function that returns TRIGGER: uses NEW (new row data) and OLD (old row data). (2) attach it with CREATE TRIGGER. Timing: BEFORE (can modify NEW), AFTER (sees committed data), INSTEAD OF (for views). Level: FOR EACH ROW or FOR EACH STATEMENT. Example: auto-update updated_at: create a function that sets NEW.updated_at = NOW() and attach as BEFORE UPDATE trigger. Triggers enable audit logging, enforcing complex constraints, and maintaining derived data.

PostgreSQL supports both functions and stored procedures (since v11). Functions are created with CREATE FUNCTION, return a value (or setof records), and can be called in SQL expressions. They support PL/pgSQL, SQL, Python, and other languages. Stored procedures (CREATE PROCEDURE) can execute transaction commands (COMMIT/ROLLBACK within them) — functions cannot. Functions are called with SELECT func(); procedures with CALL proc(). PL/pgSQL is the most common language: it supports variables, loops, conditionals, cursors, and exception handling. Use LANGUAGE SQL for simple set-returning functions; LANGUAGE plpgsql for procedural logic; LANGUAGE plpython3u for Python logic.

pg_stat_activity is a system view showing one row per server process, displaying information about current database connections and queries. Key columns: pid (process ID), usename (user), datname (database), state (active/idle/idle in transaction), query (current or last query), query_start (when query started), wait_event_type/wait_event (what it's waiting for), application_name, client_addr. Common uses: find long-running queries: SELECT pid, now() - query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC;. Terminate a query: SELECT pg_cancel_backend(pid); (graceful) or pg_terminate_backend(pid); (forceful).

Find long-running queries: SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > INTERVAL '5 minutes' AND state = 'active';. Cancel a query (sends SIGINT, allows graceful cleanup): SELECT pg_cancel_backend(pid);. Terminate the connection (sends SIGTERM): SELECT pg_terminate_backend(pid);. Kill all idle-in-transaction connections older than 5 minutes: SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND query_start < NOW() - INTERVAL '5 minutes';. Set idle_in_transaction_session_timeout in postgresql.conf to automatically terminate such sessions. Always investigate the cause of long queries before killing — they may indicate missing indexes or lock contention.

Table partitioning (PostgreSQL 10+ declarative) splits a large table into smaller physical pieces while appearing as one logical table. Types: PARTITION BY RANGE (date ranges — most common), PARTITION BY LIST (specific values per partition — e.g., by country), PARTITION BY HASH (distribute evenly by hash). Create: CREATE TABLE orders (id BIGINT, order_date DATE, ...) PARTITION BY RANGE (order_date);. Add partition: CREATE TABLE orders_2024 PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');. Benefits: partition pruning (query only relevant partitions), faster bulk deletes (DROP TABLE partition vs DELETE), parallel query per partition, improved vacuum efficiency. Index each partition independently.

PostgreSQL supports multi-dimensional arrays for any data type. Declare: tags TEXT[], scores INTEGER[]. Insert: INSERT INTO posts (tags) VALUES (ARRAY['postgres', 'sql', 'database']); or VALUES ('{\"postgres\",\"sql\"}'). Access element (1-based): tags[1]. Slice: tags[1:2]. Append: array_append(tags, 'newtag'). Contains: tags @> ARRAY['postgres'] (does tags contain 'postgres'?). Overlap: tags && ARRAY['sql', 'nosql']. Unnest to rows: SELECT unnest(tags) FROM posts;. Array length: array_length(tags, 1). Index: CREATE INDEX ON posts USING GIN(tags); for @> and && queries. Arrays are useful for storing sets of related values without a junction table.

hstore is a PostgreSQL extension that stores key-value pairs in a single column. Enable: CREATE EXTENSION hstore;. Store: INSERT INTO products (attrs) VALUES ('color => red, size => XL, material => cotton');. Access a key: attrs->'color' returns 'red'. Check key exists: attrs ? 'color'. All keys: akeys(attrs). All values: avals(attrs). Convert to JSON: hstore_to_json(attrs). Update a key: attrs || 'color => blue'. Delete a key: delete(attrs, 'color'). Index: CREATE INDEX ON products USING GIN(attrs);. In modern PostgreSQL, JSONB is generally preferred over hstore for new applications, as it supports nested structures and has better tooling.

Advisory locks are application-level locks managed by PostgreSQL that your code explicitly acquires and releases — the database doesn't use them automatically. They're useful for ensuring only one process runs a specific task (distributed mutex). Session-level (persist until released or session ends): SELECT pg_advisory_lock(12345); / SELECT pg_advisory_unlock(12345);. Transaction-level (auto-released at transaction end): SELECT pg_advisory_xact_lock(12345);. Non-blocking try: SELECT pg_try_advisory_lock(12345); returns false instead of blocking. Use integer keys; use hashtext('my_lock_name') to convert strings. Advisory locks are stored in memory only and are not logged. Common uses: preventing concurrent cron jobs, distributed leader election, and ensuring single-process batch jobs.

PostgreSQL creates a new OS process per connection — each connection consumes ~5-10MB of RAM and CPU. High connection counts (hundreds or thousands) degrade performance significantly. Connection pooling maintains a pool of database connections that are reused across application requests. PgBouncer is the most popular pooler. Modes: Session mode (connection held for entire client session), Transaction mode (connection returned to pool after each transaction — highest efficiency), Statement mode (returned after each statement — limited use). Configure: max_client_conn (clients), default_pool_size (server connections per database/user). PgBouncer sits between the app and PostgreSQL, often reducing server connections from thousands to tens. pgpool-II also provides load balancing and replication.

WAL (Write-Ahead Logging) is the mechanism PostgreSQL uses to ensure data durability and enable crash recovery. Before modifying any data page, PostgreSQL writes a log record to the WAL (in pg_wal/) first. On crash, PostgreSQL replays WAL records to restore a consistent state. WAL is also the basis for replication — streaming replication ships WAL records to standby servers. Key concepts: LSN (Log Sequence Number — position in WAL), checkpoint (periodic sync of dirty pages to disk — reduces crash recovery time), wal_level (minimal/replica/logical), archive_mode (copy WAL files for PITR — Point-In-Time Recovery). WAL ensures durability with synchronous_commit = on. Setting it to off improves throughput but risks recent data loss on crash.

Streaming replication is PostgreSQL's built-in mechanism for maintaining standby servers that are continuously updated from a primary. The primary streams WAL records to standbys in real time. Setup: configure wal_level = replica, max_wal_senders on primary; use pg_basebackup to create the initial standby; configure primary_conninfo on standby. Standbys are by default hot standbys — they accept read-only queries, offloading reads from the primary. Synchronous replication: primary waits for standby to confirm WAL receipt before acknowledging commit — zero data loss but higher latency. Asynchronous replication: primary doesn't wait — potential for small lag but better performance. Monitor replication lag: SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag FROM pg_stat_replication;.

B-tree (default): balanced tree supporting equality (=), range (<, >), sorting, and LIKE prefix patterns. Best for most scalar data types. Hash: only equality (=); slightly faster than B-tree for equality-only workloads; not WAL-logged in older versions. GIN (Generalized Inverted Index): for composite values (arrays, JSONB, full-text tsvector); maps each element to its containing rows; fast for @>, ?, @@ operators; large, slow to update. GiST (Generalized Search Tree): extensible framework for geometric data, full-text, network addresses; supports PostGIS spatial queries; faster to update than GIN. BRIN (Block Range Index): stores min/max per block range; tiny size; useful for very large tables with naturally ordered data (e.g., time-series); not good for random access patterns.

PostgreSQL has built-in full-text search (FTS). Key types: tsvector (preprocessed document), tsquery (search query). Convert text: to_tsvector('english', 'The quick brown fox'). Search: to_tsquery('english', 'quick & fox'). Match operator: @@: WHERE to_tsvector('english', content) @@ to_tsquery('english', 'postgres & database'). Add a generated column for efficiency: ALTER TABLE articles ADD COLUMN tsv TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || content)) STORED;. Index: CREATE INDEX ON articles USING GIN(tsv);. Ranking: ts_rank(tsv, query) and ts_rank_cd. Highlighting: ts_headline(content, query). For simple substring search, pg_trgm extension with trigram GIN indexes is another option.

The PostgreSQL query planner (optimizer) converts a parsed SQL query into an efficient execution plan. It uses statistics from pg_statistic (collected by ANALYZE) to estimate row counts and data distribution for each plan alternative. The planner generates multiple candidate plans using a dynamic programming algorithm (for ≤ 8 tables) or a genetic algorithm (GEQO, for many tables). It estimates cost in arbitrary units combining seq_page_cost, random_page_cost, and cpu_*_cost parameters. It considers join orderings, join algorithms (Nested Loop, Hash Join, Merge Join), scan types (Seq Scan, Index Scan, Index Only Scan, Bitmap Scan), and parallelism. Use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) to inspect the chosen plan and identify bad estimates caused by stale statistics or correlation issues.

Common causes of poor plans: (1) Stale statistics: run ANALYZE tablename or wait for autovacuum. Increase default_statistics_target for better estimates on skewed columns: ALTER TABLE t ALTER COLUMN c SET STATISTICS 500;. (2) Incorrect row estimates: check EXPLAIN ANALYZE for large discrepancies between estimated and actual rows. (3) Wrong cost parameters: random_page_cost (default 4.0) is too high for SSDs — set to 1.1. (4) Correlation issues: the planner doesn't know about multi-column correlations — use extended statistics: CREATE STATISTICS stat1 ON col1, col2 FROM table;. (5) Bad join ordering: use SET join_collapse_limit = 1 to force explicit ordering. (6) CTE optimization fence (pre-v12): move CTEs inline or use MATERIALIZED/NOT MATERIALIZED hints.

Logical replication (PostgreSQL 10+) replicates data at the logical (row) level rather than the physical (WAL block) level. Unlike streaming replication, logical replication can replicate select tables, replicate between different PostgreSQL versions (for zero-downtime upgrades), and replicate to external systems. Based on a publish/subscribe model: Publisher: CREATE PUBLICATION mypub FOR TABLE orders, users;. Subscriber: CREATE SUBSCRIPTION mysub CONNECTION '...' PUBLICATION mypub;. Requirements: wal_level = logical. Supports INSERT, UPDATE, DELETE, TRUNCATE replication. Limitations: DDL changes are not replicated, sequences are not replicated. Use cases: selective replication, data migration, multi-master setups (with care), feeding data to external systems (Debezium CDC).

Row-Level Security (RLS) restricts which rows are visible or modifiable per user, enabling fine-grained access control directly in the database. Enable on a table: ALTER TABLE orders ENABLE ROW LEVEL SECURITY;. Create policies: CREATE POLICY user_isolation ON orders FOR ALL TO app_user USING (user_id = current_user_id());. The USING expression filters SELECT/UPDATE/DELETE; WITH CHECK filters INSERT/UPDATE. Superusers and table owners bypass RLS unless ALTER TABLE ... FORCE ROW LEVEL SECURITY is set. Use SET app.current_user_id = 42 with current_setting('app.current_user_id')::INTEGER to pass the user context. RLS is essential for multi-tenant SaaS applications — it enforces tenant isolation at the database level, preventing application bugs from leaking data.

Table bloat occurs when dead tuples (from updates and deletes) accumulate faster than VACUUM can reclaim them, causing the table's physical size to grow far beyond its live data size. This wastes disk space and degrades sequential scan performance. Measure bloat: use the pgstattuple extension (SELECT * FROM pgstattuple('tablename');) or queries against pg_class. Causes: high UPDATE/DELETE volume, insufficient autovacuum frequency, long-running transactions preventing dead tuple removal. Fixes: (1) Tune autovacuum aggressiveness per table. (2) VACUUM FULL tablename — rewrites the table compactly but requires an exclusive lock (use in maintenance windows). (3) pg_repack extension — rewrites tables online without an exclusive lock. (4) Partition large tables to vacuum partitions independently. Index bloat is separate — use REINDEX CONCURRENTLY.

A deadlock occurs when two or more transactions are each waiting for a lock held by the other. PostgreSQL automatically detects deadlocks using a deadlock detection algorithm that runs when a lock wait exceeds deadlock_timeout (default 1 second). When detected, PostgreSQL aborts one of the transactions (the "victim") with error code 40P01. The aborted transaction must be retried by the application. Prevention strategies: (1) Always acquire locks in a consistent order (e.g., always lock user before order). (2) Use SELECT ... FOR UPDATE SKIP LOCKED for job queues to avoid contention. (3) Keep transactions short. (4) Use LOCK TABLE ... NOWAIT to fail immediately instead of waiting. (5) Monitor with pg_locks joined with pg_stat_activity. Log deadlocks: log_lock_waits = on in postgresql.conf.

Regular (object) locks are automatically acquired by PostgreSQL when accessing tables, rows, or system objects — they protect internal data structures and enforce ACID properties. Types include: AccessShareLock (SELECT), RowShareLock (SELECT FOR UPDATE), RowExclusiveLock (INSERT/UPDATE/DELETE), ShareLock (CREATE INDEX), AccessExclusiveLock (ALTER TABLE — blocks all). View held locks: SELECT * FROM pg_locks;. Advisory locks have no inherent meaning to PostgreSQL — they are application-defined semaphores. They don't affect database objects and are managed entirely by application code via pg_advisory_lock(). Regular locks protect data integrity; advisory locks protect application-level invariants like "only one job processor should run at a time." Both are visible in pg_locks (advisory locks have locktype = 'advisory').

Effective partitioning strategy: (1) Choose partition key wisely — most queries should filter by it to enable partition pruning (skipping irrelevant partitions). Range partitioning on created_at is most common for time-series data. (2) Create a default partition to catch out-of-range data: CREATE TABLE orders_default PARTITION OF orders DEFAULT;. (3) Create local indexes on each partition. (4) Use PARTITION PRUNING: verify with EXPLAIN that only relevant partitions are scanned. (5) Automate partition creation with pg_partman extension. (6) Drop old partitions instantly: DROP TABLE orders_2020 — far faster than DELETE. (7) Attach/detach partitions: ALTER TABLE orders DETACH PARTITION orders_2020 CONCURRENTLY; (v14+). Enable enable_partition_pruning = on (default). Use partition_pruning parameter in queries.

pg_cron is a PostgreSQL extension that acts as a cron scheduler running inside the database. It allows scheduling SQL queries and stored procedure calls directly from PostgreSQL without external schedulers. Install: CREATE EXTENSION pg_cron;. Add a job: SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM ANALYZE orders');. Schedule uses standard cron syntax (minute, hour, day, month, weekday). Run every 5 minutes: '*/5 * * * *'. View jobs: SELECT * FROM cron.job;. View history: SELECT * FROM cron.job_run_details ORDER BY start_time DESC LIMIT 20;. Remove: SELECT cron.unschedule(job_id);. Runs in the database as a background worker. Useful for: refreshing materialized views, purging old data, generating reports, and running maintenance tasks.

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) is the most comprehensive query analysis tool. Key elements to examine: (1) Node types: Seq Scan (full table — bad for large tables), Index Scan, Index Only Scan (best — no heap access), Bitmap Heap Scan, Hash Join, Merge Join, Nested Loop. (2) Rows estimate vs actual: large discrepancies indicate stale statistics (rows=1 estimate vs actual rows=50000 → run ANALYZE). (3) Cost: cost=startup..total in planner units. (4) Buffers: Buffers: shared hit=X read=Y — high read means cache miss. (5) Loops: actual time is per-loop; multiply by loops for total. (6) Filter: rows removed by filter — indicates a non-indexed filter condition. Use explain.depesz.com or explain.dalibo.com to visualize plans. Always run EXPLAIN ANALYZE on production data, not empty test databases.

A LATERAL join allows a subquery in the FROM clause to reference columns from preceding tables — it's like a correlated subquery but in FROM position, and can return multiple rows and columns. Without LATERAL, a subquery in FROM cannot see outer tables. Example: get the last 3 orders for each user: SELECT u.name, o.* FROM users u, LATERAL (SELECT * FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 3) o;. A correlated subquery in SELECT/WHERE also references the outer query but is re-executed for every outer row — the planner may or may not optimize this as a join. LATERAL joins are often faster because the planner can choose efficient join strategies. LATERAL is required when the function returns rows based on the outer row: CROSS JOIN LATERAL unnest(tags) AS tag or LEFT JOIN LATERAL function(u.id) ON true.

pg_partman is a PostgreSQL extension that automates the creation and maintenance of time-based and serial-based partitioned tables. Without pg_partman, you must manually create new partitions before data arrives — miss this and data goes to the default partition or errors. With pg_partman: (1) Create the parent table with declarative partitioning. (2) Set up the partition set: SELECT partman.create_parent('public.orders', 'created_at', 'native', 'monthly');. (3) pg_partman automatically creates pre-built future partitions and maintains a retention policy. (4) Schedule maintenance: SELECT partman.run_maintenance(); — typically via pg_cron. It handles: creating new partitions in advance (premake setting), dropping old partitions based on retention, and moving data from the default partition. Essential for production time-series tables.

Zero-downtime migrations require careful ordering to avoid table locks. Safe operations: adding a nullable column (instant in Postgres 11+ with a default), adding an index (CONCURRENTLY), adding a not-exists constraint. Dangerous operations requiring workarounds: (1) Add NOT NULL column: add nullable → backfill → add constraint as NOT VALID → VALIDATE CONSTRAINT (only takes ShareUpdateExclusiveLock). (2) Add foreign key: ADD CONSTRAINT ... NOT VALID then VALIDATE CONSTRAINT. (3) Reindex: REINDEX CONCURRENTLY. (4) Change column type: add new column → backfill → update trigger → cut over → drop old column. (5) Rename column: use a view or rename-then-add-alias approach. Tools: squawk (CI linter), pgroll, and frameworks like strong_migrations (Rails gem) enforce safe patterns.

Exclusion constraints are a generalization of unique constraints — they ensure that for any two rows in the table, at least one of a set of comparisons evaluates to false. While UNIQUE constraints only check equality (=), exclusion constraints can use any operator from a GiST or SP-GiST index. The classic use case is preventing overlapping time ranges: CREATE TABLE bookings (room_id INT, during TSTZRANGE, EXCLUDE USING GIST (room_id WITH =, during WITH &&)); — this prevents two bookings for the same room with overlapping time ranges. Requires btree_gist extension for combining scalar columns with range columns. Other uses: preventing overlapping geometric regions, ensuring no two network addresses overlap. Cannot be deferred (unlike foreign keys).

pg_dump creates backups of PostgreSQL databases. Plain SQL dump: pg_dump -U postgres -d mydb > backup.sql. Custom format (best for large DBs — compressed, parallelizable): pg_dump -Fc -d mydb -f backup.dump. Directory format (parallel dump): pg_dump -Fd -j 4 -d mydb -f backup_dir/ — uses 4 parallel workers. Dump specific tables: pg_dump -t tablename -d mydb > table.sql. pg_dumpall dumps all databases plus global objects (roles, tablespaces). pg_restore restores custom/directory format: pg_restore -d mydb -Fc backup.dump. Parallel restore: pg_restore -j 4 -d mydb backup_dir/. For continuous backup (PITR): use pg_basebackup + WAL archiving with tools like Barman, pgBackRest, or WAL-G.

PgBouncer is a lightweight connection pooler that sits between the application and PostgreSQL. PostgreSQL forks a new OS process per connection (~5-10MB RAM). With 1000 application connections, PostgreSQL would have 1000 processes — this degrades performance severely due to process overhead, context switching, and lock table size. PgBouncer maintains a small pool of actual PostgreSQL connections and multiplexes many client connections onto them. Configuration: pool_mode = transaction (most efficient — connection returned to pool after each transaction), default_pool_size = 20 (20 server connections per database/user), max_client_conn = 1000. Limitations in transaction mode: cannot use session-level features like prepared statements (use protocol-level prepared statements), advisory locks, SET commands, or LISTEN/NOTIFY. Pgcat and Odyssey are modern alternatives with more features.