Advanced SQL

ROW_NUMBER() assigns a unique sequential integer to every row regardless of ties. RANK() assigns the same rank to tied rows and leaves gaps (e.g., 1,2,2,4). DENSE_RANK() assigns the same rank to tied rows but without gaps (1,2,2,3). Choose ROW_NUMBER() for pagination, RANK()/DENSE_RANK() for leaderboard-style ranking where tied values should be treated equally. All three are window functions requiring an OVER(ORDER BY ...) clause. They can also be partitioned with PARTITION BY to reset rankings within groups.

SELECT name, score,
  RANK() OVER (ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rn
FROM leaderboard;

ROWS defines the frame boundary by counting physical rows relative to the current row, making it deterministic regardless of duplicate values. RANGE defines the frame logically based on the ORDER BY column value, so all rows with the same value as the current row are included in the frame together. For example, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW will include all rows with the same ORDER BY value as the current row, while ROWS limits to exactly the rows up to the current physical position. ROWS is generally preferred for running totals to avoid unexpected frame expansion with duplicates.

SELECT date, amount,
  SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales;

A recursive CTE is a WITH clause that references itself, enabling hierarchical or graph traversal queries that would otherwise require procedural loops. It consists of an anchor member (base case) combined via UNION ALL with a recursive member that references the CTE name. The database engine iterates, accumulating results until no new rows are produced. Common use cases include traversing org charts, bill-of-materials explosions, finding shortest paths in graphs, and generating date series. Most databases enforce a maximum recursion depth to prevent infinite loops.

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth+1
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org;

A LATERAL join (LATERAL in PostgreSQL/MySQL, CROSS APPLY in SQL Server/Oracle) allows a subquery in the FROM clause to reference columns from preceding tables in the same FROM clause. Unlike a regular subquery that executes independently, a lateral join recomputes for each row of the outer table, similar to a correlated subquery but returning multiple rows and columns. Lateral joins enable top-N per group queries efficiently, allow complex table-valued functions to be applied per row, and produce cleaner syntax than repeated correlated subqueries. They are particularly powerful when combined with LIMIT inside the lateral subquery.

SELECT u.user_id, recent.*
FROM users u,
LATERAL (
  SELECT * FROM orders o
  WHERE o.user_id = u.user_id
  ORDER BY o.created_at DESC
  LIMIT 3
) AS recent;

Phantom reads occur when a transaction re-executes a query and finds new rows inserted by another committed transaction. The SERIALIZABLE isolation level prevents phantom reads by ensuring transactions execute as if they ran serially. READ COMMITTED prevents dirty reads but allows non-repeatable reads and phantoms. REPEATABLE READ prevents dirty and non-repeatable reads but in standard SQL still allows phantoms (though PostgreSQL's REPEATABLE READ implementation uses snapshot isolation which also prevents phantoms in practice). SERIALIZABLE uses predicate locking or optimistic concurrency to fully isolate transactions.

A covering index includes all columns referenced by a query (SELECT list, WHERE clause, JOIN condition, ORDER BY) within the index itself. When the query optimizer identifies a covering index, it can satisfy the entire query by reading the index B-tree without performing a separate heap fetch (table lookup). This eliminates random I/O to the main table, dramatically reducing I/O cost for read-heavy queries. In PostgreSQL the INCLUDE clause adds non-key columns to leaf pages. In SQL Server this is called an 'included columns' index. The trade-off is increased index size and slower writes. Use covering indexes for frequently executed critical queries.

CREATE INDEX idx_orders_cov
ON orders (customer_id, status)
INCLUDE (amount, created_at);

EXPLAIN shows the query plan the optimizer intends to use, including estimated row counts and costs. EXPLAIN ANALYZE actually executes the query and shows actual runtime statistics: actual rows processed, actual execution time per node, number of loops, and whether estimates matched reality. Discrepancies between estimated and actual rows indicate stale statistics, requiring ANALYZE on the table. EXPLAIN ANALYZE also reveals buffer hit/miss counts with BUFFERS option, enabling I/O analysis. It is essential for diagnosing why a query is slow — the optimizer may choose a suboptimal plan based on bad statistics.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42;

Predicate pushdown is an optimization where the query engine moves filter conditions as close to the data source as possible — into subqueries, views, CTEs, or remote data sources — so that fewer rows are materialized and transferred upstream. In distributed systems like Spark or Presto, pushing predicates to the storage layer (e.g., Parquet file scan) avoids deserializing irrelevant rows entirely. In SQL Server, predicate pushdown into remote servers reduces network traffic. Materialized views and CTEs that act as optimization fences can block pushdown, causing full scans. Understanding pushdown behavior is critical for writing performant queries over large datasets.

SARGable (Search ARGument ABLE) predicates are WHERE clause conditions that can leverage indexes efficiently because the indexed column is isolated on one side of the comparison. Non-SARGable predicates apply functions to the indexed column (e.g., YEAR(created_at) = 2024, LOWER(name) = 'alice'), preventing index seeks and forcing full scans. To make predicates SARGable: rewrite date functions as range conditions (created_at >= '2024-01-01' AND created_at < '2025-01-01'), create function-based indexes, or use generated/computed columns. SARGability is the single most common missed optimization opportunity in application SQL.

-- Non-SARGable (full scan)
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- SARGable (index seek)
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

NOT IN with a subquery returns no rows if any value in the subquery result is NULL, because SQL's three-valued logic makes the comparison indeterminate. This is a frequent correctness bug. NOT EXISTS uses an existence check with a correlated subquery and handles NULLs correctly — it returns TRUE when no matching row exists, regardless of NULL values. Additionally, NOT EXISTS often allows the optimizer to use an anti-join execution strategy that stops scanning as soon as a match is found, making it more efficient. For large datasets, a LEFT JOIN ... WHERE right.key IS NULL anti-join pattern can also be used and is often optimal.

-- Correct: NOT EXISTS
SELECT * FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM returns r WHERE r.order_id = o.id
);

GROUPING SETS allows specifying exactly which grouping combinations to compute in one pass, avoiding UNION ALL of multiple GROUP BY queries. ROLLUP(a,b,c) generates subtotals and a grand total in hierarchical order: (a,b,c), (a,b), (a), (). CUBE(a,b,c) generates all possible combinations of grouping, including cross-tabulation: 2^n sets. The GROUPING() function returns 1 for columns that are aggregated away (NULL due to rollup), distinguishing intentional NULLs in data from grouping NULLs. These extensions are SQL standard and supported in PostgreSQL, SQL Server, Oracle, and BigQuery. They dramatically reduce query complexity for reporting workloads.

SELECT region, product, SUM(sales)
FROM sales_data
GROUP BY ROLLUP(region, product);

Pessimistic locking acquires locks before reading or modifying data, blocking other transactions until released. It prevents conflicts but reduces concurrency and risks deadlocks. Examples include SELECT ... FOR UPDATE and explicit table locks. Optimistic locking assumes conflicts are rare: reads proceed without locks, but at commit time the transaction checks if data was modified (using a version column or timestamp) and rolls back if a conflict is detected. Optimistic locking is preferred in read-heavy, low-contention workloads for higher throughput; pessimistic locking suits write-heavy, high-contention scenarios where conflicts are frequent and retries are expensive.

-- Pessimistic
SELECT * FROM accounts WHERE id=1 FOR UPDATE;
-- Optimistic
UPDATE accounts SET balance=?, version=version+1
WHERE id=1 AND version=?;

Multi-Version Concurrency Control (MVCC) allows readers and writers to operate concurrently without blocking each other by maintaining multiple versions of each row. PostgreSQL stores old and new row versions in the same heap table, tagged with transaction IDs (xmin, xmax) indicating which transactions can see each version. A reader sees a consistent snapshot of the database as of its transaction start without acquiring read locks. Writers create new row versions rather than overwriting. Dead row versions are reclaimed by VACUUM. This approach eliminates read-write blocking at the cost of table bloat and the need for periodic vacuuming to reclaim space.

A partial index is built on a subset of rows defined by a WHERE predicate. This makes the index smaller, faster to maintain, and more likely to fit in memory. Partial indexes are ideal for queries that consistently filter on a fixed condition — for example, indexing only active users, unprocessed orders, or non-NULL values. A full index on a low-cardinality flag column is mostly wasted space; a partial index on the minority condition is compact and highly selective. The query WHERE clause must match or imply the index predicate for the optimizer to use it.

CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';

DELETE removes rows one at a time, fires triggers, can be filtered with WHERE, and is fully transactional (rollback supported). It is slow for large tables due to per-row processing and logging. TRUNCATE removes all rows instantly by deallocating data pages, bypasses row-level triggers, is DDL in most databases (though transactional in PostgreSQL), and cannot be filtered. DROP removes the entire table object including its structure, indexes, and constraints — it is irreversible without a backup. For clearing large tables, TRUNCATE is orders of magnitude faster than DELETE but cannot be used if foreign key constraints reference the table.

Nested loop join iterates the outer table row-by-row and probes the inner table for each row; efficient when the inner table is small or indexed. Hash join builds a hash table from the smaller (build) input, then probes it with the larger (probe) input; best for large unsorted joins without indexes, uses memory. Merge join requires both inputs sorted on the join key; it linearly scans both sorted streams simultaneously; optimal when inputs are already sorted or can be sorted cheaply. The query optimizer chooses based on table sizes, available indexes, and memory. Understanding these strategies helps write index-friendly join queries.

A regular view is a stored query that executes fresh every time it is referenced, providing no performance benefit over the underlying query. A materialized view stores the query result physically on disk, allowing fast reads without recomputing. The trade-off is staleness: the materialized view must be refreshed (REFRESH MATERIALIZED VIEW in PostgreSQL) to reflect base table changes. Some databases support incremental refresh; others require full recomputation. Materialized views are invaluable for expensive aggregation queries in reporting, BI dashboards, and pre-computed features in ML pipelines. They can also be indexed for further query acceleration.

CREATE MATERIALIZED VIEW monthly_sales AS
SELECT date_trunc('month', created_at) AS month, SUM(amount)
FROM orders GROUP BY 1;
REFRESH MATERIALIZED VIEW monthly_sales;

PostgreSQL can split a query into parallel workers to scan large tables concurrently. The number of workers is controlled by max_parallel_workers_per_gather and max_parallel_workers. The optimizer enables parallel plans when the estimated cost exceeds parallel_setup_cost and per-row cost exceeds parallel_tuple_cost. Parallel plans apply to sequential scans, index scans, hash joins, aggregations, and sorts. Tuning requires increasing max_worker_processes at the system level, setting appropriate per-query worker limits, and ensuring the table is large enough to justify parallelism. You can force parallel plans using SET max_parallel_workers_per_gather = 4.

SET max_parallel_workers_per_gather = 4;
EXPLAIN SELECT COUNT(*) FROM large_table WHERE status = 'active';

A composite index covers multiple columns in a defined order. The leftmost prefix principle states that the index can be used for queries filtering on the leading column(s) but not on later columns alone. For example, an index on (a, b, c) supports queries filtering on a, on (a, b), and on (a, b, c), but not on b or c alone. Column order should place the most selective column (highest cardinality) first for range queries, but for equality filters, putting the equality columns before range columns maximizes index utilization. Incorrect column order is a top cause of unexpectedly slow queries despite having indexes.

CREATE INDEX idx_orders_composite ON orders (customer_id, status, created_at);

COALESCE(expr1, expr2, ...) returns the first non-NULL expression in its argument list; it is used to provide default values when data may be NULL. NULLIF(expr1, expr2) returns NULL if both expressions are equal, otherwise returns expr1; it is used to avoid division-by-zero by converting zero denominators to NULL. Together they handle common data quality issues. COALESCE is ANSI standard and more portable than database-specific NVL or ISNULL functions. Both short-circuit evaluation in most databases. A common pattern is NULLIF(col, '') to convert empty strings to NULL before applying COALESCE.

SELECT COALESCE(phone, email, 'unknown') AS contact,
       revenue / NULLIF(visits, 0) AS revenue_per_visit
FROM users;

UNBOUNDED PRECEDING extends the frame to the start of the partition; UNBOUNDED FOLLOWING extends to the end. CURRENT ROW is the current row itself. The EXCLUDE clause (SQL:2011 standard, supported in PostgreSQL 14+, SQLite) removes certain rows from the frame: EXCLUDE CURRENT ROW omits the current row, EXCLUDE GROUP omits all rows with the same ORDER BY value as the current row, and EXCLUDE TIES omits only the peers (not the current row itself). These are useful for computing statistics excluding the current observation, such as leave-one-out cross-validation metrics within groups.

SELECT id, score,
  AVG(score) OVER (
    ORDER BY id
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    EXCLUDE CURRENT ROW
  ) AS avg_excl_self
FROM results;

A deadlock occurs when two or more transactions each hold a lock and wait for a lock held by the other, creating a circular dependency. Neither can proceed without the other releasing first. Databases detect deadlocks using a wait-for graph: if a cycle is detected, the deadlock detector selects a victim transaction (usually the cheapest to rollback), aborts it, and allows the remaining transaction to proceed. Prevention strategies include acquiring locks in a consistent global order, using short transactions, avoiding explicit lock escalation, and using SKIP LOCKED for queue-style workloads. In PostgreSQL, deadlock detection runs every deadlock_timeout milliseconds.

The FILTER (WHERE condition) clause, introduced in SQL:2003 and supported in PostgreSQL, SQLite, DuckDB, and others, applies a condition to each row before it participates in the aggregate. This allows multiple conditional aggregations in a single query without CASE WHEN workarounds. It is more readable, often faster, and semantically clearer than CASE expressions inside aggregates. FILTER works with COUNT, SUM, AVG, and all other aggregates, including window functions. SQL Server and MySQL do not natively support FILTER, requiring CASE WHEN NULL workarounds.

SELECT
  COUNT(*) FILTER (WHERE status = 'paid') AS paid_count,
  SUM(amount) FILTER (WHERE status = 'paid') AS paid_total
FROM orders;

Partitioning divides a table's data into logical or physical segments (partitions) within a single database instance, managed transparently by the database engine. It improves query performance through partition pruning and simplifies data lifecycle management. Sharding distributes data across multiple independent database instances (shards), each holding a subset of rows identified by a shard key. Sharding enables horizontal scaling beyond a single machine but adds application complexity: cross-shard queries require scatter-gather, distributed transactions are hard, and schema changes must be applied to all shards. Partitioning is a database feature; sharding is an architectural pattern implemented at the application or middleware layer.

The N+1 problem occurs when an application fetches N parent records and then issues N separate queries to fetch related child records, totaling N+1 database roundtrips. For example, fetching 100 users and then querying orders for each user individually. The fix is to batch the child query: fetch all users once, collect their IDs, then fetch all orders WHERE user_id IN (...) in a single query, and join results in application memory. ORMs often solve this with eager loading (JOIN FETCH in JPA, includes in ActiveRecord, select_related in Django). In SQL, a single JOIN or a lateral join returns both parents and children together.

-- Batch fetch instead of N+1
SELECT u.id, u.name, o.order_id, o.amount
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.id = ANY(ARRAY[1,2,3,...]);

Table partitioning splits a large table into smaller physical pieces (partitions) sharing the same schema, managed transparently by the database. PostgreSQL supports three strategies: RANGE partitioning (e.g., by date range, one partition per month), LIST partitioning (e.g., by country or status code), and HASH partitioning (distributes rows by hash of the partition key for even distribution). The optimizer performs partition pruning to scan only relevant partitions when the WHERE clause filters on the partition key, greatly reducing I/O. Partitioning also simplifies data archival: dropping an old partition is instant compared to DELETE-ing millions of rows.

CREATE TABLE orders (id BIGINT, created_at DATE, amount NUMERIC)
PARTITION BY RANGE (created_at);
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

DISTINCT ON (expr) is a PostgreSQL extension that returns one row per unique value of the specified expression, selecting the first row within each group according to the ORDER BY clause. It efficiently implements 'latest record per group' or 'top-1 per partition' queries without a lateral join or ROW_NUMBER() window function. The ORDER BY must begin with the same columns used in DISTINCT ON. It is not standard SQL but is often more readable and can be faster than equivalent window function approaches. The query returns one representative row per distinct value, making it ideal for deduplication and latest-state lookups.

SELECT DISTINCT ON (user_id)
  user_id, session_id, started_at
FROM sessions
ORDER BY user_id, started_at DESC;

Prior to PostgreSQL 12, CTEs (WITH clauses) were always materialized — the CTE query executed once and its result was stored in a temporary table, preventing the optimizer from pushing predicates into the CTE or merging it with the outer query. This 'optimization fence' behavior sometimes caused CTEs to be slower than equivalent subqueries. From PostgreSQL 12, non-recursive CTEs without side effects are inlined by default, allowing full optimizer access. You can force materialization with WITH ... AS MATERIALIZED (...) or prevent it with AS NOT MATERIALIZED. Understanding this behavior is critical when migrating queries across PostgreSQL versions.

-- Force materialization (PostgreSQL 12+)
WITH expensive AS MATERIALIZED (
  SELECT * FROM big_table WHERE complex_condition
)
SELECT * FROM expensive WHERE id = 1;

A bloom filter index in PostgreSQL (via the bloom extension) uses a probabilistic data structure that can test whether a value is definitely NOT in a set, with a small false-positive rate. Unlike B-tree indexes which require one index per column combination, a single bloom index can cover arbitrary combinations of columns, making it useful for tables queried with many different column combinations (multi-column equality predicates) where creating individual indexes for each combination is impractical. It works for equality conditions only, not range queries. The false positive rate is tunable via the index parameters. It is most beneficial for large tables with many low-selectivity columns.

CREATE EXTENSION bloom;
CREATE INDEX idx_bloom ON events
USING bloom (event_type, user_id, region);

PostgreSQL provides rich JSON support with two types: json (stores as text, preserves formatting) and jsonb (binary, indexed, faster for queries). Key operators: -> returns JSON object/array, ->> returns text, #> navigates nested paths, @> checks containment. Functions include jsonb_each(), jsonb_array_elements(), json_agg(), jsonb_set(), and jsonb_build_object(). GIN indexes on jsonb columns support fast containment queries (@>) and key existence queries (?). jsonb_path_query() supports JSONPath expressions (SQL:2016 standard). For analytics, jsonb_to_recordset() unpacks JSON arrays into rows, enabling SQL aggregation over JSON-embedded data without separate ETL.

SELECT data->>'name', data->'address'->>'city'
FROM users
WHERE data @> '{"active": true}'::jsonb;

UNION combines result sets from two SELECT statements and removes duplicate rows using an implicit DISTINCT operation, which requires sorting or hashing to deduplicate. UNION ALL combines result sets including all duplicates and performs no deduplication, making it significantly faster. Use UNION ALL when you know the result sets are mutually exclusive (e.g., partitioned tables) or when duplicates are intentional. Using UNION when duplicates cannot exist wastes resources on unnecessary sort/hash operations. INTERSECT and EXCEPT also have ALL variants following the same logic.

UNNEST() expands an array column into a set of rows, one row per array element. In PostgreSQL it can also expand multiple arrays in parallel with ordinality. It is essential for normalizing denormalized array-stored data, generating test data, and unpacking JSON arrays converted to PostgreSQL arrays. Combined with WITH ORDINALITY, it preserves the array index as a column. UNNEST() is often combined with GROUP BY to aggregate over array elements across rows. For JSON arrays, jsonb_array_elements() serves a similar purpose. It enables SQL-native array processing without requiring application-side loops.

SELECT user_id, tag
FROM users, UNNEST(tags) AS tag
WHERE tag = 'machine_learning';

A Foreign Data Wrapper (FDW) is a PostgreSQL extension that allows querying external data sources — other PostgreSQL databases, MySQL, Oracle, MongoDB, CSV files, S3, REST APIs, Kafka — as if they were local tables using standard SQL. The postgres_fdw extension connects to remote PostgreSQL instances with predicate and column pushdown for efficiency. FDWs implement the FDW API (fdw_handler, fdw_validator) which defines how to scan, insert, update, and delete foreign rows. Column and predicate pushdown reduces data transferred from the remote source. FDWs enable federated queries across heterogeneous data sources without ETL, useful in data mesh architectures.

CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_db FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'remote', dbname 'analytics');
CREATE FOREIGN TABLE remote_users (...) SERVER remote_db;

