SQL

Database / 15 Sep 2026 / 6 min read

PostgreSQL Extended Statistics Model Correlated Columns

PostgreSQL normally collects statistics for each column independently. That model works well when predicates on separate columns are close to independent, but it can misestimate row counts when the values move together. A table might store country_code and currency_code, for example. If most rows with country_code = 'JP' also have currency_code = 'JPY', multiplying the two single-column selectivities treats a strong relationship as coincidence. The resulting cardinality estimate can be far below the actual row count.

PostgreSQL Database

Database / 14 Sep 2026 / 5 min read

PostgreSQL Skip Scan Reuses Multicolumn B-Tree Prefixes

A multicolumn B-tree is ordered first by its leading key, then by later keys inside each leading-key group. That ordering normally favors predicates that constrain the left side of the index. PostgreSQL 18 can also use skip scan in selected cases where a query constrains a later key and leaves an earlier key without an equality condition. Skip scan does not turn column order into an irrelevant detail. It changes the cost of some searches by allowing the executor to perform repeated targeted probes instead of reading a large continuous span of the index.

Database PostgreSQL

Database / 14 Sep 2026 / 4 min read

PostgreSQL Expression Indexes Store Derived Keys

A PostgreSQL index key does not have to be a column copied directly from a table row. It can be the result of an expression computed from that row. The stored key then represents the transformed value, allowing a matching predicate to use ordinary indexed access instead of computing the expression across every candidate row. Case-normalized text is a compact example. An application may preserve the original spelling of an email address while searching on a normalized form:

PostgreSQL Database

Database / 14 Sep 2026 / 6 min read

PostgreSQL CTE Materialization Controls Planner Boundaries

A PostgreSQL common table expression can either become part of the surrounding query plan or remain a separately computed result. That distinction changes more than plan shape. It controls whether restrictions can move across the CTE boundary and whether repeated references can cause repeated computation. Since PostgreSQL 12, a non-recursive, side-effect-free CTE is eligible for folding into its parent query. PostgreSQL normally folds such a CTE when the parent references it once. Multiple references normally lead to materialization instead. MATERIALIZED and NOT MATERIALIZED make that boundary explicit when the default does not fit the query.

PostgreSQL Database

Database / 08 Sep 2026 / 8 min read

Get Changed Rows Directly with SQLite RETURNING

A database write often creates information the application immediately needs. An INSERT may generate an ID and timestamp. An UPDATE may calculate a new counter. A DELETE may need to return enough data for an audit event. The familiar approach is to write first and query afterward, but SQLite has a cleaner option for many of these cases: RETURNING. Here’s the idea: INSERT INTO jobs (name) VALUES ('resize-images') RETURNING id, name, created_at; The write and the values I care about stay in one SQL statement. That is convenient, but the more interesting part is understanding exactly what RETURNING promises—and what it does not.

Database SQLite

Database / 08 Sep 2026 / 8 min read

Enforce Column Types with SQLite STRICT Tables

SQLite’s flexible typing is useful until an application accidentally relies on it. I have seen schemas declare an INTEGER column and then assume that every stored value must be an integer. In an ordinary SQLite table, that assumption is too strong: SQLite can preserve a value that cannot be converted to the column’s preferred type. That flexibility is intentional, but for application data I often want mistakes to fail at the write boundary instead of surfacing later in a query.

Database SQLite

Database / 06 Sep 2026 / 12 min read

Use SQLite WITHOUT ROWID for Composite Primary Keys

A table with a composite primary key often looks straightforward in SQL: two or more columns together identify one row. In SQLite, however, the storage layout depends on whether the table is an ordinary rowid table or a WITHOUT ROWID table. That difference matters when the natural key is already the identity you use for nearly every lookup. An ordinary SQLite table normally keeps a hidden integer rowid as its storage key and implements a non-integer or composite PRIMARY KEY with a separate unique index. A WITHOUT ROWID table instead makes the declared primary key the key of the table’s main B-tree.

Database SQLite

Database / 05 Sep 2026 / 10 min read

Derive Row Values in SQLite with Generated Columns

Applications often store values that can be calculated from other columns: an order-line total from quantity and unit price, a normalized search key from text, or a duration from two timestamps. The tempting approach is to calculate the value in application code and save both the inputs and the result. That creates two sources of truth. If one code path updates the inputs but forgets to update the derived value, the row becomes internally inconsistent.

Database SQLite

Database / 04 Sep 2026 / 10 min read

Use Partial Indexes to Index the Rows You Actually Query

A normal database index contains an entry for every table row that qualifies for the indexed columns. That is often appropriate, but some applications repeatedly query only a small, stable subset of a table. Consider a task table where most tasks eventually become completed or archived, while the application dashboard mainly reads current open tasks. A full index on project_id keeps index entries for historical rows even though those rows are rarely part of the hot query path.

Database SQL

Database / 04 Sep 2026 / 12 min read

Use EXISTS and NOT EXISTS for Relationship Checks in SQL

