PostgreSQL

PostgreSQL is an object-relational database management system (ORDBMS), a descendant of the POSTGRES code developed at Berkeley, released under a liberal open-source license. It covers much of the SQL standard and is extensible in place: functions, data types, operators and index methods can be added to the core.

In our stack it is the containerised data layer: one database per application, one container per database, data on a mounted volume. Docker provides the runtime, so the data outlives the container.

How it keeps data consistent

MVCC does the work: each SQL statement sees a snapshot of the data as it was when that statement started, and read locks do not conflict with write locks.

• Read Committed is the default: a query sees only what was committed before it began.
• Repeatable Read also rules out phantom reads; Serializable uses Serializable Snapshot Isolation, where predicate lock memory shortfalls raise serialization failures.
• Sequences are the exception: a change is immediately visible to other transactions and is not rolled back on abort.

Extensions, and where pgvector fits

CREATE EXTENSION runs an extension’s script in the current database and records the objects it creates, so DROP EXTENSION can clean them up. It usually needs superuser privileges unless the extension is marked trusted.

pgvector adds vector similarity search: single and half-precision, binary and sparse vectors, and L2, inner product, cosine, L1, Hamming and Jaccard distances. Without an index it scans every row for an exact result; an HNSW or IVFFlat index trades recall for speed. Embeddings then live under the same transactions and backups as their rows.

Backup and restore

pg_dump makes a consistent logical export without blocking readers or writers: pg_dump dbname > dumpfile. Text dumps are restored with psql, the custom (-Fc) and directory (-Fd) archives through pg_restore, and only directory format dumps in parallel with -j. A restore executes arbitrary code of the source superusers’ choice, so untrusted dumps must be inspected first.

Crash safety and point-in-time recovery (PITR) rest on the write-ahead log: changes are logged before they reach the data files, so a commit only has to flush the WAL. Segment files in pg_wal are 16 MB by default, and archiving them alongside a base backup allows a restore to any point the WAL covers. Archiving uses wal_level, archive_mode and archive_command, and pg_dump output is logical and cannot be part of it.

Operational limits the project documents

• Connections. max_connections defaults to typically 100 and can only be set at server start; raising it raises the shared memory sized from it. superuser_reserved_connections defaults to 3, reserved_connections to 0, and listen_addresses to localhost only.
• Vacuum. UPDATE and DELETE leave the old row version behind, so tables need periodic vacuuming. Plain VACUUM marks space for reuse; VACUUM FULL returns it to the operating system but needs an ACCESS EXCLUSIVE lock. Autovacuum schedules dynamically, never issues VACUUM FULL, protects against transaction ID wraparound, and should not be disabled.
• Version upgrades. Minor releases keep the internal storage format, so replacing the binaries is enough. Major releases can change it: those need a dump and restore with pg_dumpall, or pg_upgrade, plus the Migration section of the release notes.

With those guarantees the database is a predictable component. Maintenance and dump jobs run in n8n, and the agents that read it are described under Hermes Agent.

Further reading

• PostgreSQL 18 documentation - the official manual, always for the current major release.
• pgvector - the source and README of the vector search extension.
• Routine Vacuuming - autovacuum, table bloat and wraparound protection in detail.

CyberElectro runs PostgreSQL in containers with one database per application, and uses the pgvector image where vector search is needed.