PostgreSQL

PostgreSQL使用双引号对包含关键字的标识符进行转义.

\ 开头的命令被称为元命令(meta-commands),
在启动psql时加上 -E flag可以输出元命令使用的SQL查询语句.

\list\l 查看全部数据库
\list+\l+ 查看全部数据库(详细)

\c mydb 连接(使用)mydb数据库

PostgreSQL没有真正意义上的存储引擎, 它的数据库是一体的.
因此它缺乏像MySQL那样更换存储引擎来改进数据库性能的能力.

PostgreSQL支持咨询锁, 这是一个独立于数据表的功能, 可被应用程序用作分布式锁.

咨询锁可以避免客户端同时执行某些重要的SQL语句, 例如数据库模式迁移或其他类型的分布式事务.
咨询锁在会话结束时自动释放, 在客户端崩溃时不会造成死锁.

锁的key是任意bigint.

由会话持有, 直到手动释放或断开连接.
会话锁不尊重事务语义, 事务回滚不会对其产生任何影响.

-- 获取锁(如果失败, 则返回错误)
SELECT pg_advisory_lock(1);
-- 尝试获取锁(如果成功, 返回true; 如果失败, 返回false)
SELECT pg_try_advisory_lock(1);
-- 释放锁
SELECT pg_advisory_unlock(1);

事务锁在事务结束时被释放.

-- 获取锁(如果失败, 则返回错误)
SELECT pg_advisory_xact_lock(1);
-- 尝试获取锁(如果成功, 返回true; 如果失败, 返回false)
SELECT pg_try_advisory_xact_lock(1);
-- 释放锁
SELECT pg_advisory_xact_unlock(1);

https://www.postgresql.org/docs/14/warm-standby.html

wal_keep_size选项决定了主服务器上会保存多少WAL段.
如果主服务器回收了WAL段, 而从服务器还没有同步, 则从服务器将无法继续同步,
需要从新的基础备份上重新启用从服务器.

为解决此同步问题, PostgreSQL 9.4引入了Replication Slots的特性,
在所有从服务器收到WAL段以前, 不会回收WAL段.
在这种情况下, 如果从服务器一直掉线, 则主服务器会持续积累WAL段, 最终可能导致主服务器磁盘耗尽.

  • repmgr: https://repmgr.org/
  • Patroni: https://github.com/zalando/patroni

PostgreSQL标准的复制方式, 主数据库上的事务立即完成, 然后才开始复制, 副本服务器上的数据会稍有滞后.

在同步复制模式下, 只有所有副本都复制完成后, 主数据库上的事务才会完成.

将数据库dump为SQL文件, dump过程不会让数据库下线.

优点:

  • 生成的备份文件比文件级备份的数据量要小, 因为dump不需要转储索引数据.

缺点:

  • 本地dump需要存储空间用于存储dump数据.
  • 对大型数据库而言速度较慢.
  • 会在dump期间降低数据库吞吐量.
  • 数据恢复比较慢.

根据通讯方式, 有两种方法:

  • 通过公开的PostgreSQL端口用pg_dump执行远程转储.
  • 通过SSH执行远程pg_dump, 转储数据通过stdout重定向至本地文件.

缺点:

  • 受远程传输的速度限制, 数据库快照隔离存在的时间可能过久.
  • 不支持增量备份.
  • 不支持断点续传.

此例子假设使用docker-compose部署PostgreSQL:

'cd /app && docker-compose exec -T {postgres} pg_dump --username {postgres} --password {password} --dbname {db} --data-only' \
> db.dump

缺点:

  • 不可在不同的运行平台和PostgreSQL版本里恢复.
  • 只能备份整个数据库.

独占式备份指的是先后调用以下两个底层命令的备份方式:

  • SELECT pg_start_backup('label')
  • SELECT pg_stop_backup()

使用这种备份方式时, 将在两个命令之间进行文件级的复制备份.

这种备份方式已经被pg_basebackup替代, 在未来的版本中很可能被移除.

将pg_basebackup与WAL重放结合起来的一种备份方式.

具体做法是使用pg_basebackup制作基本数据库备份,
再用WAL的archive_command设置一个"将WAL文件复制到其他地方"的脚本.

在PostgreSQL 9.4版本之后, 也可以通过建立从服务器, 然后以流复制(Streaming Replication)的方式替代基于archive_command和文件的方法.

优点:

  • 可以实现基于时间点的还原.
  • 增量备份.
  • 无需文件系统支持快照.

缺点:

  • WAL日志需要额外的存储空间.
  • Barman: https://pgbarman.org/
  • pgBackRest: https://pgbackrest.org/

PostgreSQL可以在运行时进行基于文件系统的快照备份, 它在重启时会根据WAL日志恢复数据库.

用rsync进行备份停机时间并不长, 因为实际上会执行两次rsync命令:

  • 第一遍执行时, 数据库仍处于运行状态, 此时的目的是备份大部分不变的数据.
  • 第二遍执行时, 要求将数据库下线, 然后通过 rsync --checksum 进行基于校验码的差异同步.
    • 使用checksum参数的原因:
      默认情况下, rsync是基于时间戳和文件大小判断内容是否相同的, 这不能保证准确.
    • 将数据库下线的原因:
      如果数据库保持在线状态, 则无法保证文件具有一致性(下线数据库相当于"手动拍摄快照").

一般来说, 第二遍备份会比第一遍备份快很多.

在突然断电后导致的文件系统损坏或遭遇硬件故障时, 可能会遇到以下错误:

[26] LOG:  database system was interrupted; last known up at 2021-04-05 06:53:05 UTC
[26] LOG:  invalid primary checkpoint record
[26] PANIC:  could not locate a valid checkpoint record
[1] LOG:  startup process (PID 26) was terminated by signal 6: Aborted
[1] LOG:  aborting startup due to startup process failure
[1] LOG:  database system is shut down

以下每一项操作都应该在有备份的前提下进行!!!

此错误可以通过清除WAL(预写日志)这一最终级别的解决方案修复:

