主题： sql
-
在 PostgreSQL 中使用 tsvector 实现全文搜索
PostgreSQL 会借助语言配置，把文本转换成由归一化词位（lexeme）组成的 tsvector，再用 tsquery 进行匹配、用 ts_rank 评分排序，并通过 GIN 索引加速；它开箱即用地处理词干提取和停用词，但不处理拼写错误或同义词。
-
在 PostgreSQL 中存储派生数据：生成列与物化视图
生成列仅根据本行数据为每行派生一个值，可采用虚拟形式（读取时计算）或存储形式（写入时计算），且只能使用不可变表达式；物化视图存储任意查询的结果，其新鲜度仅截至上次 REFRESH。需要索引的逐行规范化适合存储生成列，允许滞后的昂贵聚合适合物化视图，两者都不合适时则维护汇总表。
-
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.
-
使用 WITH 编写公共表表达式与递归查询
WITH 为子查询命名，供语句的其余部分引用。WITH RECURSIVE 先计算非递归项，再重复计算递归项，直到不再产生新行，从而用一次查询遍历任意深度的树和图。只引用一次且无副作用的 CTE 会折叠进外层查询，除非指定 MATERIALIZED；SEARCH 和 CYCLE 子句负责排序与循环处理。
-
PostgreSQL 规划器统计：统计目标、相关列与估算偏差
规划器根据 ANALYZE 采样得到的列统计信息估算行数，包括常见值、直方图和不同值数量，并假设列彼此独立。过时统计、偏斜列的统计条目不足、相关列和表达式都会导致误估。应提高特定列的统计目标，为相关列或表达式创建扩展统计，重新分析，再比较估算行数与实际行数。
-
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 查询：通过计数发现，用批量加载修复
加载 N 条父记录后，再逐条访问延迟加载的关联，会发出 N+1 次查询。成本随数据量而非代码量增长，因此小型固定测试数据集无法暴露问题。应断言每次请求的查询数，让不需要的延迟加载抛错；对单对象关联使用连接，对集合使用基于 IN 的第二次批量查询。
-
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 动作。最常被忽略的两项决定，是引用动作的选择，以及是否为引用列建立索引。
-
使用 pg_stat_statements 找出开销最大的语句
pg_stat_statements 按规范化语句汇总整个服务器上的调用次数、执行时间和块计数。通过 shared_preload_libraries 加载它，按 total_exec_time 排序并另按 calls 排序，比较平均值与最大值，查看缓冲区列，再对排名靠前的语句使用 EXPLAIN ANALYZE，并按 queryid 比较修改前后。
机器可读： JSON