主题： data-modelling
-
在 PostgreSQL 中设计仅追加的时间序列表
将测量值存入按时间范围分区的表，使用 timestamptz、由序列与时间组成的复合键，以及匹配查询方式的索引：单序列查询使用 B-tree，全表时间扫描使用 BRIN。保留期通过分离并删除分区实现，而不是 DELETE。这些选择源于数据按时间顺序到达，并按完整时间片移除。
-
缓慢变化维度：覆盖值、添加行或添加列
维度属性变化时（客户搬家、产品重新分类），类型 1 覆盖原值并丢失历史，类型 2 使用新代理键添加一行，包含生效日期、失效日期和当前行标志，类型 3 则在额外列中保留旧值。快照工具每次运行时比较更新时间戳或一组列，以实现类型 2。
-
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.
-
将同意和偏好记录作为数据：选择了什么、何时选择、通过哪个界面
将个人选择建模为只追加的事件（主体、用途、选择、时间、来源、文本版本），并派生出当前状态视图，让每个使用方在实际使用时读取。用户行上的一个布尔值无法回答某次操作发生时用户同意了什么，或哪些用户受到横幅缺陷影响。
-
Data minimisation as a schema and logging discipline
Store only the personal data a feature needs, at the coarsest precision that serves it, and keep identifiers out of logs by allowlisting fields; every column, log field and derived store that carries personal data is a design decision with a purpose, an owner and an expiry, not a default.
-
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.
-
PostgreSQL 声明式约束：CHECK、UNIQUE 与带 ON DELETE 的外键
约束让数据库对所有写入者拒绝无效状态，而不只是约束应用：CHECK 处理行级规则，NOT NULL 规定必填值，UNIQUE 保证唯一性，外键则应显式选择 ON DELETE 动作。最常被忽略的两项决定，是引用动作的选择，以及是否为引用列建立索引。
-
Star schema basics: facts, dimensions and declaring the grain
A star schema stores measurements in fact tables and descriptive context in dimension tables linked by keys; the design starts by declaring the grain, what one fact row represents, because every measure and dimension must be consistent with it. Fully additive measures sum across any dimension, semi-additive ones not across time, and ratios must be stored as their components.
-
Pseudonymisation versus anonymisation as engineering techniques
Pseudonymisation swaps direct identifiers for tokens while keeping a way back (a key or a lookup table) and leaves one record per person; anonymisation aims to remove the association between records and people for everyone. Use keyed HMAC per purpose for pseudonyms, and treat a pseudonymised table as personal data with quasi-identifiers still in it.
-
标识列、序列，以及自动生成的 ID 为何会有缺号
标识列是 PostgreSQL 为数据行自动编号的标准方式。编号来自在事务控制之外分配值的序列，因此回滚、崩溃、缓存以及 ON CONFLICT 插入都会留下缺号。缺号是正常现象；连续无缺号的编号需要独立且串行化的计数器。
机器可读： JSON