docker run --rm -it -v <DATA>:/var/lib/postgresql/data postgres:13 bash
su postgres # 切换到postgres帐户, 其他帐户不能使用pg_resetwal命令
pg_resetwal /var/lib/postgresql/data # 如果命令失败, 则需要加上--force强制执行

但是, 此操作很可能会 损坏数据库的完整性, 例如破坏原有的表约束, 因此数据库在恢复后会无法正常使用, 需要转储并重建数据库.

请先阅读下方的"陷阱"一节.

  1. 1.
    导出数据
# 从官方Docker容器里导出数据
docker exec {container_name} pg_dump --username postgres --dbname {db_name} --data-only > db.dump
  1. 1.
    停止docker container
  2. 2.
    删除docker volume
  3. 3.
    创建docker volume
  4. 4.
    启动docker container
  5. 5.
    导入数据
# 导入数据(-i是必须的, 否则无法读取stdin)
docker exec -i {container_name} psql --username postgres --dbname {db_name} < db.dump

在数据库内容可丢失的前提下, 为了最大程度的保留未损坏的数据, 可以考虑此方案.

  1. 1.
    --inserts--data-only 标志转储数据库
  2. 2.
    恢复旧备份
  3. 3.
    导入数据
    在此步骤中, 会由于重复记录的主键冲突而输出错误信息, 忽略它们即可.

例如不能导出customized options.

因此应该先用程序初始化数据库模式, 然后再导入数据(用pg_dump导出数据时启用 --data-only 标志以免模式发生冲突).

在导入数据时会出现提示, 通常会导致相关的表导入失败
由于pg_dump默认使用 =COPY= 转储, 在恢复表时若出现约束错误, 整个表都会导入失败.

请先阅读下方的"陷阱"一节.
在pg_dump导出时启用 --inserts 标志, 此标志将脚本中的 COPY 换为 INSERT.

导入完成后不必手动清理不满足约束的数据(因为这些数据无法插入数据库).

导入数据的速度会大幅降低.

如果用INSERT插入大量记录, 则数据库会打印大量日志, 这可能导致硬盘空间不足.

如果使用Docker容器, 则应该限制容器保留的日志量.

SELECT *
FROM (
SELECT *
, ROW_NUMBER() OVER(PARTITION BY column_name) AS row
FROM table_name
) AS dups
WHERE dups.row > 1

pg_dump是官方提供的工具, 可以在保持数据库运行的情况下备份数据库.

PostgreSQL特有的 SELECT DISTINCT ON 子句可以在查询时只去除特定表达式的重复项,
保留查询中得到的其他表达式.
格式为 SELECT DISTINCT ON (expression) field_alias, 字段别名是必须的, 但可以与原名相同.

如果查询包含ORDER BY子句, 则用于去重复的字段必须是ORDER BY的第一个字段,
这是为了保证查询的稳定性(在去重复时, 除了第一个出现的行以外都会因DISTINCT而被删除),
这个特性也导致了ORDER BY的功能往往与人们的预想不符,
因此在使用 SELECT DISTINCT ON 时经常需要嵌套查询.

SELECT DISTINCT ON (expression) alias
, other_field
FROM my_table;

设置变量.
SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' | DEFAULT }

无论如何优化, PostgreSQL的全文搜索在大表(此处特指千万级以上数据集)上都无法稳定在10s内完成查询,
即使使用NVME接口的SSD作为存储介质也不可能实现.
单核运行时, 甚至JIT编译都可能花费数秒.

综上所述, 不应该将PostgreSQL用于大表的全文搜索.

PostgreSQL的全文搜索设计得很好, 但仍然缺乏全文搜索引擎的一些关键功能, 例如分面(facet).

PostgreSQL自带的全文搜索模块.

https://www.postgresql.org/docs/13/textsearch.html

  1. 1.
    在已有的表上添加一个tsvector类型的列来保存文本的tokens(需要添加触发器来自动更新值).
ALTER TABLE documents
ADD COLUMN tsv tsvector;
  1. 1.
    通过GIN或GiST编制索引(以加速 @@ 操作符), PostgreSQL建议将GIN索引用于全文搜索.
CREATE INDEX tsv_idx
ON documents
USING GIN (tsv);

注1: GIN索引不支持权重和位置值, 当查询需要这些值时, 会访问具体的列, 导致性能下降.
注2: 实测提高work_mem不能提升全文搜索的性能.

如果索引是表达式索引, 则其使用的to_tsvector函数必须是两参数版本
(单参数版本会在处理每一行时猜测文本搜索配置, 模糊的文本搜索配置不能被放在一起索引,
这是PostgreSQL的限制).
如果需要包含多种文本搜索配置, 则可以建立另一个列用来存储文本搜索配置,
然后再使用两参数版本的to_tsvector引用那一列的值.

  1. 1.
    全文搜索:
SELECT *
FROM documents
WHERE tsv @@ plainto_tsquery('查询内容');

带有rank的全文搜索由于需要访问具体的tsvector列, 性能会显得比较昂贵, 是一项I/O密集型操作.

SELECT id
, tsv
FROM documents
WHERE tsv @@ plainto_tsquery('查询内容')
ORDER BY ts_rank(tsv, plainto_tsquery('查询内容')) DESC;
  • ts_rank
    按lexeme出现的频率(词频)排名(包含此lexeme越多, 返回的值越大), tsvector需要带有计数值.
  • ts_rank_cd, 按cover density ranking(简称为CDR)计算排名.
  • ts_headline, 突出显示文档的一部分(需要提供原始文本).

参考:

  • https://www.compose.com/articles/indexing-for-full-text-search-in-postgresql/
  • https://www.lateral.io/resources-blog/full-text-search-in-milliseconds-with-postgresql

PostgreSQL不支持中文全文搜索.
虽然有为PostgreSQL提供中文的开源插件zhparser, 但该插件并没有得到很好的维护.
自行维护语言支持需要大量的精力和专有领域的知识, 缺乏可行性.

出于语言支持方面的理由(包括语言支持的可持续性), 建议使用外部搜索引擎.

