13. Extensions and the Postgres Ecosystem

PostgreSQL's core is deliberately small in scope, but its extension system lets third parties add data types, index methods, functions, background workers, and planner hooks that behave as if they were built in. This chapter covers how extensions are packaged, installed, versioned, and built; the extensions that turn PostgreSQL into a spatial database, a time-series store, or a vector search engine; how applications talk to PostgreSQL through drivers, ORMs, and transactions; and the operational choices of running it in production, from managed cloud services and Kubernetes operators to cost planning and observability. Most real PostgreSQL deployments are defined as much by this ecosystem as by the server itself, so knowing what it offers, and what it costs, is part of knowing PostgreSQL.

<a id="131-working-with-extensions"></a>

13.1 Working with Extensions

This subchapter explains what an extension is at the file, catalog, and process level, how to install and upgrade one safely, how dependencies between extensions and database objects are tracked, which modules ship with PostgreSQL itself, and how to package your own code as an extension.

The Extension System

Extensibility as a design principle is one of PostgreSQL's defining traits: the catalog describes types, operators, functions, and index methods as data, so new ones can be added without changing the server. But adding them by hand is messy. Before PostgreSQL 9.1, installing an add-on meant running a SQL script that created dozens of loose functions and types in your database. Nothing recorded that they belonged together: pg_dump dumped every one of them as if you had written it, upgrading the add-on meant hand-editing objects, and removing it meant hunting them down one by one.

An extension solves this by turning a collection of SQL objects into a named, versioned package that the database tracks as a single unit. The server knows which objects belong to which extension, which version is installed, and how to upgrade from one version to another.

Analogy. Installing an add-on with a raw script is like copying an application's files into random folders on your computer. An extension is like installing it through a package manager: the system knows what was installed, which version, what it depends on, and how to upgrade or remove it cleanly.

Anatomy of an extension on disk

An extension is made of up to three kinds of files, installed by an OS package (postgresql-17-postgis-3, postgresql17-contrib) or by make install:

$ pg_config --sharedir
/usr/share/postgresql/17
/usr/share/postgresql/17/extension/
    pg_trgm.control              <-- metadata: default version, library, relocatability, trust
    pg_trgm--1.3.sql             <-- install script for version 1.3
    pg_trgm--1.3--1.4.sql        <-- update script from 1.3 to 1.4
    pg_trgm--1.4--1.5.sql
    pg_trgm--1.5--1.6.sql

$ pg_config --pkglibdir
/usr/lib/postgresql/17/lib
    pg_trgm.so                   <-- compiled C code (only for extensions that need it)

The control file is a small key-value file:

# pg_trgm.control
comment = 'text similarity measurement and index searching based on trigrams'
default_version = '1.6'               # version installed when CREATE EXTENSION gives none
module_pathname = '$libdir/pg_trgm'   # substituted for MODULE_PATHNAME in the SQL scripts
relocatable = true                    # objects can be moved to another schema later
trusted = true                        # non-superusers with CREATE on the database may install it

Three levels of "installed"

Much confusion about extensions comes from mixing up three separate levels:

  SERVER (per installation)          DATABASE (per database)               PROCESS (per backend)
 +--------------------------+       +-------------------------------+      +------------------------+
 | OS package installed     |       | row in pg_extension           |      | shared library (.so)   |
 | .control, .sql, .so      | ----> | member objects created        | ---> | loaded into the        |
 | files present on disk    | CREATE| membership recorded in        | first| backend's memory       |
 |                          | EXT.. | pg_depend (deptype 'e')       | call |                        |
 +--------------------------+       +-------------------------------+      +------------------------+
   "available"                         "installed"                           "loaded"
   pg_available_extensions             pg_extension, \dx                     shared_preload_libraries,
                                                                             or loaded on demand

• Available: the files are on the server. Every database could use the extension, but none does yet.
• Installed: CREATE EXTENSION has run in one database. Extensions are per database; installing pg_trgm in shop does nothing for analytics.
• Loaded: a backend process has loaded the shared library into memory. Most extensions load lazily the first time one of their C functions is called.

-- What could be installed on this server?
SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
ORDER BY name;

-- What is installed in the current database, and in which schema?
SELECT extname, extversion, extnamespace::regnamespace AS schema, extrelocatable
FROM pg_extension;

-- psql shortcuts
\dx              -- installed extensions
\dx+ pg_trgm     -- every object that belongs to pg_trgm

Even PL/pgSQL is an extension: \dx in a fresh database shows plpgsql, installed by default in template1.

What an extension can add

Extension point	Examples
Data types and operators	hstore, citext, ltree, PostGIS geometry, pgvector vector
Functions and aggregates	pgcrypto, tablefunc, fuzzystrmatch
Index operator classes	pg_trgm (GIN/GiST on text), btree_gist, btree_gin
Index access methods	bloom, pgvector's hnsw and ivfflat
Table access methods	Citus columnar storage, OrioleDB (requires patched server)
Foreign data wrappers	postgres_fdw, file_fdw
Procedural languages	plpgsql, plpython3u, plv8
Background workers	pg_cron, TimescaleDB job scheduler
Planner and executor hooks	pg_stat_statements, auto_explain, pg_hint_plan

Libraries that must be preloaded

Extensions that allocate shared memory, register background workers, or install hooks for every query must be loaded when the server starts, through shared_preload_libraries:

# postgresql.conf (changing this requires a server restart)
shared_preload_libraries = 'pg_stat_statements, timescaledb, pg_cron'

Loading mechanism	When it takes effect	Typical use
shared_preload_libraries	Server start (restart required)	Shared memory, background workers, global hooks: pg_stat_statements, timescaledb, pg_cron, citus
session_preload_libraries	Each new session	Per-session hooks without restarting: auto_explain
LOAD 'auto_explain'	Current session only	Debugging one session
Lazy loading	First call of a C function	Most extensions: pg_trgm, hstore, PostGIS

Forgetting the preload step produces errors like pg_stat_statements must be loaded via "shared_preload_libraries", even though CREATE EXTENSION succeeded.

Extensions and pg_dump

Because the server knows which objects belong to an extension, pg_dump emits only CREATE EXTENSION pg_trgm WITH SCHEMA public;, not the hundreds of member objects. On restore, the target server recreates them from its installed files, which is why the extension must be available on the target server. Data in extension-owned configuration tables is dumped only if the extension registered them with pg_extension_config_dump().

Security implications

An extension's C code runs inside the server process with full access to all data and memory. Installing one is as sensitive as installing a kernel module:

• Only superusers can install untrusted extensions. Since PostgreSQL 13, extensions marked trusted = true (for example pg_trgm, hstore, pgcrypto, citext) can be installed by any role with CREATE privilege on the database; the install script still runs with superuser rights, so trusted status is granted only to extensions that cannot be used to escalate privileges.
• Only install extensions from sources you trust, pinned to versions you have reviewed. A malicious or buggy extension can crash the server or read any table.
• Install scripts can be affected by the installing user's search_path. Installing into a dedicated schema that ordinary users cannot write to (see the next topic) avoids object-hijacking attacks.

Key takeaways

• An extension is a named, versioned bundle of SQL objects, optionally backed by a shared library, that the database tracks as a unit.
• Distinguish available (files on disk), installed (per database, pg_extension), and loaded (shared library in a process).
• Extensions that need shared memory, background workers, or global hooks must be listed in shared_preload_libraries, which requires a restart.
• pg_dump emits only CREATE EXTENSION; the extension's files must exist on any server you restore to.
• Extension code runs with full server privileges; trusted extensions (PG 13+) let database owners install a vetted subset without superuser rights.

🧪 Practice

1. (Beginner) List the extensions available on your server and those installed in your current database. Use \dx+ plpgsql to see what objects PL/pgSQL contributes.
2. (Intermediate) Install pg_trgm in one database, then connect to a second database on the same server and try SELECT similarity('postgres', 'postgis');. Explain the result, then find which extensions on your server are marked trusted (SELECT name FROM pg_available_extension_versions WHERE trusted;).
3. (Interview) "A developer ran CREATE EXTENSION pg_stat_statements successfully, but querying the view fails. What is going on, and why does this extension need special treatment?" Hint: Shared memory allocated at postmaster start, shared_preload_libraries, and the difference between installed and loaded.

CREATE EXTENSION and Versioning

Installing an extension takes one statement, but the choices it embeds (which schema, which version, which privileges) affect every later upgrade, dump, and restore. Versioning adds a second subtlety: an extension has a SQL-level version recorded in each database and a binary version on disk, and the two can drift apart.

The CREATE EXTENSION statement

CREATE EXTENSION [ IF NOT EXISTS ] extension_name
    [ WITH ] [ SCHEMA schema_name ]     -- where member objects are created
             [ VERSION version ]        -- defaults to default_version in the control file
             [ CASCADE ]                -- also install required extensions

Choosing a schema. By default, member objects are created in the first schema of the current search_path, usually public. That scatters dozens of extension functions among application objects and makes public hard to lock down. A common convention is a dedicated extensions schema:

CREATE SCHEMA IF NOT EXISTS extensions;
REVOKE CREATE ON SCHEMA extensions FROM PUBLIC;          -- nobody but the owner may add objects
GRANT USAGE ON SCHEMA extensions TO PUBLIC;              -- everyone may use them

CREATE EXTENSION IF NOT EXISTS pg_trgm  WITH SCHEMA extensions;
CREATE EXTENSION IF NOT EXISTS citext   WITH SCHEMA extensions;

-- Make the functions and operators visible without schema-qualifying them
ALTER DATABASE shop SET search_path = "$user", public, extensions;

Some extensions fix their schema (relocatable = false with a schema setting) or cannot be moved after installation. Relocatable ones can be moved later:

ALTER EXTENSION pg_trgm SET SCHEMA extensions;   -- only if the control file says relocatable = true

How versions work

Extension version strings are labels chosen by the author (1.6, 3.5.2, 0.8.0); PostgreSQL does not interpret them as numbers. What matters is which install scripts and update scripts exist. The update scripts form a graph, and ALTER EXTENSION ... UPDATE finds the shortest path through it:

  pg_trgm--1.3.sql  (install script for 1.3)

  1.3 ---> 1.4 ---> 1.5 ---> 1.6
       |        |        |
       |        |        +-- pg_trgm--1.5--1.6.sql
       |        +----------- pg_trgm--1.4--1.5.sql
       +-------------------- pg_trgm--1.3--1.4.sql

  CREATE EXTENSION pg_trgm VERSION '1.6'   -> runs 1.3 install, then 1.3->1.4, 1.4->1.5, 1.5->1.6
  ALTER EXTENSION pg_trgm UPDATE TO '1.6'  -> from installed 1.4: runs 1.4->1.5, 1.5->1.6

-- Which update paths exist?
SELECT source, target, path
FROM pg_extension_update_paths('pg_trgm')
WHERE path IS NOT NULL;

-- Upgrade the SQL objects to the newest version available on disk
ALTER EXTENSION pg_trgm UPDATE;               -- to default_version
ALTER EXTENSION pg_trgm UPDATE TO '1.6';      -- to a specific version

Downgrade scripts are rarely provided; treat extension upgrades as one-way and test them on a copy first.

Two versions that can drift

Upgrading the OS package replaces the files on disk immediately, including the shared library, but each database keeps its SQL objects at the old version until someone runs ALTER EXTENSION ... UPDATE there:

  After "apt upgrade postgresql-17-postgis-3":

  on disk:      postgis-3.so  = 3.5.2       postgis.control default_version = 3.5.2
  database A:   extversion    = 3.4.0       <-- SQL definitions still describe 3.4.0
  database B:   extversion    = 3.5.2       <-- already updated

Well-maintained extensions keep the new library compatible with old SQL definitions, but new functions are missing until the update, and some extensions (TimescaleDB in particular) refuse to work until the SQL version matches the loaded library. A query to find stale installations in a database:

SELECT e.extname,
       e.extversion       AS installed,
       a.default_version  AS available
FROM pg_extension e
JOIN pg_available_extensions a ON a.name = e.extname
WHERE e.extversion IS DISTINCT FROM a.default_version;

Run it in every database, since extensions are per database. A typical upgrade routine:

1. Read the extension's release notes; some updates rewrite data or rebuild indexes.
2. Upgrade the OS package on the primary and all standbys (a standby without the new library breaks after failover).
3. If the extension is preloaded, restart the server so the new library is loaded.
4. In each database: ALTER EXTENSION name UPDATE; (some extensions, such as TimescaleDB, require this to be the first command in a fresh session).
5. Re-run the stale-version query to confirm.

Extensions across major PostgreSQL upgrades

Compiled extensions are built for one PostgreSQL major version. Before a major upgrade, install the extension packages for the new major version. pg_upgrade --check fails if a library referenced by the old cluster is missing in the new one, and after the upgrade it may write an update_extensions.sql script containing the ALTER EXTENSION ... UPDATE statements you still need to run.

Removing extensions

DROP EXTENSION hstore;
-- ERROR:  cannot drop extension hstore because other objects depend on it
-- DETAIL:  column attrs of table products depends on type hstore
-- HINT:  Use DROP ... CASCADE to drop the dependent objects too.

DROP EXTENSION hstore CASCADE;   -- also drops the products.attrs column and its data

CASCADE on DROP EXTENSION can silently remove columns, indexes, and views that use the extension's types or functions. Always run it without CASCADE first and read the dependency list.

Extensions in migrations

Schema-migration tools (11.4) usually create extensions in an early migration. CREATE EXTENSION IF NOT EXISTS is convenient but checks only existence, not version: two environments can both "have" PostGIS while running different versions with different behavior. Record expected versions, and check them in CI or at application startup.

Key takeaways

• Install extensions into a dedicated schema with restricted CREATE privileges, and expose it through search_path.
• Extension versions are labels connected by update scripts; ALTER EXTENSION ... UPDATE walks the shortest path, and downgrades are usually impossible.
• Package upgrades change files on disk at once, but each database's SQL objects stay at the old version until ALTER EXTENSION ... UPDATE runs in it.
• Install new extension packages for the target major version before pg_upgrade, and on every standby.
• DROP EXTENSION ... CASCADE can delete columns and data; check dependencies first.

🧪 Practice

1. (Beginner) Create an extensions schema, install citext into it, and create a table with a citext column. Verify with \dx which schema the extension lives in.
2. (Intermediate) Install pg_trgm at an older version listed in pg_available_extension_versions, inspect pg_extension_update_paths('pg_trgm'), then update it step by step and compare \dx+ pg_trgm output before and after.
3. (Interview) "After an OS patch window, one of twelve databases on a server started throwing errors from a PostGIS function, while the others were fine. What would you check?" Hint: Per-database extversion versus the version on disk, preloaded libraries and restarts, and which databases ran ALTER EXTENSION ... UPDATE.

Managing Extension Dependencies

Extensions rarely live alone. They depend on other extensions, your tables depend on their types and functions, and they depend on OS libraries and server configuration. Each kind of dependency can break an upgrade, a restore, or a failover if nobody tracks it.

Three kinds of dependencies

  OS libraries                   extensions                       your schema
 +----------------+         +----------------------+         +---------------------------+
 | GEOS, PROJ,    | <------ | postgis              | <------ | column  stores.location   |
 | GDAL, libxml2  |  links  |   ^          ^       |  uses   |   type geography          |
 +----------------+         |   |requires  |       |  type   | index   stores_location   |
                            | postgis_     pgrouting|         |   gist opclass            |
                            | topology             |         | view    nearby_stores     |
                            +----------------------+         +---------------------------+
      (1) extension -> OS          (2) extension -> extension       (3) schema -> extension

1. Extension to OS libraries. PostGIS links against GEOS, PROJ, and GDAL. Upgrading those libraries can change the results of geometric operations or coordinate transformations without any change to PostGIS itself.
2. Extension to extension. Declared in the control file with requires, for example earthdistance requires cube.
3. Your objects to extension objects. Columns using an extension's type, indexes using its operator classes, views and functions calling its functions. These are tracked in pg_depend.

Extension-to-extension requirements

CREATE EXTENSION earthdistance;
-- ERROR:  required extension "cube" is not installed
-- HINT:  Use CREATE EXTENSION ... CASCADE to install required extensions too.

CREATE EXTENSION earthdistance CASCADE;
-- NOTICE:  installing required extension "cube"

-- Which extensions does a given version require?
SELECT name, version, requires
FROM pg_available_extension_versions
WHERE requires IS NOT NULL AND installed;

A required extension cannot be dropped while dependents exist, and a dependent extension's functions may refer to objects in the required extension's schema. Since PostgreSQL 16, extension scripts can write @extschema:cube@ to reference that schema explicitly, and the server then prevents relocating cube because doing so would break earthdistance.

Finding what depends on an extension

Every member object of an extension has a pg_depend row with deptype = 'e' pointing at the extension. Your own objects depend on those members through ordinary 'n' (normal) dependencies. To find columns that use types provided by hstore:

SELECT c.oid::regclass               AS table_name,
       a.attname                     AS column_name,
       format_type(a.atttypid, a.atttypmod) AS type
FROM pg_attribute a
JOIN pg_class   c ON c.oid = a.attrelid
JOIN pg_depend  d ON d.classid = 'pg_type'::regclass   -- the column's type...
                 AND d.objid = a.atttypid
                 AND d.deptype = 'e'                   -- ...is a member of an extension
JOIN pg_extension e ON e.oid = d.refobjid
WHERE e.extname = 'hstore'
  AND a.attnum > 0 AND NOT a.attisdropped
  AND c.relkind IN ('r', 'p');                         -- ordinary and partitioned tables

A simpler, version-agnostic trick is to start a transaction, try the drop, and read the error detail:

BEGIN;
DROP EXTENSION hstore;     -- fails with a DETAIL list of dependent objects
ROLLBACK;

Dependencies on server configuration

Some extensions depend on settings outside the database: preloaded libraries, GUCs such as cron.database_name, or wal_level = logical for logical decoding plugins. These are invisible to pg_depend and must be captured in configuration management (see Infrastructure as Code for Databases).

Keeping environments consistent

The most common extension failures in practice are not bugs but drift: production has PostGIS 3.4, staging has 3.5, a developer laptop has none. A cheap defense is an extension manifest that every environment is checked against:

-- Run in each database of each environment and diff the output in CI
SELECT extname, extversion
FROM pg_extension
ORDER BY extname;

-- expected-extensions.txt (checked into the repository)
btree_gist   1.7
citext       1.6
pg_trgm      1.6
plpgsql      1.0
postgis      3.5.2

Where dependencies bite

Situation	What breaks	Prevention
Restore to another server	CREATE EXTENSION fails: files not installed	Install the same packages first; compare pg_available_extensions
Physical standby promoted	Queries fail: shared library missing on standby	Install identical extension packages on every node
Logical replication subscriber	Table with an extension type cannot be created or applied	Install the extension on the subscriber before creating the subscription
Major version upgrade	pg_upgrade --check reports missing libraries	Install extension builds for the new major version
OS library upgrade (PROJ, GEOS, ICU)	Changed results, invalid indexes in rare cases	Pin versions, test, reindex where the extension's docs require it
DROP EXTENSION ... CASCADE	Columns and indexes silently dropped	Never use CASCADE without reading the dependency list

Key takeaways

• Track three dependency layers: extensions on OS libraries, extensions on other extensions (requires), and your schema on extension objects (pg_depend).
• CASCADE resolves requirements on install, but on drop it removes your dependent objects.
• Settings such as shared_preload_libraries are dependencies too, invisible to the catalog.
• Every server that may run your data (standbys, subscribers, restore targets, upgraded clusters) needs the same extension packages.
• Keep an extension manifest under version control and diff it across environments.

🧪 Practice

1. (Beginner) Install earthdistance with CASCADE, then try to drop cube. Read the error and explain it.
2. (Intermediate) Create a table with an hstore column and a GIN index on it, then write a query against pg_depend that lists every object in your schema that depends on any member of hstore.
3. (Interview) "We failed over to a standby and half the application's queries started failing with could not access file "$libdir/vector". Replication was healthy. Explain the failure and how you would prevent it." Hint: WAL replays data pages, not packages; the shared library lives on each server's file system.

Contrib Modules Overview

Not every useful extension is a third-party project. The PostgreSQL source tree has a contrib directory of modules maintained by the core developers, released with every server version, and tested on every platform the server supports. They are the first place to look before reaching for an outside extension: they are always version-compatible with your server and are available on nearly every managed service.

Getting them. On most distributions, contrib modules are a separate package (postgresql17-contrib on RHEL-family systems) or included in the main server package (recent Debian and Ubuntu packages). If pg_available_extensions shows only a handful of rows, the contrib package is probably missing.

The modules, grouped by purpose

Category	Modules	What they give you
Observability	pg_stat_statements, auto_explain	Per-query statistics and automatic plan logging (7.5)
Storage inspection	pg_buffercache, pgstattuple, pageinspect, pg_visibility, pg_freespacemap, pg_walinspect	Look inside shared buffers, pages, bloat, visibility map, WAL
Integrity and repair	amcheck, pg_surgery	Verify B-tree and heap consistency; last-resort repair of corrupted tuples
Performance helpers	pg_prewarm, bloom, btree_gin, btree_gist	Warm the cache after restart; bloom indexes; scalar types in GIN/GiST (needed for exclusion constraints on int plus range)
Text and search	pg_trgm, fuzzystrmatch, unaccent, citext, dict_int, dict_xsyn	Similarity and fuzzy matching, accent removal, case-insensitive text
Data types	hstore, ltree, cube, seg, isn, intarray, lo	Key-value maps, hierarchical labels, multidimensional cubes, ISBN/EAN, integer array operators
Geography (simple)	earthdistance	Great-circle distances on a sphere (with cube or point)
Cryptography and IDs	pgcrypto, uuid-ossp	Hashing, encryption, password hashing; UUID generation
External data	postgres_fdw, file_fdw, dblink	Query other PostgreSQL servers and files (12.4)
Reporting	tablefunc	crosstab pivots, connectby hierarchies
Security and auth	passwordcheck, auth_delay, sslinfo, sepgsql	Password policy hooks, brute-force slowdown, SSL details, SELinux integration
Sampling	tsm_system_rows, tsm_system_time	TABLESAMPLE methods by row count or time

Modules are occasionally removed when they become obsolete; adminpack and old_snapshot, for instance, were dropped in PostgreSQL 17. Check the release notes when upgrading.

A few that pay off immediately

pg_trgm makes LIKE '%term%' and fuzzy matching indexable:

CREATE EXTENSION pg_trgm;

CREATE INDEX customers_name_trgm ON customers USING gin (name gin_trgm_ops);

SELECT name FROM customers WHERE name ILIKE '%ndrea%';   -- can use the trigram index

SELECT name, similarity(name, 'Jonh Smiht') AS score     -- typo-tolerant lookup
FROM customers
WHERE name % 'Jonh Smiht'                                -- % = similarity above pg_trgm.similarity_threshold
ORDER BY score DESC
LIMIT 5;

citext gives case-insensitive comparisons, useful for email addresses (a nondeterministic ICU collation is the modern alternative):

CREATE EXTENSION citext;
CREATE TABLE users (email citext PRIMARY KEY);
INSERT INTO users VALUES ('Ana@Example.com');
SELECT * FROM users WHERE email = 'ana@example.com';     -- matches

pgcrypto handles password hashing inside the database when you cannot do it in the application:

CREATE EXTENSION pgcrypto;
UPDATE accounts SET password_hash = crypt('s3cret', gen_salt('bf', 12)) WHERE id = 1;   -- bcrypt, cost 12
SELECT id FROM accounts WHERE id = 1 AND password_hash = crypt('s3cret', password_hash);

pg_buffercache shows what occupies shared memory:

CREATE EXTENSION pg_buffercache;
SELECT c.relname, count(*) AS buffers,
       pg_size_pretty(count(*) * current_setting('block_size')::int) AS size
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
WHERE b.reldatabase = (SELECT oid FROM pg_database WHERE datname = current_database())
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 10;

amcheck detects index corruption before it causes wrong answers:

CREATE EXTENSION amcheck;
SELECT bt_index_check('orders_pkey'::regclass, heapallindexed => true);   -- raises an error if inconsistent

Contrib vs. third-party extensions

Aspect	Contrib	Third-party (PostGIS, pgvector, TimescaleDB)
Maintainers	PostgreSQL core team	Independent projects or companies
Release cycle	Same as the server	Independent; must track server majors
License	PostgreSQL License	Varies (GPL, Apache, source-available)
Availability on managed services	Nearly universal	Varies by provider and version
Scope	Focused, small utilities	Often large subsystems

Key takeaways

• Contrib modules ship with PostgreSQL, are maintained by the core team, and match your server version.
• Check contrib before adopting a third-party extension: pg_trgm, citext, pgcrypto, btree_gist, and pg_stat_statements cover many common needs.
• Diagnostic modules (pg_buffercache, pgstattuple, pageinspect, amcheck) are essential operational tools.
• Contrib modules are still extensions: they must be installed per database with CREATE EXTENSION.

🧪 Practice

1. (Beginner) Install pg_trgm, create a trigram GIN index on a text column, and compare the plans of WHERE col ILIKE '%abc%' with and without the index.
2. (Intermediate) Use pgstattuple to measure dead tuples in a table before and after a large UPDATE, then after VACUUM. Use pg_buffercache to see how much of the table is cached.
3. (Interview) "You need an exclusion constraint that prevents two bookings of the same room (an integer column) with overlapping time ranges. The CREATE TABLE fails with an operator class error. Which contrib module fixes this, and why is it needed?" Hint: GiST indexes for scalar equality, btree_gist, and EXCLUDE USING gist (room_id WITH =, during WITH &&).

Building Custom Extensions

You might build your own extension for two reasons. The first is packaging: you have SQL functions, types, or views that many databases need, and you want them installed, versioned, and upgraded as a unit instead of copied around in migration scripts. The second is capability: you need something SQL and PL/pgSQL cannot do efficiently, such as a new data type, a fast C function, or an index operator class.

Choosing an implementation route

Route	Language	Good for	Caveats
SQL-only extension	SQL, PL/pgSQL	Shared function libraries, domain types, views	Easiest; no compilation; same speed as plain SQL functions
C extension (PGXS)	C	New types, operators, index support, hot-path functions	Must be rebuilt per major version; bugs can crash the server
Rust extension (pgrx)	Rust	Same as C with memory safety and a modern toolchain	Large binaries; still rebuilt per major version
Trusted Language Extensions (pg_tle)	PL/pgSQL, PL/v8, PL/Rust	Custom extensions on managed services that forbid C	Limited to trusted languages; provider support varies

A complete SQL plus C extension

The example builds textutils, which offers a SQL function slugify(text) in version 1.0 and adds a C function hamming_distance(bigint, bigint) in version 1.1 (useful for comparing 64-bit perceptual image hashes).

textutils/
  textutils.control
  textutils--1.0.sql
  textutils--1.0--1.1.sql
  textutils--1.1.sql            <-- optional: direct install script for new users
  textutils.c
  Makefile
  test/sql/textutils.sql
  test/expected/textutils.out

The control file:

# textutils.control
comment = 'Text and hash utilities'
default_version = '1.1'
module_pathname = '$libdir/textutils'
relocatable = false
schema = textutils               # always installed in this schema (created if missing)
trusted = false

Version 1.0 install script:

-- textutils--1.0.sql
\echo Use "CREATE EXTENSION textutils" to load this file. \quit   -- prevents running it with psql -f

CREATE FUNCTION @extschema@.slugify(input text)
RETURNS text
LANGUAGE sql
IMMUTABLE STRICT PARALLEL SAFE
SET search_path = pg_catalog, pg_temp              -- never resolve names through the caller's path
RETURN trim(BOTH '-' FROM
            regexp_replace(lower(input), '[^a-z0-9]+', '-', 'g'));

The update script from 1.0 to 1.1 only adds what changed. Released scripts are never edited afterward; databases already on 1.0 have run the old one:

-- textutils--1.0--1.1.sql
\echo Use "ALTER EXTENSION textutils UPDATE TO '1.1'" to load this file. \quit

CREATE FUNCTION @extschema@.hamming_distance(bigint, bigint)
RETURNS integer
AS 'MODULE_PATHNAME', 'hamming_distance'          -- library from module_pathname, C symbol name
LANGUAGE C
IMMUTABLE STRICT PARALLEL SAFE;

The C code:

/* textutils.c */
#include "postgres.h"
#include "fmgr.h"
#include "port/pg_bitutils.h"

PG_MODULE_MAGIC;                        /* ABI check: refuses to load into a different major version */

PG_FUNCTION_INFO_V1(hamming_distance);  /* declares the version-1 calling convention */

Datum
hamming_distance(PG_FUNCTION_ARGS)
{
    int64   a = PG_GETARG_INT64(0);     /* STRICT guarantees arguments are not NULL */
    int64   b = PG_GETARG_INT64(1);

    /* number of differing bits */
    PG_RETURN_INT32(pg_popcount64((uint64) (a ^ b)));
}

The Makefile uses PGXS, the build infrastructure installed with the server headers, so you do not need the PostgreSQL source tree:

# Makefile
EXTENSION = textutils
MODULES   = textutils                                   # builds textutils.so from textutils.c
DATA      = textutils--1.0.sql textutils--1.0--1.1.sql textutils--1.1.sql
REGRESS   = textutils                                   # test/sql/textutils.sql vs test/expected/
REGRESS_OPTS = --inputdir=test

PG_CONFIG ?= pg_config                                  # selects which PostgreSQL to build against
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

make                                              # compile against the pg_config found on PATH
sudo make install                                 # copy .so, .control, and .sql files into place
make installcheck                                 # run regression tests against a running server
make PG_CONFIG=/usr/lib/postgresql/16/bin/pg_config   # build for another major version

A regression test is a SQL file plus its expected output; pg_regress runs the file and diffs the result:

-- test/sql/textutils.sql
CREATE EXTENSION textutils;
SELECT textutils.slugify('Hello, World! 2026');
SELECT textutils.hamming_distance(0, 255);
SELECT textutils.slugify(NULL);

The same function in Rust with pgrx

// src/lib.rs, created by `cargo pgrx new textutils`
use pgrx::prelude::*;

pgrx::pg_module_magic!();

#[pg_extern(immutable, parallel_safe, strict)]
fn hamming_distance(a: i64, b: i64) -> i32 {
    (a ^ b).count_ones() as i32          // no manual Datum handling; pgrx generates the SQL wrapper
}

cargo pgrx init                  # download and build test PostgreSQL versions
cargo pgrx run pg17              # build, install into a scratch server, open psql
cargo pgrx test pg17             # run #[pg_test] tests inside a real server
cargo pgrx package               # produce installable files for deployment

Rules that keep custom extensions maintainable

• Never modify a released install or update script. Add a new update script instead; databases that already ran the old one would otherwise diverge from new installs.
• Pin search_path in functions, or reference @extschema@ explicitly, so callers cannot hijack name resolution.
• Declare volatility and parallel safety honestly. Marking a function IMMUTABLE when it reads tables or settings produces wrong results in indexes and cached plans.
• Rebuild for every major version. C and Rust extensions are tied to the server's internal ABI; PG_MODULE_MAGIC refuses mismatched loads.
• Allocate memory with palloc in C so it is freed with the memory context, and report errors with ereport, never exit or abort. See C Language Functions for the calling conventions.
• Test upgrades, not only installs: install 1.0, update to 1.1, and confirm the result matches a fresh 1.1 install (\dx+ output should be identical).

Key takeaways

• An extension needs a control file and install script; update scripts and a shared library are optional.
• SQL-only extensions are the simplest way to version and share database code across many databases.
• PGXS builds C extensions against an installed server; make installcheck runs pg_regress tests.
• pgrx lets you write extensions in Rust with generated SQL wrappers and in-server tests.
• Released scripts are immutable; every change ships as a new update script, and upgrades must be tested as carefully as installs.

🧪 Practice

1. (Beginner) Package two of your own SQL functions as a SQL-only extension with a control file and one install script. Install it with make install and CREATE EXTENSION.
2. (Intermediate) Add version 1.1 with a new function and an update script. Verify that "install 1.0 then update" and "install 1.1 directly" produce identical \dx+ output, and write a pg_regress test for both functions.
3. (Interview) "Your team wants to deploy a C extension that worked on a self-hosted PostgreSQL 16 to a managed PostgreSQL 17 service. What obstacles do you expect?" Hint: ABI per major version, superuser and file-system access on managed services, provider allowlists, and alternatives such as pg_tle or rewriting in a trusted language.

<a id="132-notable-extensions"></a>

13.2 Notable Extensions

A handful of extensions turn PostgreSQL into a specialized system without giving up SQL, transactions, or the rest of your data. This subchapter covers spatial data with PostGIS, time series with TimescaleDB, vector similarity search with pgvector, the hstore key-value type, UUID generation, and when PostgreSQL's built-in geometric types are enough.

PostGIS for Spatial Data

Many applications ask spatial questions: which stores are within 2 km of this customer, which delivery zone contains this address, which roads cross this river. You can store latitude and longitude as two numeric columns, but then every question becomes hand-written trigonometry that no index can help with, and harder questions (does this polygon overlap that one?) become research projects. PostGIS adds spatial types, hundreds of spatial functions following the OGC Simple Features and SQL/MM standards, and spatial indexing, making PostgreSQL one of the most capable geographic databases available, open source or not.

Core concepts

• Geometry types: POINT, LINESTRING, POLYGON, and their MULTI* variants, plus GEOMETRYCOLLECTION.
• Spatial reference system (SRS) and SRID: coordinates mean nothing without a reference system. The SRID is an integer naming one; 4326 is WGS 84 longitude/latitude in degrees (what GPS gives you), 3857 is Web Mercator (web map tiles), and local projections such as UTM zones measure in meters. Definitions live in the spatial_ref_sys table.
• Two spatial types:

Aspect	geometry	geography
Model	Flat (Cartesian) plane	Sphere/spheroid (Earth)
Units	Units of the SRS (degrees for 4326, meters for UTM)	Always meters
Accuracy over long distances	Distorted unless projected suitably	Correct anywhere on Earth
Function coverage	Complete	Smaller subset
Speed	Faster	Slower (spherical math)
Typical use	Local or projected data, complex analysis	Global point data, distance queries in meters

Analogy. geometry is a paper map: easy to measure with a ruler, accurate for a city, misleading for the distance between Rome and Tokyo. geography is a globe: harder to draw on, but distances are true everywhere.

A store locator

CREATE EXTENSION postgis;
SELECT postgis_full_version();      -- PostGIS, GEOS, PROJ versions: record these

CREATE TABLE stores (
    id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name     text NOT NULL,
    location geography(Point, 4326) NOT NULL       -- typmod constrains shape and SRID
);

INSERT INTO stores (name, location) VALUES
    ('Termini',  ST_SetSRID(ST_MakePoint(12.5011, 41.9010), 4326)::geography),   -- ST_MakePoint(LONGITUDE, LATITUDE)
    ('Trastevere', ST_SetSRID(ST_MakePoint(12.4700, 41.8890), 4326)::geography),
    ('EUR',      'SRID=4326;POINT(12.4680 41.8300)'::geography);                 -- EWKT text form

CREATE INDEX stores_location_gist ON stores USING gist (location);

-- Stores within 2 km of a customer, nearest first
SELECT name,
       round(ST_Distance(location, ST_MakePoint(12.4823, 41.8955)::geography)) AS meters
FROM stores
WHERE ST_DWithin(location, ST_MakePoint(12.4823, 41.8955)::geography, 2000)   -- index-assisted radius filter
ORDER BY meters;

-- The 3 nearest stores regardless of distance (KNN index scan)
SELECT name
FROM stores
ORDER BY location <-> ST_MakePoint(12.4823, 41.8955)::geography
LIMIT 3;

Point in polygon

CREATE TABLE delivery_zones (
    id   int PRIMARY KEY,
    name text NOT NULL,
    area geometry(MultiPolygon, 4326) NOT NULL
);
CREATE INDEX delivery_zones_area_gist ON delivery_zones USING gist (area);