Generated columns compute their value automatically from other columns in the same row, either stored physically on disk (STORED) or computed on the fly during reads (VIRTUAL). They are useful for pre-computing expensive expressions, maintaining denormalized derived values consistently, enabling indexes on computed expressions without manual function-based indexes, and ensuring derived values never go stale. For example, a full_name generated column combining first_name and last_name, or a revenue_usd column applying a currency conversion rate. Stored generated columns can be indexed. They replace triggers used for this purpose, are simpler, and are maintained atomically by the database.

ALTER TABLE products ADD COLUMN
  revenue_usd NUMERIC GENERATED ALWAYS AS (price * exchange_rate) STORED;

NTILE(n) divides a window partition into n approximately equal buckets and assigns each row a bucket number from 1 to n based on its position in the ORDER BY sequence. If the number of rows is not evenly divisible by n, earlier buckets receive one extra row. NTILE is used for percentile binning (NTILE(100) gives percentile ranks), creating balanced data splits for training/test sets, quartile analysis, and bucketed reporting. Unlike PERCENT_RANK() and CUME_DIST() which return fractions, NTILE returns an integer bucket label, making it directly usable as a grouping key in downstream aggregations.

SELECT user_id, revenue,
  NTILE(4) OVER (ORDER BY revenue) AS quartile
