Hidden Features of PostgreSQL [closed]

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)