Many SQL queries do not actually need data from a related table. They only need to answer a yes-or-no question: Does this customer have at least one paid order? Is this project missing an active owner? Does any inventory row satisfy this product requirement? A common first attempt is to join the tables and then remove duplicates. That can work, but it makes the query produce more rows than the problem requires and then asks a later operation to repair the result.

Database SQL

Database / 04 Sep 2026 / 10 min read

Traverse Hierarchical Data with Recursive SQL CTEs

Hierarchical data appears everywhere: employees report to managers, comments reply to other comments, folders contain folders, and categories form parent-child trees. The table structure is usually simple. The query is the hard part. A normal join follows a fixed number of relationships. A recursive common table expression, or recursive CTE, can follow the same relationship repeatedly until there are no more rows to visit. The key mental model is: start with an anchor set, repeatedly derive the next set from the previous one, then return the accumulated rows.

Database SQL

Database / 04 Sep 2026 / 11 min read

Prevent Lost Updates with Optimistic Locking in SQL

Two users can read the same database row, make different changes, and both believe their update succeeded. If the second write silently replaces the first, the application has a lost update. This is easy to miss because each individual SQL statement can be valid. The bug appears only when multiple requests overlap in time. One practical way to prevent this is optimistic locking: let readers proceed without holding a database lock, but make every write prove that the row is still the version the writer originally read.

Database SQL

Database / 03 Sep 2026 / 13 min read

Use SQL Window Functions Without Losing Row Detail

Many SQL problems ask for a calculation across several rows while still returning each original row. For example, you may need to show every order together with the customer’s running spend, rank products inside each category, or compare today’s measurement with the previous one. A regular aggregate such as SUM() or AVG() can calculate across rows, but a GROUP BY query usually collapses those rows into one result row per group.

Database SQL

Database / 03 Sep 2026 / 9 min read

Understand SQL NULL with Three-Valued Logic

NULL is one of the easiest SQL concepts to recognize and one of the easiest to reason about incorrectly. The problem starts when NULL is treated as if it were an ordinary value such as 0, an empty string, or the word "unknown". It is none of those. In SQL, NULL represents the absence of a known value, and comparisons involving that absence often produce a third logical result: UNKNOWN.

Database SQL

Database / 02 Sep 2026 / 8 min read

SQLite Savepoints: Partial Rollback Inside a Transaction

A transaction normally gives application code an all-or-nothing boundary: either commit its changes or roll them all back. Some workflows need a smaller recovery point inside that larger unit of work. SQLite provides that recovery point with savepoints. A savepoint lets code mark a position inside a transaction, perform additional work, and later undo only the changes made after that position. The surrounding transaction can remain active. Savepoints are useful for batch processing, optional sub-operations, library code that may run inside an existing transaction, and workflows where one recoverable step should not discard earlier valid work.

Database SQLite

Web Development / 01 Sep 2026 / 5 min read

Safe Database Migrations with the Expand-and-Contract Pattern

A schema migration can be syntactically correct and still cause an outage. Production deployments often run old and new application versions at the same time, background workers may lag behind, and a large table can turn a simple-looking DDL statement into a long lock. The expand-and-contract pattern reduces those risks by splitting an incompatible change into compatible stages. Why one-step schema changes are risky Suppose an application wants to rename users.full_name to users.display_name.

Databases SQL

Web Development / 01 Sep 2026 / 7 min read

Keyset Pagination in SQL for Fast, Stable APIs

Pagination looks simple until a table becomes large or new rows are inserted while a client is paging through results. LIMIT and OFFSET are easy to understand, but deep offsets can become expensive and changing data can make rows appear twice or disappear between requests. Keyset pagination, also called seek pagination, avoids those problems by asking for rows after a known position instead of asking the database to skip a number of rows.

SQL Databases

Database / 01 Sep 2026 / 4 min read

Keyset Pagination for Stable and Efficient Database Queries

Pagination looks straightforward with LIMIT and OFFSET, but deep offsets become increasingly expensive and can produce unstable results when rows are inserted or deleted between requests. Keyset pagination, also called seek pagination, uses the last seen sort key as the starting point for the next query. Why OFFSET degrades A typical query is: SELECT id, created_at, title FROM posts ORDER BY created_at DESC LIMIT 50 OFFSET 100000; The database still has to find and skip preceding rows before returning the page. There is also a correctness problem: if a new row is inserted at the front between requests, offsets shift and a user may see a duplicate or miss an item.

Database SQL

Database / 01 Sep 2026 / 3 min read

Enforce Non-Overlapping Time Ranges with PostgreSQL Exclusion Constraints

Applications that schedule rooms, equipment, or maintenance windows often need a simple invariant: two active reservations for the same resource must not overlap. Checking for conflicts in application code looks easy, but concurrent transactions can both pass the check before either inserts. PostgreSQL can enforce this invariant inside the database with range types and exclusion constraints. Model the interval explicitly A half-open timestamp range includes its start and excludes its end. That lets adjacent bookings touch without overlapping.

Database PostgreSQL