-- 查询数据库支持用于全文索引的语言
SELECT cfgname FROM pg_ts_config;

tsquery 一种表示查询条件的数据类型, 另有 to_tsquery 函数可以将字符串转换为此类型.

  • 逻辑运算符 AND(&), OR(|), NOT(!).
  • <-> 接近运算符(用于连接两个单词, 对短语进行搜索, 中间的 - 可以替换为数字, 表示跳过x个单词).
    该运算符是tsquery_phrase函数的语法糖.
  • 用于改变运算优先级的括号.

一种表示tokens(令牌列表)的数据类型.

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;
-- 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'
-- 以这种形式生成的tsvector不会将单词标准化.

tsvector最大只支持1MiB的文本, 更大的文本会报错.

array_to_tsvector仅会将数组原样转换为tsvector, 不会进行任何处理, 这意味着:

  • 不会将大写字母转换为小写字母
  • 不会将字符串拆分为lexeme

当使用array_to_tsvector时, 需要手动把输入的值转换为小写.

SELECT array_to_tsvector('{fat,cat,Rat}'::text[])
@@ to_tsquery('Rat');
-- false
SELECT array_to_tsvector('{fat,cat,Rat}'::text[])
@@ to_tsquery('rat');
-- false
SELECT array_to_tsvector('{fat,cat,rat}'::text[])
@@ to_tsquery('Rat');
-- true
SELECT array_to_tsvector('{fat,cat,rat}'::text[])
@@ to_tsquery('rat');
-- true

array_to_tsvector不会返回位置和计数信息, 这使得ts_rank等函数不可用.

to_tsvector 函数可以将字符串转换为tsvector, 在转换过程中会基于数据库的语言对单词标准化.

SELECT to_tsvector('The quick brown fox jumped over the lazy dog.');
-- 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2
-- 数字是相关单词首次出现在字符串中的指针位置(从1开始).
-- 转换时会将单词标准化为词素(lexeme)供搜索使用, 例子中的jumped被转换为jump, lazy被转换为lazi.

在使用to_tsvector时, 可以用第一个参数设置使用的配置.
在默认情况下, to_tsvector会省略掉英语中的you, a, an等词,
为了阻止这种行为, 可以使用 'pg_catalog.simple' (可以简写为 'simple').

可以用setweight设置tokens的权重标签, 权重标签可以是A, B, C, D中的一个, 其中A是权重最大的值.

SELECT setweight(to_tsvector(coalesce('Hello World!','')), 'A') ||
setweight(to_tsvector(coalesce('Hello new day!','')), 'B');
-- 'day':5B 'hello':1A,3B 'new':4B 'world':2A
-- 数字后的字母是权重标签, D权重是默认权重, 在结果中会被省略.

注: 当存在权重值时, 搜索会执行[[recheck]].

用来查询倒排索引.
左值和右值需要各有一个tsvector和tsquery类型, 先后顺序无所谓.

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector
@@ 'cat & rat'::tsquery;
-- true
SELECT 'fat & cow'::tsquery
@@ 'a fat cat sat on a mat and ate a fat rat'::tsvector;
-- false
SELECT to_tsvector('The quick brown fox jumped over the lazy dog')
@@ to_tsquery('fox & (dog | clown) & !queen');
-- true

字组索引是一种类似于全文索引的计数, 但它是基于字符而不是单词的, 这使得字组索引的查全率明显高于基于单词的查询.

https://www.postgresql.org/docs/13/pgtrgm.html

gin_trgm_ops会在指定的列上使用三字组索引(trigram), 即使用滑动窗口为每3个连续的字母创建索引.
这种GIN索引可以加速文本的模糊匹配(LIKE, ILIKE, ~, ~*), 例如 LIKE '%abcdefg%'.

注1: pg_trgm扩展使用数据库定义的语言环境, 因此 当数据库语言被设置为C时, 将无法处理中文字符.
任意UTF-8语言环境都可以支持中文字符, 包括(C.UTF-8).

-- 激活扩展
CREATE EXTENSION pg_trgm;
SELECT show_trgm('你好世界, 今天的天气不错');
-- en_US.utf8
-- ["0xae5160","0xc7f84d","0xd56590","0xd6576d","0xebddfa","0xec38b0","0xf32b60","0xf98da8","0x325192","0x360c5b","0x3bb058","IgR","0x6b365b"]
-- C.UTF-8
-- ["0xae5160","0xc7f84d","0xd56590","0xd6576d","0xebddfa","0xec38b0","0xf32b60","0xf98da8","0x325192","0x360c5b","0x3bb058","IgR","0x6b365b"]
-- C
-- []

注2: 三字组索引滑动窗口的性质注定它创建出来的索引体积很大.
注3: 建立三字组GIN索引的时间可能很长.

-- 激活扩展
CREATE EXTENSION pg_trgm;
SELECT show_trgm('dadb4b54e2345679a8861ab52e4128ea')
-- [" d"," da","128","1ab","234","28e","2e4","345","412","456","4b5","4e2","52e","54e","567","61a","679","79a","861","886","8ea","9a8","a88","ab5","adb","b4b","b52","b54","dad","db4","e23","e41","ea "]
-- 结果是排序过的, 可以看出为每3个字母都创建了元素, 头尾通过空白字符补位.
-- 创建三字组索引
CREATE INDEX users_search_idx ON users
USING GIN (first_name gin_trgm_ops, last_name gin_trgm_ops);

参考: https://www.cybertec-postgresql.com/en/postgresql-more-performance-for-like-and-ilike-statements/

pg_trgm典型查询性能应远低于textsearch的性能, 因为pg_trgm查询需要执行recheck.

参考: https://pgroonga.github.io/reference/pgroonga-versus-textsearch-and-pg-trgm.html

需要使用名为 pg_bigm 的第三方模块, 使用GIN索引.

http://pgbigm.osdn.jp/index_en.html
https://github.com/pgbigm/pg_bigm

和pg_trgm一样, 在查询时需要进行recheck, 但pg_bigm提供了一个名为pg_bigm.enable_recheck的开关.

