Linux, Performance, PostGIS, 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 an example (non unique) index
create index i1 on t (reloptions);
# create a new column with unique values
alter table t add column id bigint generated always as identity not null;
# create a primary key constraint using the above unique column
alter table t add constraint pk primary key (id);
# 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 or as percentage: dead_tuple_percent))
# make sure the contrib package is installed: dnf install postgresql18-contrib
CREATE EXTENSION pgstattuple;
\x
select * from pgstattuple('pgbench_accounts');
update pgbench_accounts set filler='XXX';
select * from pgstattuple('pgbench_accounts');

# remove dead tables and shrink file on disk
# with an exclusive lock on the table
vacuum full pgbench_accounts;
# or online (pg_repack).
apt -y install postgresql-18-repack
create extension pg_repack;
pg_repack -d mydb -t pgbench_accounts

# 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 (timestamp with time zone)
select * from pg_stat_all_indexes where indexrelname='t_i1';
# enable sending of TCP Keepalive packets from the server to the client to avoid TCP Idle connection timeouts (due to OS or firewalls). This will send a packet every 30 seconds.
# errors for example: 2026-08-16 10:34:49  ERROR: Database error: SSL error: unexpected eof while reading
# or:
#server closed the connection unexpectedly
#        This probably means the server terminated abnormally
#        before or while processing the request.
psql "host=remote.de dbname=gisdemo user=gisdemo sslmode=require options='-c tcp_keepalives_idle=30 -c tcp_keepalives_interval=10 -c tcp_keepalives_count=5'"

Useful resources

Websites, Links, Videos and Tools

Books

0