Database

Software Engineering / 12 Sep 2026 / 7 min read

Expand and Contract at Database Schema Boundaries

A database column can be structurally valid and still be incompatible with the application processes using it. Renaming customer_name to display_name, for example, is trivial as a data-definition operation on many databases. The harder boundary appears when one application process still issues queries against the old name while another process already expects the new one. That overlap is common whenever application replacement is not atomic. Rolling deployments, multiple service instances, delayed workers, and independent consumers can leave more than one application version active at the same time. A schema migration then has two audiences: the database engine and every executable version that can reach the database during the transition.

Software Engineering Database

Database / 09 Sep 2026 / 9 min read

Back Up a Live SQLite Database Safely with Python

Copying an SQLite file looks like an obvious backup strategy: find the .db file and copy it somewhere safe. That can be acceptable when the database is definitely idle, but it is the wrong abstraction for a database that may be changing while the copy runs. SQLite provides an Online Backup API specifically for this problem. Python exposes it as sqlite3.Connection.backup(), so an application can copy a live database into another SQLite database while preserving a consistent database snapshot.

SQLite Python

Database / 08 Sep 2026 / 7 min read

Store JSON Faster with SQLite JSONB

I like SQLite’s JSON functions because they let me keep a small amount of flexible data without immediately turning every property into a column. The trade-off is easy to miss: if I store JSON as text, SQLite has to parse that text before it can navigate the structure. Since SQLite 3.45.0, there is another option. SQLite can persist its binary JSON representation, called JSONB, directly in a BLOB. Here’s the idea: if the database is going to inspect the same JSON repeatedly, I can let SQLite store the representation it already wants to process instead of making it parse the text again.

Database SQLite

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 / 5 min read

Understanding Write Skew and Transaction Isolation

Transaction isolation is often explained with dirty reads and lost updates, but another anomaly is especially important for multi-row business rules: write skew. Write skew occurs when concurrent transactions read the same valid state, make decisions independently, and update different rows in a way that produces an invalid combined state. Because they do not overwrite the same row, ordinary write-conflict detection may not stop them. A simple invariant Imagine an on-call table where at least one doctor must remain available:

Database Transactions

Database / 02 Sep 2026 / 7 min read

SQLite WAL Mode: Concurrency, Checkpoints, and Operational Pitfalls

SQLite is often chosen because it keeps deployment simple: an application can get transactional storage without operating a separate database server. As workloads become more concurrent, however, the default rollback journal can make read and write activity interfere more than expected. Write-ahead logging (WAL) changes that coordination model. Readers can usually continue while a writer commits changes, but WAL does not turn SQLite into a multi-writer database. Correct operation still depends on short transactions, sensible busy handling, and checkpoints that can make progress.

Database SQLite

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

Database / 02 Sep 2026 / 7 min read

PostgreSQL Partial Indexes for Focused Query Workloads

A normal PostgreSQL index contains entries for every table row that has indexable values. That is often appropriate, but some workloads repeatedly query a small, well-defined subset of a much larger table. A partial index stores entries only for rows that satisfy an index predicate. When the predicate matches a stable access pattern, the index can be smaller and cheaper to maintain than an equivalent full-table index. The trade-off is specificity: PostgreSQL can use the partial index only when it can determine at planning time that the query condition implies the index predicate.

Database PostgreSQL

Database / 02 Sep 2026 / 5 min read

Covering Indexes and Index-Only Scans for Faster Database Reads

An index normally helps a database find rows. A covering index can go further: it contains all columns needed by a query, allowing the database engine to answer some reads without fetching every matching row from the table. That can reduce random I/O for read-heavy workloads, but it also makes indexes larger and writes more expensive. Covering is a workload-specific optimization, not a reason to copy every selected column into every index.

Database PostgreSQL

Database / 01 Sep 2026 / 5 min read

Transaction Isolation and Safe Database Retries

Database transactions make groups of reads and writes atomic, but atomicity alone does not answer what concurrent transactions are allowed to observe. That is the job of isolation. The practical challenge appears when correct transactions conflict. Strong isolation can intentionally abort one transaction rather than allow an invalid interleaving. Applications need to distinguish those retryable concurrency failures from ordinary errors. Isolation protects invariants, not just statements Consider two concurrent requests that reserve the last available item.

Database Transactions

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