A practical first session in psql. Connect locally and remotely, use the backslash commands you need daily, understand databases vs schemas, create an application role with least privilege, and fix the most common connection errors.
What an index does, how to create one without locking a busy table, how to read a simple EXPLAIN plan, multi-column index order, and the common reasons PostgreSQL ignores an index you expected it to use.
Find integer keys close to 2,147,483,647, buy time when inserts already fail, and convert the column to bigint with either a rewrite or an online add-backfill-swap.
When EXPLAIN shows estimated rows far from actual rows, PostgreSQL picks bad joins and scans. Find misestimates, raise per-column statistics targets, add extended statistics for correlated columns and functional dependencies, and know what the planner still cannot see.
DROP TABLE or TRUNCATE ran on production. Find the exact time from logs, restore a copy of the cluster to just before it with PITR (pgBackRest, base backup plus WAL, or managed-service restore), then dump the table from the copy and load it into production.
Throughput collapses on the primary or a hot standby with LWLock waits on subtransaction SLRU buffers. Find the code that creates subtransactions, the long transaction that turns them into a problem, and fix both.
Decode PostgreSQL connection errors, check pg_hba.conf rules with pg_hba_file_rules, fix SSL, md5 and SCRAM mismatches, and reload safely without a restart.
PostgreSQL 14 gets its last release in November 2026. After that, no security or bug fixes. Find every v14 instance, pick a target (17 or 18 for most), check extensions and application compatibility, and choose between pg_upgrade, logical replication and the cloud provider's major version upgrade.
Copy-ready SQL for common PostgreSQL access patterns - owner, read-write and read-only roles, default privileges for future tables, checking who has what, and fixing the usual permission denied errors.
Managed PostgreSQL services look similar but differ in storage architecture, HA behaviour, extensions, version support and limits on superuser access. List what your applications need (extensions, versions, replication, scale), compare services on those points within your cloud, and test failover, upgrades and performance before committing.
SaaS-style applications must keep tenants isolated without making operations impossible. Compare database-per-tenant, schema-per-tenant and shared tables with a tenant key, enforce isolation with row-level security where tables are shared, and plan for noisy neighbours, per-tenant restores and growth.
Logical dumps and physical backups solve different problems. Use physical backups with WAL archiving for point-in-time recovery of whole clusters, logical dumps for portability and single-object restores, compare pgBackRest, Barman and WAL-G for self-managed servers, and rely on managed PITR plus extra copies on cloud services.
Huge time-series and event tables make vacuum slow, purges painful and indexes bloated. Partition by time with declarative partitioning, automate partition creation, drop or detach old partitions instead of deleting rows, and migrate existing tables with minimal downtime.
PostgreSQL's defaults suit a tiny server. Size shared_buffers relative to RAM, set effective_cache_size so the planner knows about the OS cache, raise work_mem carefully because it multiplies per sort and connection, give maintenance work enough memory, and verify with buffer hit ratios and temp file statistics.
pg_stat_statements shows which queries consume the most time overall, which is where tuning pays off. Rank queries by total time, fix the top ones with EXPLAIN and targeted indexes, remove unused and duplicate indexes that slow writes and vacuum, and track results over time.
patronictl list shows no Leader, every node is a replica or stopped, and applications can't write. The usual causes are a lost etcd quorum, no candidate passing the failover rules, or a paused cluster. Diagnose each one and get a leader back without risking split-brain.
The primary server is down, without Patroni or another manager to fail over. Check the standby's lag, fence the old primary so you don't get two primaries, promote with pg_promote, repoint applications, and rebuild the old primary as a standby with pg_rewind.
PostgreSQL warns that a database must be vacuumed within N transactions, or stops accepting writes altogether. Find what is holding back freezing, such as long transactions, abandoned replication slots or orphaned prepared transactions, remove it, and run a targeted VACUUM to get write access back.
pg_upgrade needs an outage and an in-place change. Instead, build a new cluster on the new major version, replicate into it with logical replication, test it under real data, then switch over in minutes with an easy way back.
Move a self-managed PostgreSQL database to Aurora PostgreSQL. Check extensions and settings, choose dump/restore, native logical replication or AWS DMS, run the cut-over, then adjust for how Aurora storage, replicas and failover behave.
Hundreds or thousands of short-lived application connections waste PostgreSQL memory and CPU. Add PgBouncer in transaction pooling mode, size the pools, find the session features that break, and roll it out without an outage.
Move PostgreSQL from VMs or on-premises to Flexible Server. Check extensions and superuser dependencies, size compute, storage and networking, then migrate offline with pg_dump or online with the built-in migration service. Finish with the cut-over and post-migration tuning.
The pg_wal directory keeps growing until the volume fills. Find what is holding WAL back, usually a stale replication slot or a failing archive_command, release it safely and cap it.
The PostgreSQL commands and queries DBAs use every week. Covers psql shortcuts, server and database info, active sessions and locks, slow queries with pg_stat_statements, sizes and bloat, vacuum and autovacuum, indexes, replication, backups with pg_dump and pgBackRest, and roles.
Use pg_blocking_pids and pg_locks to find the session at the head of a lock queue, clear it safely, and stop DDL and idle transactions from freezing the app again.
Find who holds the connections, clean up idle-in-transaction sessions, and fix the cause with connection pooling instead of raising max_connections.
Protect PostgreSQL against site or region loss with a streaming standby in the DR region and a pgBackRest repository there too. Covers a Patroni standby cluster, promoting the standby, PITR from object storage, and failback.
Build a three-node PostgreSQL cluster with automatic failover using Patroni and etcd, route clients with HAProxy or a VIP, choose synchronous mode settings, and run switchovers and health checks.
Prepare, check, run and validate a PostgreSQL major version upgrade with pg_upgrade, including extensions, replicas, statistics and a rollback plan.
Configure a single PostgreSQL server for production. Covers OS settings, memory parameters, WAL and checkpoint tuning, autovacuum, connection pooling, logging, pg_hba security and pgBackRest backups.
Measure write, flush and replay lag from the primary, tell network and disk bottlenecks from replay conflicts, and handle canceled queries on hot standbys.
Run EXPLAIN with the right options, spot misestimates, sequential scans, sorts spilling to disk and nested loops gone wrong, and know which fix each one needs.
Find tables with dead tuples, the long transactions that block vacuum, and transaction ID wraparound risk, then tune autovacuum for each table.
No posts in this category yet.