PostgreSQL Best Practices: 10 Core Rules for Production

PostgreSQL best practices are the core rules for running a database safely, fast and sustainably in production. The most important are: tested backups with point-in-time recovery (PITR), high availability with automatic failover, a connection pooler, settings sized to memory, least privilege and encrypted connections, autovacuum that runs regularly, continuous monitoring and staying on a supported version.

This guide is for teams running PostgreSQL in production or preparing to. We grouped the recommendations under ten headings and answered the “why” for each one. The details are based on the PostgreSQL 18 documentation, and we ran every query on PostgreSQL 18.

At a Glance: 10 Core Rules for PostgreSQL

1. Stay on a Supported Version

The PostgreSQL community supports each major version for five years and releases minor versions at least every three months. Minor releases contain only bug and security fixes; they need no dump or pg_upgrade and are usually applied by updating the packages and restarting the service. The community recommends always running the latest minor release.

We cover target version and method selection for leaving PostgreSQL 14 in detail in our PostgreSQL 14 end of life guide.

2. Backups: A Backup You Cannot Restore Is Not a Backup

PostgreSQL offers three backup approaches, and they complement rather than replace each other:

For production we recommend physical backups with continuous WAL archiving using a tool such as pgBackRest. Backups should be kept off the database server, preferably in a different location, and at least one copy should be on immutable storage as protection against ransomware.

3. High Availability: Automate Failover

PostgreSQL supports standby servers with streaming replication out of the box. It does not, however, include a mechanism that detects a failed primary and promotes a replica automatically. The common solution is Patroni, together with a distributed configuration store such as etcd that it uses to make decisions.

• Plan for at least three servers: a primary, a replica and an odd number of members for the decision layer (etcd). Three etcd members survive one failure, five survive two.
• Define your tolerance for data loss: with asynchronous replication the last transactions can be lost at failover. If that is unacceptable, use synchronous replication.
• Keep the connection address stable: applications should connect to a virtual IP, a load balancer or a DNS name, not to a server address.
• Test failover: before go-live and at regular intervals afterwards.

We use Autobase to install these components consistently. We describe what we watch for in our article on building a high-availability PostgreSQL environment with Autobase.

4. Use a Connection Pooler

PostgreSQL opens a separate operating system process for each connection. As connections grow, memory use and context-switching costs grow with them. For applications that open hundreds or thousands of concurrent connections, raising max_connections is not the answer; a connection pooler such as PgBouncer is.

Idle connections that the application never closes are another common problem. Sessions that start a transaction and then wait without finishing it in particular block VACUUM. Close them after a set time with idle_in_transaction_session_timeout.

-- Sessions by state, and the oldest open transaction
SELECT state, count(*) AS sessions,
       max(now() - xact_start) AS oldest_transaction
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY sessions DESC;

5. Size Memory and Checkpoint Settings

PostgreSQL’s defaults are chosen conservatively so it can run on a small machine. These are the first settings to review on a production server:

work_mem is the parameter that needs the most care. A value of 64 MB, for example, can be used several times by one complex query, and multiplied by hundreds of connections it can exceed the server’s memory. It is safer to keep it low globally and raise it per session or per role for heavy reporting queries.

6. Tune the Operating System for the Database

7. Security: Least Privilege, SCRAM and TLS

• Do not give applications superuser rights. Each application should connect with its own role and hold only the privileges it needs in its own database. Use the postgres role for administration only.
• Store passwords with SCRAM-SHA-256. MD5 password support was deprecated in PostgreSQL 18; find roles using MD5 and reset their passwords.
• Keep pg_hba.conf rules narrow. Name a specific database, role and network in every rule; never use the trust method for network connections.
• Encrypt connections. Use hostssl for remote connections and set the minimum TLS version to 1.2.
• Keep the database off the internet. Port 5432 should be open only to application servers.
• Record privileged activity. Log connections, disconnections and DDL; consider the pgAudit extension for audit requirements.

-- Roles with superuser or other elevated attributes
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolreplication, rolbypassrls
FROM pg_roles
WHERE rolsuper OR rolcreaterole OR rolreplication OR rolbypassrls
ORDER BY rolname;
-- Roles still using MD5 password hashes
SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%';

8. Never Turn Off Autovacuum; Tune It

PostgreSQL does not delete old versions of updated and deleted rows immediately. Autovacuum cleans up these “dead rows”. It also updates the query planner’s statistics and prevents transaction ID wraparound. Turning autovacuum off may look like a quick performance win, but in the medium term it leads to bloated tables and, in the worst case, a database that refuses new transactions.

On large, heavily updated tables the default thresholds can kick in too late. Tune autovacuum per table to run more often on those tables, and watch these two queries regularly:

-- Tables with the most dead rows and their last autovacuum
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
-- Transaction ID age per database (autovacuum_freeze_max_age default: 200 million)
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

9. Continuous Monitoring

Good monitoring shows problems before users notice them. Define alerts for at least these metrics:

The pg_stat_statements extension is the foundation of query-level performance analysis. It becomes available once it is added to shared_preload_libraries and the server is restarted:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Top 10 queries by total execution time
SELECT round(total_exec_time::numeric, 0) AS total_ms,
       calls,
       round(mean_exec_time::numeric, 1) AS mean_ms,
       left(query, 80) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

To see slow queries in the server log as well, turn on log_min_duration_statement (for example 1 second), log_lock_waits for lock waits and log_checkpoints for checkpoint behavior.

10. Design the Schema Right from the Start

Quick Self-Assessment

If you can answer “yes” to each of these questions, your PostgreSQL environment stands on solid foundations:

1. Is a supported major version with the latest minor release running?
2. Has a restore to a separate server been tested in the last three months?
3. When the primary goes down, does a replica take over without anyone stepping in?
4. Do applications connect through a connection pooler?
5. Are shared_buffers and work_mem set for the server’s memory?
6. Does no application connect with superuser rights?
7. Are all remote connections encrypted?
8. Is autovacuum on and is transaction ID age monitored?
9. Are there alerts for replication lag, disk usage and backup success?
10. Does every table have a primary key?

PostgreSQL with Aryasoft

Aryasoft’s senior-only team offers the services it provides for SQL Server for PostgreSQL as well: health checks, architecture and installation, high availability and backup design, performance tuning, version upgrades and migrations from SQL Server or Oracle to PostgreSQL. A health check shows how far the rules in this guide are applied in your environment, and we report the findings in priority order. For ongoing support, see our managed database services, which include a remote DBA service.

Frequently Asked Questions

Sources

The details in this guide were checked against the official documentation below in November 2026.

1. PostgreSQL: Versioning Policy
2. PostgreSQL documentation: Backup and Restore
3. PostgreSQL documentation: Continuous Archiving and Point-in-Time Recovery
4. PostgreSQL documentation: High Availability, Load Balancing, and Replication
5. PostgreSQL documentation: Resource Consumption
6. PostgreSQL documentation: Write Ahead Log
7. PostgreSQL documentation: Query Planning (effective_cache_size)
8. PostgreSQL documentation: Managing Kernel Resources (overcommit, huge pages)
9. PostgreSQL documentation: Password Authentication
10. PostgreSQL documentation: The pg_hba.conf File
11. PostgreSQL documentation: Routine Vacuuming
12. PostgreSQL documentation: The Cumulative Statistics System
13. PostgreSQL documentation: pg_stat_statements
14. PostgreSQL documentation: Client Connection Defaults (idle_in_transaction_session_timeout)
15. PostgreSQL documentation: Identity Columns
16. PgBouncer: Features
17. Patroni documentation