PostgreSQL的隐藏功能

Question

我很惊讶这个问题还没有被发布。你知道哪些关于Postgres的有趣技巧吗？不常见的配置选项和缩放/性能技巧尤其受欢迎。

我相信我们可以打败 MySQL 相应 线程 上的9条评论 ：）

- ramanujan

Answer 1

由于PostgreSQL比MySQL更加合理，因此没有太多的“技巧”可以报告；-）

手册中有一些不错的性能技巧。

还有一些其他与性能相关的事情需要注意：

• 确保autovacuum已打开
• 确保已经查看了您的postgres.conf（有效缓存大小、共享缓冲区、工作内存...有很多选项可供调整）。
• 使用pgpool或pgbouncer将“真实”的数据库连接最小化。
• 学习如何使用EXPLAIN和EXPLAIN ANALYZE。学会阅读输出结果。
• CLUSTER可以根据索引对磁盘上的数据进行排序。这可大幅提高大型（大部分为只读）表的性能。聚集是一次性操作：随后更新表时，更改不会聚集。

以下是我发现有用的一些东西，它们不是针对配置或性能本身。

查看当前正在发生的情况：

select * from pg_stat_activity;

搜索杂项函数：

select * from pg_proc WHERE proname ~* '^pg_.*'

查找数据库的大小：

select pg_database_size('postgres');
select pg_size_pretty(pg_database_size('postgres'));

查找所有数据库的大小：

select datname, pg_size_pretty(pg_database_size(datname)) as size
  from pg_database;

查找表和索引的大小：

select pg_size_pretty(pg_relation_size('public.customer'));

或者，要列出所有表和索引（可能更容易制作视图）：

select schemaname, relname,
    pg_size_pretty(pg_relation_size(schemaname || '.' || relname)) as size
  from (select schemaname, relname, 'table' as type
          from pg_stat_user_tables
        union all
        select schemaname, relname, 'index' as type
          from pg_stat_user_indexes) x;

另外，您可以嵌套事务，回滚部分事务++

test=# begin;
BEGIN
test=# select count(*) from customer where name='test';
 count 
-------
     0
(1 row)
test=# insert into customer (name) values ('test');
INSERT 0 1
test=# savepoint foo;
SAVEPOINT
test=# update customer set name='john';
UPDATE 3
test=# rollback to savepoint foo;
ROLLBACK
test=# commit;
COMMIT
test=# select count(*) from customer where name='test';
 count 
-------
     1
(1 row)

- tommym

---

感谢您。编辑：添加有关CLUSTER的信息。 - tommym

在我注意到的8.4 beta psql中，显示数据库大小是“\l”的一个功能。在那之前，我认为8.3有一个pg_size_pretty()函数来美化以字节为单位的大小。 - araqnid

谢谢你的提示！我之前不知道 pg_size_pretty。我已经更新了我的答案并加入了它。 - tommym

替换(answer, 'per say', 'per se') - asjo

Answer 2

除了设置和使用适当的索引之外，让PostgreSQL表现更佳的“最简单”的技巧就是为其提供更多RAM（如果您尚未这样做）。在大多数默认安装中，shared_buffers的值都太低（我个人认为）。您可以在postgresql.conf中设置此值。将此数字除以128以获得Postgres可以声明的内存量（以MB为单位）的近似值。如果您增加它足够多，这将使PostgreSQL飞快。别忘了重启PostgreSQL。

在Linux系统上，当PostgreSQL无法再次启动时，您可能已经达到了kernel.shmmax限制。使用以下命令将其设置得更高：

sysctl -w kernel.shmmax=xxxx

为了使其持久化，将kernel.shmmax条目添加到/etc/sysctl.conf中。 这里可以找到许多Postgresql技巧：

• http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks

- ChristopheD

Answer 3

Postgres在日期时间处理方面拥有非常强大的功能，这得益于其INTERVAL支持。

例如：

select NOW(), NOW() + '1 hour';
              now              |           ?column?            
-------------------------------+-------------------------------
 2009-04-18 01:37:49.116614+00 | 2009-04-18 02:37:49.116614+00
(1 row)



select current_date ,(current_date +  interval '1 year')::date;
    date             |  date            
---------------------+----------------
 2014-10-17          | 2015-10-17
(1 row)

你可以将许多字符串转换为间隔类型。

- Yann Ramin

Answer 4

COPY

我来介绍一下。每当我从SQLite切换到Postgres时，通常会有一些非常大的数据集。关键是要使用COPY FROM加载表格，而不是使用INSERTS进行操作。请参阅文档：

http://www.postgresql.org/docs/8.1/static/sql-copy.html

以下示例使用竖杠（|）作为字段分隔符将表格复制到客户端：

COPY country TO STDOUT WITH DELIMITER '|';

