Topic: sql
-
Full-text search in PostgreSQL with tsvector
PostgreSQL turns text into a tsvector of normalised lexemes using a language configuration, matches it against tsquery, ranks with ts_rank and indexes it with GIN; it handles stemming and stop words but not typos or synonyms out of the box.
-
Storing derived data in PostgreSQL: generated columns versus materialized views
A generated column derives one value per row from that row alone, either virtual (computed on read) or stored (computed on write), and may use only immutable expressions; a materialized view stores the result of any query and is only as fresh as its last REFRESH. Use stored generated columns for per-row normalisation that should be indexed, materialized views for expensive aggregates that may lag, and a maintained summary table when neither fits.
-
Upsert, row limits and identifier quoting across PostgreSQL, MySQL, SQLite and SQL Server
PostgreSQL's ON CONFLICT, MySQL's ON DUPLICATE KEY UPDATE, SQLite's PostgreSQL-style ON CONFLICT and SQL Server's MERGE are three families of upsert syntax, LIMIT/OFFSET has at least three shapes, and a quoted identifier means double quotes, backticks or square brackets depending on the engine.
-
NULL in SQL: three-valued logic and its traps
NULL means unknown, so comparisons with NULL yield unknown rather than true or false; WHERE filters drop unknown rows, NOT IN with a NULL matches nothing, and aggregates skip NULLs. Use IS NULL, IS DISTINCT FROM and COALESCE deliberately.
-
Common table expressions and recursive queries with WITH
WITH names a subquery for the rest of a statement; WITH RECURSIVE evaluates a non-recursive term, then repeats a recursive term until it produces no new rows, which walks trees and graphs of any depth in one query. Single-use, side-effect-free CTEs are folded into the outer query unless MATERIALIZED is written, and SEARCH and CYCLE clauses handle ordering and loops.
-
Planner statistics in PostgreSQL: statistics targets, correlated columns and misestimates
The planner estimates row counts from per-column statistics that ANALYZE samples (most common values, histograms, distinct counts) and assumes independent columns; misestimates come from stale statistics, skewed columns with too few entries, correlated columns and expressions. Raise the statistics target on specific columns, create extended statistics for correlated columns or expressions, re-analyze, and compare estimated with actual rows.
-
Reading a PostgreSQL query plan with EXPLAIN ANALYZE
EXPLAIN shows the planner's chosen tree with estimated costs; EXPLAIN ANALYZE runs the query and adds actual times and row counts. Compare estimated with actual rows, find the node with the largest actual time, and check for sequential scans on large tables and misestimated joins.
-
NULL in SQL: three-valued logic and its pitfalls
NULL means unknown, so any comparison with NULL yields neither true nor false but unknown. WHERE discards unknown rows, NOT IN with a single NULL matches nothing, aggregates skip NULL, and a unique constraint allows multiple NULLs. Use IS NULL, IS DISTINCT FROM and COALESCE deliberately.
-
Savepoints and the aborted-transaction state in PostgreSQL
After any error inside a transaction block PostgreSQL rejects every further command until the block is rolled back; a savepoint set before a risky statement lets ROLLBACK TO SAVEPOINT discard only that part and continue. Use savepoints deliberately and sparingly, and make error handlers roll back instead of retrying on the same connection.
-
Cursor pagination instead of offsets: pages that stay stable when data changes
Offset pagination makes the database compute every skipped row anyway, and pages shift whenever rows are inserted or deleted in between. Cursor (keyset) pagination asks for "the next 20 after key X", uses the index and returns each row exactly once. It requires a unique sort order and costs you page numbers.
-
Schema conventions for a new PostgreSQL database: names, identifiers, timestamps and text
Decide a handful of conventions before the first migration: lower-case snake_case names that never need quoting, one id strategy applied everywhere, timestamptz for every point in time with created_at on every table, text instead of varchar(n), and explicit NOT NULL and foreign keys; write them down so every later migration follows them.
-
Bulk operations instead of per-row loops: round trips, transactions and COPY
A loop that sends one statement per row pays a round trip, a parse and often a commit per row; a bulk operation sends the set in one statement, one stream or one transaction. PostgreSQL's own guidance is to commit once, use COPY for loads and build indexes after loading; client libraries offer executemany and copy for the same reason.
-
Data quality checks: freshness, volume, nulls and uniqueness as a minimum set
Four cheap checks catch most broken load runs: is the source fresh enough, did a plausible number of rows arrive, are keys and measures populated, and is the declared grain unique? Write each check as a query that returns bad rows, run it after loading and before publishing, and separate warnings from blocking failures.
-
What connection-pool size relative to CPU cores have teams settled on for a PostgreSQL server, and which measurement made them change it?
Open question: the PostgreSQL wiki offers a sizing formula for active connections built around core count and effective spindle count, and the server's max_connections default is typically 100; what pool sizes have teams actually ended up with after tuning, how far from the formula were they, and what evidence (lock waits, queueing at the pooler, CPU saturation, latency) drove each change?
-
Soft deletes versus archive tables
A soft delete keeps the row with a deleted_at marker, so every query must filter it out and every unique constraint must become a partial index; an archive table moves the row out of the live table, so live queries stay simple and history lives in one place. Choose by who reads deleted data and how often, and put the choice into constraints and views rather than into every query.
-
Writing an upsert with INSERT ... ON CONFLICT
INSERT ... ON CONFLICT (columns) DO UPDATE SET ... turns insert-or-update into one atomic statement driven by a unique index: name the conflict target, update only the columns that should change, use EXCLUDED for the proposed row, and add a WHERE that skips no-op updates. MERGE covers the multi-branch cases ON CONFLICT cannot express.
-
N+1 queries: detecting them by counting and fixing them by batching
Loading N parent rows and then touching a lazy relationship on each emits N+1 queries; the cost grows with data, not code, so it passes small-fixture tests. Detect it by asserting query counts per request, make unwanted lazy loads raise, and fix it with joins for to-one relations and IN-batched second queries for collections.
-
Normalising to third normal form and choosing when to denormalise
Normal forms remove repeating groups and facts stored in more than one place; third normal form means every non-key column depends on the key and nothing else. Normalise by default for transactional data and denormalise only in named, derived columns whose source of truth stays normalised.
-
Declarative constraints in PostgreSQL: CHECK, UNIQUE and foreign keys with ON DELETE
Constraints make the database reject invalid states for every writer, not just the application: CHECK for per-row rules, NOT NULL for required values, UNIQUE for identity, and foreign keys with an explicit ON DELETE action. Choosing the referential action and indexing the referencing column are the two decisions most often skipped.
-
Finding the statements that cost the most with pg_stat_statements
pg_stat_statements aggregates call counts, execution time and block counts per normalised statement across the whole server. Load it through shared_preload_libraries, rank by total_exec_time and separately by calls, compare mean with maximum, read the buffer columns, then take the top statements to EXPLAIN ANALYZE and compare before and after by queryid.
Machine-readable: JSON