需要使用名为 PGroonga 的第三方模块(该模块是Groonga项目的PostgreSQL插件), 使用专用的 pgroonga 索引.

查询性能远高于 pg_bigm + GIN, 和 textsearch 平级, 但建立的索引也占用更大的空间(至少是GIN索引的2倍).

https://pgroonga.github.io/

基于字组的全文搜索包含两个步骤:

  1. 1.
    从索引中检索候选结果.
  2. 2.
    从候选结果中选择正确的结果, 该步骤称为recheck.
    所以存在此步骤是因为字组索引的 搜索词 和 实际的索引 未必是相等的:
    举例来说, 搜索'trial', 会在双字组索引里以这些索引值获得候选结果: 'al', 'ia', 'ri', 'tr'.
    仅靠这些索引值并不能保证搜索结果就是'trial', 还可能是别的词, 因此需要执行recheck.

recheck的效率很低, 因此会拖累全文搜索, 该步骤不能被简单关闭, 关闭后会导致结果不准确.

参考: https://pgbigm.osdn.jp/pg_bigm_en-1-1.html
通过重新编译关闭recheck: https://stackoverflow.com/a/61680253/5462167

PostgreSQL目前还不支持分片, 但已有很多fork和像Citus这样的扩展可以实现分片.

理论上, 可以通过FDW(Foreign Data Wrapper)配合表分区实现分片, 但这种方式并不优雅和完善.

PostgreSQL 10增加了声明式表分区功能, 从而解决了过去只能通过表继承和存储过程进行表分区的问题.

  • 通过将数据在物理上按一定的规则分别存储, 提高了删除/VACUUM的性能.
  • 列表分区和范围分区会替代索引的前几列, 从而减小索引的大小.
  • 未来的可伸缩性.
  • 父表上的唯一索引必须包含所有具有唯一索引的列(即主键列和唯一列).
  • 如果需要添加新分区, 则默认分区里不能存在已经符合新分区的记录.
    为了添加新分区, 则必须执行以下方案中的一个:
    1. 1.
      先把默认分区的数据提取出来, 在添加新分区后重新插入.
    2. 2.
      取消默认分区的绑定, 添加新分区后, 将符合条件的数据移动至新分区, 然后重新绑定.
      这会使得表的维护过程变得异常繁琐.

另一种解决方案是不使用默认分区, 而是由触发器在每次插入记录前确保对应的分区存在.

建议使用pg_dump将表导出后修改schema, 再重新导入.
pg_dump需要开启 --load-via-partition-root 选项, 将分区聚合为单个表.

这是因为分区表不能创建覆盖所有表的索引, 每个索引都是在单独的表上建立的.

分区表会在每个会话中加载分区元数据, 因此会增加内存用量.
查询所需的同时连接越多, 则性能越差.

分区数量增加会增加查询计划的时间, 分区越多, 则计划时间越长.

对多个分区的查询会引入并行步骤, 这可能比单表查询慢.
考虑设置与并行查询相关的属性:

  • max_worker_processes, 决定为最大的进程数.
    为达到最高性能, 应设置为核心数.
  • max_parallel_workers, 决定最大的并行worker数量, 不得超过 max_worker_processes.
    为达到最高性能, 应设置为核心数.
  • max_parallel_workers_per_gather, 决定每个Gather查询能够使用的最大并行worker数量.
    需要通过反复尝试来确定这个值, 建议最初将其设置为核心数的一半.

这会使得pg_dump导出的结果无法导入, 因为顺序不满足外键约束.
该错误可以稳定复现.
直到PosgreSQL 13, pg_dump的导出顺序仍有问题, 尚不知是否会在PostgreSQL 14里修复.

建议在导入前卸载掉所有的外键约束.

根据特定列的值的范围分区.

根据特定列的值分区.

根据特定列的值的模数和余数分区.

GIN索引从表的列中提取值将其存储到树结构(类似于B+树)来生成索引.
建立索引的速度较慢, 检索速度较快.
用于全文搜索时, GIN索引通常会很大, 可能比一般btree索引大10倍以上.

当PostgreSQL搜索GIN索引时, 总是使用位图扫描(Bitmap Scan).

CREATE TABLE search_idx (
id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
, key_str TEXT NOT NULL
, val_str TEXT NOT NULL
, val_int INT
, val_date TIMESTAMPTZ
);
-- 创建两个GIN索引的方案
CREATE INDEX search_idx_key_str_idx ON search_idx
USING GIN (to_tsvector('english', key_str));
CREATE INDEX search_idx_val_str_idx ON search_idx
USING GIN (to_tsvector('english', val_str));
-- 创建一个GIN索引的方案
CREATE INDEX search_idx_key_str_idx ON search_idx
USING GIN (
setweight(to_tsvector('english', key_str), 'A')
||setweight(to_tsvector('english', val_str), 'B')
);

fastupdate是GIN索引的一个选项, 默认开启.

该选项通过为索引创建一个临时的缓冲区来推迟实际的索引编制操作.
缺点是扫描索引时, 还需要扫描此缓冲区.

缓冲区的大小由gin_pending_list_limit决定, 默认为4MB.

PostgreSQL也实现了一般数据类型的GIN索引, 被称作btree_gin索引.

尽管对于一般数据类型来说, 查询速度会低于btree索引, 但一般数据类型字段能与可全文搜索的字段形成多列索引.
根据官网文档所说, btree_gin索引可能比单独创建btree索引和GIN索引要有效率.

btree_gin索引不能约束字段的唯一性, 而且经常不能在具有排序的查询计划中被利用.
fastupdate据称在btree_gin索引下不是那么有必要.

实际使用时发现在, 未经分区的表上, btree_gin多列索引占用的空间要显著小于纯GIN索引(接近二分之一),
并且在这种情况下甚至没有明显的性能区别.
在分区表上, 使用分区字段的btree_gin多列索引占用的空间要小幅大于纯GIN索引.
考虑到多列索引的局限性和btree_gin索引在运算符支持方面的局限性, 使用btree_gin索引可能是不划算的.

  1. 1.
    启用扩展 CREATE EXTENSION btree_gin;
  2. 2.
    只要在创建GIN索引时引入一般数据类型的字段, 就会创建出btree_gin索引

