SQL & Indexing

An index is an auxiliary structure that maps column values to row locations so the engine can find rows without scanning the whole table. The default index in PostgreSQL is a B-tree, which keeps keys sorted and supports equality and range lookups.

N+1 means one query fetches a list of N rows, then the code runs one more query per row to load related data — 1 + N round-trips, each paying network and planning cost. Fix it by fetching everything in one query: a JOIN, or a single batched WHERE id IN (...) / = ANY($1) keyed by the parent ids.

LEFT JOIN Orders onto Customers on the customer id, then filter WHERE o.customer_id IS NULL — the anti-join keeps customers with no matching order. NOT EXISTS is equivalent; NOT IN works too but returns nothing if the subquery has a NULL, so prefer LEFT JOIN ... IS NULL or NOT EXISTS.

A B-tree is a balanced, sorted, multi-way search tree with high fan-out, so it stays shallow for millions of rows and is PostgreSQL's default index. Sorted keys let it serve equality, range, ORDER BY, and prefix lookups. Search, insert, and delete are all O(log n), as it self-balances by splitting and merging nodes.

A composite index on (a, b, c) is one B-tree sorted by a, then b, then c. By the leftmost-prefix rule it serves filters on a leading prefix — a, a,b, a,b,c — but not b or c alone. Put equality columns first, any range column last.

LEFT JOIN Employee onto Departments so every department survives, GROUP BY d.id, d.name, and use COUNT(e.id) — counting the employee column gives 0 for empty departments, whereas COUNT(*) counts the one all-NULL joined row as 1. An INNER JOIN would drop empty departments.

EXPLAIN prints the planner's chosen execution plan — join order, scan types, and cost estimates — without running the query. EXPLAIN ANALYZE actually runs it and adds real row counts and timings, so comparing estimated versus actual rows reveals bad estimates, needless scans, or a missing index.

Take MAX(salary) where salary < (SELECT MAX(salary) FROM Employee): the inner max is the top salary, so the outer max is the runner-up. With no second salary this returns a single NULL row cleanly. The ORDER BY salary DESC LIMIT 1 OFFSET 1 variant instead returns no rows.

Join Employee to itself: alias one copy e (employee) and another m (manager) on e.manager_id = m.id, then keep rows where e.salary > m.salary. The same table appears twice under different aliases, walking the manager_id → id relationship within one table.

Filter to RU rows in WHERE country = 'ru', GROUP BY customer.id, email, then sum each cart with SUM(amount * price). HAVING filters groups whose total is >= 1000 (it runs after aggregation, unlike WHERE), ORDER BY that sum DESC, and LIMIT 10 keeps the top rows.

Logical order is FROM/JOIN → WHERE → GROUP BY → aggregates → HAVING → SELECT → ORDER BY. WHERE runs before grouping so it cannot reference SUM(...); HAVING runs after. INNER JOIN drops customers with no cart row, so they never form a group; LEFT JOIN keeps them with a NULL aggregate.

Rank rows with DENSE_RANK() OVER (ORDER BY salary DESC) in a subquery, then filter WHERE rnk = N. DENSE_RANK handles ties — shared salaries share a rank and the next rank is not skipped, so N=2 is the second distinct salary. Use ROW_NUMBER for exactly one row, RANK to skip ranks on ties.

Partitioning splits one logical table into smaller physical child tables by a key (range, list, or hash); the planner prunes to the relevant partitions, shrinking scans and easing per-partition maintenance like VACUUM. It stays within one server. PostgreSQL does not shard across servers out of the box — that needs an extension like Citus or application-level routing.

B-tree is the default — equality, ranges, ordering. Hash serves equality only. GIN indexes composite values: arrays, jsonb, full-text. GiST covers geometric, range, and nearest-neighbour searches. BRIN suits very large, physically-ordered tables.

Postgres MVCC leaves dead row versions behind after every UPDATE/DELETE. VACUUM reclaims that space for reuse inside the table and refreshes the visibility map and planner statistics; autovacuum runs it in the background. Plain VACUUM neither returns disk to the OS nor locks the table — only VACUUM FULL does, taking an exclusive lock and rewriting the whole table.

When a query returns a large fraction of the table, a sequential scan is cheaper than many random index lookups, so the planner skips the index. Stale statistics, a function or type cast on the indexed column, or very low selectivity also make the planner judge the index not worth using.

Use a windowed SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). The OVER ... ORDER BY makes the aggregate cumulative, and the explicit frame sums every row up to the current one. Add PARTITION BY dept_id for a per-department running total.

Number rows per department with ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) in a subquery, then keep WHERE rn = 1. PARTITION BY resets the ranking per department, so row 1 is that department's top earner. A correlated subquery on MAX(salary) per department is the alternative.

The write-ahead log records every change durably to the WAL before the corresponding data pages are flushed. A commit is acknowledged once its WAL records hit disk. After a crash the engine replays committed WAL records and discards uncommitted ones, so durability holds even though dirty data pages were never written.

The index is still chosen, yet it returns 12 rows while touching 4120 buffers — the signature of bloat: MVCC dead tuples from the heavy UPDATE/DELETE traffic accumulated faster than autovacuum could reclaim them, so the heap and index are full of dead pages the scan must wade through. Fix: make autovacuum more aggressive on this table, run VACUUM, and REINDEX (or pg_repack) to rebuild the bloated index.

Five bugs. sql.Open runs per call, leaking a new pool each time — open one *sql.DB in NewStore and reuse it. The fmt.Errorf result is discarded and the method returns no error — return a wrapped error instead. The fmt.Sprintf query is SQL-injectable — use a parameterized $1..$4 query with the args passed to Exec. go db.Exec is fire-and-forget, dropping the error and ordering — call it synchronously. And there is no context — accept ctx and use ExecContext.

PostgreSQL stamps every row version with a 32-bit transaction id (XID), and visibility is judged by comparing XIDs in a circular space. As XIDs advance, very old ones could appear to lie in the future — wraparound — making live rows seem to vanish. VACUUM prevents it by freezing old rows: marking them visible-to-all so their original XID no longer matters.