PostgreSQL: the relational default database for Swiss SMEs and AI stacks

What is PostgreSQL?

PostgreSQL is a relational open-source database developed since 1996 under the PostgreSQL License (an MIT-like, very permissive license). In May 2026, version 17 is the stable standard; version 18 is in beta. PostgreSQL powers Stripe, Notion, Instagram, GitLab, and thousands of other production systems -- it is the database that is least often the wrong choice.

Functionally PostgreSQL covers much more than the classic SQL repertoire. Native JSON and JSONB columns allow semi-structured data without a separate document DB. Window functions, Common Table Expressions (CTEs), and recursive queries enable reports that are awkward in MySQL. With the pgvector extension Postgres becomes a production-ready vector database for RAG workloads up to about 1 million embeddings. PostGIS turns the same instance into a geo database for property or logistics applications.

The license is a practical bonus: no royalties, no redistribution restrictions, no license inspections. An SME may use, modify, and pass on PostgreSQL freely -- including in commercial SaaS products. Compared to the SSPL license of MongoDB or Redis (since 2024), this is a real advantage.

In our Hetzner stack, several Postgres instances run per server in Docker containers: one for Fairlane clients, one for Acquanta CRM, one for Pexeos. Backup via WAL archiving plus basebackup. The instances are isolated but share hardware -- a cost-efficient configuration for an SME with several products.

Why it matters

The database decision lasts 5-10 years. Wrong choice means later migrations that eat weeks or months of developer time. PostgreSQL is the safe default in May 2026 for three reasons.

First: feature breadth without extra servers. One Postgres instance handles relational data, JSON documents, full-text search, vector search, and geo queries. A team that starts an SME app with MySQL plus MongoDB plus Elasticsearch plus Qdrant operates four servers, four backup strategies, four monitorings. Postgres does 80 percent of that in one instance -- less complexity, less risk.

Second: nFADP and GDPR readiness without renegotiation. Self-host on Hetzner Falkenstein or Helsinki is a 30-minute install, no third-country transfer required. DPA falls away because no external data processors are involved. For fiduciary, law, insurance, and Swiss public sector this is the path of least resistance.

Third: migration options stay open. The Postgres wire protocol is spoken by CockroachDB (distributed), Supabase (BaaS), Neon (serverless), and AWS Aurora Postgres-compatible. A team that must switch in 3 years has a clear migration path -- usually a dump-restore with minimal changes. MongoDB or DynamoDB migrations are by contrast multi-week projects with schema redesign.

The cost rounds out the picture: Postgres on Hetzner CPX31 (4 vCPU, 16 GB RAM, 160 GB SSD) costs CHF 25/month. With an additional CHF 30 storage box for backups, you land at CHF 55/month for a production stack that competes with AWS RDS (USD 70+) or Azure Database for PostgreSQL (USD 80+) -- with full EU data residency.

How it works

PostgreSQL is a classic multi-process server: a postmaster process accepts connections; for each connection a backend process is forked. Plus helper processes for WAL writer, checkpointer, autovacuum, and background workers. On a 16 GB server, 100-200 connections run comfortably; beyond that one puts pgbouncer in front as connection pool.

The memory architecture separates two caches: shared_buffers (typically 25 percent of RAM) holds data pages, the OS page cache holds the rest. WAL (write-ahead log) writes every change first sequentially to disk, then data pages are written asynchronously. This architecture enables PITR (point-in-time recovery): archive WAL segments and replay to any point between two basebackups.

A minimal production setup looks like this:

CREATE TABLE clients ( id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL, metadata JSONB DEFAULT '{}', created_at TIMESTAMPTZ DEFAULT now() );

CREATE INDEX idx_metadata_gin ON clients USING GIN(metadata); CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE rag_chunks ( id BIGSERIAL PRIMARY KEY, client_id BIGINT REFERENCES clients(id), text TEXT, embedding VECTOR(1536) );

CREATE INDEX ON rag_chunks USING hnsw (embedding vector_cosine_ops);

The extensions mechanic is Postgres core strength. Over 250 extensions exist: pgvector for embeddings, PostGIS for geo, pg_partman for automatic partitioning, pg_stat_statements for query performance, TimescaleDB for time series, Citus for horizontal scaling. Each extension is a drop-in -- CREATE EXTENSION is enough, no server restart.

