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'"

Measuring the amount of network data

I created the following script to measure the amount of reduction in network data by using zstd compression (protocol_compression) in PosgreSQL (s.a. here). Here is the script:

#!/usr/bin/bash
#
# Version 1.0 03.09.2026
#
set -euo pipefail

# ===== CONFIG =====
PGHOST="lin8"
PGPORT="5432"
PGDATABASE="mydb"
PGUSER="postgres"
PGPASSWORD="changeme"
TABLE="compression_test"
ROWS=10000
REPEAT_STR="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789"
REPEAT_CNT=100

export PGPASSWORD

PSQL_ARGS=("-c" "SELECT * FROM $TABLE;")
#PSQL_ARGS=("-c" "\COPY $TABLE TO '/dev/null'")
#PSQL_ARGS=("-c" "BEGIN;" "-c" "TRUNCATE TABLE $TABLE;" "-c" "\COPY $TABLE FROM '/tmp/data.dmp';" "-c" "COMMIT;")

# ===== Helper (unchanged – used only for setup) =====
run_psql() { psql -h "$PGHOST" -p "$PGPORT" -d "$PGDATABASE" -U "$PGUSER" -c "$1" -t -A; }

# ===== Ensure test data (unchanged) =====
if [[ $(run_psql "SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name='$TABLE');") != "t" ]]; then
    echo "Creating table $TABLE..."
    run_psql "CREATE TABLE $TABLE (id SERIAL PRIMARY KEY, payload TEXT);
              INSERT INTO $TABLE (payload) SELECT repeat('$REPEAT_STR', $REPEAT_CNT) FROM generate_series(1,$ROWS);" >/dev/null
    run_psql "\COPY $TABLE TO '/tmp/data.dmp'" >/dev/null
else
    echo "Table $TABLE already exists."
fi

# ===== Measure =====
measure() {
    local label="$1"
    local comp="$2"
    echo "--- $label ---"

    local time_file=$(mktemp)
    local ss_file=$(mktemp)

    # Resolve PGHOST to an IP address (numeric)
    local PGHOST_IP=$(getent hosts "$PGHOST" | awk '{print $1}' | head -1)
    if [[ -z "$PGHOST_IP" ]]; then
        # fallback: use host command (if getent not available)
        PGHOST_IP=$(host "$PGHOST" 2>/dev/null | head -1 | awk '{print $NF}')
    fi
    if [[ -z "$PGHOST_IP" ]]; then
        # final fallback: use the hostname as given (might not match ss -n)
        PGHOST_IP="$PGHOST"
    fi

    # Run psql with the query, capture time, and write ss output to file
    {
        time -p PGPASSWORD=$PGPASSWORD PGCOMPRESSION=$comp \
            psql -h "$PGHOST" -p "$PGPORT" -d "$PGDATABASE" -U "$PGUSER" \
                "${PSQL_ARGS[@]}" \
                -c "\! sleep 1" \
                -c "\! ss -t -i -p -n > $ss_file"
    } 2> "$time_file" > /dev/null

    # Now extract counters from the ss file using the remote endpoint
    local psql_block=$(grep -A10 "$PGHOST_IP:$PGPORT" "$ss_file" | grep -A1 "psql")
    local bytes_received=$(echo "$psql_block" | grep -oE 'bytes_received:[0-9]+' | head -1 | cut -d: -f2)
    local bytes_sent=$(echo "$psql_block" | grep -oE 'bytes_sent:[0-9]+' | head -1 | cut -d: -f2)
    local runtime=$(grep '^real' "$time_file" | awk '{print $2}')

    # Print results
    echo "  psql network data received: $(awk "BEGIN {printf \"%.2f\", ${bytes_received:-0}/1024}") KB"
    echo "  psql network data sent:     $(awk "BEGIN {printf \"%.2f\", ${bytes_sent:-0}/1024}") KB"
    echo "  runtime:                    ${runtime:-N/A} seconds"
    echo

    # Clean up
    rm -f "$time_file" "$ss_file"
}

# ===== Run measurements =====
measure "without compression" "off"
measure "with zstd compression" "zstd"
Sample Output (click to expand): 
script started on the PostgreSQL server (RHEL 10 VM):
--- without compression ---
   psql network data received: 60731.93 KB
   psql network data sent:     0.12 KB
   runtime:                    18.28 seconds
--- with zstd compression ---
   psql network data received: 110.19 KB
   psql network data sent:     0.15 KB
   runtime:                    18.32 seconds


script started on another VM on the same host as the PostgreSQL server 
(RHEL 9):
--- without compression ---
   psql network data received: 60731.93 KB
   psql network data sent:     0.12 KB
   runtime:                    20.03 seconds
--- with zstd compression ---
   psql network data received: 110.19 KB
   psql network data sent:     0.15 KB
   runtime:                    18.74 seconds


script started on another system (laptop) on the same LAN as the 
PostgreSQL server (Ubuntu 26):
--- without compression ---
   psql network data received: 60731.93 KB
   psql network data sent:     0.12 KB
   runtime:                    25.29 seconds
--- with zstd compression ---
   psql network data received: 110.19 KB
   psql network data sent:     0.15 KB
   runtime:                    9.78 seconds

Useful resources

Websites, Links, Videos and Tools

Books

0