Andrew Mercer
on this page

PostgreSQL Overview

Concepts referenced throughout the rest of this section — Administration, Replication, and HA with Citus.

Cluster, Database, and Schema — Not What the Names Suggest

PostgreSQL overloads a few terms in ways that trip people up coming from MySQL/MariaDB:

  • A cluster (in the PostgreSQL sense) is a single initdb-created data directory served by one postgres process on one port — not a multi-node replication cluster. One PostgreSQL server = one cluster, full stop, whether or not replication is involved.
  • A database is a fully isolated namespace within a cluster — you cannot join across databases in a single query the way you can across MySQL databases on the same server. \c/\connect actually opens a new backend connection.
  • A schema is a namespace inside a database (default: public) — this is the closer analog to what MySQL calls a "database."

Roles, Not Just Users

PostgreSQL has a single concept, roles, that covers both what other systems call users and groups. A role can: - Log in (LOGIN attribute) — this is what makes it usable like a "user" - Be granted to other roles (group-style membership) - Own objects, hold privileges, and have those privileges inherited by member roles

CREATE USER is literally shorthand for CREATE ROLE ... LOGIN. Understanding this avoids confusion when a permissions problem turns out to be a role-membership question rather than a straightforward grant.

MVCC — Why VACUUM Exists

PostgreSQL uses Multi-Version Concurrency Control: an UPDATE doesn't overwrite a row in place, it writes a new row version and marks the old one dead. Readers never block writers and vice versa, because each transaction sees a consistent snapshot of the data as of its start.

The cost: dead row versions accumulate and need to be reclaimed. VACUUM (and autovacuum, which runs by default) does this reclamation. Neglecting vacuum on a high-write table is one of the most common causes of PostgreSQL performance degradation over time, and in extreme cases (transaction ID wraparound) can force the database into a read-only state — this is worth knowing exists even before you tune anything.

WAL — Write-Ahead Log

Every change is written to the Write-Ahead Log before it's applied to the actual data files. This is what makes crash recovery possible (replay the WAL since the last checkpoint) and is also the mechanism that both physical and logical replication are built on — see Replication. wal_level controls how much detail is captured, and must be raised above the default for replication to work.

Key Configuration Files

All three typically live in the data directory (/var/lib/pgsql/<version>/data/ on RHEL-family systems, /etc/postgresql/<version>/main/ on Debian/Ubuntu):

File Purpose
postgresql.conf Server-wide settings: memory, WAL, connections, listen_addresses
pg_hba.conf Host-Based Authentication — who can connect, from where, using which method
pg_ident.conf Maps OS/external identities to PostgreSQL role names (used with ident/peer auth)

pg_hba.conf is read top-to-bottom, first match wins — a broad rule placed above a narrower one will shadow it silently. This trips people up constantly when troubleshooting "why is this connection using the wrong auth method."

Default Port

5432/tcp. Referenced throughout the Administration and Citus docs when opening firewall rules.

Where to Go Next