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

• PostgreSQL Performance on EXT4 and XFS
• PostgreSQL on EXT4, XFS, Btrfs and ZFS

Wasted space & vacuuming

• Monitoring Your Bloat in Postgres
• Achieving Correct Bloat Estimates of JSON Data in PostgreSQL

About JSONB

• When to Avoid JSONB in a PostgreSQL Schema
• Choosing NoSQL: HStore vs JSON vs JSONB

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: Explain Depesz

Misc.

A few links …

• PostgreSQL: Speeding up GROUP BY and Joins
• Creating indexes without locking the table: PostgreSQL CREATE INDEX CONCURRENTLY