-- Which zone serves this customer?
SELECT z.name
FROM delivery_zones z
WHERE ST_Contains(z.area, ST_SetSRID(ST_MakePoint(12.4823, 41.8955), 4326));

-- Area in square meters: cast to geography, or transform to a metric projection
SELECT name, ST_Area(area::geography) AS m2 FROM delivery_zones;
SELECT name, ST_Area(ST_Transform(area, 32633)) AS m2 FROM delivery_zones;   -- UTM zone 33N

How spatial indexing works

A GiST index on a spatial column stores each shape's bounding box. Queries run in two phases:

  Phase 1: index filter (fast, approximate)        Phase 2: exact test (slow, precise)
  +----------------------------+                   +------------------------------+
  | does the bounding box of   |  candidates  -->  | ST_Contains / ST_Intersects  |
  | the shape overlap the      |                   | on the real geometry         |
  | query's bounding box? (&&) |                   | (GEOS computational geometry)|
  +----------------------------+                   +------------------------------+

Functions such as ST_DWithin, ST_Intersects, and ST_Contains perform phase 1 automatically. Writing WHERE ST_Distance(a, b) < 2000 does not: it computes the exact distance for every row. Prefer ST_DWithin.

Common pitfalls

Pitfall	Symptom	Fix
Swapping latitude and longitude	Points land in the ocean or Antarctica	ST_MakePoint(lon, lat): X before Y
ST_Distance on geometry(…, 4326)	Distance in degrees, meaningless	Use geography, or ST_Transform to a metric SRID
ST_Distance(...) < r in WHERE	Sequential scan	ST_DWithin(a, b, r)
Mixed SRIDs	ERROR: Operation on mixed SRID geometries	Constrain columns with typmods; ST_Transform inputs
Huge, complex polygons	Slow containment tests	ST_Subdivide into smaller pieces and index those
Invalid geometries from imports	Wrong results or errors	ST_IsValid, ST_MakeValid on load

The ecosystem around it

• Loading data: shp2pgsql (Shapefiles), ogr2ogr from GDAL (almost any format), osm2pgsql (OpenStreetMap), raster2pgsql.
• Output: ST_AsGeoJSON, ST_AsText, ST_AsMVT (Mapbox vector tiles served straight from SQL).
• Companion extensions: postgis_raster, postgis_topology, postgis_sfcgal (3D), pgrouting (shortest paths on road networks), h3 (hexagonal grid indexing).
• Tools: QGIS for desktop analysis, GeoServer, pg_tileserv and Martin for tile serving.

Key takeaways

• PostGIS adds standard spatial types, functions, and GiST-based indexing to PostgreSQL.
• Every shape has an SRID; 4326 is longitude/latitude in degrees, and ST_MakePoint takes longitude first.
• geography measures in meters on the Earth; geometry is planar and faster, best with projected SRIDs.
• Index-aware predicates (ST_DWithin, ST_Intersects, ST_Contains, <->) use bounding boxes first, then exact tests.
• Record PostGIS, GEOS, and PROJ versions; library upgrades can change results.

🧪 Practice

1. (Beginner) Create the stores table, insert ten points around your city, and find the stores within 1 km of a chosen point, ordered by distance.
2. (Intermediate) Load a polygon dataset (for example, administrative boundaries via ogr2ogr), then for 100,000 random points count how many fall in each polygon. Compare plans with and without the GiST index, and with ST_Subdivided polygons.
3. (Interview) "A 'nearby restaurants' query uses WHERE ST_Distance(location, :point) < 0.01 on a geometry(Point, 4326) column and is both slow and inaccurate. Explain both problems." Hint: Units of SRID 4326, why degree-based distances vary with latitude, and why the predicate cannot use the index.

TimescaleDB for Time Series

Time-series data (metrics, sensor readings, events, prices) has a recognizable shape: rows arrive in time order, are rarely updated, are queried mostly over recent windows, are aggregated into time buckets, and eventually expire. Plain PostgreSQL handles this with declarative partitioning, BRIN indexes, and scheduled jobs, but you must build and run all of that yourself. TimescaleDB packages these patterns into an extension: automatic time partitioning, columnar compression, incrementally maintained rollups, and retention policies, all queried with ordinary SQL.

Hypertables and chunks

A hypertable looks like one table to queries, but TimescaleDB stores it as many child tables called chunks, each covering a time interval (and optionally a range of another dimension). New chunks are created automatically as data arrives.

                       hypertable "metrics"  (what you query)
  +---------------------------------------------------------------------------+
  |  chunk 1          chunk 2          chunk 3          chunk 4 (current)      |
  |  Oct 1            Oct 2            Oct 3            Oct 4                  |
  |  [compressed,     [compressed,     [row store,      [row store, hot,       |
  |   columnar]        columnar]        recent]          receiving inserts]    |
  +---------------------------------------------------------------------------+
     ^ dropped by retention policy after 90 days
  Queries with WHERE time > now() - interval '6 hours' touch only chunk 4 (chunk exclusion).

Small, recent chunks keep their indexes in memory, inserts stay fast as the table grows to billions of rows, and dropping old data means dropping whole chunks instead of running massive DELETEs that create bloat.

Setting it up

# postgresql.conf: TimescaleDB runs background jobs and planner hooks, so it must be preloaded
shared_preload_libraries = 'timescaledb'

CREATE EXTENSION timescaledb;

CREATE TABLE metrics (
    time        timestamptz      NOT NULL,
    device_id   int              NOT NULL,
    cpu         double precision,
    temperature double precision
);

-- Turn it into a hypertable partitioned by time, one chunk per day
SELECT create_hypertable('metrics', by_range('time', INTERVAL '1 day'));

CREATE INDEX ON metrics (device_id, time DESC);    -- created on every chunk automatically

Choose a chunk interval so that the most recent chunks (with their indexes) fit comfortably in memory; one day is a reasonable start for moderate ingest rates.

Querying with time buckets

-- 15-minute averages for the last 6 hours
SELECT time_bucket('15 minutes', time) AS bucket,
       device_id,
       avg(cpu)          AS avg_cpu,
       max(temperature)  AS max_temp
FROM metrics
WHERE time > now() - INTERVAL '6 hours'
GROUP BY bucket, device_id
ORDER BY bucket, device_id;

-- Latest reading per device (uses the (device_id, time DESC) index)
SELECT DISTINCT ON (device_id) device_id, time, cpu
FROM metrics
ORDER BY device_id, time DESC;

time_bucket is like date_trunc but accepts arbitrary intervals (5 minutes, 6 hours) and offsets.

Continuous aggregates

Dashboards that recompute hourly averages over a year of raw data are slow. A continuous aggregate is a materialized view that TimescaleDB refreshes incrementally, recomputing only buckets whose underlying data changed:

CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS bucket,
       device_id,
       avg(cpu)         AS avg_cpu,
       max(temperature) AS max_temp
FROM metrics
GROUP BY bucket, device_id;

-- Refresh every 30 minutes, covering data from 3 hours ago up to 1 hour ago
SELECT add_continuous_aggregate_policy('metrics_hourly',
    start_offset      => INTERVAL '3 hours',
    end_offset        => INTERVAL '1 hour',
    schedule_interval => INTERVAL '30 minutes');

By default, queries on the view can combine materialized buckets with not-yet-materialized recent raw data ("real-time aggregation"), so results are current.

Compression and retention

-- Store older chunks in compressed columnar form, grouped by device, ordered by time
ALTER TABLE metrics SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device_id',     -- one compressed batch per device
    timescaledb.compress_orderby   = 'time DESC'
);
SELECT add_compression_policy('metrics', INTERVAL '7 days');   -- compress chunks older than 7 days

-- Drop raw chunks older than 90 days (the hourly aggregate can be kept longer)
SELECT add_retention_policy('metrics', INTERVAL '90 days');

-- Inspect background jobs and chunk sizes
SELECT * FROM timescaledb_information.jobs;
SELECT * FROM chunks_detailed_size('metrics') ORDER BY chunk_name;

Compression ratios of 90% or more are common for metrics, because values within a device's column change slowly and compress well. Recent TimescaleDB releases rename compression to the "columnstore" (add_columnstore_policy and related settings); the older names remain available. The API has evolved across 2.x versions, so check the documentation for the version you run.

Licensing and availability

Component	License	Implication
Core (hypertables, time_bucket)	Apache 2.0	Usable anywhere
Compression, continuous aggregates, policies, many advanced functions	Timescale License (TSL), source-available	Free to self-host; cloud providers other than the vendor may not offer these features

Many managed services ship only the Apache-licensed edition, or no TimescaleDB at all, so check before designing around compression or continuous aggregates.

TimescaleDB vs. plain PostgreSQL

Need	Plain PostgreSQL	TimescaleDB
Time partitioning	Declarative partitioning plus pg_partman or cron jobs	Automatic chunks
Small indexes for huge append-only tables	BRIN	B-tree per chunk; BRIN also possible
Rollups	Materialized views, refreshed fully	Incremental continuous aggregates
Compression	TOAST only (per value)	Columnar per chunk
Retention	DROP/DETACH PARTITION jobs	add_retention_policy
Operational cost	No extra dependency	Extension upgrades, preload, license considerations

Key takeaways

• Hypertables split data into time-based chunks automatically, keeping inserts and recent queries fast at any total size.
• time_bucket aggregates into arbitrary intervals; continuous aggregates maintain rollups incrementally.
• Columnar compression and retention policies manage storage for old data with background jobs.
• TimescaleDB must be preloaded, and many advanced features are under a source-available license that affects managed-service availability.
• Plain partitioning plus BRIN is a viable alternative when you need only part of this feature set.

🧪 Practice

1. (Beginner) Create the metrics hypertable, insert a week of synthetic data with generate_series, and inspect the chunks with show_chunks('metrics').
2. (Intermediate) Create the hourly continuous aggregate and a compression policy. Compare table size and the latency of a 7-day dashboard query against raw data, the compressed data, and the aggregate.
3. (Interview) "Our IoT table receives 50,000 rows per second and has grown to 4 billion rows. Inserts are getting slower and deleting old data takes hours. What would you change, with or without TimescaleDB?" Hint: Index size versus memory, time partitioning, dropping partitions instead of deleting, BRIN, and rollups.

pgvector for Embeddings and Similarity Search

Machine-learning models can turn text, images, or audio into embeddings: fixed-length vectors of numbers (for example 768 or 1,536 floats) where similar meanings end up close together in space. "How do I reset my password?" and "I forgot my login credentials" share few words but have nearby embeddings. Finding the stored items closest to a query vector powers semantic search, recommendations, deduplication, and retrieval-augmented generation (RAG) for language models. pgvector adds a vector type, distance operators, and approximate nearest-neighbor indexes, so embeddings can live next to the data they describe instead of in a separate vector database.

Analogy. Imagine every document pinned to a point on an enormous map where nearby pins are about similar things. A search is "drop a pin for my question and find the closest pins". An exact search measures the distance to every pin; an index is like a network of signposts that gets you to the right neighborhood quickly, at the cost of occasionally missing a pin just around the corner.

Types and distance operators

Operator	Distance	Use when
<->	Euclidean (L2)	Embeddings where magnitude matters
<=>	Cosine distance (1 − cosine similarity)	Most text embedding models
<#>	Negative inner product	Normalized vectors (equivalent ranking to cosine, faster)
<+>	Manhattan (L1)	Specific models, sparse features

Besides vector (32-bit floats), pgvector provides halfvec (16-bit floats, half the storage), sparsevec (mostly-zero vectors), and bit (binary vectors with Hamming <~> and Jaccard <%> distances).

Semantic search over documents

CREATE EXTENSION vector;

CREATE TABLE documents (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id  int    NOT NULL,
    content    text   NOT NULL,
    embedding  vector(1536) NOT NULL          -- dimension must match the embedding model
);

-- Approximate nearest-neighbor index; opclass must match the operator used in queries
CREATE INDEX documents_embedding_hnsw
    ON documents USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);      -- graph connectivity and build-time search width

-- Top 10 most similar documents to a query embedding ($1 computed by the application)
SELECT id, content, embedding <=> $1 AS distance
FROM documents
ORDER BY embedding <=> $1                     -- ORDER BY distance + LIMIT is what triggers the index
LIMIT 10;

From Python, the pgvector package registers the type with the driver:

import psycopg
from pgvector.psycopg import register_vector

with psycopg.connect("dbname=app") as conn:
    register_vector(conn)                              # adapts numpy arrays <-> vector
    query_vec = embed("How do I reset my password?")   # your embedding model call
    rows = conn.execute(
        "SELECT id, content FROM documents ORDER BY embedding <=> %s LIMIT 5",
        (query_vec,),
    ).fetchall()

Exact vs. approximate search

Without an index, PostgreSQL computes the distance to every row: perfect recall (it finds the true nearest neighbors) but linear cost. Approximate indexes trade a little recall for orders-of-magnitude speed.

Aspect	HNSW	IVFFlat
Structure	Multi-layer proximity graph	Vectors clustered into lists buckets around centroids
Build	Slower, more memory; can be built on an empty table	Faster; needs representative data first to compute centroids
Query speed / recall	Better trade-off in most cases	Good, degrades if data drifts from the original centroids
Query-time knob	SET hnsw.ef_search = 100; (default 40)	SET ivfflat.probes = 10; (default 1)
Updates	Handles inserts well	Recall drops as new data diverges; rebuild periodically

-- Raise recall for one transaction at the cost of latency
BEGIN;
SET LOCAL hnsw.ef_search = 200;
SELECT id FROM documents ORDER BY embedding <=> $1 LIMIT 10;
COMMIT;

-- Large index builds: give the build memory and parallel workers
SET maintenance_work_mem = '8GB';
SET max_parallel_maintenance_workers = 7;

Indexes support up to 2,000 dimensions for vector and 4,000 for halfvec; higher-dimensional embeddings can be indexed as halfvec via an expression index, or reduced in dimension.

The filtering problem

Real queries filter: "the most similar documents for tenant 42". An approximate index returns the ef_search nearest candidates first, and the WHERE clause is applied afterwards. If tenant 42 owns 1% of the rows, most candidates are discarded and you may get fewer than 10 results.

-- May return fewer than 10 rows when tenant 42 is a small fraction of the table
SELECT id FROM documents
WHERE tenant_id = 42
ORDER BY embedding <=> $1
LIMIT 10;

Remedies, from simplest to most structural:

• Iterative index scans (pgvector 0.8+): SET hnsw.iterative_scan = relaxed_order; keeps scanning the graph until enough rows pass the filter.
• Partial indexes for a few large, fixed categories: CREATE INDEX ... WHERE tenant_id = 42.
• Partitioning by tenant or category, so each partition has its own index.
• A B-tree on the filter column for highly selective filters: the planner can then filter first and compute exact distances on the few remaining rows.

Hybrid search

Embeddings capture meaning but can miss exact terms such as product codes; full-text search does the opposite. Combining them with reciprocal rank fusion often beats either alone. The query assumes a tsv tsvector column (for example, a generated column over content) with a GIN index:

WITH semantic AS (
    SELECT id, row_number() OVER (ORDER BY embedding <=> $1) AS rank
    FROM documents ORDER BY embedding <=> $1 LIMIT 50
),
keyword AS (
    SELECT id, row_number() OVER (ORDER BY ts_rank_cd(tsv, q) DESC) AS rank
    FROM documents, websearch_to_tsquery('english', $2) AS q
    WHERE tsv @@ q
    ORDER BY ts_rank_cd(tsv, q) DESC LIMIT 50
)
SELECT id,
       coalesce(1.0 / (60 + s.rank), 0) + coalesce(1.0 / (60 + k.rank), 0) AS score   -- RRF, k = 60
FROM semantic s FULL JOIN keyword k USING (id)
ORDER BY score DESC
LIMIT 10;

pgvector vs. a dedicated vector database

Consideration	pgvector	Dedicated vector database
Data consistency	Embeddings and source rows in one transaction	Two systems to keep in sync
Filtering and joins	Full SQL	Product-specific filter language
Operations	One database to back up, secure, monitor	Another system to run
Very large scale (billions of vectors), specialized quantization	Possible but demanding (memory for HNSW)	Often purpose-built for it

Key takeaways

• pgvector stores embeddings as vector, halfvec, sparsevec, or bit, and ranks by L2, cosine, inner product, or L1 distance.
• Approximate indexes (HNSW, IVFFlat) are used only for ORDER BY distance LIMIT k with the operator matching the index's opclass.
• Tune recall with hnsw.ef_search or ivfflat.probes; measure recall against exact search.
• Filters are applied after the approximate scan; use iterative scans, partial indexes, or partitioning for filtered searches.
• Keeping vectors in PostgreSQL gives transactional consistency and SQL joins at the cost of handling scale and memory yourself.

🧪 Practice

1. (Beginner) Create a table with vector(3), insert a few points, and compare the rankings produced by <->, <=>, and <#> for the same query vector.
2. (Intermediate) Load 1,000,000 random 384-dimension vectors, build an HNSW index, and measure recall@10 (overlap with exact search, using SET enable_indexscan = off for the exact baseline) and latency for ef_search values 20, 40, 100, and 200.
3. (Interview) "Our multi-tenant RAG application sometimes returns only two or three results for small tenants, even though they have hundreds of relevant documents. Why, and how would you fix it?" Hint: Approximate index scans return a bounded candidate set before the WHERE filter; iterative scans, partitioning, or per-tenant indexes.

hstore Key-Value Store

Sometimes each row needs a set of attributes that differs from row to row: product specifications (a laptop has ram and cpu, a shirt has size and color), tags on map features, or user-defined metadata. Adding a column for every possible attribute is impractical, and an entity-attribute-value table makes simple queries painful. hstore, a contrib module that predates PostgreSQL's JSON support by years, stores a flat map of text keys to text values in a single column.

Basics

CREATE EXTENSION hstore;

CREATE TABLE products (
    id    int PRIMARY KEY,
    name  text NOT NULL,
    attrs hstore NOT NULL DEFAULT ''
);

INSERT INTO products VALUES
    (1, 'Laptop', 'ram => 16GB, cpu => "8 cores", color => silver'),
    (2, 'Shirt',  'size => M, color => blue'),
    (3, 'Lamp',   'color => NULL');                      -- values may be NULL; keys may not

Operators and functions