BRIN索引是一种作用在大型表上的轻量级负索引(用于在查询时排除数据而不是查找数据).

BRIN索引建立这样在一个假设上:
数据的物理存储顺序与字段的含义有关联,
因此BRIN索引可以将相关的数据块内的记录的字段范围编制成索引(只保存字段的最大值和最小值),
从而能够在查询时利用索引快速跳过那些不符合此字段范围的数据.

当记录的字段值缩小时, 由于更新成本较高(顺序扫描此物理块), 默认情况下BRIN索引不会更新索引的最大值信息.

在只需要查询少数记录的情况下, BRIN索引的查询性能通常比Btree差, 但其他开销都比Btree小得多:
BRIN索引的大小可能只占Btree索引的5%~15%, 建立/插入索引的性能也远高于Btree索引.

典型的BRIN索引使用场景具有以下特征:

  • 大表(表中至少要有超过10万条记录, 否则查询计划器不会考虑BRIN索引)
  • 某一过滤字段具有自然紧凑性(从而让字段的块范围足够小)
  • 表中的记录通常是仅追加的(尽可能避免删除和更新, 从而确保有序的数据能在磁盘上相邻)
  • 查询经常需要顺序扫描(例如各种聚合命令)
  • 不希望继续使用Btree索引(索引太大, 或插入成本太高)

一种树状索引, 会自动平衡自身以保证检索速度.
检索性能比GIN差很多, 但建立索引的速度比GIN快得多.

据说占用的磁盘空间也小于GIN, 但在我的FTS项目中无法复现这一点.
GiST的查询性能不仅数倍慢于GIN(即使上调work_mem也无济于事), 还多占用了1G的硬盘空间.

文档在GiST索引中以固定长度的签名表示, 有可能发生哈希冲突, 在少数情况下会出现错误匹配.
常用于地理位置的索引和集群.

CREATE INDEX idx_fts_object_vector
ON fts_object
USING GIST (vector);

GiST的扩展.

GIN索引用于全文搜索的改进版, 通过在索引里添加位置信息, 避免了在复杂查询中访问具体的列.
https://github.com/postgrespro/rum

RUM索引比GIN索引的构建速度更慢.

项目更新似乎不是很积极, 项目页面上的TODO没有被完成.

CTID字段是表中隐式存在的系统字段, 它会在 VACUUM FULL 之后改变.

PostgreSQL没有在DBMS里内置cron功能, 定时调度必须由外部工具触发.

pg_cron是PostgreSQL的扩展, 安装后可以通过SQL语句安排cron.

物化视图是一种被缓存的视图, 用于优化查询性能.
仅在创建物化视图时, 可以一并创建首次缓存, 之后 缓存必须由视图的所有者手动刷新.

为了触发物化视图的刷新, 一般需要为其设置触发器或CRON.

物化视图不支持触发器.

CREATE TABLE AS只在创建表时查询一次, 之后就不再能够刷新; 物化视图能够刷新.

这是刷新物化视图的并行版本, 要求物化视图中必须有至少一个唯一索引.
这种刷新不会锁定物化视图的只读查询, 但 刷新速度要比一般刷新慢很多, 在一项测试中发现并行刷新要慢1倍以上.

截止PostgreSQL 13, 修改物化视图(修改视图的定义本身)的唯一途径是删除后重建.

https://www.postgresql.org/docs/13/config-setting.html

PostgreSQL可以通过postgresql.conf文件进行配置, 这些配置也可以通过SQL完成.

pg_stat_statements以一定的性能为代价, 记录数据库查询的统计信息(不要与pg_stats搞混),
并保留SQL语句本身(记录时会把真实数据替换为占位符), 使用它的主要目的是 定位慢查询.

网络上只有一份写于2011年的有关pg_stat_statements影响查询性能的记录.
https://pgsnaga.blogspot.com/2011/10/performance-impact-of-pgstatstatements.html

其结论是pg_stat_statements降低了10%的查询性能, 这是相当大的影响, 因此不建议在生产中使用.

由于pg_stat_statments需要额外的共享内存, 因此需要配置postgres.conf来加载它:

shared_preload_libraries = 'pg_stat_statements'

在修改完postgres.conf后, 需要重新启动PostgreSQL.

-- 开启此功能
CREATE EXTENSION pg_stat_statements;
-- 查询记录的统计信息
-- 如果没有通过shared_preload_libraries加载, 则会提示
SELECT *
FROM pg_stat_statements;
-- 显示各种查询的总消耗时间和平均消耗时间
SELECT (total_time / 1000 / 60) AS total
, (total_time/calls) AS avg
, query
FROM pg_stat_statements
ORDER BY 1 DESC
LIMIT 100;

该属性被用来配置查询被记录的最低毫秒数, 超过此毫秒数的查询会被输出在日志里.

在一些情况下, 会出现超长时间甚至似乎永远无法返回结果的查询.

该值是一个整数, 单位为毫秒(可以使用带有时间单位的字符串), 用于设置查询语句的超时时间.
0代表禁用此功能, 默认为0.

-- 语句级别
SET LOCAL statement_timeout = '60s';
-- 会话级别
SET statement_timeout = '60s';
-- 用户级别
ALTER ROLE postgres SET statement_timeout = '60s';
-- 数据库级别
ALTER DATABASE mydatabase SET statement_timeout = '60s';

使用 ANALYZE 更新统计信息, 以使查询计划器更准确地优化查询, 建议在每个大修改后执行此命令.

ANALYZE: 解释此查询时应该实际运行一次查询.
BUFFERS: 在解释查询结果中记录有关缓存的信息.

EXPLAIN (ANALYZE, BUFFERS)的结果是从下往上读的, 最早发生的操作在最下方.

查询计划可视化: https://explain.dalibo.com/

该属性的值可能是 hit=数字read=数字.
hit代表命中缓存的块的数量, read代表未命中缓存的块的数量(需要读取磁盘).

