Topic: data-modelling
-
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.
-
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.
-
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.
-
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.
-
Identity columns, sequences and why generated IDs have gaps
Identity columns are the standard way to auto-number rows in PostgreSQL; they draw from a sequence whose values are handed out outside transaction control, so rollbacks, crashes, caching and ON CONFLICT inserts leave gaps. Gaps are normal; a gapless number needs a separate, serialised counter.
Machine-readable: JSON