テーマ： sql
-
PostgreSQLでのtsvectorを使った全文検索
PostgreSQLは、言語設定を使ってテキストを正規化された語彙素(lexeme)からなる`tsvector`に変換し、それを`tsquery`と照合し、`ts_rank`でスコアを付け、GINでインデックスを作成する。ステミングとストップワードの処理は標準で行われるが、タイプミスや同義語には標準では対応しない。
-
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.
-
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: dreiwertige Logik und ihre Fallen
NULL bedeutet unbekannt, darum ergibt jeder Vergleich mit NULL weder wahr noch falsch, sondern unbekannt; WHERE verwirft unbekannte Zeilen, NOT IN mit einem NULL trifft nichts, Aggregate überspringen NULL, und ein Unique-Constraint lässt mehrere NULL zu. IS NULL, IS DISTINCT FROM und COALESCE bewusst einsetzen.
-
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 statt Offsets: Seiten, die bei Änderungen stabil bleiben
Offset-Pagination lässt die Datenbank alle übersprungenen Zeilen trotzdem berechnen und verschiebt Seiten, sobald dazwischen eingefügt oder gelöscht wird; Cursor- oder Keyset-Pagination fragt «die nächsten 20 nach Schlüssel X», nutzt den Index und liefert jede Zeile genau einmal. Voraussetzung ist eine eindeutige Sortierung, der Preis ist der Verzicht auf Seitenzahlen.
-
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.
-
Datenqualitätsprüfungen: Aktualität, Menge, Nullwerte und Eindeutigkeit als Mindestsatz
Vier billige Prüfungen fangen die meisten kaputten Ladeläufe: Ist die Quelle frisch genug, kam eine plausible Zeilenzahl, sind Schlüssel und Kennzahlen gefüllt, ist die erklärte Körnung eindeutig? Jede Prüfung als Abfrage formulieren, die fehlerhafte Zeilen liefert, nach dem Laden und vor dem Veröffentlichen ausführen, Warnung und Blockade trennen.
-
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.
-
Window functions: aggregates without collapsing rows
A window function computes a value over a set of rows related to the current row (OVER with PARTITION BY and ORDER BY) while keeping every input row, which expresses running totals, rankings, top-N per group and previous-row comparisons in one pass; the frame clause decides which rows the function sees.
機械可読： JSON