FROM customers;

A star schema organizes a data warehouse with a central fact table (containing measurements and foreign keys) surrounded by dimension tables (descriptive attributes). Queries join the fact table to one or more dimensions. This denormalized structure minimizes joins compared to a normalized OLTP schema, making analytical queries faster. The trade-off is data redundancy in dimensions. A snowflake schema normalizes dimensions into sub-dimensions, reducing redundancy but requiring more joins. Modern columnar storage engines (Redshift, BigQuery, ClickHouse) combined with vectorized execution have made star schemas highly efficient. Dimension tables should be kept small enough to broadcast in distributed query engines.

FETCH FIRST n ROWS WITH TIES returns the top n rows plus any additional rows that are tied with the nth row on the ORDER BY key, preventing arbitrary exclusion of tied records. Without WITH TIES, if multiple rows share the same ORDER BY value as the last included row, the database arbitrarily picks which to include. WITH TIES is equivalent to a query using RANK() <= n (not ROW_NUMBER()). It is supported in SQL Server, Oracle, PostgreSQL 13+, and DuckDB. Use it in top-N queries where fairness among tied records matters, such as leaderboards or recommendation lists.

SELECT product, revenue
FROM products
ORDER BY revenue DESC
FETCH FIRST 5 ROWS WITH TIES;