cost由两个数字组成, 第一个数字代表检索第一行的成本, 第二个数字代表检索所有行的估计成本.

cost = (读取的磁盘页数 * seq_page_cost) + (扫描的行数 * cpu_tuple_cost)

返回结果的估计行数

kB为单位的行平均大小.

EXPLAIN ANALYZE结果中的actual_time代表该操作所处于的时间段.
A...B意味着从时间戳A到时间戳B的这段时间在执行此操作, B - A 是实际花费的时间.

如果操作被执行多次, 则loops会大于1.
此时actual_time代表的是每次操作的平均时间, (B - A) * loops 是实际花费的时间.

意味着全表扫描, 表越大, 则性能越差.

顾名思义, 嵌套循环是循环里面包含了循环, 时间复杂度为O(n^2):
循环层数越多, 表越大, 性能就越差.

auto_explain是一个模块, 可以对查询自动输出EXPLAIN.
通过配置, 可以让它只输出慢查询的EXPLAIN.

如果设置此模块, 则可以考虑关闭重复的内置日志功能, 例如log_min_duration_statement.

PostgreSQL缓存以下信息:

  • 表数据
  • 索引
  • 查询计划

一般来说, 统计单个表的缓存命中率没有什么意义.
缓存空间由所有表共享, 单个表一般远大于缓存大小, 所以单表缓存命中率低是正常的.

-- ratio值越接近1越好
SELECT relname AS table_name
, heap_blks_read AS heap_read
, heap_blks_hit AS heap_hit
, (heap_blks_hit - heap_blks_read) / NULLIF(heap_blks_hit, 0)::float AS ratio
FROM pg_statio_user_tables;
-- ratio值越接近1越好
SELECT sum(heap_blks_read) AS heap_read
, sum(heap_blks_hit) AS heap_hit
, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) AS ratio
FROM pg_statio_user_tables;
-- ratio值越接近1越好
SELECT indexrelname AS idx_name
, idx_blks_read AS idx_read
, idx_blks_hit AS idx_hit
, (idx_blks_hit - idx_blks_read) / idx_blks_hit::float AS ratio
FROM pg_statio_user_indexes;
-- ratio值越接近1越好
SELECT sum(idx_blks_read) AS idx_read
, sum(idx_blks_hit) AS idx_hit
, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) AS ratio
FROM pg_statio_user_indexes;

PostgreSQL的缓存不能通过SQL语句清空.

网络上可查的清空缓存的方法是在停止PostgreSQL服务后,
手动清空OS缓存(通过 echo 3 > /proc/sys/vm/drop_caches 实现).

这对在Docker中运行的PostgreSQL容器似乎没有明显的效果.

shared_buffers决定了PostgreSQL使用的内存缓存量.

普遍认为, 应将此项设置为系统可用内存的25%, 默认为128MB.

https://www.postgresql.org/docs/current/pgprewarm.html

这是PostgreSQL用于预热数据库(将数据加载进缓存)的模块, 预热时需要指定需要加载的数据块的范围.

一般来说无法决定应该预热哪些块, 而随机载入部分数据没有什么道理可言, 所以并不会使用此模块.

https://www.postgresql.org/docs/13/locale.html

在 ISO C 以外的数据库语言设置下, 排序性能会显著降低.
该语言设置是在创建数据库时决定的, 无法在创建数据库后修改.

SHOW LC_COLLATE; 查看当前的数据库语言设置.

注: CC.UTF-8 是不同的语言设置, 前者不是UTF8编码,
在一些需要UTF8编码的场合会遇到困难(例如三字组索引), 因此建议使用后者.

通过 pg_dumppg_restore / pgsql 将数据库重建.
另请参阅故障恢复有关的章节, 以避开与数据库重建有关的常见的陷阱.

# 从官方Docker容器里导出数据
docker exec <container_name> pg_dump --username postgres --dbname <db_name> > db.dump
# 导入数据(-i是必须的, 否则无法读取stdin)
docker exec -i <container_name> psql --username postgres --dbname <db_name> < db.dump

PostgreSQL的每个查询默认最多使用4MB的内存空间(SHOW work_mem;), 因此超出的部分会被转移到硬盘,
这可能会导致大数据集的排序性能受到影响.

在这种情况下, EXPLAIN ANALYZE 的输出结果里会显示 Sort Method: external merge Disk: xxxkB,
表明查询使用的硬盘空间.

PostgreSQL可以直接在会话中修改work_mem的值:

SET work_mem = '64MB';
SELECT ...;
RESET work_mem; -- 如果不需要重置回原始值, 则不需要此行
SET LOCAL work_mem = '64MB';
SELECT ...;

对于大表来说, 随机选取一行不能使用 ORDER BY 子句, 因为这会对所有候选行进行排序.

PostgreSQL提供了 TABLESAMPLE 子句, 可用于达成此目的.

另有tsm_system_rows模块, 可提供指定返回记录数而不是返回总记录的百分比的SYSTEM采样功能.

-- 选取表中的百分之一行中的第一行
SELECT column
FROM table
TABLESAMPLE BERNOULLI(1)
LIMIT 1;
-- 选取表中的百分之一行中的第一行, 性能高于BERNOULLI, 随机性较弱
SELECT column
FROM table
TABLESAMPLE SYSTEM(1)
LIMIT 1;
SELECT column
FROM table
WHERE random() < 0.01
LIMIT 1;

autovacuum是PostgreSQL的可选功能, 但强烈建议开启.
在默认配置下, autovaccum已启用.

autovacuum会自动执行 VACUUMANALYZE.

-- 检查autovacuum是否开启
SHOW AUTOVACUUM;
-- 打印autovacuum的统计信息
SELECT schemaname
, relname
, last_vacuum
, last_autovacuum
, vacuum_count
, autovacuum_count
FROM pg_stat_user_tables;

字段索引可以被复合查询使用, 但复合索引不能被其子集或交集利用.

PostgreSQL亦不支持松散索引扫描, 目前仅可通过CTE模拟此行为.
该功能自2018年就已被计划添加, 但还未正式实现: https://commitfest.postgresql.org/19/1741/