Expression	Meaning	Example result
attrs -> 'color'	Value for a key (or NULL)	silver
attrs ? 'ram'	Has key	true
attrs ?& ARRAY['size','color']	Has all keys
`attrs ?	ARRAY['ram','size']`	Has any key
attrs @> 'color => blue'	Contains these pairs
`attrs		'stock => 5'`
attrs - 'color'	Remove a key
akeys(attrs), avals(attrs)	Keys or values as arrays
each(attrs)	Set of (key, value) rows
hstore_to_jsonb(attrs)	Convert to jsonb

-- Blue products of size M
SELECT id, name FROM products WHERE attrs @> 'color => blue, size => M';

-- Index containment and key-existence queries
CREATE INDEX products_attrs_gin ON products USING gin (attrs);

-- Update one key without rewriting the others
UPDATE products SET attrs = attrs || 'stock => 12' WHERE id = 1;

-- Key frequency across the catalog
SELECT key, count(*) FROM products, each(attrs) GROUP BY key ORDER BY count(*) DESC;

Limitations

• Values are text only. 16GB and 16 are strings; numeric comparison needs casts and validation.
• No nesting. One level of key/value pairs; no arrays or sub-objects.
• No standard format. Applications must parse the "k"=>"v" syntax or rely on driver adapters (psycopg and others provide them).

hstore vs. jsonb

Aspect	hstore	jsonb
Structure	Flat text-to-text map	Nested objects, arrays, numbers, booleans, null
Availability	Extension (contrib)	Built in
Interchange	PostgreSQL-specific format	JSON, universal
Query language	Operators above	Operators plus SQL/JSON path (3.4)
Indexing	GIN, GiST, B-tree/hash on expressions	GIN (jsonb_ops, jsonb_path_ops), expression indexes
Size and speed for flat maps	Slightly more compact, fast	Comparable

For new designs, jsonb is almost always the better choice. hstore remains relevant in existing schemas, in tools that emit it (for example, osm2pgsql --hstore stores OpenStreetMap tags as hstore), and for trigger code that diffs rows, since hstore(NEW) - hstore(OLD) neatly yields the changed columns:

-- In an audit trigger: only the columns that changed
SELECT hstore(NEW) - hstore(OLD);    -- e.g. "price"=>"12.50", "updated_at"=>"2026-10-06 ..."

Migrating to jsonb

ALTER TABLE products
    ALTER COLUMN attrs DROP DEFAULT,
    ALTER COLUMN attrs TYPE jsonb USING hstore_to_jsonb_loose(attrs),   -- _loose turns '16' into 16, 't' into true
    ALTER COLUMN attrs SET DEFAULT '{}';
-- Rewrites the table under an ACCESS EXCLUSIVE lock: schedule it, or migrate via a new column in batches.
-- Recreate the GIN index afterwards with a jsonb operator class.

Key takeaways

• hstore stores flat text-to-text maps in one column, with containment and key-existence operators that GIN can index.
• Values are always text and cannot nest, which limits its use for structured data.
• jsonb supersedes hstore for most new designs; hstore survives in legacy schemas, OSM tooling, and row-diffing in triggers.
• Convert with hstore_to_jsonb (all strings) or hstore_to_jsonb_loose (infers numbers and booleans).

🧪 Practice

1. (Beginner) Create the products table, then find all products that have a color key but no size key.
2. (Intermediate) Write a trigger that logs only changed columns of a table into an audit table using hstore(NEW) - hstore(OLD). Then rewrite it with jsonb and compare the code.
3. (Interview) "A legacy table stores product attributes in an hstore column, and the team wants range queries on a numeric attribute such as weight. What are the options?" Hint: Casting with an expression index, generated columns, or migrating to jsonb or real columns for frequently filtered attributes.

uuid-ossp and Identifier Generation

The UUID type stores 128-bit identifiers efficiently; the question here is how to generate them, and which kind to generate. Different UUID versions encode different information, and the choice affects privacy, determinism, and index performance.

UUID versions at a glance

Version	Content	Properties	Typical use
v1	Timestamp + clock sequence + MAC address	Roughly time-ordered (but bytes are not sortable); leaks MAC and creation time	Legacy systems
v3 / v5	MD5 / SHA-1 hash of a namespace + name	Deterministic: same input, same UUID	Stable IDs derived from natural keys or URLs
v4	122 random bits	Unpredictable, no ordering	General-purpose IDs
v7	48-bit Unix millisecond timestamp + random bits	Sortable by creation time, unpredictable within a millisecond	Primary keys in B-tree-indexed tables

Where generation functions come from

Function	Source	Notes
gen_random_uuid()	Built in since PG 13 (earlier via pgcrypto)	v4; no extension needed
uuidv4()	Built in since PG 18	Alias for gen_random_uuid()
uuidv7()	Built in since PG 18	Time-ordered v7; optional interval argument shifts the timestamp
uuid_generate_v1(), uuid_generate_v1mc()	uuid-ossp	v1 with real MAC, or random multicast MAC
uuid_generate_v3(ns, name), uuid_generate_v5(ns, name)	uuid-ossp	Name-based, deterministic
uuid_generate_v4()	uuid-ossp	v4; equivalent to gen_random_uuid()
uuid_extract_version(), uuid_extract_timestamp()	Built in since PG 17	Inspect existing UUIDs (timestamp extraction for v1 since PG 17, for v7 since PG 18)

Note that the extension name contains a hyphen and must be quoted:

CREATE EXTENSION "uuid-ossp";

-- Deterministic IDs: the same URL always maps to the same UUID
SELECT uuid_generate_v5(uuid_ns_url(), 'https://example.com/products/42');
SELECT uuid_generate_v5(uuid_ns_url(), 'https://example.com/products/42');   -- identical result

-- Your own namespace for business keys (generate once, keep as a constant)
SELECT uuid_generate_v5('f3b1c2a4-5d6e-4f70-8a9b-0c1d2e3f4a5b'::uuid, 'customer:ACME-0042');

uuid_ns_url(), uuid_ns_dns(), uuid_ns_oid(), and uuid_ns_x500() return the standard namespaces from RFC 4122 (now RFC 9562).

Why the version matters for indexes

A B-tree index keeps keys sorted. Random v4 keys land on a random leaf page on every insert:

  Random v4 inserts                         Time-ordered v7 inserts
  +----+----+----+----+----+----+           +----+----+----+----+----+----+
  | *  |    | *  |  * |    | *  |  pages    |    |    |    |    |    | ** |  <- all inserts hit the
  +----+----+----+----+----+----+           +----+----+----+----+----+----+     rightmost leaf
  every leaf page is "hot"                  only the newest page is hot
  -> more pages in memory, more page splits, -> compact index, cache-friendly,
     more full-page images in WAL              fewer page splits

On a table with hundreds of millions of rows whose index no longer fits in memory, random keys cause a cache miss on most inserts. Time-ordered v7 keys behave much like a bigint sequence while remaining globally unique and generatable by clients.

-- PostgreSQL 18+
CREATE TABLE orders (
    id         uuid PRIMARY KEY DEFAULT uuidv7(),
    created_at timestamptz NOT NULL DEFAULT now(),
    total      numeric(12,2) NOT NULL
);

SELECT id, uuid_extract_timestamp(id) FROM orders LIMIT 3;   -- creation time is embedded in the key

On older versions, generate v7 UUIDs in the application (most languages have libraries) or use a third-party extension.

Choosing a strategy

Requirement	Recommended
Single database, internal IDs, maximum compactness	bigint GENERATED ALWAYS AS IDENTITY
IDs generated by clients, merged across systems, or exposed in URLs	UUID v7 (or v4 if creation time must stay hidden)
Same input must always produce the same ID	UUID v5 with a fixed namespace
Creation time must not be inferable from the ID	UUID v4
Interoperating with systems that already use v1	uuid-ossp's uuid_generate_v1mc() (avoids leaking the MAC address)

Key takeaways

• gen_random_uuid() (v4) is built in since PostgreSQL 13 and uuidv7() since 18; uuid-ossp is needed mainly for v1, v3, and v5.
• Quote the extension name: CREATE EXTENSION "uuid-ossp";.
• v5 gives deterministic IDs from a namespace plus name; v4 gives unpredictable IDs; v7 gives time-ordered IDs.
• Random keys scatter B-tree inserts across the whole index; time-ordered keys keep inserts local and cache-friendly.
• v1 and v7 reveal creation time, and v1 can reveal a MAC address; consider this when IDs are public.

🧪 Practice

1. (Beginner) Generate one UUID of each version available to you and use uuid_extract_version() (PG 17+) to confirm which is which.
2. (Intermediate) Create two tables with a UUID primary key, one defaulting to v4 and one to v7 (or app-generated v7). Insert 10 million rows into each and compare insert time, index size, and WAL volume (pg_current_wal_lsn() before and after).
3. (Interview) "We're moving from integer IDs to UUIDs so that mobile clients can create records offline. A colleague is worried about index performance. What would you recommend?" Hint: Locality of B-tree inserts, time-ordered versions, information leakage, and storage size compared to bigint.

postgis vs. Geometric Types Trade-offs

PostgreSQL has had geometric types since its research origins: point, line, lseg, box, path, polygon, and circle, with operators and GiST support. So why install PostGIS at all? And when is the built-in set enough? The answer depends on whether your coordinates describe the Earth.

Built-in types in action

Built-in types model shapes on an abstract, infinite, flat plane, with no notion of units or projections. They are a good fit for floor plans, game worlds, warehouse layouts, or chart coordinates:

CREATE TABLE seats (
    id  int PRIMARY KEY,
    pos point NOT NULL              -- (x, y) in meters from the venue's corner
);
CREATE INDEX seats_pos_gist ON seats USING gist (pos);

-- Seats inside a rectangular section
SELECT id FROM seats WHERE pos <@ box '((0,0),(20,10))';

-- Three seats nearest the stage center (KNN with GiST)
SELECT id FROM seats ORDER BY pos <-> point '(10,25)' LIMIT 3;

-- Is a point within a circular exclusion zone?
SELECT point '(4,4)' <@ circle '((5,5),2)';

A middle ground: earthdistance

The contrib earthdistance module treats the Earth as a perfect sphere and computes great-circle distances from latitude and longitude. It is useful for "within N km" queries when PostGIS is unavailable:

CREATE EXTENSION earthdistance CASCADE;   -- installs cube too

CREATE INDEX stores_earth_idx ON stores_plain USING gist (ll_to_earth(lat, lon));

SELECT name
FROM stores_plain
WHERE earth_box(ll_to_earth(41.8955, 12.4823), 2000) @> ll_to_earth(lat, lon)     -- index-assisted box
  AND earth_distance(ll_to_earth(41.8955, 12.4823), ll_to_earth(lat, lon)) < 2000; -- exact sphere distance

Note the argument order here is latitude first, the opposite of PostGIS's ST_MakePoint(lon, lat).

Comparison

Aspect	Built-in geometric types	earthdistance	PostGIS
Model	Flat plane	Sphere	Plane and spheroid, any projection
Coordinate systems	None	Lat/lon only	Thousands (SRIDs), with transformations
Distance accuracy on Earth	Wrong beyond small areas	Within about 0.5% (sphere approximation)	Accurate on the WGS 84 spheroid
Shapes	Point, line, segment, box, path, polygon, circle	Points only	All OGC types, curves, 3D, rasters, topology
Operations	Basic overlap, containment, distance	Distance and bounding boxes	Hundreds: buffers, unions, intersections, simplification, validation
Standards and formats	PostgreSQL-specific text format	None	WKT, WKB, GeoJSON, KML, MVT, GML
Tool interoperability	Minimal	Minimal	QGIS, GDAL, GeoServer, map libraries
Install footprint	None	Contrib	Large extension plus GEOS, PROJ, GDAL

Decision guide

  Do the coordinates represent places on Earth?
   |
   +-- No  (floor plan, game, CAD, chart) ---------------------> built-in geometric types
   |
   +-- Yes
        |
        +-- Only "distance between points" or "within radius",
        |   moderate accuracy, PostGIS unavailable -------------> earthdistance (or lat/lon + bounding box)
        |
        +-- Polygons, containment, routing, projections,
            GIS file formats, or map tooling ----------------------> PostGIS

The main costs of PostGIS are operational: a large extension with native library dependencies to keep consistent across servers, and upgrades to coordinate across PostGIS, GEOS, PROJ, and PostgreSQL itself. For anything resembling GIS work, those costs are almost always worth paying; reimplementing spatial algorithms with built-in types is not.

Key takeaways

• Built-in geometric types are planar, unit-less, and dependency-free; ideal for non-geographic 2D coordinates.
• earthdistance provides spherical point distances with index support, but no shapes or projections.
• PostGIS is the right choice for real-world geography: SRIDs, accurate distances, rich operations, standard formats, and tool support.
• The trade-off is operational complexity versus correctness and capability.

🧪 Practice

1. (Beginner) Model a warehouse floor with box columns for shelves and point columns for robots, and find the shelves containing each robot.
2. (Intermediate) Compute the distance between two cities using built-in point (treating lon/lat as x/y), earthdistance, and PostGIS geography. Explain the differences in the results.
3. (Interview) "A startup stores restaurant locations as point and computes distance with <-> to show nearby places. Users in Oslo complain the results seem wrong. Explain why and what you would change." Hint: Degrees of longitude shrink with latitude, a flat-plane distance on angles, and geography-aware alternatives.

<a id="133-application-integration"></a>

13.3 Application Integration

A database is only as useful as the applications that talk to it, and most production incidents involving PostgreSQL begin in application code: a forgotten transaction, an ORM generating a thousand queries, a retry that charged a customer twice. This subchapter covers the wire protocol and drivers, ORMs and their pitfalls, query builders and raw SQL, transaction handling, failure and retry strategies, and testing against a real PostgreSQL.

Client Drivers and Protocol Basics

Every application, from psql to a Java microservice, talks to PostgreSQL over the same frontend/backend protocol: a message-based protocol over TCP (or a Unix socket), currently version 3.0, with 3.2 introduced in PostgreSQL 18. A driver is the library that implements this protocol for a programming language and maps PostgreSQL types to language types. Understanding the protocol explains many behaviors that otherwise seem mysterious: why prepared statements exist, why a "transaction is aborted" error persists, and how query cancellation works.

Analogy. The protocol is like a formal exchange of numbered forms at a counter. The client hands over a form (Parse, Bind, Execute), the server stamps and returns forms (ParseComplete, DataRow, CommandComplete), and after each batch the clerk says "ready" and shows a light indicating whether you are mid-transaction. Drivers are clerks fluent in your language who fill out the forms for you.

A connection's life

 Client (driver)                                        Server (backend process)
   |  -- StartupMessage (user, database, options) -->   |   fork a backend for this connection
   |  <-- AuthenticationSASL (SCRAM-SHA-256) ---------   |
   |  -- SASLInitialResponse / SASLResponse --------->   |
   |  <-- AuthenticationOk                               |
   |  <-- ParameterStatus (server_version, TimeZone, ...) x N
   |  <-- BackendKeyData (pid, secret key for cancel)   |
   |  <-- ReadyForQuery ('I' = idle)                     |
   |                                                     |
   |  Simple query protocol                              |
   |  -- Query("SELECT 1; SELECT 2") --------------->   |   text SQL, may contain several statements,
   |  <-- RowDescription, DataRow, CommandComplete x 2  |   no parameters, results always text
   |  <-- ReadyForQuery ('I')                            |
   |                                                     |
   |  Extended query protocol                            |
   |  -- Parse("S1", "SELECT * FROM t WHERE id = $1") -> |   SQL and parameters travel separately
   |  -- Bind("S1", params=[42], result format=binary) ->|
   |  -- Execute --------------------------------------> |
   |  -- Sync -----------------------------------------> |
   |  <-- ParseComplete, BindComplete, DataRow*, CommandComplete
   |  <-- ReadyForQuery ('I' idle | 'T' in transaction | 'E' failed transaction)

Key ideas:

• Simple vs. extended query. The extended protocol sends SQL text and parameter values separately. Parameters are never parsed as SQL, which is the structural defense against SQL injection, and a parsed statement can be reused as a prepared statement.
• Transaction status. Every ReadyForQuery carries the session's transaction state. E means an error occurred inside a transaction; every further statement fails with current transaction is aborted, commands ignored until end of transaction block until the client sends ROLLBACK.
• Text and binary formats. Values can be transferred as text ('2026-10-06') or in binary. Binary is faster for numbers, timestamps, and arrays; drivers choose per type.
• Cancellation uses a separate connection carrying the backend PID and secret key from BackendKeyData. This is why cancel can fail through some proxies, and why a timeout on the client side does not stop the server-side query unless the driver sends a cancel.
• Asynchronous messages such as NotificationResponse (for LISTEN/NOTIFY) and NoticeResponse can arrive at any time.
• COPY has its own sub-protocol for bulk data streaming.
• Pipelining (libpq pipeline mode since PostgreSQL 14) sends many queries without waiting for each result, removing network round trips from latency.

Drivers by language

Language	Common drivers	Implementation	Notes
C	libpq	Reference implementation	Basis of many other drivers; honors PG* environment variables and pg_service.conf
Python	psycopg 3, asyncpg	libpq wrapper / pure protocol	psycopg supports sync and async, COPY, pipelines; asyncpg is async-only and binary-first
Java/JVM	pgjdbc, R2DBC PostgreSQL	Pure Java	JDBC standard; prepareThreshold controls server-side preparation
Go	pgx	Pure Go	Native interface and database/sql adapter
Node.js	node-postgres (pg), postgres.js	Pure JS (optional libpq)
.NET	Npgsql	Pure C#	Rich type mapping, used by Entity Framework
Rust	tokio-postgres, sqlx	Pure Rust	sqlx checks SQL against a live database at compile time
Ruby	pg gem	libpq wrapper	Used by ActiveRecord

libpq-based drivers automatically gain libpq features (connection service files, multi-host strings, GSSAPI, new authentication methods); pure-protocol drivers implement them independently and sometimes lag behind.

Connection strings

postgresql://app_user@db1.internal:5432,db2.internal:5432/shop?sslmode=verify-full&target_session_attrs=read-write&application_name=checkout&connect_timeout=5

