It's easy to hit a confusing SQL bug because of one quirk: you write SELECT on the very first line, yet the database evaluates it near the very end. Try writing WHERE SUM(sales) > 4000 and you get a syntax error. Add GROUP BY, and the individual receipts you still needed disappear. Later, your table grows to tens of millions of rows, queries crawl, and two transactions suddenly freeze each other.
Let's take one simple sales table—store_sales(id, store, sales, sale_date)—and walk through seven core SQL mechanisms. The examples are tested on PostgreSQL, though the same logic applies across most relational databases.
Filtering Order and Window Functions: When Rows Collapse and When They Don't
Let's start with the mix-up between WHERE and HAVING. The difference comes down to timing. When you need to drop unwanted raw rows before any math happens, WHERE does the job. It runs before GROUP BY, so it can use regular indexes and shrink the dataset right away.
HAVING kicks in after GROUP BY, once rows are grouped and sums are ready. That's where you filter on aggregates like SUM() or COUNT(). If you put a store filter inside HAVING, the database sums every store first and only then throws away the ones you didn't want. Pure wasted work.
-- Filter out extra stores with WHERE,
-- then group and keep totals above 4000
SELECT store, SUM(sales) AS total_sales
FROM store_sales
WHERE store IN ('store1', 'store2')
GROUP BY store
HAVING SUM(sales) > 4000;
Here, only rows for store1 and store2 survive the first step, then the database sums sales and keeps stores totaling over 4000. You couldn't write that in WHERE because the sums don't exist yet at that stage.
Now take a different task: list every individual sale, but show the store's total revenue and each receipt's rank right beside it. A plain GROUP BY won't work—it collapses all rows for a store into one summary row. Window functions with OVER() solve this. They compute a total or rank across a "window" of rows tied to the current row, without merging the rows together.
-- Compute store revenue and receipt rank
-- while keeping every sale as its own row
SELECT
store,
sale_date,
sales,
SUM(sales) OVER (PARTITION BY store) AS store_total,
ROW_NUMBER() OVER (
PARTITION BY store
ORDER BY sales DESC
) AS rn
FROM store_sales;
PARTITION BY store splits the table into separate windows per store, and ORDER BY sales DESC inside ROW_NUMBER() ranks receipts from largest to smallest. Click through the steps in the mini-demo below to watch the difference live.
How store_sales Changes Across WHERE, HAVING, and OVER()
Switching between step three and step four makes it obvious: GROUP BY shrinks four rows into two totals, and HAVING keeps one. The window function hides nothing—it just appends calculated columns on the right.
CTEs vs. Nested Subqueries: Readability and Hidden Materialization
When a query grows three levels of nested SELECT statements, you have to read it from the inside out. SQL offers CTEs (Common Table Expressions) via WITH for this. A CTE declares a named temporary result before the main query: you read the code top to bottom, can reuse the block multiple times, and can walk category trees with WITH RECURSIVE.
-- Move aggregation into a named step top_stores
WITH top_stores AS (
SELECT store, SUM(sales) AS total_sales
FROM store_sales
GROUP BY store
)
SELECT *
FROM top_stores
WHERE total_sales > 4000;
A myth often pops up in code reviews: rewrite a subquery as a CTE and it will run five times faster. Sadly, no. According to the PostgreSQL documentation on WITH queries, starting in version 12, the planner does not materialize a single-use, side-effect-free CTE by default. It simply folds it into the main query so it can push WHERE down to indexes. A CTE referenced multiple times is computed once and cached by default. You can override this explicitly with AS MATERIALIZED or AS NOT MATERIALIZED if the planner picks a poor plan.
Three Ways to Speed Up Reads: Partitions, Materialized Views, and Covering Indexes
Once store_sales hits tens of millions of rows, three different tools help speed up reads.
First is partitioning (Partitioned Tables). You physically split a large table into chunks, such as by sale_date year: before 2023, 2023, 2024, and 2025+. See the full syntax in the PostgreSQL partitioning documentation.
-- Parent table defines the date-range partitioning rule
CREATE TABLE store_sales (
id BIGINT,
store TEXT,
sales NUMERIC,
sale_date DATE NOT NULL
) PARTITION BY RANGE (sale_date);
-- Physical partition for 2024
CREATE TABLE store_sales_2024 PARTITION OF store_sales
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
If a query filters with WHERE sale_date >= '2024-01-01', partition pruning kicks in: the database never even opens the 2022–2023 files. Dropping an old year becomes an instant DROP TABLE instead of a heavy DELETE. Just don't add partitions to small tables—they only add complexity.
Second is a MATERIALIZED VIEW. A normal VIEW stores nothing on disk; it's just a saved query that runs fresh every time. Data stays live, yet heavy reports hammer the database. A materialized view saves the computed result on disk: reads are instant, though numbers stay frozen until you refresh it.
-- Save the computed revenue summary to disk
CREATE MATERIALIZED VIEW mv_store_totals AS
SELECT store, SUM(sales) AS total_sales
FROM store_sales
GROUP BY store;
-- Unique index enables non-blocking refreshes
CREATE UNIQUE INDEX idx_mv_store_totals_store
ON mv_store_totals (store);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_store_totals;
A regular REFRESH blocks readers for the entire recalculation. Adding CONCURRENTLY avoids blocking, though it requires a UNIQUE INDEX on the view.
Third is covering indexes with INCLUDE. A standard index on store finds matching rows, yet the database still has to visit the main table (heap) to fetch sales. Put sales in INCLUDE, and PostgreSQL stores it in the B-tree index leaf pages without making it part of the sort key.
-- Store sales in index leaves for table-free reads
CREATE INDEX idx_store_sales_cov
ON store_sales (store)
INCLUDE (sales);
Now the planner can choose an Index-Only Scan and answer the query straight from the index. Per the Index-Only Scans documentation, PostgreSQL relies on the visibility map maintained by autovacuum. If that map goes stale, table fetches come back. Also, extra INCLUDE columns bloat the index and slow down INSERT/UPDATE. You can always verify whether partition pruning or an Index-Only Scan kicked in using EXPLAIN (ANALYZE, BUFFERS).
Deadlocks: How Two Transactions Freeze Each Other and Three Rules to Stop Them
A deadlock happens when two transactions wait in a cycle. Transaction A locks row 1 and waits for row 2, while Transaction B holds row 2 and waits for row 1. Neither can move forward.
A classic production case is opposing account transfers (1 -> 2 and 2 -> 1) where code locks rows in whatever order the parameters arrived. PostgreSQL detects the cycle and forcibly rolls back one transaction with deadlock detected (SQLSTATE 40P01).
-- Always lock rows in consistent ascending id order
BEGIN;
SELECT id, sales
FROM store_sales
WHERE id IN (101, 205)
ORDER BY id
FOR UPDATE;
UPDATE store_sales
SET sales = sales + 500
WHERE id = 101;
COMMIT;
Protection rests on three simple rules. First, keep transactions short: never make network calls or external API requests inside BEGIN ... COMMIT. Second, always lock tables and rows in the same predictable order, such as ascending id, so opposing transfers simply line up in a queue. Third, add retry logic in your application code for error 40P01 so a rare conflict under load stays invisible to the user.

