PostgreSQL的隐藏特性 [英] Hidden Features of PostgreSQL

问题描述

我很惊讶，这个还没有发布。任何有趣的技巧，你知道在Postgres？模糊配置选项和缩放/技巧是特别受欢迎。

I'm surprised this hasn't been posted yet. Any interesting tricks that you know about in Postgres? Obscure config options and scaling/perf tricks are particularly welcome.

我相信我们可以击败相应的 MySQL线程：）

I'm sure we can beat the 9 comments on the corresponding MySQL thread :)

推荐答案

因为postgres比MySQL ，没有那么多技巧报告; - ）

Since postgres is a lot more sane than MySQL, there are not that many "tricks" to report on ;-)

手动有一些不错的

The manual has some nice performance tips.

其他一些与成效相关的事项请留意：

A few other performance related things to keep in mind:

• 确保自动真空已打开


• 确保您已浏览过postgres.conf（有效缓存大小，共享缓冲区，工作记忆...很多选项


• 使用pgpool或 pgbouncer 保留您的真正的数据库连接到最少


• 了解如何 EXPLAIN 和EXPLAIN ANALYZE工程。了解如何阅读输出。


• CLUSTER 根据索引对磁盘上的数据进行排序。可以显着提高大型（大多数）只读表的性能。聚类是一次性操作：当表随后更新时，更改不会聚集。

• Make sure autovacuum is turned on
• Make sure you've gone through your postgres.conf (effective cache size, shared buffers, work mem ... lots of options there to tune).
• Use pgpool or pgbouncer to keep your "real" database connections to a minimum
• Learn how EXPLAIN and EXPLAIN ANALYZE works. Learn to read the output.
• CLUSTER sorts data on disk according to an index. Can dramatically improve performance of large (mostly) read-only tables. Clustering is a one-time operation: when the table is subsequently updated, the changes are not clustered.

这里有一些我发现有用的东西不是配置或性能相关的。

Here's a few things I've found useful that aren't config or performance related per se.

查看当前发生的情况：

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

查找所有数据库的大小：

Find size of all databases:

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

查找表和索引的大小：

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

或者，列出所有表和索引（可能更容易看到这个） p>

Or, to list all tables and indexes (probably easier to make a view of this):

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;

哦，你可以嵌套交易，回滚部分交易++

Oh, and you can nest transactions, rollback partial transactions++

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)

这篇关于PostgreSQL的隐藏特性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！