Connection pooling maintains a pool of pre-established database connections that are reused across application requests, instead of opening and closing a connection per request. Establishing a PostgreSQL connection takes 20-100ms and consumes server memory per connection. Without pooling, high-concurrency applications quickly exhaust the database's max_connections, causing connection failures. Tools like PgBouncer (PostgreSQL-specific, supports transaction pooling), HikariCP (JVM), and asyncpg's built-in pool (Python) manage pooling. Transaction-level pooling in PgBouncer allows many more application threads than actual database connections by returning the connection to the pool after each transaction completes.

TABLESAMPLE allows sampling a fraction of a table's rows for approximate query results without scanning the full table. PostgreSQL supports two methods: BERNOULLI (each row independently included with the given probability, slower but true random) and SYSTEM (page-level sampling, faster but less uniform). TABLESAMPLE is useful for exploratory data analysis, building quick approximate reports, and testing queries on representative subsets without full scans. The REPEATABLE (seed) option makes sampling deterministic across runs. For large analytical tables, sampling can reduce query time from minutes to seconds while providing statistically useful approximations.

SELECT AVG(amount)
FROM orders TABLESAMPLE BERNOULLI(10)
REPEATABLE (42);

Temporal tables track the history of row changes over time, maintaining when each version was valid. SQL:2011 defines two types: system-time temporal tables (automatically track database transaction time using system-maintained period columns) and application-time temporal tables (user-managed validity periods). Queries use FOR SYSTEM_TIME AS OF, BETWEEN, or FROM ... TO clauses to access historical snapshots. SQL Server implements system-versioned temporal tables automatically; PostgreSQL requires pg_temporal or custom trigger-based solutions. Temporal tables are essential for audit logging, point-in-time recovery of business data, slowly changing dimensions in data warehouses, and regulatory compliance use cases.

