PostgreSQL

relationfilesystemsqldatabase

PostgreSQL - The world’s most advanced open source relational database

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: PostgreSQL Wiki - Disk Usage

Tuning

Tuning the filesystem

Wasted space & vacuuming

About JSONB

Backup & Restore

Simple backup command:

pg_dump --format=c <dbname>

Simple restore command:

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

--format=c writes the custom archive format, which pg_restore reads. Note that --role is ignored by pg_dump when the output is an archive format (-Fc, -Ft, -Fd): for those, set the role at restore time on pg_restore, as shown above. --role only takes effect on pg_dump for plain SQL output (-Fp).

Tools

Visualize explain analyze results: Explain Depesz

Misc.

A few links …

Related