Parameter	Purpose
sslmode=verify-full	Encrypt and verify the server certificate and hostname (10.1)
target_session_attrs=read-write	With several hosts, connect only to the one accepting writes (the primary)
application_name	Appears in pg_stat_activity and logs; essential for diagnosis
connect_timeout	Fail fast instead of hanging on an unreachable host
options=-c statement_timeout=5s	Set server parameters for the session at connect time
keepalives_idle, tcp_user_timeout	Detect dead connections at the TCP level

Parameterized queries in practice

import psycopg

with psycopg.connect("dbname=shop application_name=reports") as conn:
    # Parameters are sent separately from the SQL text: no injection, no quoting bugs
    rows = conn.execute(
        "SELECT id, total FROM orders WHERE customer_id = %s AND created_at >= %s",
        (customer_id, since),
    ).fetchall()

    # NEVER build SQL with string formatting:
    # conn.execute(f"SELECT * FROM orders WHERE customer_id = {customer_id}")   # injection risk

    # Bulk load with COPY: orders of magnitude faster than row-by-row INSERT
    with conn.cursor().copy("COPY events (ts, kind, payload) FROM STDIN") as copy:
        for e in events:
            copy.write_row((e.ts, e.kind, e.payload))

// JDBC: PreparedStatement uses the extended protocol; ? placeholders become $1, $2
try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement(
         "SELECT id, total FROM orders WHERE customer_id = ? AND created_at >= ?")) {
    ps.setLong(1, customerId);
    ps.setObject(2, since);                       // java.time.OffsetDateTime maps to timestamptz
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            process(rs.getLong("id"), rs.getBigDecimal("total"));   // numeric -> BigDecimal, never double
        }
    }
}

Type mapping traps

PostgreSQL type	Trap	Safer mapping
numeric	Converted to binary floating point, losing cents	Decimal (Python), BigDecimal (Java), string
timestamp vs. timestamptz	Driver applies the client's time zone unexpectedly	Use timestamptz and time-zone-aware types (3.2)
bigint	JavaScript numbers lose precision above 2^53	Strings or BigInt
jsonb	Returned as a string by some drivers	Register a JSON adapter
Arrays, composite types, enums	Unsupported or returned as text by some drivers	Check driver docs; register custom types

Key takeaways

• All clients use the same message-based protocol; drivers translate it into language idioms and types.
• The extended query protocol separates SQL from parameters, preventing injection and enabling prepared statements.
• ReadyForQuery reports transaction status; after an error inside a transaction, only ROLLBACK helps.
• Set application_name, timeouts, and sslmode in every connection string.
• Map numeric, timestamptz, and bigint carefully to avoid silent data corruption in the application.

🧪 Practice

1. (Beginner) Connect with two different application_name values and find both sessions in pg_stat_activity.
2. (Intermediate) Insert 1,000,000 rows from your language of choice three ways: one INSERT per row in autocommit, one transaction with batched multi-row INSERTs, and COPY. Compare timings and explain the differences in terms of round trips and commits.
3. (Interview) "An application's HTTP request times out after 30 seconds, but DBAs see the query still running in the database minutes later. Why, and how would you fix it?" Hint: Client-side timeouts versus statement_timeout, the protocol's out-of-band cancel request, and connection reuse.

ORM Usage and Pitfalls

An object-relational mapper (ORM) maps tables to classes and rows to objects, so application code can say order.customer.name instead of writing a join. ORMs such as Hibernate/JPA, Django ORM, SQLAlchemy, ActiveRecord, Entity Framework, and Prisma remove a lot of boilerplate: CRUD operations, relationship navigation, change tracking, and schema migrations. The cost is that SQL becomes invisible, and invisible SQL is where performance and correctness problems hide.

Analogy. An ORM is like a personal assistant who runs errands for you. "Get me the customer for each of these 100 orders" is a single sentence for you, but if the assistant drives to the store 100 separate times, you only notice when everything is slow.

The N+1 query problem

The most common ORM performance bug. Loading a list and then touching a lazy relationship on each element issues one query for the list plus one per element:

# SQLAlchemy 2.x
orders = session.scalars(select(Order).order_by(Order.id.desc()).limit(100)).all()   # 1 query
for order in orders:
    print(order.customer.name)     # lazy load: SELECT ... FROM customers WHERE id = ?  (100 queries)

  SELECT ... FROM orders ORDER BY id DESC LIMIT 100;
  SELECT ... FROM customers WHERE id = 17;
  SELECT ... FROM customers WHERE id = 942;
  ... 98 more round trips: each ~0.5 ms of network + parse + execute

Fix it by declaring what you need up front:

from sqlalchemy.orm import joinedload, selectinload

orders = session.scalars(
    select(Order)
    .options(joinedload(Order.customer))          # many-to-one: one JOIN
    .options(selectinload(Order.items))           # one-to-many: one extra query WHERE order_id IN (...)
    .order_by(Order.id.desc())
    .limit(100)
).all()

# Django equivalents
Order.objects.select_related("customer").prefetch_related("items")[:100]

Other common pitfalls

Pitfall	What happens	Remedy
Over-fetching	SELECT * loads large text/jsonb columns you never use	Select specific columns (load_only, .only(), projections)
Row-by-row bulk operations	Updating 50,000 objects issues 50,000 UPDATEs	Bulk UPDATE ... WHERE, COPY, or the ORM's bulk API
Check-then-insert uniqueness	"Does this email exist? No, insert" races between concurrent requests	Unique constraint plus INSERT ... ON CONFLICT, handle the violation
Read-modify-write updates	account.balance = account.balance + 10 in Python loses concurrent updates	UPDATE ... SET balance = balance + 10, or SELECT ... FOR UPDATE
OFFSET pagination	Page 5,000 scans and discards 100,000 rows	Keyset pagination: WHERE id < :last_id ORDER BY id DESC LIMIT 20
Lazy loading outside a session	DetachedInstanceError, LazyInitializationException	Load eagerly inside the unit of work
Long-lived sessions	Transactions open across requests or user think time	Scope sessions to one request or job
Implicit COUNT(*)	Pagination widgets count huge tables on every page	Estimate, cache, or drop exact counts
Generated migrations	Auto-generated DDL takes heavy locks or rewrites tables	Review every migration (11.4)
Lowest-common-denominator SQL	No jsonb, arrays, RETURNING, CTEs, or partial indexes	Use PostgreSQL-specific dialect features or raw SQL where it matters

Making the SQL visible

You cannot fix what you cannot see. During development, log every statement:

engine = create_engine("postgresql+psycopg://app@localhost/shop", echo=True)   # SQLAlchemy prints SQL

# Hibernate
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.generate_statistics=true

In production, use database-side evidence: pg_stat_statements shows a query shape with an enormous calls count and tiny mean_exec_time, the signature of N+1:

SELECT calls, round(mean_exec_time::numeric, 3) AS mean_ms, left(query, 80) AS query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

Some teams add tests that assert a maximum number of queries per request (Django's assertNumQueries, or counting statements via a SQLAlchemy event listener).

When the ORM fits, and when it does not

Good fit	Poor fit
CRUD-heavy business applications	Reporting and analytics with aggregations and window functions
Rich domain models with relationships	Bulk data processing and ETL
Teams wanting consistent patterns and migrations	Queries that rely heavily on PostgreSQL features
Moderate data volumes per request	Hot paths where every millisecond counts

Most mature applications use both: the ORM for routine CRUD, and hand-written SQL (through the ORM's raw-SQL escape hatch or a query builder) for the 10% of queries that matter most.

Key takeaways

• ORMs remove boilerplate but hide the SQL; always know which queries your code generates.
• N+1 queries come from lazy-loaded relationships in loops; fix them with eager loading (joinedload, selectinload, select_related, prefetch_related).
• Enforce invariants with database constraints and atomic SQL, not ORM-level checks.
• Use keyset pagination, bulk operations, and column projections for large data.
• Use the ORM for routine work and drop to SQL where PostgreSQL features or performance demand it.

🧪 Practice

1. (Beginner) Turn on SQL logging in your ORM, load a list of 20 parent objects, and access a child relationship on each. Count the queries, then fix it with eager loading.
2. (Intermediate) Write a concurrent test where 20 threads register the same email address using an ORM "exists, then save" check. Show the duplicate, then fix it with a unique constraint and conflict handling.
3. (Interview) "A page that lists 50 orders takes 2 seconds in production but 80 ms on a developer laptop with the same code. The slowest individual query takes 3 ms. What is likely going on?" Hint: Number of queries times network round-trip time, lazy loading, and how to confirm it with pg_stat_statements or ORM statistics.

Query Builders and Raw SQL

Between "the ORM writes all SQL" and "concatenate strings by hand" lies a spectrum of tools. Choosing a point on that spectrum is a trade-off between control over the exact SQL and safety and convenience in composing it.

  more control, all PostgreSQL features                           more abstraction, less SQL
  <------------------------------------------------------------------------------------------->
  raw SQL with          SQL-first code           query builders            ORMs
  parameters            generation                                         
  (psycopg, pgx,        (sqlc, PgTyped,          (jOOQ, Kysely, knex,      (Hibernate, Django,
   JDBC)                 sqlx macros)             SQLAlchemy Core, Drizzle) ActiveRecord, Prisma)

Raw SQL with parameters

Raw SQL gives full access to PostgreSQL: CTEs, window functions, INSERT ... ON CONFLICT, RETURNING, jsonb operators, LATERAL. Its weaknesses are composition (building queries with optional filters) and type safety (results are untyped rows).

# Upsert with RETURNING: one round trip, atomic, impossible to express in many ORMs
row = conn.execute(
    """
    INSERT INTO inventory (sku, warehouse_id, qty)
    VALUES (%s, %s, %s)
    ON CONFLICT (sku, warehouse_id)
    DO UPDATE SET qty = inventory.qty + EXCLUDED.qty
    RETURNING qty
    """,
    (sku, warehouse_id, delta),
).fetchone()

Dynamic SQL safely

Parameters can only replace values. Table names, column names, and keywords cannot be parameters, which tempts people into string formatting. Each part of a query has its own safe technique:

Part of the query	Example	Safe technique
Values	WHERE id = 42	Bind parameters (%s, $1, ?)
Identifiers	ORDER BY created_at	Quote with the driver (psycopg.sql.Identifier), quote_ident/format('%I') in SQL, plus an allowlist
Keywords	ASC / DESC	Allowlist mapping from user input to fixed strings
Whole fragments	Optional filters	Compose from fixed fragments; never interpolate user text

from psycopg import sql

SORTABLE = {"created": "created_at", "total": "total"}      # allowlist of user-facing names
DIRECTIONS = {"asc": sql.SQL("ASC"), "desc": sql.SQL("DESC")}

def list_orders(conn, customer_id, sort="created", direction="desc", status=None):
    filters = [sql.SQL("customer_id = %s")]
    params = [customer_id]
    if status is not None:                                   # optional filter
        filters.append(sql.SQL("status = %s"))
        params.append(status)

    query = sql.SQL("SELECT id, total, status FROM orders WHERE {where} ORDER BY {col} {dir} LIMIT 50").format(
        where=sql.SQL(" AND ").join(filters),
        col=sql.Identifier(SORTABLE[sort]),                  # KeyError for anything not allowlisted
        dir=DIRECTIONS[direction],
    )
    return conn.execute(query, params).fetchall()

Query builders

A query builder represents SQL as composable objects, so optional clauses and reusable fragments are natural, while the result still maps one-to-one onto SQL you can predict. Typed builders also check column names and result types at compile time.

// Kysely (TypeScript): types come from a generated Database interface
const rows = await db
  .selectFrom("orders")
  .innerJoin("customers", "customers.id", "orders.customer_id")
  .select(["orders.id", "orders.total", "customers.name"])
  .where("orders.status", "=", status)                    // compiled to a bind parameter
  .$if(minTotal !== undefined, (qb) => qb.where("orders.total", ">=", minTotal!))
  .orderBy("orders.created_at", "desc")
  .limit(50)
  .execute();                                             // rows typed as { id: string; total: string; name: string }[]

await db
  .insertInto("inventory")
  .values({ sku, warehouse_id: warehouseId, qty: delta })
  .onConflict((oc) => oc.columns(["sku", "warehouse_id"])
    .doUpdateSet((eb) => ({ qty: eb("inventory.qty", "+", eb.ref("excluded.qty")) })))
  .execute();

// jOOQ (Java): classes generated from the live schema
var result = ctx.select(ORDERS.ID, ORDERS.TOTAL, CUSTOMERS.NAME)
    .from(ORDERS)
    .join(CUSTOMERS).on(CUSTOMERS.ID.eq(ORDERS.CUSTOMER_ID))
    .where(ORDERS.STATUS.eq(status))
    .orderBy(ORDERS.CREATED_AT.desc())
    .limit(50)
    .fetch();

SQL-first code generation

Tools such as sqlc (Go, Kotlin, Python, TypeScript), PgTyped (TypeScript), and sqlx's compile-time macros (Rust) take the opposite approach: you write plain SQL files, and the tool analyzes them against the schema to generate typed functions.

-- queries/orders.sql (input to sqlc)
-- name: GetOrdersByCustomer :many
SELECT id, total, status
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT $2;

// generated by sqlc: typed parameters and result structs
rows, err := q.GetOrdersByCustomer(ctx, db.GetOrdersByCustomerParams{CustomerID: 42, Limit: 50})

The SQL stays reviewable by anyone who knows SQL, PostgreSQL features are fully available, and schema changes that break a query fail the build instead of production.

Choosing

Criterion	Raw SQL	SQL-first codegen	Query builder	ORM
Access to PostgreSQL features	Full	Full	Most	Limited
Dynamic/optional clauses	Manual	Limited	Excellent	Good
Compile-time type safety	None	Strong	Strong (typed builders)	Varies
Predictability of generated SQL	Exact	Exact	High	Low to medium
Relationship handling, change tracking	None	None	None	Built in
Learning curve	SQL	SQL plus tool	Builder API	ORM concepts and SQL anyway

Key takeaways

• Raw SQL gives full power but needs disciplined parameterization and careful composition.
• Parameters cover values only; identifiers need quoting plus allowlists, and keywords need allowlists.
• Query builders make dynamic queries composable while keeping the SQL predictable.
• SQL-first generators (sqlc, PgTyped, sqlx) keep SQL as the source of truth and add type checking at build time.
• Mixing approaches is normal: an ORM for CRUD, a builder or raw SQL for complex or performance-critical queries.

🧪 Practice

1. (Beginner) Rewrite a query that builds ORDER BY from a request parameter with string formatting into a version that uses an allowlist and identifier quoting.
2. (Intermediate) Implement a search endpoint with five optional filters, once with a query builder and once with raw SQL composition. Compare the readability and the SQL generated for several filter combinations.
3. (Interview) "A code review flags cursor.execute(f\"SELECT * FROM {table} WHERE id = %s\", (id,)) as an injection risk, but the author says the id is parameterized. Who is right, and how would you fix it?" Hint: What bind parameters can and cannot replace, and where table comes from.

Transaction Management in Application Code

Transactions are defined in SQL, but in practice they are started, committed, and rolled back by application code, often implicitly by a framework. Most transaction bugs are not about SQL at all: they come from transactions that are too long, too short, accidentally nested, or wrapped around things a database cannot roll back.

Where transactions come from

Mechanism	Example	Default behavior
Driver autocommit	JDBC setAutoCommit(true), psycopg autocommit=True	Each statement commits by itself
Driver implicit transaction	psycopg 3 without autocommit, Python DB-API	First statement opens a transaction; you must commit()
Explicit block	with conn.transaction():	Commit on success, rollback on exception
Framework annotation	Spring @Transactional, Django atomic(), Rails transaction do	Boundaries at method or block level
ORM unit of work	SQLAlchemy Session, Hibernate Session	Changes flushed and committed together

Explicit, short, and scoped

import psycopg

def transfer(conn: psycopg.Connection, src: int, dst: int, amount: int) -> None:
    with conn.transaction():                       # BEGIN; COMMIT on success, ROLLBACK on exception
        cur = conn.execute(
            "UPDATE accounts SET balance = balance - %s WHERE id = %s AND balance >= %s",
            (amount, src, amount),                 # the condition makes the check and debit atomic
        )
        if cur.rowcount == 0:
            raise InsufficientFunds()              # exception leaves the block: ROLLBACK
        conn.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, dst))
        conn.execute(
            "INSERT INTO ledger (src, dst, amount) VALUES (%s, %s, %s)", (src, dst, amount)
        )

// Spring: the proxy opens a transaction before the method and commits after it
@Service
public class TransferService {
    @Transactional(isolation = Isolation.REPEATABLE_READ, timeout = 5)
    public void transfer(long src, long dst, BigDecimal amount) {
        accounts.debit(src, amount);
        accounts.credit(dst, amount);
        ledger.record(src, dst, amount);
    }
}

Spring's annotation-driven transactions work through proxies: a call from another method of the same class (this.transfer(...)) bypasses the proxy and runs with no transaction. Runtime exceptions trigger rollback; checked exceptions do not, unless configured with rollbackFor.

The long-transaction anti-pattern

  BEGIN
   |  UPDATE orders SET status = 'paying' ...        <-- row lock acquired
   |  call payment provider over HTTP (2-30 s)       <-- holding locks, holding a pooled connection,
   |  UPDATE orders SET status = 'paid' ...              blocking vacuum's cleanup horizon
  COMMIT

Long transactions hold row locks (blocking other writers), keep a pooled connection busy, prevent vacuum from removing dead rows across the whole database, and, if the process dies, leave an idle in transaction session until a timeout kills it. Worse, the HTTP call cannot be rolled back: if the commit fails afterwards, the customer has paid for an order the database says is unpaid.

Better structure: keep external calls outside transactions, and record intent durably.

  Tx 1: INSERT order (status = 'pending'), INSERT outbox row "charge order 123"   -- short
  Worker: read outbox, call payment provider with idempotency key "order-123"      -- no transaction held
  Tx 2: UPDATE order SET status = 'paid', mark outbox row done                     -- short

This is the transactional outbox pattern: the database change and the message announcing it commit atomically, and a separate worker delivers the message with retries and an idempotency key.