-- SQL Server temporal query
SELECT * FROM employees
FOR SYSTEM_TIME AS OF '2024-01-01';

A non-correlated subquery executes independently of the outer query — it runs once, returns a result, and the outer query uses that result. A correlated subquery references columns from the outer query, so it is re-executed for each row of the outer query, making it O(n) in the number of outer rows. Correlated subqueries are often equivalent to joins and can be rewritten as such for better performance, since joins allow the optimizer to choose an efficient join algorithm while correlated subqueries may force nested-loop execution. EXISTS, IN, and scalar subqueries in SELECT or WHERE clauses are common patterns where correlation matters for performance.

-- Correlated (slow for large tables)
SELECT u.name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS cnt
FROM users u;

Database statistics are metadata about the distribution of data in tables and indexes — including row counts, column cardinalities, most common values, histogram buckets, and correlation between column ordering and physical storage. The PostgreSQL query optimizer uses these statistics (collected by ANALYZE or autovacuum) to estimate the number of rows a query predicate will filter and choose the most efficient execution plan. Stale statistics cause the optimizer to make poor estimates, potentially choosing a nested loop join over a hash join for large tables. Running ANALYZE after bulk data loads or significant data changes ensures accurate planning.

ANALYZE orders;
-- Check statistics
SELECT * FROM pg_stats WHERE tablename = 'orders' AND attname = 'status';