因此, 最佳实践是 为每个查询可能使用到的字段分别建立索引.

与排序有关的查询, 应该尽可能建立复合索引, 以免出现无法利用索引的情况.

SELECT relname
, 100 * idx_scan / (seq_scan + idx_scan) AS percent_of_times_index_used
, n_live_tup AS rows_in_table
FROM pg_stat_user_tables
WHERE seq_scan + idx_scan > 0
ORDER BY n_live_tup DESC;

PostgreSQL的开发团队认为不应手动指定查询计划,
因为查询计划可能随着数据库发生变化, 当下的查询计划很可能不适合未来的查询,
只有基于统计方法才能保证查询的正确性.

上述说法实际上是站不住脚的, 因为hint syntax和基于成本常量的查询计划可以同时存在.
PostgreSQL的查询计划器在生产中的确选择了很多愚蠢的查询计划, 带来的性能问题经常是不能容忍的.
根据经验, 由于查询计划器选择了愚蠢的查询方式, 有时甚至会出现几十倍甚至上百倍的性能差异.

PostgreSQL不支持为查询指定索引(即其他DBMS里的hint syntax).

出于调试目的, PostgreSQL提供了一些选项用来关闭特定类型的计划器:
https://www.postgresql.org/docs/13/runtime-config-query.html#RUNTIME-CONFIG-QUERY-ENABLE

  • SET enable_seqscan = off; 关闭顺序扫描, 这可以强制查询计划使用索引.
    如果查询只可能通过顺序扫描执行, 则最后仍然会使用顺序扫描.
    此选项已知可以加速大表的全文搜索性能.
  • SET enable_bitmapscan = off; 关闭bitmap scan.
    一种说法认为在SSD上可以关闭此项, 从而强制查询计划使用Index Scan, 以提升查询速度.
    在实际测试中发现, 当表非常大时, Index Scan的速度要远慢于Bitmap Scan, 因此这种优化没有意义.

关闭特定联接类型的目的是为了测试查询计划器是否选择了性能不佳的联接方式.

  • SET enable_hashjoin = off; 关闭Hash Join.
  • SET enable_mergejoin = off; 关闭Merge Join.
  • SET enable_nestloop = off; 关闭Nest Loop.
  • SET enable_partitionwise_join = on; 默认为off, 分区联接优化.
  • SET enable_partitionwise_aggregate = on; 默认为off, 分区聚合优化.

https://www.postgresql.org/docs/13/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS

该值代表磁盘页面的顺序读取成本, 默认值为 1.0.

该值代表磁盘页面的随机读取成本, 默认值为 4.0.
应该把此值相对于 seq_page_cost 看待, 不应该将此值设置为小于 seq_page_cost 的值.

默认值是以机械硬盘为基准的, 对SSD而言, 该值可能更接近于 seq_page_cost.
实际上对于常规机械硬盘而言, 此值接近 50, 但社区测试发现 4 这个较小的值效果更好.

该值决定查询计划器是否尝试排序多表连接(除FULL JOIN以外的JOIN语句), 默认值为 8.

当值为 1 时, 将按照SQL的编写顺序进行多表连接, 查询计划不花费时间.

https://wiki.postgresql.org/wiki/Slow_Counting

该问题是PostgreSQL特有的:
由于PostgreSQL的MVCC实现允许多个事务看到不同的表状态, 因此COUNT聚合只能通过每次都遍历所有行来得到结果.
这导致PostgreSQL里的大表COUNT性能非常差.

一种解决方案是在应用级缓存查询的结果, 这样就可以最大限度避免遍历表.
这种应用级缓存必须在服务端实现, 因为客户端不可能知道什么时候应该刷新缓存.

PostgreSQL在更新值时, 会在磁盘里写入一个新行, 然后再弃用旧行, 因此具有以下特性:

  • 从头创建表比更新整个表快得多.
  • 表约束和索引会严重影响写操作.
  • 由于更新本身是一个原子操作(属于事务, 因此数据库会维护数据完整性),
    更新时会在磁盘上生成巨大的临时文件(全表更新相当于产生当前数据库的一倍的文件),
    磁盘空间不足时可能会更新失败.

通过创建新表并重命名来加速更新在理论上是可行的,
但此方案这会破坏相关的外键约束, 因此对于复杂的表来说是不可行的.

PostgreSQL可以在函数中创建和遍历游标, 但由于函数本身就是事务, 因此无法用游标解决此问题.

PostgreSQL不能在 UPDATE 时使用 LIMIT 限制数量, 但可以采用以下模式间接实现此功能:

UPDATE my_big_table
SET need_be_updated_field = 1
WHERE CTID IN (SELECT CTID
FROM my_big_table
WHERE some_condition = 1
LIMIT 100000);
-- CTID 是 PostgreSQL 表中隐式存在的系统字段.
-- CTID 字段会在 VACUUM FULL 后发生改变, 因此不能当作不变的记录, 但在这个场景很适用.

最好是编写一个脚本来循环执行此命令, 以绕过事务限制.

const { Client } = require('pg')
const client = new Client({
host: 'localhost'
, port: 5432
, user: 'postgres'
, password: 'password'
, database: 'database'
})
;(async () => {
await client.connect()
const times = Math.ceil(await count() / 100000)
for (let i = 0; i < times; i++) {
console.time(`${i+1}/${times}`)
await update()
console.timeEnd(`${i+1}/${times}`)
}
await client.end()
console.log('Done')
})()
async function count() {
const result = await client.query(`
SELECT COUNT(*)::int AS count
FROM my_big_table
WHERE some_condition = 1;
`)
console.log(result.rows[0].count)
return result.rows[0].count
}
async function update() {
await client.query(`
UPDATE my_big_table
SET need_be_updated_field = 1
WHERE CTID IN (SELECT CTID
FROM my_big_table
WHERE some_condition = 1
LIMIT 100000);
`)
}

更新完成后, 最好手动执行 VACUUM 以回收更新使用掉的磁盘空间.

