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;
# 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
pgbench -i mydb
echo "select 1;"|pgbench -c 10 -T 20 -j 10 -f - postgres
# check for table bloat
CREATE EXTENSION pgstattuple;
\x
SELECT (x).* FROM pgstattuple('pgbench_accounts') as x;

Useful resources

Websites, Links, Videos and Tools

Books

0