EXISTS returns TRUE as soon as the subquery finds a single matching row and stops scanning, regardless of how many additional matches exist. This short-circuit behavior makes it extremely efficient for existence checks — the database does not need to count all matches or retrieve all columns, just confirm one exists. For this reason, SELECT 1 inside EXISTS is conventional (column values are never returned). In contrast, COUNT(*) > 0 forces the database to count every matching row. EXISTS is especially beneficial when the subquery could match many rows, as it terminates at the first match. This makes it the preferred pattern for conditional logic in WHERE clauses.

-- Efficient: stops at first match
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

PIVOT transforms rows into columns, converting values of one column into multiple column headers. SQL Server and Oracle have native PIVOT syntax. Databases lacking it (PostgreSQL, MySQL) use conditional aggregation: a CASE WHEN or FILTER expression inside an aggregate function for each target column. This is more flexible as the column list need not be static, and a CROSSTAB function (PostgreSQL tablefunc extension) provides a cleaner syntax. Dynamic pivots (unknown column list at query time) require dynamic SQL string construction. PIVOT is common in reporting to create cross-tabulation summaries.

-- PostgreSQL conditional aggregation pivot
SELECT product,
  SUM(amount) FILTER (WHERE region='North') AS north,
  SUM(amount) FILTER (WHERE region='South') AS south
