Hidden Features of PostgreSQL [closed]

Solution 1:

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)

Solution 2:

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:

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

Solution 3:

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.