Sql | XB Field Notes

A tool that exposes the planner's chosen execution strategy.

EXPLAIN shows how Postgres will do the work

EXPLAIN prints the planner’s proposed execution plan; EXPLAIN ANALYZE executes the statement and adds measured rows and times. A slow query needs both a hypothesis and a measurement. Save the query parameters, PostgreSQL version, and original plan before changing an index or rewriting a join. Those details make the next plan comparable and prevent an improvement on one input from hiding a regression on another. ...

Bound text stays a SQL value while its intended operator still determines how it matches.

Query parameters preserve the boundary between values and SQL

A parameterized query sends the database instruction and its values through distinct API arguments. The database interprets the instruction as SQL and uses each parameter where a value belongs. This boundary works in embedded engines such as SQLite as well as client-server databases; it does not depend on a network exchange or on an application explicitly managing named server statements. ...

A single statement that inserts or, on unique contradiction, updates instead.

INSERT ... ON CONFLICT turns a race into one statement

INSERT … ON CONFLICT lets PostgreSQL resolve an insert collision inside the statement that performs the write. It replaces a fragile “check whether the key exists, then insert” sequence with an explicit conflict policy backed by an eligible index or constraint. It does not turn every insertion error into a successful update or define the application’s merge policy for you. ...

The moving slice of rows a window function aggregates around the current row.

A window frame decides which rows one aggregate sees

A window partition determines which rows belong together; a window frame determines which of those rows feed a frame-sensitive calculation for the current row. Confusing these boundaries can turn a running total into a repeated subtotal or make last_value return the current value when the report expected the final value in the partition. ...

A SQL feature that produces multiple aggregation levels from one GROUP BY clause.

GROUPING SETS build several aggregate views

GROUPING SETS asks one SQL statement to produce several aggregation levels. A sales report can return totals by region and product, region subtotals, and a grand total together. The result contains different kinds of rows, so identifying each row’s level is as important as calculating its amount correctly. ...

A referencing table is checked against its parent, with historical validation tracked separately.

A foreign key refuses to let a reference dangle

Adding a PostgreSQL foreign key to an existing table is both a schema change and a statement about old data. New writes can be checked immediately while historical rows still contain broken references. Separate the constraint’s enforcement, its validation state, and its checking time when planning a migration or diagnosing an unexpected integrity failure. ...

A row looking at a sliding window of neighbouring rows to compute rank or running totals.

Window functions give a row its neighbourhood

Window functions let a report retain individual readings while adding information from related rows. A sensor reading can show its predecessor, its change, and its share of the sensor’s total without reducing the sensor to one output row. Correctness depends on defining the population, the partition, and the ordering before interpreting those extra columns. ...