FROM sales GROUP BY product;

The CAP theorem states that a distributed system can guarantee at most two of three properties simultaneously: Consistency (every read returns the latest write), Availability (every request receives a response), and Partition tolerance (the system continues operating despite network partitions). Since network partitions are unavoidable in practice, distributed systems must choose between CP (sacrifice availability during partitions, e.g., Google Spanner, CockroachDB) and AP (sacrifice consistency for availability, e.g., Cassandra, DynamoDB). Most distributed SQL databases with strong ACID guarantees are CP systems. The PACELC theorem extends CAP by also considering latency vs consistency trade-offs in the absence of partitions.

SELECT FOR UPDATE SKIP LOCKED is a queue-processing pattern where multiple workers concurrently pick up unprocessed tasks from a database table. SKIP LOCKED causes each worker to skip rows that are already locked by another transaction rather than waiting, enabling parallel task processing without contention. This transforms a database table into an efficient job queue. Workers select, lock, and process one batch; upon commit the lock releases and the row is marked processed. It is simpler than external message queues for moderate throughput, fully ACID-compliant, and avoids dual-write consistency problems. PostgreSQL and SQL Server support it natively.

BEGIN;
SELECT * FROM jobs WHERE status = 'pending'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED;
-- process and update
UPDATE jobs SET status='done' WHERE id = ANY(...);
COMMIT;

