PostgreSQL的隐藏功能[英] Hidden Features of PostgreSQL

本文是小编为大家收集整理的关于PostgreSQL的隐藏功能的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我很惊讶这尚未发布.您在Postgres中知道的任何有趣的技巧吗?尤其欢迎模糊的配置选项和缩放/完美技巧.

我确定我们可以在相应的 MySQL Thread :)

推荐答案

由于Postgres比MySQL更为理智,因此没有太多的"技巧"来报告; - )

手册有一些不错的 pgbouncer 保持您的"真实"数据库连接到最低

  • 了解解释并解释分析工作.学会读取输出.
  • 群集根据索引,磁盘上的数据很少.可以显着提高大(大部分)仅阅读表的性能.聚类是一次性操作:随后更新表时,更改不会聚类.
  • 这是我发现有用的几件事本身与配置或性能无关的东西

    查看当前发生的事情:

    select * from pg_stat_activity;
    

    搜索MISC函数:

    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)
    

    其他推荐答案

    最简单的技巧让PostgreSQL的表现更好(当然,除了设置和使用适当的索引外)只是为了让它更多的RAM(如果您还没有完成)所以已经).在大多数默认安装上, shared_buffers 的值太低(我认为).您可以设置

    shared_buffers

    在Postgresql.conf中.将该数字除以128,以获取邮政邮政的内存数量(以MB)的要求.如果您足够增加,这将使PostgreSQL飞行.不要忘记重新启动PostgreSQL.

    在Linux系统上,当PostgreSQL无法再次启动时,您可能会达到kernel.shmmax限制.用

    将其设置得更高
    sysctl -w kernel.shmmax=xxxx
    

    要在靴子之间持续使用,请添加一个kernel.shmmax条目到/etc/sysctl.conf.

    可以在这里找到一大堆PostgreSQL技巧:

    其他推荐答案

    Postgres得益于其间隔支持,具有非常强大的DateTime处理设施.

    例如:

    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)
    

    您可以将许多字符串施放为间隔类型.

    本文地址:https://www.itbaoku.cn/post/597378.html

    问题描述

    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.

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

    推荐答案

    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:

    • 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.

    To see what's currently happening:

    select * from pg_stat_activity;
    

    Search misc functions:

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

    Find size of database:

    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;
    

    Find size of tables and indexes:

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

    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)
    

    其他推荐答案

    The easiest trick to let postgresql perform a lot better (apart from setting and using proper indexes of course) is just to give it more RAM to work with (if you have not done so already). On most default installations the value for shared_buffers is way too low (in my opinion). You can set

    shared_buffers

    in postgresql.conf. Divide this number by 128 to get an approximation of the amount of memory (in MB) postgres can claim. If you up it enough this will make postgresql fly. Don't forget to restart postgresql.

    On Linux systems, when postgresql won't start again you will probably have hit the kernel.shmmax limit. Set it higher with

    sysctl -w kernel.shmmax=xxxx
    

    To make this persist between boots, add a kernel.shmmax entry to /etc/sysctl.conf.

    A whole bunch of Postgresql tricks can be found here:

    其他推荐答案

    Postgres has a very powerful datetime handling facility thanks to its INTERVAL support.

    For example:

    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)
    

    You can cast many strings to an INTERVAL type.