这是一个典型的分页问题.
由于查询的成本太高, 以至于进行两次查询是不可行的(一次用于获取匹配结果, 一次用于获得count).

可以使用PostgreSQL的函数窗口(functions window)特性来解决此问题:

SELECT ...
, COUNT(*) OVER() AS full_count
FROM ...
LIMIT ... OFFSET ...;

此方案的查询时间要长于一次查询, 但仍然明显好于执行两次查询.

如果OFFSET值过大, 导致最终查询结果为空, 则此时将无法返回full_count, 因为没有记录可以返回.

可用于 ORDER BY 子句的索引也会被查询计划器用于MAX, MIN函数的计算.
这时, MAX, MIN函数与 (SELECT .. ORDER BY ... LIMIT 1) 的性能基本在同一级别.

提升联接性能的关键在于 确保只使用索引就完成联接.
如果查询计划器在联接表时存在Filter, 则代表仅凭索引不足以完成联接, 应该创建与查询更匹配的索引.

需要注意的是, 在一些情况下, 即使存在正确的索引, 查询计划器也会使用错误的索引.

表的大小和索引的大小会直接影响查询所需的时间.

-- 数据库的大小
SELECT pg_size_pretty(pg_database_size('database'));
-- 特定表的大小
SELECT pg_size_pretty(pg_table_size('table'));
-- 特定表的总索引大小
SELECT pg_size_pretty(pg_indexes_size('table'));
-- 列出所有表的大小
SELECT table_name
, pg_size_pretty(table_size) AS table_size
, pg_size_pretty(indexes_size) AS indexes_size
, pg_size_pretty(total_size) AS total_size
FROM (
SELECT table_name
, pg_table_size(table_name) AS table_size
, pg_indexes_size(table_name) AS indexes_size
, pg_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.tables
) AS all_tables
ORDER BY total_size DESC
) AS pretty_sizes;
-- 列出所有索引的大小
SELECT t.tablename
, indexname
, c.reltuples AS num_rows
, pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size
, pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size
, CASE WHEN indisunique THEN 'Y'
ELSE 'N'
END AS UNIQUE
, idx_scan AS number_of_scans
, idx_tup_read AS tuples_read
, idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN (
SELECT c.relname AS ctablename
, ipg.relname AS indexname
, x.indnatts AS number_of_columns
, idx_scan
, idx_tup_read
, idx_tup_fetch
, indexrelname
, indisunique
FROM pg_index AS x
JOIN pg_class AS c
ON c.oid = x.indrelid
JOIN pg_class AS ipg
ON ipg.oid = x.indexrelid
JOIN pg_stat_all_indexes AS psai
ON x.indexrelid = psai.indexrelid
) AS foo
ON t.tablename = foo.ctablename
WHERE t.schemaname = 'public'
ORDER BY 1
, 2;

该类型可以表示非常大的精确数字.
在创建列时, 可以配置最大precision(总位数)和scale(小数位数).
在不配置precision和scale的情况下, 可以存储任意精度和小数位数.

NUMERIC的底层数据物理存储形式是固定的, precision和scale只是用来强制转换输入数据,
因此建议使用无配置的类型.

DECIMAL是该类型的别名.

UUID类型并不保证唯一性, 因此需要手动添加唯一约束.

NanoID可用 CHAR(21) 表示.
经测试发现 CHAR(21)UUID 类型占用相同的存储空间,
因此使用NanoID除了看起来更短以外并没有什么优势.

在PostgreSQL里有两种时间戳类型: TIMESTAMPTIMESTAMPTZ.
TIMESTAMP是SQL标准, TIMESTAMPTZ不是SQL标准.

时间戳不应该用于精确定位记录,
因为数据库的时间戳精度与编程语言的时间戳精度很可能不同, 转换起来也较为复杂.

TIMESTAMPTZ的名称具有误导性:
字面上看, TIMESTAMP不带有时区信息, TIMESTAMPTZ带有时区信息.
而实际上, 两者都只保存基于 UTC 的偏移量(也就是时间戳), 都不保存时区信息.

TIMESTAMPTZ输出的时区信息是根据数据库的时区决定的, 只在显示时自动添加.

PostgreSQL提供了标准SQL的TRIM函数用于移除首尾空白字符, 但TRIM仅对字符ASCII 32有效,
而实际使用时会遇到的空白符则远不止这一个字符.

ltree是专为分层树状结构设计的数据类型, lquery是用于查询ltree的一种类似正则表达式的匹配模式.

典型的ltree: Top.Countries.Europe.Russia

布尔值有三种值: true, false, NULL.

其中NULL和false在WHERE子句里作为条件时, 都代表假值.

两个Row Constructor在比较时, 会按列从左到右逐项比较.
如果比较过程中遇到NULL值, 则结果为NULL(即"未知", unknown).

当列可能为NULL时, 如果需要实现类似排序时的 NULLS LASTNULLS FIRST 的模式,
应该把NULL转换为可以比较的非NULL值, 再进行比较, 以免比较在中途就以返回NULL值结束.

ROW(a, b) < ROW(c, d)
-- 等价于
a < c OR (a = c AND b < d)
SELECT * FROM pg_indexes WHERE tablename = 'mytable';
ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_date_type
USING expression;
-- expression用于将列的值转换为新值
-- 例子: 将列的类型从int4改为varchar(255)
ALTER TABLE table_name
ALTER COLUMN column_name TYPE varchar(255)
USING column_name::varchar(255);
ALTER TABLE table_name
ADD COLUMN column_name date_type;
ALTER TABLE table_name
RENAME COLUMN column_name TO new_column_name;
ALTER TABLE table_name
DROP COLUMN column_name;

PostgreSQL并没有提供此功能, 因此必须重新创建一个列位置正确的新表.

-- 将旧表重命名
ALTER TABLE table_name RENAME TO old_table_name;
-- 创建与旧表同名的新表
CREATE TABLE table_name (
...
);
-- 将旧表数据插入到新表
INSERT INTO table_name (...)
SELECT ...
FROM old_table_name;
-- 删除旧表
DROP TABLE old_table_name;