Safety nets on the server side:

ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';  -- kill sessions stuck mid-transaction
ALTER ROLE app_user SET statement_timeout = '10s';                    -- cap any single statement
ALTER ROLE app_user SET lock_timeout = '3s';                          -- fail instead of queueing forever on locks

Retrying serialization failures and deadlocks

Under REPEATABLE READ and SERIALIZABLE isolation, and with deadlocks at any level, PostgreSQL may abort a transaction that did nothing wrong: SQLSTATE 40001 (serialization failure) or 40P01 (deadlock detected). The correct response is to rerun the whole transaction, not just the failing statement:

import random, time
from psycopg import errors

def run_in_transaction(conn, work, *, attempts=5):
    for attempt in range(1, attempts + 1):
        try:
            with conn.transaction():
                return work(conn)                          # the entire unit of work is re-executed
        except (errors.SerializationFailure, errors.DeadlockDetected):
            if attempt == attempts:
                raise
            time.sleep(min(0.05 * 2 ** attempt, 1.0) * random.random())   # backoff with jitter

conn = psycopg.connect("dbname=shop", autocommit=True)       # transaction() blocks issue BEGIN/COMMIT
conn.isolation_level = psycopg.IsolationLevel.SERIALIZABLE    # applied to every BEGIN psycopg sends
run_in_transaction(conn, lambda c: transfer_logic(c, 1, 2, 100))

The work function must have no side effects outside the database (no emails, no HTTP calls) because it may run several times.

Transactions and connection poolers

With PgBouncer in transaction mode, consecutive transactions from one client may run on different server connections. Session state does not survive between transactions:

