Hidden Features of PostgreSQL

Question

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 :)

Answer 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)