Wiki Databases PostgreSQL

Measuring

How much disk space is some relation (table, index, ...) taking ?

You can use pg_relation_size():

select pg_relation_size('some_table_or_idx');

It's way better to be able to actually read the result:

select pg_size_pretty(pg_relation_size('some_table_or_idx'));

More info and scripts: https://wiki.postgresql.org/wiki/Disk_Usage

Tuning

Backup & Restore

Simple backup command:

pg_dump --format=c --role=<role> <dbname>

Simple restore command:

pg_restore --role=<role> -d <dbname> <filename>

Tools

Visualize explain analyze results: https://explain.depesz.com/

Misc.

A few links ...