Linux, Performance, PostgreSQL, Scripts, Windows

PostgreSQL Cheat Sheet

by jb

This PostgreSQL Cheat Sheet provides a compact overview of commonly used SQL statements, functions and administration commands for developers and DBAs. Whether for quick lookup, troubleshooting or learning purposes, this reference guide is designed to make working with PostgreSQL faster and more efficient.

Command snippets

# connect to a database
psql "postgresql://postgres:changeme@localhost:5432/postgres"
PGPASSWORD='changeme' psql -h localhost -U postgres -d postgres
# create a test table t with 1 million rows
create table t as select a.* from pg_catalog.pg_class a, pg_catalog.pg_class b, pg_catalog.pg_class c limit 1000000;
# create a sample index
create index i1 on t (reloptions);
# update table stats
analyze t;
# calculate cache hit ratio from pg_stat_io
SELECT (hits / (reads + hits)::float) * 100 hit_pct
FROM pg_stat_io
WHERE backend_type = 'client backend' AND object = 'relation' AND context = 'normal';
# running pgbench with a custom statement
createdb mydb
pgbench -i mydb
echo "select 1;"|pgbench -c 10 -T 20 -j 10 -f - postgres
# check for table bloat (number of dead tubles of a table(dead_tuple_count))
# make sure the contrib package is installed: dnf install postgresql18-contrib
CREATE EXTENSION pgstattuple;
\x
SELECT (x).* FROM pgstattuple('pgbench_accounts') as x;
update pgbench_accounts set filler='XXX';
SELECT (x).* FROM pgstattuple('pgbench_accounts') as x;
# get the size of a table
select pg_size_pretty(pg_table_size('t'));
# get the size of an index
select pg_size_pretty(pg_relation_size('i1'));
# when was an index last used (or: are there indexes that can be dropped?). See column last_idx_scan
select * from pg_stat_all_indexes where indexrelname='t_i1';

Useful resources

Websites, Links, Videos and Tools

Books

0