PostgreSQL知识分享-第31期

本期分享如下：

• 随机函数
• 获取PG实例的创建时间
• DBA的十项管理任务
• Extension的四种分类

一、随机函数

当我们进行测试时，经常需要生成随机数，PG提供了一些函数来满足随机性。

连续均匀分布 random

下面的语句生成1～10之间的随机整数

SELECT floor(10 * random()) + 1 FROM generate_series(1, 5)

随机Rows

SELECT * FROM tab ORDER BY random() LIMIT 5

如果表的数据量较大并且只获取随机单行，建议数据表里添加random字段

CREATE TABLE foo (
  id SERIAL PRIMARY KEY,
  info TEXT NOT NULL,
  random FLOAT8 DEFAULT random()
);

CREATE INDEX foo_random ON foo (random);

并设置random字段默认随机值，同时对random字段建立索引，最后语句如下

SELECT *
FROM  foo
WHERE random > random()
ORDER BY random ASC
LIMIT 1;

而如果使用子查询获取大量行，则可以使用TABLESAMPLE子句，基于数据块采样(bernoulli)或基于数据行采样(system)

随机分组

假如需要将整个数据集分成多个子集，可以使用如下语句

WITH random_foo AS (
    SELECT id, info
    FROM foo
    ORDER BY random()
)
SELECT row_number() over () % 2 AS group,
       id, info
FROM random_foo
ORDER BY 1;

正太分布(高斯分布)random_normal

PG 16提供了random_normal(mean,stddev)正太分布函数

SELECT random_normal(0,1)::integer,
       count(*)
FROM generate_series(1,1000)
GROUP BY 1
ORDER BY 1

执行结果如下：

┌───────────────┬───────┐
│ random_normal │ count │
├───────────────┼───────┤
│            -2 │    57 │
│            -1 │   245 │
│             0 │   391 │
│             1 │   243 │
│             2 │    58 │
│             3 │     5 │
│             4 │     1 │
└───────────────┴───────┘
(7 rows)

参考文章链接：Rolling the Dice with the PostgreSQL Random Functions

二、获取PG实例的创建时间

不考虑服务端PGDATA目录的权限，可通过如下SQL查询：

select to_timestamp ( system_identifier >> 32 ) as cluster_init from pg_control_system();

查询结果如下：

┌────────────────────────┐
│      cluster_init      │
├────────────────────────┤
│ 2023-08-11 15:55:43+08 │
└────────────────────────┘
(1 row)

上面的SQL语句适用于PG 9.6及以上的版本。

9.6以下没有pg_control_system函数，需要从服务端使用shell提取

SYSID=$(pg_controldata /opt/pgdata9525/ |  grep 'Database system identifier' | grep -oE '[0-9]+')
date -d @$((SYSID >> 32))

执行结果如下：

2023年 02月 17日 星期五 23:23:48 CST

三、DBA的十项管理任务

摘要如下：

添加statement超时
确定内存足够
检查shared buffers
使用SSL/TLS进行数据传输
建立备份
保持最新补丁版本或定期升级
使用pg_stat_statements插件
添加indexes
检测无用的indexes

参考文章链接：Top 10 Postgres Management Tasks

四、Extension的四种分类

PG的Extension由SQL或者动态库构成，根据是否需要使用load命令加载、是否需要使用CREATE EXTENSION进行创建有如下四种分类。

通过四种分类，我们可以更好的管理和调试各种Extension。

参考文章链接：Enter the matrix: the four types of Postgres extensions