Works in transaction pooling	Breaks or misbehaves
SET LOCAL inside a transaction	SET outside a transaction (applies to some other client's connection later)
Advisory locks with pg_advisory_xact_lock	Session advisory locks pg_advisory_lock
Protocol-level prepared statements (PgBouncer 1.21+ with max_prepared_statements)	SQL-level PREPARE / EXECUTE
Temporary tables created and dropped in one transaction	Temporary tables kept across transactions; LISTEN

Key takeaways

• Make transaction boundaries explicit and short; know whether your driver and framework autocommit.
• Never perform irreversible external actions inside a database transaction; use an outbox and idempotency keys.
• Set idle_in_transaction_session_timeout, statement_timeout, and lock_timeout as safety nets.
• Retry the entire transaction on 40001 and 40P01, with backoff and jitter, keeping the transaction body free of external side effects.
• Under transaction-mode pooling, rely only on transaction-scoped state.

🧪 Practice

1. (Beginner) In your language, write a function that inserts an order and its items in one transaction, and verify that an exception after the first insert leaves no rows behind.
2. (Intermediate) Run two concurrent "transfer" transactions under SERIALIZABLE that read and write overlapping rows. Trigger a serialization failure, then add the retry wrapper and confirm both eventually succeed with correct balances.
3. (Interview) "Our checkout service occasionally charges customers twice, and we see idle in transaction sessions piling up during payment provider outages. Explain how these might be related and how you would redesign the flow." Hint: External calls inside transactions, retries of non-idempotent operations, the outbox pattern, and idempotency keys.

Handling Connection Failures and Retries

Networks drop packets, servers restart for upgrades, primaries fail over to standbys, and poolers run out of connections. An application that treats every database error as fatal will have outages whenever the database has a hiccup; one that retries everything blindly can duplicate payments or amplify an overload into a full outage. Robust code distinguishes which failures are transient, which operations are safe to retry, and how to retry without making things worse.

Classifying errors by SQLSTATE

Every PostgreSQL error carries a five-character SQLSTATE code; the first two characters are the class. Drivers expose it (e.sqlstate in psycopg, SQLException.getSQLState() in JDBC, pgconn.PgError.Code in pgx).

SQLSTATE	Name	Meaning	Retry?
08xxx	connection exception	Could not connect, or connection lost	Yes, with care about ambiguous commits
57P01	admin_shutdown	Server shutting down (restart, failover)	Yes, on a new connection
57P03	cannot_connect_now	Server starting up or in recovery	Yes, after a delay
53300	too_many_connections	max_connections reached	Yes, with backoff (and fix pool sizing)
40001	serialization_failure	Concurrent transaction conflict	Yes, rerun the whole transaction
40P01	deadlock_detected	Deadlock victim	Yes, rerun the whole transaction
55P03	lock_not_available	lock_timeout or NOWAIT	Usually, with backoff
57014	query_canceled	statement_timeout or cancel	Rarely: the query is probably too slow
25006	read_only_sql_transaction	Write sent to a standby (often a stale connection after failover)	Yes, after reconnecting to the primary
23xxx	integrity constraint violation	Unique, foreign key, check violations	No: a logic or data problem
42xxx	syntax error or access rule violation	Bad SQL, missing table, permission denied	No: a bug or deployment problem

The ambiguous commit

The hardest case is a connection lost while committing:

  App                         Network                  Server
   |  COMMIT ------------------------------------------> |  commit succeeds, WAL flushed
   |                          X connection reset         |
   |  <-- error: connection lost (08006)                  |
   |
   Did the transaction commit? The application cannot know.

Blindly retrying a non-idempotent transaction (inserting a payment, incrementing a counter) can apply it twice. Make retried operations idempotent:

-- The client generates a unique key per logical operation and reuses it on retries
CREATE TABLE payments (
    idempotency_key uuid PRIMARY KEY,
    order_id        bigint NOT NULL,
    amount          numeric(12,2) NOT NULL,
    created_at      timestamptz NOT NULL DEFAULT now()
);

INSERT INTO payments (idempotency_key, order_id, amount)
VALUES ($1, $2, $3)
ON CONFLICT (idempotency_key) DO NOTHING;   -- a retry after an ambiguous commit becomes a no-op

Other techniques: conditional updates (UPDATE ... SET status = 'shipped' WHERE status = 'packed'), absolute assignments instead of increments, and checking for the result before retrying.

A retry policy

import random, time
import psycopg
from psycopg import errors

TRANSIENT = {"57P01", "57P03", "53300", "40001", "40P01", "55P03", "25006"}

def is_transient(exc: Exception) -> bool:
    state = getattr(exc, "sqlstate", None)
    if state is None:
        return isinstance(exc, psycopg.OperationalError)   # connection-level failure, no server response
    return state in TRANSIENT or state.startswith("08")

def with_retries(pool, work, *, max_attempts=5, base=0.1, cap=5.0, deadline=15.0):
    start = time.monotonic()
    for attempt in range(1, max_attempts + 1):
        try:
            with pool.connection() as conn:            # fresh connection from the pool each attempt
                with conn.transaction():
                    return work(conn)                  # must be idempotent
        except Exception as exc:
            if not is_transient(exc) or attempt == max_attempts:
                raise
            sleep = random.uniform(0, min(cap, base * 2 ** attempt))   # exponential backoff, full jitter
            if time.monotonic() + sleep - start > deadline:
                raise                                  # respect the caller's overall time budget
            time.sleep(sleep)

Why jitter? If a failover disconnects 2,000 clients at once and they all retry after exactly 100 ms, 200 ms, 400 ms, they arrive at the new primary in synchronized waves. Random jitter spreads them out.

Timeouts at every layer

A failure you never detect is worse than one that errors quickly. A dead primary may simply stop responding, leaving clients waiting on TCP for minutes.

Layer	Setting	Purpose
Connect	connect_timeout=5	Give up on unreachable hosts
TCP	keepalives_idle=30, tcp_user_timeout=10000 (ms)	Detect peers that vanished without closing the connection
Statement	statement_timeout	Bound server-side work
Lock wait	lock_timeout	Do not queue indefinitely behind DDL or long transactions
Pool checkout	connectionTimeout (HikariCP), timeout (psycopg_pool)	Fail fast when the pool is exhausted
Request	Application deadline	Overall budget; retries must fit within it

Pool hygiene

• Validate or expire connections. Pools should test connections before reuse or after idle periods, and recycle them after a maximum lifetime (HikariCP maxLifetime) shorter than any proxy, load balancer, or firewall idle timeout.
• Drain on failover. After a failover, existing connections to the old primary are broken or point at a demoted server (25006 on writes). Discard connections that report these errors instead of returning them to the pool.
• Bound the pool. A pool that grows without limit under failure turns a slow database into one with too many connections (12.4).

Failover-aware connection strings

postgresql://app@db1,db2,db3/shop?target_session_attrs=read-write&connect_timeout=3

libpq and drivers that implement multi-host strings try each host and keep only a server that accepts writes, which handles failover without DNS changes (12.3). With a virtual IP or a DNS name, keep the DNS TTL short and make sure the client actually re-resolves.

Circuit breakers

When the database is truly down, retries only add load and latency. A circuit breaker stops calling the database after a threshold of consecutive failures, fails requests immediately for a cooldown period, then lets a few probes through to test recovery. This protects both the database (no thundering herd on recovery) and the application (threads are not tied up waiting).

   CLOSED --(failures exceed threshold)--> OPEN --(cooldown elapsed)--> HALF-OPEN
     ^                                                                      |
     +-----------------------(probe succeeds)-------------------------------+
                              (probe fails) --> back to OPEN

Key takeaways

• Classify errors by SQLSTATE: connection, shutdown, serialization, deadlock, and lock-timeout errors are transient; constraint and syntax errors are not.
• A connection lost during COMMIT leaves the outcome unknown; only idempotent operations are safe to retry blindly.
• Retry with exponential backoff, jitter, a maximum attempt count, and an overall deadline, on a fresh connection.
• Configure timeouts at every layer, from TCP keepalives to statement and lock timeouts.
• Keep pools bounded and self-healing, use failover-aware connection strings, and add circuit breakers for prolonged outages.

🧪 Practice

1. (Beginner) Write code that catches a database error and prints its SQLSTATE. Trigger a unique violation, a syntax error, and a statement_timeout (SET statement_timeout = '100ms'; SELECT pg_sleep(1);).
2. (Intermediate) Run a load generator against PostgreSQL through your retry wrapper, then restart the server mid-run (pg_ctl restart -m fast). Measure failed requests and recovery time with and without retries, and with and without jitter.
3. (Interview) "During a planned failover that took 15 seconds, our API returned errors for 4 minutes afterwards. What could cause the long tail, and what would you change?" Hint: Stale pooled connections, DNS caching, missing TCP timeouts, synchronized retries, and connections pinned to the demoted primary.

Testing Against Postgres

Tests that run against an in-memory substitute (SQLite, H2, mocks) pass happily while production fails, because the substitute has different types, locking, constraint behavior, SQL dialect, and error codes. A jsonb operator, a partial index, an ON CONFLICT clause, or a serialization failure simply does not exist there. Modern tooling makes it cheap to test against a real PostgreSQL of the same major version as production, so there is little reason not to.

What to test at which level

                         /\
                        /  \     End-to-end: app + DB + dependencies, few and slow
                       /----\
                      /      \   Integration: repositories, queries, migrations,
                     /        \  transactions against real PostgreSQL (the core of DB testing)
                    /----------\
                   /            \  Database unit tests: functions, triggers, constraints,
                  /              \ RLS policies (pgTAP)
                 /----------------\
                /                  \  Pure unit tests: business logic with no database
               /--------------------\

Disposable databases with Testcontainers

Testcontainers starts a real PostgreSQL container for the test run and tears it down afterwards:

# conftest.py (pytest)
import pytest, psycopg
from testcontainers.postgres import PostgresContainer

@pytest.fixture(scope="session")
def pg_url():
    with PostgresContainer("postgres:17") as pg:                 # match the production major version
        url = pg.get_connection_url(driver=None)                 # plain postgresql:// URL
        run_migrations(url)                                      # same migration tool as production
        yield url

@pytest.fixture
def conn(pg_url):
    conn = psycopg.connect(pg_url)                               # autocommit off: first statement opens a transaction
    try:
        yield conn                                               # the test runs inside that transaction
    finally:
        conn.rollback()                                          # undo everything: fast isolation
        conn.close()

// JUnit 5 + Testcontainers
@Testcontainers
class OrderRepositoryTest {
    @Container
    static PostgreSQLContainer<?> pg = new PostgreSQLContainer<>("postgres:17");

    @Test
    void rejectsNegativeTotals() {
        var repo = new OrderRepository(dataSourceFor(pg));
        assertThrows(DataIntegrityViolationException.class,
                     () -> repo.save(new Order(1L, new BigDecimal("-5.00"))));
    }
}

Isolation strategies between tests

Strategy	How	Speed	Limitations
Transaction rollback	Each test runs inside a transaction that is rolled back	Fastest	Cannot test commits, multiple connections, or code that manages its own transactions (savepoints help partially)
Truncate after each test	TRUNCATE ... RESTART IDENTITY CASCADE on all tables	Fast	Tests cannot run in parallel on one database
Template database per test	CREATE DATABASE test_123 TEMPLATE app_template	Fast (file copy), parallel-friendly	Template must have no active connections
Container per test class	New container each time	Slow	Strongest isolation

Template databases are an underused PostgreSQL feature for parallel tests: migrate once into a template, then clone it per test or per worker:

-- once per test run
CREATE DATABASE app_template;
-- ... run migrations and load reference data into app_template ...

-- per test (or per parallel worker); fast because files are copied, not replayed
CREATE DATABASE test_worker_3 TEMPLATE app_template;
-- ... run tests ...
DROP DATABASE test_worker_3 WITH (FORCE);      -- PG 13+: disconnects lingering sessions

Testing database logic with pgTAP

pgTAP is an extension for writing tests in SQL, ideal for constraints, functions, triggers, and row-level security:

-- tests/orders_test.sql, run with: pg_prove -d app_test tests/*.sql
BEGIN;
SELECT plan(4);

SELECT has_table('public', 'orders', 'orders table exists');
SELECT col_not_null('public', 'orders', 'customer_id', 'customer_id is required');

SELECT throws_ok(
    $$INSERT INTO orders (customer_id, total) VALUES (1, -5)$$,
    '23514',                                   -- check_violation
    NULL,
    'negative totals are rejected'
);

-- Row-level security: tenant 2 must not see tenant 1's rows
SET LOCAL app.tenant_id = '2';
SET LOCAL ROLE app_user;
SELECT is_empty(
    $$SELECT * FROM orders WHERE tenant_id = 1$$,
    'RLS hides other tenants'
);

SELECT * FROM finish();
ROLLBACK;

Testing migrations

Migrations are code that runs once, in production, against real data, which makes them the riskiest code to leave untested:

• Apply all migrations to an empty database in CI (catches syntax and ordering errors).
• Apply the new migrations to a snapshot of the previous release's schema with realistic data (catches failures on existing rows, such as adding a NOT NULL column without a default).
• Check that the resulting schema matches a fresh install (compare pg_dump --schema-only output).
• For large tables, test lock behavior and duration on production-sized copies (11.4).

Testing concurrency

Race conditions only appear with more than one connection, so single-connection tests cannot find them. Use two connections and control the interleaving explicitly:

def test_concurrent_seat_booking_allows_only_one(pg_url):
    a, b = psycopg.connect(pg_url), psycopg.connect(pg_url)          # each opens its own transaction implicitly
    a.execute("SELECT * FROM seats WHERE id = 7 FOR UPDATE")          # A locks the seat
    with pytest.raises(psycopg.errors.LockNotAvailable):
        b.execute("SELECT * FROM seats WHERE id = 7 FOR UPDATE NOWAIT")  # B must not get it
    a.commit(); b.rollback()

Making test databases fast

A test database holds disposable data, so durability can be traded for speed. Never use these settings on a server with data you care about:

# postgresql.conf for throwaway test instances only
fsync = off                    # do not force writes to disk
synchronous_commit = off       # do not wait for WAL flush at commit
full_page_writes = off         # skip torn-page protection

# With Docker, the same via command-line flags, plus a RAM-backed data directory
docker run --rm -e POSTGRES_PASSWORD=test --tmpfs /var/lib/postgresql/data \
  postgres:17 -c fsync=off -c synchronous_commit=off -c full_page_writes=off

Key takeaways

• Test against real PostgreSQL of the production major version; substitutes hide dialect, type, locking, and error-code differences.
• Testcontainers or a CI service container makes disposable databases cheap; run real migrations to build the schema.
• Isolate tests with rollback, truncation, or template databases; templates enable fast parallel tests.
• Use pgTAP for constraints, functions, triggers, and RLS, and test migrations against realistic previous-release data.
• Test concurrency with multiple connections, and speed up test instances by disabling durability settings.

🧪 Practice

1. (Beginner) Set up a pytest or JUnit fixture that starts PostgreSQL with Testcontainers, runs your migrations, and wraps each test in a rolled-back transaction.
2. (Intermediate) Write pgTAP tests for a table with a check constraint, a trigger that maintains an updated_at column, and an RLS policy. Run them with pg_prove in CI.
3. (Interview) "Our tests use H2 in PostgreSQL compatibility mode and all pass, yet the release failed in production with a SQL error. Make the case for changing the testing approach." Hint: Dialect gaps (jsonb, ON CONFLICT, arrays), different constraint and locking semantics, SQLSTATE codes, and the low cost of containers.

<a id="134-postgres-in-production-environments"></a>

13.4 Postgres in Production Environments

Running PostgreSQL in production means deciding who operates it, where it runs, how its configuration is managed, what it costs, and how you will know when it is unhealthy. This subchapter compares self-hosting with managed services, examines what cloud offerings restrict, introduces Kubernetes operators and infrastructure as code for databases, and covers cost planning and observability integration.

Self-Hosted vs. Managed Services

Every PostgreSQL deployment has the same list of chores: provisioning hardware, installing and patching, configuring, backing up and testing restores, setting up replication and failover, monitoring, upgrading major versions, and responding at 3 a.m. The real question behind "self-hosted or managed?" is who does each chore, and how much control you give up in exchange.

Analogy. Self-hosting is owning a house: you choose every fixture, but you also fix the boiler. A managed service is renting a serviced apartment: the landlord fixes the boiler, but you cannot knock down walls, and the rent includes their margin.

The responsibility split

Task	Self-hosted (VMs or bare metal)	Managed service (RDS, Cloud SQL, Azure, etc.)
Hardware, OS, storage	You	Provider
PostgreSQL installation and minor patches	You	Provider (in maintenance windows)
Backups and point-in-time recovery	You build and test (11.3)	Provider runs them; you still test restores
High availability and failover	You build (12.3)	A checkbox (Multi-AZ, regional HA)
Major version upgrades	You plan and run	Provider tooling; you still plan, test, and schedule
Configuration tuning	Full control	Parameter groups with restricted settings
Extensions	Anything you can compile	Provider's allowlist
Schema design, queries, indexes	You	You
Roles, privileges, data security	You	You (provider secures the platform)
Capacity planning	You	You (choose instance size and storage)
Performance troubleshooting	You, with full access	You, with limited access (no OS, no superuser)

The bottom rows are the part teams forget: a managed service does not design your schema, fix your slow queries, or stop your application from opening 5,000 connections.

Where each option shines

Choose self-hosted when	Choose managed when
You need extensions, configuration, or versions a provider does not offer	Your team has little or no dedicated database operations expertise
You need superuser, OS access, or custom storage layouts	Time-to-market matters more than fine control
Very large or steady workloads where provider markup dominates cost	Workloads are small to medium, or vary over time
Data residency or air-gapped requirements rule out public cloud	You want HA, backups, and patching to be a configuration choice
You already run reliable stateful infrastructure (or Kubernetes operators)	Compliance certifications from the provider simplify audits

Hidden costs on both sides

• Self-hosted: engineer time for on-call, failover testing, backup verification, and upgrades; the risk that a lone expert leaves; the long tail of rare failures (corruption, disk full, split brain) that you must be ready to handle.
• Managed: per-hour markup over raw compute, storage and IOPS charges, cross-zone and egress transfer fees, limits that force workarounds, and lock-in to provider-specific features and tooling.

A middle path

Kubernetes operators (covered later in this subchapter) and automation tools such as Patroni with Ansible make self-hosting closer to managed: failover, backups, and replicas are declared rather than hand-built. Some vendors also offer "bring your own cloud" models, where they operate PostgreSQL inside your cloud account.

A decision checklist

1. Which extensions, versions, and settings do you need? Check them against provider allowlists first; this often decides the matter.
2. What recovery point and recovery time objectives (RPO/RTO) must you meet, and can the option meet them?
3. Who will be on call for the database, and do they have the skills?
4. What is the three-year total cost, including people, not only invoices?
5. How hard would it be to leave? Can you take a physical backup out, or only a logical dump?

Key takeaways

• Self-hosted versus managed is a question of who performs each operational task and how much control you retain.
• Managed services handle infrastructure, backups, patching, and HA, but schema design, query performance, security, and capacity remain your responsibility.
• Extension and configuration requirements are frequently the deciding factor.
• Compare total cost of ownership, including engineering time, on-call burden, and exit costs.

🧪 Practice

1. (Beginner) List every extension and non-default setting your application uses, and check whether two managed providers support them all.
2. (Intermediate) Estimate a three-year cost for a 16-vCPU, 128 GB, 2 TB database with one standby, self-hosted on VMs versus a managed service. Include an estimate of engineering hours for each.
3. (Interview) "Our CTO says moving to a managed database means we no longer need anyone who understands PostgreSQL. How would you respond?" Hint: The responsibility split, schema and query performance, connection management, upgrades that still need planning, and incident troubleshooting with limited access.

Cloud Offerings and Their Limitations

Managed PostgreSQL services look alike from a distance, but each changes PostgreSQL in ways that matter: what you may install, which settings you may change, how replication and upgrades work, and how you are billed. Some offerings run unmodified PostgreSQL on cloud VMs; others replace the storage layer entirely. Knowing these differences before choosing avoids painful surprises after migrating.

Two architectural families

  "Managed vanilla" (RDS for PostgreSQL, Cloud SQL,            "Disaggregated storage" (Aurora, AlloyDB, Neon)
   Azure Flexible Server, Crunchy Bridge, Aiven ...)
  +------------------+    WAL     +------------------+         +-----------+   +-----------+   +-----------+
  | primary VM       | ---------> | standby VM       |         | writer    |   | reader    |   | reader    |
  | PostgreSQL       |  streaming | PostgreSQL       |         | compute   |   | compute   |   | compute   |
  | local/block disk |            | own disk         |         +-----+-----+   +-----+-----+   +-----+-----+
  +------------------+            +------------------+               |  WAL / page requests          |
                                                                +----v-------------------------------v----+
                                                                |  shared, replicated storage service     |
                                                                |  (multiple copies across zones)         |
                                                                +-----------------------------------------+
  Behaves like self-hosted PostgreSQL;                          Fast failover and replica creation, storage
  standard tools and knowledge apply directly                   auto-grows; I/O, vacuum, and pricing behave
                                                                differently from vanilla PostgreSQL

The main offerings

Offering	Architecture	Notable characteristics
Amazon RDS for PostgreSQL	Vanilla on EC2 + EBS	Multi-AZ standbys, read replicas, Blue/Green deployments for upgrades, pg_tle for custom extensions
Amazon Aurora PostgreSQL-Compatible	Disaggregated storage	Up to 15 replicas sharing storage, fast failover, Serverless v2, I/O-Optimized vs. Standard pricing
Google Cloud SQL for PostgreSQL	Vanilla on GCE + persistent disk	Enterprise and Enterprise Plus editions, regional HA, IAM database authentication
Google AlloyDB	Disaggregated storage	Columnar engine for analytics, aggressive caching, also available as AlloyDB Omni for self-hosting
Azure Database for PostgreSQL (Flexible Server)	Vanilla on Azure VMs + managed disks	Zone-redundant HA, extensions enabled via azure.extensions
Neon	Disaggregated storage, serverless	Scale to zero, copy-on-write branches for development and previews
Supabase	Vanilla PostgreSQL plus platform	Bundled auth, REST/GraphQL APIs, realtime, storage; broad extension set
Crunchy Bridge, Aiven, others	Vanilla, multi-cloud	Closer to stock PostgreSQL; varying extension and access policies

Features and limits change frequently; treat the table as a map of the landscape and verify details against current provider documentation.

Common limitations

1. No real superuser. You get a privileged role (rds_superuser, cloudsqlsuperuser, azure_pg_admin) that cannot read server files, load arbitrary C code, change some settings, or use untrusted languages such as plpython3u.
2. Extension allowlists. Only provider-approved extensions and versions are available, sometimes lagging upstream releases. Custom C extensions are impossible; pg_tle (on RDS/Aurora) allows trusted-language extensions only.
3. Restricted configuration. Settings are changed through parameter groups or flags; some (memory sizes tied to instance class, wal_level, file locations) are fixed or require specific workflows.
4. No file system or OS access. No COPY ... FROM '/file' on the server, no pg_basebackup out of the service, no custom archive_command, no tools like perf for profiling.
5. Replication constraints. You usually cannot run a physical standby outside the provider; leaving the service means a logical dump or logical replication (12.2). Logical replication often needs a provider-specific flag (for example rds.logical_replication = 1).
6. Storage and I/O limits. Throughput and IOPS are tied to volume type, size, or instance class; some volume types use burst credits that run out under sustained load, causing sudden latency cliffs.
7. Maintenance windows and forced upgrades. Minor versions are applied on the provider's schedule; versions reaching end of life are upgraded automatically or moved to paid extended support.
8. Version lag. New PostgreSQL major versions may arrive weeks to months after upstream release.
9. Disaggregated-storage differences. Aurora and similar systems have no WAL-based standbys in the usual sense, different checkpoint and vacuum I/O behavior, and I/O-based pricing that can make a missing index or bloated table expensive in money, not just time.
10. Serverless trade-offs. Scale-to-zero services incur cold-start latency on the first connection after idleness, and autoscaling has ceilings and step delays.

Finding out what you can do

-- Who am I, and how privileged?
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb
FROM pg_roles WHERE rolname = current_user;

SELECT r.rolname AS granted_role
FROM pg_auth_members m JOIN pg_roles r ON r.oid = m.roleid
WHERE m.member = current_user::regrole;

-- Which extensions does this service offer?
SELECT name, default_version FROM pg_available_extensions ORDER BY name;
SHOW rds.extensions;                 -- RDS: the provider's allowlist

-- Which settings can I change, and how?
SELECT name, setting, context FROM pg_settings
WHERE name IN ('shared_buffers', 'wal_level', 'max_connections', 'shared_preload_libraries');

Mitigating lock-in

• Stick to standard PostgreSQL features in application code; isolate provider-specific APIs (IAM authentication, Data API) behind small adapters.
• Keep a regularly tested logical export path (pg_dump, or logical replication to an external server).
• Manage configuration as code so it can be reproduced on another platform (Infrastructure as Code for Databases).
• Track which extensions you depend on and whether alternative platforms support them.

Key takeaways

• Managed offerings fall into vanilla-PostgreSQL services and disaggregated-storage services; the latter change I/O, replication, and pricing behavior.
• Expect no superuser, extension allowlists, restricted settings, and no file system access.
• Leaving a provider usually requires logical dump or logical replication; plan the exit path in advance.
• Storage throughput limits, burst credits, and I/O pricing can turn performance problems into cost problems.
• Verify extension, version, and setting availability before committing to a provider.

🧪 Practice

1. (Beginner) On any managed PostgreSQL instance (many providers offer free tiers), run the discovery queries above and list three things you can do on a self-hosted server but not there.
2. (Intermediate) Design a migration from a managed service to another provider with under five minutes of downtime, using logical replication. List the provider-specific settings you need on each side and the steps for sequences and cut-over.
3. (Interview) "After moving from RDS to Aurora, our monthly bill doubled even though instance sizes are the same. Where would you look?" Hint: I/O-based storage pricing, full table scans and bloat, missing indexes, vacuum activity, and the I/O-Optimized pricing model.

Kubernetes Operators for Postgres

Kubernetes was designed for stateless containers that can be killed and replaced at will. A database is the opposite: it has durable state, a single writable primary, replicas that must follow it, backups, and failover logic that must never produce two primaries. Running PostgreSQL in a plain StatefulSet gives you none of that. An operator fills the gap: a controller running inside the cluster that encodes a database administrator's operational knowledge and continuously drives the actual state of the database toward a declared desired state.

Analogy. A StatefulSet is a building with numbered apartments: it guarantees each tenant keeps their apartment and mailbox, but it does not know that one tenant is the landlord. An operator is a property manager who knows who the landlord is, appoints a new one if the current one disappears, makes sure everyone has copies of the important documents, and renovates the building one floor at a time.

How an operator works

   you apply YAML                    operator (controller loop)                     Kubernetes objects
  +------------------+    watch    +-------------------------------+   create/   +------------------------+
  | kind: Cluster    | ----------> | observe actual state          |   update    | Pods (1 primary,       |
  | instances: 3     |             | compare with desired state    | ----------> |   2 replicas)          |
  | storage: 100Gi   |             | act: create pods, configure   |             | PVCs (data, WAL)       |
  | backup: s3://... |             |   replication, promote,       |             | Services: -rw, -ro, -r |
  +------------------+             |   take backups, roll upgrades |             | Secrets (credentials)  |
     Custom Resource               +-------------------------------+             | Scheduled backups      |
                                          ^            |                         +------------------------+
                                          +-- status --+   (repeats forever: "reconciliation")

The custom resource definition (CRD) adds a new object type such as Cluster; the operator watches those objects and performs the corresponding operations: bootstrapping, replica creation, failover, switchover, rolling minor upgrades, backups, and restores.

The main operators

Operator	HA mechanism	Backup tooling	Notes
CloudNativePG	Built-in, uses the Kubernetes API for leader state (no Patroni)	Barman Cloud to object storage, volume snapshots	CNCF project; designed Kubernetes-first
Crunchy Postgres for Kubernetes (PGO)	Patroni	pgBackRest	Mature, widely deployed
Zalando postgres-operator	Patroni (Spilo image)	WAL-G	Long history in production at Zalando
Percona Operator for PostgreSQL	Patroni	pgBackRest	Based on PGO, Percona distribution
StackGres	Patroni	WAL-G	Web console, integrated pooling and monitoring

A CloudNativePG cluster

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: shop-db
spec:
  instances: 3                                   # 1 primary + 2 streaming replicas
  imageName: ghcr.io/cloudnative-pg/postgresql:17.5
  primaryUpdateStrategy: unsupervised            # switch over automatically during rolling updates

  storage:
    size: 200Gi
    storageClass: fast-ssd                       # the most important performance decision
  walStorage:
    size: 50Gi                                   # separate volume for WAL

  postgresql:
    parameters:
      shared_buffers: "4GB"
      max_connections: "200"
      log_min_duration_statement: "500ms"

  resources:
    requests: { cpu: "4", memory: "16Gi" }
    limits:   { memory: "16Gi" }                 # equal request and limit: predictable, avoids eviction

  affinity:
    enablePodAntiAffinity: true
    topologyKey: topology.kubernetes.io/zone     # spread instances across availability zones

  backup:
    retentionPolicy: "30d"
    barmanObjectStore:                           # newer releases move this to the Barman Cloud plugin
      destinationPath: s3://shop-db-backups/
      s3Credentials:
        accessKeyId:     { name: backup-creds, key: ACCESS_KEY_ID }
        secretAccessKey: { name: backup-creds, key: SECRET_ACCESS_KEY }
---
apiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata:
  name: shop-db-daily
spec:
  schedule: "0 0 3 * * *"                        # six-field cron (with seconds): 03:00 daily
  cluster:
    name: shop-db

The operator creates services named shop-db-rw (always the primary), shop-db-ro (replicas only), and shop-db-r (any instance). Applications connect to shop-db-rw; after a failover, the service points to the new primary.

kubectl apply -f shop-db.yaml
kubectl cnpg status shop-db                  # plugin: topology, replication lag, backups
kubectl cnpg promote shop-db shop-db-2       # planned switchover to a specific instance
kubectl cnpg psql shop-db                    # open psql on the primary

What still needs careful thought

Concern	Why it matters	Guidance
Storage class	Network block storage adds latency; local disks are fast but tied to a node	Benchmark; local NVMe with replicas for resilience is common for demanding workloads
Memory limits	Exceeding the limit triggers the OOM killer, which kills a backend and forces crash recovery	Size shared_buffers, work_mem, and connections within the limit; consider huge pages
CPU limits	CFS throttling causes latency spikes	Often set CPU requests but no CPU limit, or use guaranteed QoS with static CPU policy
Pod disruption	Node drains during cluster upgrades evict database pods	Operators install PodDisruptionBudgets; drain one node at a time
Zone spread	All instances in one zone means one outage takes all	Anti-affinity on the zone topology key
Backups	Volumes are not backups; a deleted namespace can delete PVCs	Object-store backups and WAL archiving, tested restores into a new cluster
Major upgrades	Operators differ in support (in-place pg_upgrade, import, logical replication)	Read the operator's upgrade documentation and rehearse
Team skills	Debugging now spans Kubernetes, storage, networking, and PostgreSQL	Do not adopt operators without Kubernetes operational experience

Key takeaways

• Operators encode database operations (bootstrap, replication, failover, backups, upgrades) in a controller that reconciles declared state.
• CloudNativePG, Crunchy PGO, Zalando, Percona, and StackGres differ mainly in HA mechanism (native versus Patroni) and backup tooling.
• Applications connect through operator-managed services (-rw, -ro) that follow the primary across failovers.
• Storage choice, memory limits, CPU throttling, zone spread, and tested object-store backups decide whether it works well in production.
• Operators reduce toil but add Kubernetes complexity; they suit teams already fluent in running stateful workloads on Kubernetes.

🧪 Practice

1. (Beginner) Install CloudNativePG on a local cluster (kind or minikube), create a three-instance cluster, and connect to the -rw service with psql.
2. (Intermediate) While running pgbench against the -rw service, delete the primary pod. Measure how long writes fail, identify the new primary, and check that the old pod rejoins as a replica.
3. (Interview) "Our PostgreSQL pods on Kubernetes keep getting restarted under load, and logs show 'server process was terminated by signal 9'. What is happening and how would you fix it?" Hint: Container memory limits and the OOM killer, per-connection memory (work_mem times operations times connections), shared_buffers sizing, and connection pooling.

Infrastructure as Code for Databases

A database configured by hand through a console or an SSH session is a snowflake: nobody knows exactly how it was set up, staging differs from production in ways nobody can list, and rebuilding it after a disaster depends on someone's memory. Infrastructure as code (IaC) describes the database infrastructure in version-controlled files that tools apply automatically, so environments are reproducible, changes are reviewed like code, and drift is detectable.

Layers of a database deployment

Different layers change at different rates and are owned by different tools:

  +-------------------------------------------------------------------------------------+
  | 5. Data                     backups, restores, seed and reference data             |  backup tooling
  +-------------------------------------------------------------------------------------+
  | 4. Schema                   tables, indexes, functions, migrations                 |  migration tool (11.4)
  +-------------------------------------------------------------------------------------+
  | 3. Database objects         databases, roles, grants, extensions, default privileges|  Terraform postgresql
  |                                                                                     |  provider, Ansible, SQL
  +-------------------------------------------------------------------------------------+
  | 2. Server configuration     parameters, pg_hba, preloaded libraries, replication   |  parameter groups, Ansible,
  |                                                                                     |  operator Cluster spec
  +-------------------------------------------------------------------------------------+
  | 1. Infrastructure           instances, storage, networks, security groups, DNS     |  Terraform / OpenTofu,
  |                                                                                     |  Pulumi, CloudFormation
  +-------------------------------------------------------------------------------------+

Keeping schema migrations (layer 4) out of infrastructure tools is a deliberate choice: migrations are ordered, data-aware, and released with application code, while infrastructure changes are declarative and slower-moving.

Provisioning a managed instance with Terraform

resource "aws_db_parameter_group" "app" {
  name   = "app-pg17"
  family = "postgres17"

  parameter {
    name  = "log_min_duration_statement"
    value = "500"                                   # ms
  }
  parameter {
    name         = "shared_preload_libraries"
    value        = "pg_stat_statements,auto_explain"
    apply_method = "pending-reboot"                 # static parameter: applied at next reboot
  }
}

resource "aws_db_instance" "app" {
  identifier                   = "app-prod"
  engine                       = "postgres"
  engine_version               = "17.5"
  instance_class               = "db.r7g.xlarge"
  storage_type                 = "gp3"
  allocated_storage            = 200
  max_allocated_storage        = 1000              # storage autoscaling ceiling
  multi_az                     = true
  db_name                      = "app"
  username                     = "app_admin"
  manage_master_user_password  = true              # generated and stored in Secrets Manager, not in state
  parameter_group_name         = aws_db_parameter_group.app.name
  backup_retention_period      = 14
  storage_encrypted            = true
  deletion_protection          = true              # API refuses deletion
  auto_minor_version_upgrade   = true
  allow_major_version_upgrade  = false             # major upgrades are planned events, not side effects
  apply_immediately            = false             # defer disruptive changes to the maintenance window

  lifecycle {
    prevent_destroy = true                         # Terraform refuses plans that destroy this resource
  }
}

Managing roles and grants

# cyrilgdn/postgresql provider: connects to the database itself
resource "postgresql_role" "app_rw" {
  name     = "app_rw"
  login    = true
  password = var.app_rw_password          # stored in Terraform state: protect state, or prefer IAM auth
}

resource "postgresql_extension" "pg_trgm" {
  name     = "pg_trgm"
  database = "app"
  schema   = "extensions"
}

resource "postgresql_grant" "app_rw_tables" {
  database    = "app"
  role        = postgresql_role.app_rw.name
  schema      = "public"
  object_type = "table"
  privileges  = ["SELECT", "INSERT", "UPDATE", "DELETE"]
}

resource "postgresql_default_privileges" "app_rw_future_tables" {
  database    = "app"
  role        = postgresql_role.app_rw.name
  schema      = "public"
  owner       = "app_owner"                # tables created later by the migration role
  object_type = "table"
  privileges  = ["SELECT", "INSERT", "UPDATE", "DELETE"]
}

Self-hosted configuration with Ansible

# roles/postgresql/tasks/main.yml (excerpt)
- name: Render postgresql.conf overrides
  ansible.builtin.template:
    src: postgresql.auto.conf.j2
    dest: /etc/postgresql/17/main/conf.d/90-managed.conf
    owner: postgres
    mode: "0640"
  notify: reload postgresql                 # handler runs pg_ctl reload (or flags a restart if needed)

- name: Ensure extensions are installed in app database
  community.postgresql.postgresql_ext:
    name: "{{ item }}"
    db: app
    schema: extensions
  loop: [pg_trgm, citext]
  become_user: postgres

For Kubernetes, the operator's Cluster manifest is the configuration as code, typically applied through GitOps tools such as Argo CD or Flux.

Dangers specific to databases

Danger	Example	Safeguard
Destructive replacement	Changing an immutable attribute (identifier, some storage or encryption settings) plans a destroy-and-recreate	Read every plan; prevent_destroy, deletion_protection, policy checks in CI
Unplanned restarts	A static parameter or instance class change applied immediately	apply_immediately = false, maintenance windows, reviewing which parameters need restarts
Secrets in state	Passwords stored in plain text in Terraform state	Encrypted remote state with restricted access; generated secrets in a secrets manager; IAM or certificate auth
Drift	Someone changes a parameter in the console during an incident	Scheduled terraform plan to detect drift; reconcile or codify the change afterwards
Ownership overlap	Terraform and a migration tool both manage grants	One owner per object type, documented
Major upgrades via IaC	Bumping engine_version triggers an untested upgrade	Treat major upgrades as runbooks (11.4), with IaC updated to match

Key takeaways

• Describe database infrastructure, configuration, and database objects in version-controlled code, and keep schema migrations in a dedicated migration tool.
• Terraform (or OpenTofu, Pulumi) provisions instances, parameter groups, and networking; database-level providers or Ansible manage roles, grants, and extensions.
• Protect stateful resources with prevent_destroy, deletion_protection, and careful plan review.
• Keep secrets out of state where possible, and detect drift with scheduled plans.
• Assign exactly one tool as the owner of each kind of object.

🧪 Practice

1. (Beginner) Write Terraform (or a Docker Compose file plus SQL scripts) that creates a database, two roles, and grants for a local PostgreSQL, then destroy and recreate it to confirm it is reproducible.
2. (Intermediate) Change a static parameter (such as shared_preload_libraries) in a parameter group with Terraform and observe what the plan and apply do and when the change takes effect. Then make a manual console change and detect it with terraform plan.
3. (Interview) "A routine Terraform apply in CI deleted and recreated our production database. How could this happen, and what controls would have prevented it?" Hint: Attributes that force replacement, unreviewed plans, prevent_destroy, deletion_protection, policy-as-code, and separating apply permissions for stateful resources.

Cost and Resource Planning

Under-provisioning a database causes outages and slow queries; over-provisioning silently wastes money every month. Good capacity planning estimates what the workload needs today, projects growth, and maps both onto concrete resources and costs, then checks the estimate against real metrics (Capacity and Growth Monitoring).

The resources a database consumes

Resource	Driven by	Symptoms when short
Memory	Working set (hot data and indexes), shared_buffers plus OS cache, per-connection and per-query memory (work_mem, maintenance_work_mem)	Low cache hit ratio, high read I/O, temp files, OOM kills
CPU	Query volume and complexity, concurrency, parallel query, vacuum, compression, TLS	High run queue, latency rising with load
Storage capacity	Tables, indexes, bloat, TOAST, WAL, temp files, replication slots, local backups	Disk full: the server stops accepting writes
Storage I/O (IOPS, throughput)	Cache misses, checkpoints, WAL writes, vacuum, index maintenance	High I/O wait, latency spikes at checkpoints
Network	Replication streams, result sizes, backups to object storage, cross-zone traffic	Replication lag, slow large reads, transfer bills
Connections	Application instances times pool size	too many connections, memory pressure (12.4)

The working set rule

The most important sizing question is whether the working set, the data and indexes touched by regular queries, fits in memory. If it does, most reads are served from RAM; if not, every miss becomes a disk read, and performance degrades sharply.

  RAM  = shared_buffers (~25%) + OS page cache (most of the rest) + per-connection memory + OS
         |<----------- effective cache for the working set ----------->|

A worked estimate

An order-management system expects 30 million orders per year, each with an average of 3 items.

Item	Calculation	Result
orders heap	30 M rows × ~250 bytes (including 24-byte tuple header, padding, page overhead)	~7.5 GB/year
order_items heap	90 M rows × ~120 bytes	~11 GB/year
Indexes	~60% of heap for this schema (PK, foreign keys, 2 secondary indexes)	~11 GB/year
Bloat and free space allowance	+25%	~7.5 GB/year
Data total		~37 GB/year
3-year projection, 40% annual growth in volume	37 + 52 + 73	~162 GB
WAL, temp files, slots, headroom	Keep disk below ~70% used	Provision ~250 GB, autoscaling enabled
Working set	Last 90 days of orders and items plus hot indexes, year 3	~25 GB
Memory	Working set + shared_buffers, connections, OS	64 GB instance in year 3; 32 GB at launch

Measure, do not guess, row sizes for your schema: load a sample and use pg_relation_size and pg_total_relation_size (6.1).

-- Actual bytes per row and index-to-heap ratio on a representative sample
SELECT relname,
       pg_relation_size(oid) / greatest(reltuples, 1)                 AS heap_bytes_per_row,
       round((pg_indexes_size(oid)::numeric / greatest(pg_relation_size(oid), 1)), 2) AS index_ratio
FROM pg_class
WHERE relname IN ('orders', 'order_items');

-- Growth tracking: store this daily and trend it
SELECT now()::date AS day, pg_database_size(current_database()) AS bytes;

Where the money goes in the cloud

Cost component	What drives it	Ways to reduce it
Compute (instance hours)	Instance class, number of instances (primary, standby, replicas)	Right-size from metrics, ARM-based instances, reserved capacity or savings plans for steady workloads
High availability	A standby typically doubles compute and storage cost	Use HA for production; skip for dev and test
Storage capacity	Allocated GB, including unused headroom	Remove bloat and unused indexes, archive or partition-and-drop old data
Provisioned IOPS / throughput	IOPS tier or volume type	Better caching (more RAM) often costs less than more IOPS
I/O requests (on I/O-billed services)	Every page read from storage	Indexes, avoiding full scans, vacuum health
Backups	Retention beyond the free allowance, snapshot copies across regions	Tune retention; lifecycle policies in object storage
Data transfer	Cross-zone replication and app traffic, cross-region replicas, egress to the internet	Keep apps in the same zone as the primary where sensible; compress
Non-production environments	Full-size copies left running	Smaller instances, scheduled shutdown, branching or thin clones

Planning practices

• Target headroom, not 100%. Size for peak load at roughly 50-70% CPU and keep disks below about 70-80% full, leaving room for spikes, vacuum, index builds, and failover.
• Remember failover capacity. Replicas that serve reads must be able to absorb traffic if one of them, or the primary, fails.
• Optimize before scaling. A missing index or N+1 pattern can consume more CPU than doubling the instance would give you; check pg_stat_statements first.
• Review quarterly. Compare projections with actual growth and utilization, and right-size in both directions.
• Tag and attribute. Tag database resources by team and environment so costs are visible to the people who drive them.

Key takeaways

• Plan for memory (working set), CPU, storage capacity, I/O, network, and connections, each with its own symptoms when short.
• Whether the working set fits in RAM is the single most important sizing question.
• Estimate data size from measured row and index sizes, project growth, and add headroom for bloat, WAL, and operations.
• Cloud costs include HA multipliers, IOPS, I/O requests, backups, and data transfer, not only instance size.
• Query optimization is often the cheapest capacity upgrade; review plans against metrics regularly.

🧪 Practice

1. (Beginner) For a database you have access to, list the ten largest tables with heap size, index size, and total size, and compute the index-to-heap ratio for each.
2. (Intermediate) Build a capacity model for a table of your choice: load 1 million representative rows, measure bytes per row and index ratio, then project storage and working-set memory for 3 years at 50% annual growth.
3. (Interview) "Our database bill has grown 3x in a year while traffic grew 40%. Walk me through how you would investigate." Hint: Break the bill down by component, look for bloat and unused indexes, I/O-heavy queries, oversized non-production copies, cross-zone traffic, and backup retention.

Observability Stack Integration

Subchapter 7.5 described what PostgreSQL exposes: statistics views, pg_stat_statements, logs, and wait events. In production, those signals must flow into the same observability stack as the rest of your systems, so that a spike in API latency can be traced to a database query, and a database alert reaches the right person with enough context to act. Observability rests on three pillars: metrics (numbers over time), logs (discrete events), and traces (the path of one request across services).

Reference architecture

                    +---------------------------- PostgreSQL ----------------------------+
                    |  pg_stat_* views     pg_stat_statements     server logs (jsonlog)  |
                    +-------|----------------------|--------------------------|----------+
                            | SQL                  | SQL                      | files / stderr
                            v                      v                          v
                    +-----------------+   +-----------------+        +-------------------+
                    | postgres_exporter|   | pgwatch / query |        | log shipper       |
                    | (Prometheus      |   | analytics       |        | (Vector, Fluent   |
                    |  format metrics) |   |                 |        |  Bit, OTel coll.) |
                    +--------|--------+   +--------|--------+        +---------|---------+
                             v                     v                           v
                    +-----------------+    +---------------+           +----------------+
   app traces ----> | metrics store   |    | dashboards    | <-------- | log store      |
   (OpenTelemetry)  | (Prometheus,    | -> | (Grafana)     |           | (Loki, Elastic,|
         |          |  Mimir, etc.)   |    +---------------+           |  OpenSearch)   |
         v          +--------|--------+                                +----------------+
   trace store               v                                                 |
   (Tempo, Jaeger)  +-----------------+                                        v
                    | alerting        | --> on-call (pager, chat)        pgBadger reports
                    | (Alertmanager)  |
                    +-----------------+

Managed services provide their own equivalents (CloudWatch and Performance Insights, Cloud Monitoring and Query Insights, Azure Monitor), which can usually be exported to a central stack.

Metrics: what to collect and alert on

Signal	Source	Why it matters	Example alert
Availability	pg_up from the exporter	Can we connect at all?	Down for 1 minute
Connections	pg_stat_activity by state	Saturation, leaks, idle in transaction	Above 80% of max_connections for 5 minutes
Transaction rate and rollbacks	pg_stat_database	Throughput; error spikes	Rollback ratio suddenly rising
Cache hit ratio	pg_stat_database blks_hit/blks_read	Working set versus memory	Falls well below its normal baseline
Replication lag	pg_stat_replication, pg_last_xact_replay_timestamp()	Stale reads, failover data loss	Lag above 30 seconds
Replication slot retained WAL	pg_replication_slots	Disk filling from an abandoned slot	Retained WAL above 20% of disk
Transaction ID age	age(datfrozenxid)	Wraparound risk (8.5)	Above 1 billion
Longest transaction	pg_stat_activity.xact_start	Blocks vacuum, holds locks	Older than 15 minutes
Locks and waits	pg_locks, wait events	Contention	Sessions waiting on locks above a threshold
Disk usage	Node exporter or provider metrics	Disk full stops writes	Above 80%, and projected full within 7 days
Checkpoints, temp files, deadlocks	pg_stat_checkpointer (PG 17+) / pg_stat_bgwriter, pg_stat_database	Tuning signals	Deadlocks above zero; temp file growth

Prometheus alert rules using standard postgres_exporter metrics:

groups:
  - name: postgresql
    rules:
      - alert: PostgresDown
        expr: pg_up == 0
        for: 1m
        labels: { severity: page }
        annotations:
          summary: "PostgreSQL {{ $labels.instance }} is unreachable"

      - alert: PostgresConnectionsHigh
        expr: sum by (instance) (pg_stat_activity_count) / on (instance) pg_settings_max_connections > 0.8
        for: 5m
        labels: { severity: warn }
        annotations:
          summary: "{{ $labels.instance }} is using over 80% of max_connections"

      - alert: PostgresDeadlocks
        expr: increase(pg_stat_database_deadlocks[10m]) > 0
        labels: { severity: warn }

Prefer alerts on symptoms users feel (errors, latency, unavailability) and on impending hard failures (disk full, wraparound, slot retention) over alerts on every metric that moves; the rest belongs on dashboards.

Logs: structured and correlated

# postgresql.conf
logging_collector = on
log_destination = 'jsonlog'                 # PG 15+: one JSON object per line, easy to parse and ship
log_min_duration_statement = 500ms          # slow queries
log_lock_waits = on                         # waits longer than deadlock_timeout
log_temp_files = 0                          # every temp file spill
log_autovacuum_min_duration = 10s
log_checkpoints = on
log_connections = on
log_disconnections = on
compute_query_id = on                       # query_id appears in logs and pg_stat_statements

With JSON logs, each entry carries fields such as user, dbname, application_name, query_id, and error_severity, so a log platform can filter "all errors from the checkout service" or join a slow log entry to its pg_stat_statements row through query_id. For text logs, include the same information in log_line_prefix (for example '%m [%p] %q%u@%d app=%a qid=%Q '). Tools like pgBadger turn logs into reports of the slowest and most frequent queries, lock waits, and errors.

Traces: connecting requests to queries

Distributed tracing shows where time goes within one request. Instrumented drivers and ORMs (via OpenTelemetry libraries) create a span for each query with its duration and SQL text, so a slow checkout request can be seen as "180 ms of which 150 ms is one query":

  trace 4bf92f35...  POST /checkout                        [===============================] 210 ms
    span  cart-service: load cart                          [====]                             25 ms
    span  db: SELECT ... FROM cart_items WHERE cart_id=$1   [===]                             18 ms
    span  db: UPDATE inventory SET qty = qty - $1 ...           [=====================]      150 ms  <-- lock wait
    span  payment-service: authorize                                                [==]     15 ms

The database itself does not know about traces. To carry the context to the server side, SQLCommenter-style instrumentation appends a comment with the trace context to each query:

UPDATE inventory SET qty = qty - $1 WHERE sku = $2
/*application='checkout',route='%2Fcheckout',traceparent='00-4bf92f3577b34da6a3ce929d0e0e4736-00f067aa0ba902b7-01'*/

The comment then appears in pg_stat_activity.query and in logged statements, so a DBA looking at a slow or blocked query can jump to the exact trace that issued it. pg_stat_statements groups by queryid, computed from the parsed query, so differing comments do not fragment its statistics (only the stored sample text keeps the first comment seen).

Making it usable

• Set application_name everywhere, so every signal can be broken down by service.
• Use consistent labels (cluster, instance, role primary/replica, environment) across metrics, logs, and traces.
• Build dashboards around questions: "Is the database healthy?", "Which queries cost the most?", "Is replication keeping up?", rather than one graph per metric.
• Link alerts to runbooks that explain what the alert means and the first diagnostic steps (Health Checks and Alerting).
• Watch the watcher: alert if metrics stop arriving, and make sure monitoring queries themselves are cheap (avoid scanning large catalogs every 15 seconds).

Key takeaways

• Integrate PostgreSQL metrics, logs, and traces into the organization's common observability stack.
• postgres_exporter with Prometheus and Grafana is the standard open-source metrics path; managed services offer equivalents.
• Alert on user-visible symptoms and impending hard failures (disk, wraparound, slots, replication), and keep the rest on dashboards.
• Use jsonlog, compute_query_id, and application_name to make logs filterable and joinable with pg_stat_statements.
• Trace context in SQL comments links individual database queries to the application requests that issued them.

🧪 Practice

1. (Beginner) Run postgres_exporter against a local PostgreSQL, scrape it with Prometheus, and graph active connections by state and transactions per second in Grafana.
2. (Intermediate) Enable jsonlog and compute_query_id, generate a slow query, and find its log entry and its pg_stat_statements row using the shared query_id. Then add an alert for replication slot retained WAL and test it by stopping a subscriber.
3. (Interview) "Users report the checkout page is intermittently slow, but database CPU and the average query latency look normal. How would you use observability tooling to find out whether the database is involved?" Hint: Percentiles rather than averages, traces with per-query spans, lock waits and wait events, application_name breakdowns, and correlating trace IDs with logged statements.