テーマ： databases
-
Keep transaction boundaries visible
Document which state changes commit together and what can happen between database transactions and external calls.
-
PostgreSQLでのtsvectorを使った全文検索
PostgreSQLは、言語設定を使ってテキストを正規化された語彙素(lexeme)からなる`tsvector`に変換し、それを`tsquery`と照合し、`ts_rank`でスコアを付け、GINでインデックスを作成する。ステミングとストップワードの処理は標準で行われるが、タイプミスや同義語には標準では対応しない。
-
Event sourcing and CQRS: what they buy and what they cost
Event sourcing stores every state change as an immutable event and derives current state by replay; CQRS separates the write model from read models. Both add auditability and flexibility at the price of complexity and eventual consistency.
-
Managing PostgreSQL extensions: installing, versioning, updating and dumping them
An extension packages SQL objects and often a shared library under one name with a control file and versioned scripts; CREATE EXTENSION installs it per database, ALTER EXTENSION UPDATE applies the author's update scripts, and pg_dump emits only the CREATE EXTENSION line. Keep the installed files, the catalog version and the loaded library in step, especially across package upgrades and pg_upgrade.
-
Seed data and fixtures for local databases: small, idempotent and versioned with the schema
Separate reference data (needed everywhere), sample data (development and demos) and test data (created by tests); write the seed as idempotent code with upserts on natural keys, keep the sample set small and named, run it after migrations in both the setup script and CI, and never seed developer machines from raw production data.
-
Designing an append-only time-series table in PostgreSQL
Store measurements in a table partitioned by time range with timestamptz, a composite key of series and time, indexes matched to the query pattern (B-tree per series, BRIN for whole-table time scans) and retention implemented by detaching and dropping partitions instead of DELETE; the choices follow from rows arriving in time order and leaving in whole time slices.
-
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.
-
VACUUM, autovacuum and table bloat
PostgreSQL's MVCC leaves dead row versions behind after updates and deletes; VACUUM reclaims them and maintains statistics and transaction-id wraparound protection. Autovacuum should stay on and be tuned for busy tables.
-
Ephemeral databases in containers for integration tests
Start the real database engine in a throwaway container per test run, keep its data on memory, apply migrations once to a template database and copy it per test file; tests then exercise the real planner, constraints and isolation behaviour instead of an in-memory substitute.
-
Transaction isolation levels in practice
Read committed, repeatable read and serializable trade concurrency for consistency; knowing which anomalies each level allows decides when to add explicit locks or retries.
-
Datenbankänderungen ohne Ausfall: Expand und Contract
Ein Schema in drei einzeln auslieferbaren Schritten ändern: erweitern (neue Spalte oder Tabelle anlegen, alte behalten), migrieren (doppelt schreiben und in Häppchen nachfüllen), zusammenziehen (Altes entfernen, sobald aller Code das Neue nutzt); lange Sperren vermeiden, indem keine Tabelle in einem Statement umgeschrieben wird und lock_timeout jede Wartezeit begrenzt.
-
Test data without production personal data
Give development, CI and staging realistic data by generating it: classify columns, write seeded generators that pass the application's validators, produce volume with generate_series, copy only distributions from production, mark generated records recognisably, and remove the shortcut of dumping production.
-
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.
-
Read-only maintenance mode: serving reads while writes are paused
For storage moves, failovers and long migrations, a service can keep serving reads and refuse writes with a clear message instead of going dark; PostgreSQL's default_transaction_read_only makes new transactions read-only at the database as a backstop, and HTTP 503 with Retry-After tells clients when to try again. The mode needs one switch, a user-facing message and a rehearsal.
-
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.
-
JSONB columns: what they are good for and when a column is better
jsonb stores parsed JSON in a binary form that can be indexed with GIN and queried with containment and path operators; it suits sparse, externally defined or genuinely variable attributes. Data with a fixed shape, data that needs constraints, foreign keys or per-field updates, and large frequently changed documents belong in ordinary columns.
-
Preventing SQL injection with parameterised queries
Never build SQL by concatenating untrusted strings; pass values as parameters so the driver sends them separately from the statement, and allow-list any identifiers that must be dynamic.
-
Database connection pooling and its limits
Each PostgreSQL connection is a process with memory cost; applications should keep a small pool sized to real concurrency, set acquisition timeouts, and never let request handlers open ad hoc connections.
-
Measured PostgreSQL SKIP LOCKED claims with four concurrent queue consumers
Four concurrent transactions each claimed 25 synthetic jobs in PostgreSQL 16.15. The returned 100 IDs were unique and no jobs remained unclaimed; this verifies one bounded claim phase, not exactly-once processing or broker replacement.
-
Sagas: multi-step workflows across services with compensation instead of rollback
A saga is a sequence of local transactions in different services, each triggering the next; if a step fails, earlier steps are undone by compensating transactions the developer writes. Sagas restore consistency without distributed transactions but give up isolation, so intermediate states are visible and compensations must be designed, not assumed.
機械可読： JSON