An index-only scan retrieves query results entirely from the index without visiting the heap (main table), achieving maximum I/O efficiency. PostgreSQL uses it when all columns referenced by the query are present in the index (covering index) and the visibility map indicates that the heap pages are all-visible (i.e., no dead tuples that need visibility checks). VACUUM keeps the visibility map updated by marking pages as all-visible after removing dead tuples. If the visibility map is stale, the planner may fall back to a regular index scan. Monitoring index-only scan effectiveness: pg_stat_user_indexes.idx_blks_hit vs heap fetches.

LAG(expr, offset, default) returns the value of expr from a row offset rows before the current row within the partition. LEAD(expr, offset, default) returns the value from offset rows after. Both are non-materializing window functions that enable row-to-row comparisons without self-joins. Practical uses: computing period-over-period growth (current revenue minus previous period revenue), detecting consecutive events (status changed from the previous row), calculating time-between-events, and identifying session boundaries in clickstream data. The default parameter handles boundary rows where there is no preceding or following row.

SELECT date, revenue,
  LAG(revenue) OVER (ORDER BY date) AS prev_revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS delta
FROM daily_sales;

A transaction is a unit of work that either fully completes or fully rolls back. ACID properties: Atomicity — all operations in a transaction succeed or all fail (no partial writes). Consistency — the database moves from one valid state to another, enforcing constraints and rules. Isolation — concurrent transactions appear to execute serially, preventing interference (controlled by isolation levels). Durability — committed transactions survive crashes, guaranteed by write-ahead logging (WAL). Most relational databases (PostgreSQL, MySQL InnoDB, SQL Server, Oracle) provide full ACID compliance. NoSQL databases often sacrifice some ACID properties for performance and horizontal scalability.

Table bloat occurs when PostgreSQL accumulates dead tuples — old row versions created by UPDATE and DELETE that are no longer visible to any transaction — without reclaiming their space. Since PostgreSQL's MVCC keeps old versions in the heap, heavy write workloads accumulate dead rows that increase table size, slow sequential scans, and pollute buffer cache. VACUUM reclaims dead tuple space for reuse within the table but does not return space to the OS. VACUUM FULL rewrites the table compactly (requiring an ACCESS EXCLUSIVE lock, causing downtime). pg_squeeze and pg_repack are online alternatives. Autovacuum monitors bloat and runs VACUUM automatically based on cost-based thresholds.

SELECT relname, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;