Replication uses streaming replication with primary and standby. A standby can be synchronous or asynchronous; for an SME asynchronous suffices. On failover the standby is promoted to primary via pg_promote. With Patroni and etcd you can build auto-failover -- but for most SME setups manual failover (RTO 15-30 min) is enough and saves complexity.

Backup standard: daily pg_basebackup to a storage box plus continuous WAL archiving via pg_receivewal. For recovery: basebackup + WAL segments to the desired point in time, then pg_ctl start. A 50 GB database is back in 10-20 minutes.

PostgreSQL to production in 5 steps

1. 01Provision a Hetzner server (CPX31 or larger), install Docker, run PostgreSQL 17 as a container with mounted volume.
2. 02Create schema and extensions: CREATE EXTENSION pgvector / pg_stat_statements / pg_audit -- per use case. Define tables with BIGSERIAL, TIMESTAMPTZ, and foreign keys.
3. 03Set up backups: daily pg_basebackup to Hetzner Storage Box, continuous WAL archiving via pg_receivewal, 30-day daily plus monthly retention.
4. 04Wire monitoring: enable pg_stat_statements, postgres_exporter for Prometheus, Grafana dashboard with query latency, connections, and cache hit ratio.
5. 05Test recovery: replay a backup, simulate PITR to a point 1 hour ago -- document and store in the runbook.

When to use PostgreSQL

PostgreSQL is the right choice when (a) data is or can be relationally structured, (b) transaction safety (ACID) is required, or (c) the team wants a proven, documented standard DB without lock-in risk.

Concrete scenarios: a client portal for a fiduciary office with 200 customers, invoices, receipts, and time tracking. A law-firm app with cases, documents, fee tracking, and case management. An insurance CRM with contracts, claims, commissions. A SaaS platform with multi-tenant schema and audit trails. A RAG knowledge base with pgvector for 100k-1M embeddings.

Often overlooked Postgres cases: time-series data (with the TimescaleDB extension) up to several billion entries, job-queue systems (with SKIP LOCKED), eventual-consistency stores (with logical replication as outbox pattern), real-time dashboards (with pg_notify and Listen/Notify). Whoever starts a new SME system in 2026 should begin with Postgres and add another DB only when a concrete bottleneck appears.

For Swiss regulated industries -- fiduciary (Art. 957a CO), law (professional secrecy Art. 321 StGB), insurance (FINMA supervision), public sector -- Postgres self-host on CH/EU hardware is operationally the simplest and legally the cleanest configuration. Audit trail via pg_audit extension, row-level security for client separation, logical replication for disaster recovery.

When not to use

For pure OLAP workloads (aggregates over billions of rows), ClickHouse or DuckDB are faster -- Postgres is row-oriented, columnar DBs reach 50-100x speedup on pure analytics. Whoever aggregates 500M telemetry entries per month in a SaaS product does not belong in Postgres.

For multi-region setups with synchronous consistency across CH + DE + USA, CockroachDB is the better choice -- Postgres replication is asynchronous or synchronous with heavy performance loss over WAN. Whoever needs data consistency under 100 ms latency across three sites looks at Cockroach.

For embedded use cases without a server (desktop apps, mobile apps, embedded devices), SQLite fits better -- one file, no daemon, no network stack. Postgres always needs a running server process and makes no sense in single-tenant embedded scenarios.

For workloads with truly schemaless data -- 50+ different sensor types each with its own schema, dynamic CMS with user-defined fields -- MongoDB can be simpler. But careful: in 80 percent of cases Postgres jsonb plus GIN index suffices, and Mongo brings SSPL license topics that Postgres does not have.

In-memory caching, pub/sub, and job queues belong in Redis -- not because Postgres cannot do them, but because Redis latency is in microseconds vs. Postgres in milliseconds. Redis is the right complement to Postgres, not a replacement.

Trade-offs

FAQ

Related topics

Sources

1. PostgreSQL 17 Release Notes · 2026-05
2. PostgreSQL Versioning Policy and Support Lifecycle · 2026-05
3. pgvector extension - GitHub releases · 2026-05
4. PostgreSQL Licensing FAQ · 2026-05