PostgreSQL

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

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

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

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

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. 1.
   全文搜索:

SELECT *
  FROM documents
 WHERE tsv @@ plainto_tsquery('查询内容');

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

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

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

-- 激活扩展
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
-- []

-- 激活扩展
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);

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

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

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')
           );

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

-- 选取表中的百分之一行中的第一行
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;

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);
  `)
}

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