PostgreSQL Reference

Production-grade relational database with rich types, JSONB, and advanced indexing.

Connection and CLI

Connect with psql

# Connect to local database
psql -d myapp

# Connect to remote host
psql -h db.example.com -U appuser -d myapp

# Connection string (libpq format)
psql "postgresql://appuser:pass@db.example.com:5432/myapp?sslmode=require"

psql meta-commands

psql -d myapp <<'SQL'
\l                    -- list databases
\dt                   -- list tables in current schema
\dt+                  -- list tables with sizes
\d devices            -- describe table (columns, indexes, constraints)
\di                   -- list indexes
\dv                   -- list views
\dn                   -- list schemas
\du                   -- list roles
\conninfo             -- current connection info
\x                    -- toggle expanded display (one column per line)
\timing on            -- show query execution time
SQL

Execute SQL from file or command line

psql -d myapp -f schema.sql
psql -d myapp -c "SELECT COUNT(*) FROM users;"

# CSV output
psql -d myapp -c "COPY (SELECT * FROM devices) TO STDOUT WITH CSV HEADER" > devices.csv

Database and Role Management

Create database and user

sudo -u postgres psql <<'SQL'
CREATE USER appuser WITH PASSWORD 'changeme';
CREATE DATABASE myapp OWNER appuser;
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;
SQL

Grant granular permissions

-- Read-only role
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE myapp TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

-- Assign role to a user
GRANT readonly TO monitoring_user;

Data Types

PostgreSQL-specific types worth knowing

CREATE TABLE network_inventory (
    id          SERIAL PRIMARY KEY,           -- auto-increment integer
    device_id   UUID DEFAULT gen_random_uuid(),
    hostname    VARCHAR(255) NOT NULL,
    mgmt_ip    INET,                          -- IP address with validation
    mac_addr   MACADDR,                       -- MAC address
    subnet     CIDR,                          -- network/prefix
    config     JSONB DEFAULT '{}',            -- binary JSON (indexable)
    tags       TEXT[] DEFAULT '{}',           -- array of strings
    metadata   HSTORE,                        -- key-value pairs
    created_at TIMESTAMPTZ DEFAULT NOW()      -- timestamp with timezone
);

Prefer TIMESTAMPTZ over TIMESTAMP. Prefer JSONB over JSON (supports indexing and equality checks). Prefer TEXT over VARCHAR unless you need a length constraint.

JSONB Operations

Query and manipulate JSON data

-- Extract text value
SELECT config->>'hostname' AS hostname FROM devices;

-- Extract nested value
SELECT config->'network'->>'vlan' FROM devices;

-- Filter on JSON field
SELECT * FROM devices WHERE config->>'status' = 'active';

-- Check key existence
SELECT * FROM devices WHERE config ? 'hostname';

-- Update a JSON field (merge)
UPDATE devices
SET config = config || '{"vlan": 20}'::jsonb
WHERE id = 1;

-- Remove a key
UPDATE devices
SET config = config - 'deprecated_field';

-- Index for fast JSON queries
CREATE INDEX idx_devices_config ON devices USING GIN (config);

Array Operations

Working with PostgreSQL arrays

-- Insert with array
INSERT INTO servers (hostname, tags) VALUES ('web-01', ARRAY['production', 'web']);

-- Query: contains element
SELECT * FROM servers WHERE 'production' = ANY(tags);

-- Query: contains all elements
SELECT * FROM servers WHERE tags @> ARRAY['web', 'production'];

-- Append to array
UPDATE servers SET tags = array_append(tags, 'monitored') WHERE hostname = 'web-01';

-- Unnest array to rows
SELECT hostname, unnest(tags) AS tag FROM servers;

Backup and Restore

pg_dump — logical backup

# Dump single database (custom format, compressed)
pg_dump -Fc myapp > myapp_$(date +%F).dump

# Dump as plain SQL
pg_dump myapp > myapp_$(date +%F).sql

# Dump schema only (no data)
pg_dump --schema-only myapp > schema.sql

# Dump specific table
pg_dump -t devices myapp > devices.sql

pg_restore — restore from custom format

# Restore to existing database
pg_restore -d myapp myapp_2026-04-10.dump

# Restore creating the database
pg_restore -C -d postgres myapp_2026-04-10.dump

pg_dumpall — all databases and roles

pg_dumpall > full_cluster_$(date +%F).sql

Performance and Analysis

EXPLAIN ANALYZE — actual execution metrics

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42;

Key things in EXPLAIN output:

Seq Scan — full table scan. Consider adding an index.
Index Scan / Index Only Scan — good, using an index.
actual time — real wall-clock time in milliseconds.
rows — actual rows vs estimated rows. Large discrepancy means stale statistics.

Refresh statistics after bulk operations

ANALYZE devices;     -- update statistics for one table
ANALYZE;             -- update statistics for all tables

Find slow queries with pg_stat_statements

-- Enable extension (once)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 slowest queries by total time
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Table Size and Bloat

Check table and index sizes

-- Table sizes including indexes
SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       pg_size_pretty(pg_relation_size(relid)) AS table_size,
       pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Database size

SELECT pg_size_pretty(pg_database_size('myapp'));

Useful System Queries

Active connections

SELECT pid, usename, datname, state, query_start, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start;

Kill a stuck query

SELECT pg_cancel_backend(12345);     -- graceful (SIGINT)
SELECT pg_terminate_backend(12345);  -- forceful (SIGTERM)

List all indexes on a table

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'devices';