将数据从文件复制到国家表中：

COPY country FROM '/usr1/proj/bray/sql/country_data';

请参阅此处：如何更快地在sqlite3中进行批量插入？

- ramanujan

---

这对于CSV导入也非常方便。 - ChristopheD

在最近的版本中（至少是8.3，可能更早），如果您在与COPY相同的事务中创建或截断正在填充的表，则不会触及WAL日志，并且性能将更快。http://www.postgresql.org/docs/8.3/static/populate.html - TREE

Answer 5

• 我最喜爱的是generate_series: 终于有了一种干净的方法来生成虚拟行集。

• 能够在子查询的LIMIT子句中使用相关值：

SELECT  (
        SELECT  exp_word
        FROM    mytable
        OFFSET id
        LIMIT 1
        )
FROM    othertable

能够在自定义聚合中使用多个参数（文档未涵盖）：请参见我的博客文章中的示例。

- Quassnoi

---

+1，generate_series() 对于许多事情（例如每当您需要一个“虚拟表”时）都非常有用。第二个代码片段看起来也很有趣。 - j_random_hacker

Answer 6

我非常喜欢Postgres中支持的一些列数据类型。例如，有专门用于存储网络地址和数组的列类型。这些列类型对应的函数（网络地址 / 数组）让你能够在查询中执行许多复杂操作，而在MySQL或其他数据库引擎中则需要通过代码处理结果。请注意保留HTML标签。

- Chad Birch

---

如果标准类型不适合您，您可以轻松地创建自己的类型！ - bortzmeyer

Answer 7

一旦你了解了它们，数组就变得非常酷。假设你想要在页面之间存储一些超链接，你可能会考虑创建一个类似这样的表：

CREATE TABLE hyper.links (
     tail INT4,
     head INT4
);

如果您需要索引“tail”列，并且有大约2亿个链接行（如维基百科），则会得到一个巨大的表格和巨大的索引。

但是，使用PostgreSQL，您可以使用此表格格式：

CREATE TABLE hyper.links (
     tail INT4,
     head INT4[],
     PRIMARY KEY(tail)
);

为了获取链接的所有标题，您可以发送以下命令（自8.4版本以来unnest()是标准函数）：

SELECT unnest(head) FROM hyper.links WHERE tail = $1;

与第一种选项相比，这个查询非常快（unnest()很快，索引也很小）。此外，您的表格和索引占用的RAM内存和硬盘空间要少得多，特别是当您的数组很长时，它们被压缩为Toast表时。数组真的非常有用。

注意：虽然unnest()将从数组生成行，但array_agg()将行聚合成数组。

- Nicholas Leonard

Answer 8

物化视图的设置非常简单：

CREATE VIEW my_view AS SELECT id, AVG(my_col) FROM my_table GROUP BY id;
CREATE TABLE my_matview AS SELECT * FROM my_view;

这将创建一个新表my_matview，该表具有与my_view相同的列和值。可以设置触发器或cron脚本来保持数据最新，或者如果您懒得做：

TRUNCATE my_matview;
INSERT INTO my_matview SELECT * FROM my_view;

- Cameron

Answer 9

• 继承...实际上是多重继承（指父子“继承”，不是许多Web框架在与postgres工作时实现的1对1关系继承）。

• PostGIS（空间扩展），一个出色的附加组件，提供了全面的几何函数和坐标存储。广泛应用于许多开源地理库（如OpenLayers、MapServer、Mapnik等），绝对比MySQL的空间扩展好得多。

• 使用不同语言编写过程，例如C、Python、Perl等（如果您是开发人员而不是db-admin，则使您的编码变得更容易）。

  而且所有过程都可以被存储在外部（作为模块），并可以通过指定的参数在运行时调用或导入。这样，您就可以源代码控制该代码并轻松调试代码。

• 关于数据库中实现的所有对象（即表、约束、索引等）的大型和全面的目录。

  我总是发现运行一些查询并获取所有元信息（例如，约束名称和它们已实现的字段，索引名称等）非常有帮助。

  对我来说，当我必须加载新数据或对大表进行大规模更新时（我会自动禁用触发器并删除索引），这些都变得非常方便，然后在处理完成后轻松地重新创建它们。有人优秀地编写了这些查询语句。

  http://www.alberton.info/postgresql_meta_info.html

• 一个数据库下的多个模式，如果您的数据库有大量表，则可以使用它们，您可以将模式视为类别。所有表（无论其模式如何）都可以访问父数据库中存在的所有其他表和函数。

- Nakh

---

+1 我简直不敢相信多重继承排名这么靠后。 - Adam Gent

Answer 10

你不需要学习如何解读"explain analyze"的输出，因为有一个工具：http://explain.depesz.com

- AAS