SQL | XB Field Notes

Representative plans compare Limit over Sort over Scan with Limit over ordered index access. Both preserve the same ten tenant-17 IDs. The index is commonly used, but exact plan shape varies; the illustration was not rerun.

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. ...

A bound LIKE pattern A_1 branches to both A_1 and Ab1 because underscore matches one character. Local in-memory SQLite fixture.

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. ...

An eligible uniqueness rule selects the conflict. With existing total 5 and proposed total 3, an additive update yields 8 while replacement yields 3. The merge expression determines the policy.

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. ...

For current id 1, the default day-based RANGE includes amounts 10 and 20, while explicit ROWS ordered by day,id includes only 10.

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. ...

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. ...

After ADD FOREIGN KEY NOT VALID, historical invoice 11 still points to missing customer 99, but new invoice 12 with the same reference is rejected. Historical validation and deferred checking are separate decisions.

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. ...

Sensor A keeps values 10,15,12 with predecessors NULL,10,15 and total 37 on every row. Filtering before lag changes the population.

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. ...