Databases compared: PostgreSQL, MySQL/MariaDB, SQLite, MongoDB, Redis, ClickHouse, CockroachDB, SurrealDB, DuckDB, Supabase

What is a modern database choice?

The database decision lasts 5-10 years. It dictates schema, backup strategy, license model, and scaling options. Choose the wrong stack and you pay later in migrations that tie up developers for months.

As of May 2026, the field has sorted itself. Classically relational are PostgreSQL, MySQL/MariaDB, SQLite, CockroachDB, and Supabase (Postgres-based). Document-oriented is MongoDB. Key-value plus cache is Redis. Columnar for analytics are ClickHouse and DuckDB. Multi-model is SurrealDB as the youngest player.

For a Swiss SME with fiduciary software, a client portal, and 100-500 users, the answer holds with 90 percent confidence: PostgreSQL as primary database, plus Redis for sessions and rate limiting, plus optionally pgvector for AI knowledge bases. The other eight options are specialists for specific problems -- whoever needs them usually knows why.

Why it matters

Three axes decide the choice: data model, operations model, license compatibility.

Data model: Relational data with clear tables, foreign keys, and joins belongs in PostgreSQL or MySQL. If schemas change daily and fields are optional (sensor data, log entries, game state), MongoDB fits better. If 99 percent of queries are aggregates over millions of rows (reporting, BI), data belongs in ClickHouse or DuckDB. Whoever needs KV cache, pub/sub, or sessions builds Redis alongside -- not instead of -- the primary DB.

Operations: Self-host vs. cloud decides running cost and responsibility. PostgreSQL on a Hetzner CPX31 costs CHF 25/month plus 1-2 hours maintenance. Supabase Pro starts at USD 25/month for a Postgres with auth, storage, realtime fully configured. CockroachDB Cloud begins around USD 0-25/month for serverless and scales by volume. With < 5 databases, self-host is often cheaper. With Swiss data residency duty, Hetzner Falkenstein/Helsinki + PostgreSQL is the clear default.

License: The SSPL of MongoDB and Redis (Redis since 2024) excludes cloud hyperscalers but not SME self-hosters. Watch CockroachDB: was BSL, switched in 2024 to Enterprise License for large customers, but announced in 2026 that self-host for SMEs returns under Apache 2.0. SurrealDB runs BSL with 4-year Apache 2.0 conversion -- i.e. version 1.0 becomes Apache in 2028. For license compliance (public sector, regulated industry), read the license texts carefully before going to production.

The ten options in detail

PostgreSQL (PostgreSQL License, MIT-like, self-host): the industry-default DB. JSON support, full-text search, pgvector for AI, PostGIS for geo. Scales vertically very far -- Stripe and Notion run production on Postgres. Default choice in 90 percent of SME setups.

MySQL/MariaDB (GPL-2 / MariaDB BSL): classic LAMP stack DB. MariaDB is the open-source fork of MySQL since 2009, default in most Linux distros today. MySQL is Oracle, MariaDB is MariaDB Foundation. Functionally comparable to Postgres on simple setups, less powerful on JSON, window functions, and CTEs.

SQLite (Public Domain, embedded): single-file DB without server. Perfect for single-server apps, desktop software, mobile apps. With Litestream, live replication to S3. Scales per file to roughly 1 TB. Realty51 and Fairlane run on SQLite -- a deliberate choice for single-tenant setups without concurrent-write pressure.

MongoDB (SSPL, self-host + Atlas Cloud): document DB with JSON-based schema. Good for flexible data without a fixed schema. Atlas Cloud from USD 9/month (M0 free, M10 from USD 57). For 2026 new starts, first check whether Postgres with jsonb columns suffices -- in 80 percent of cases yes.

Redis (BSD historically, SSPL since 2024, self-host + cloud): in-memory KV store, pub/sub, streams. Very fast (microseconds). Standard for sessions, rate limits, job queues, pub/sub notifications. Valkey is the Linux Foundation fork of Redis 2024 with a true open-source license -- already available in many distros as drop-in replacement by 2026.

ClickHouse (Apache 2.0, self-host + cloud): columnar analytics DB. Very fast for aggregates over billions of rows, e.g. web analytics, logs, telemetry. Yandex origin, today own ClickHouse Inc. Cloud offering from USD 0/month (free tier) to enterprise. Overkill for SME reporting in 2026, exact fit for SaaS analytics.

CockroachDB (BSL, 2026 conversion to Apache 2.0 for self-host SMEs): distributed Postgres-compatible SQL. Built for multi-region, automatic failover, ACID transactions across nodes. Cloud serverless from USD 0/month. Self-host free up to 10 GiB storage. Sensible when an SME has sites in CH + DE + USA and needs consistent data everywhere -- rare, but then exactly the right DB.

SurrealDB (BSL, 4-year Apache 2.0 conversion): young multi-model -- relational, document, graph, and vector in one DB. Written in Rust, very actively developed, in 2026 used productively in version 2.x. Free plan 1 GB storage, Start plan around USD 15/month. Promising but not yet the default suggestion for SMEs -- too young for long-term critical data.

DuckDB (MIT, embedded): columnar OLAP DB as single file. Perfect for local data analysis, Pandas replacement, reporting on file snapshots. Runs in Python, Node, R, Rust. Does not replace a production DB but complements it for analytical workloads. Ideal for fiduciary reporting over 100k+ client bookings.

Supabase (Apache 2.0, self-host + EU cloud): Postgres-based backend-as-a-service with auth, storage, realtime, edge functions. Free tier 500 MB plus 50k MAU, Pro USD 25/month. EU region (Frankfurt) available -- usable for GDPR with DPA. Very fast deployment for startups and MVPs. Lock-in moderate since Postgres itself is the standard.

Selection workflow in 6 steps

1. 01Define data model: relational (Postgres/MySQL), document (Mongo), columnar (ClickHouse/DuckDB), KV (Redis).
2. 02Estimate volume: < 10 GB easy, 10-1000 GB standard setup, > 1 TB needs index and partition strategy.
3. 03Clarify data residency: must stay in CH/EU? Self-host on Hetzner Falkenstein or Supabase EU region.
4. 04Check license: SSPL (Mongo, Redis) blocks cloud resale, BSL (Cockroach, Surreal) has Apache 2.0 conversion with schedule.
5. 05Define backup strategy: PITR-capable (Postgres WAL, Mongo Atlas), snapshot-only (SQLite via Litestream), timestamped.
6. 06Check migration path: is a switch in 3 years thinkable? Postgres-compatible DBs (Cockroach, Supabase) make it easy.

Recommendation by use case

Swiss SME with client portal, < 100k records: PostgreSQL self-host on Hetzner Falkenstein. With pgvector for AI knowledge bases, jsonb for flexible data, PostGIS for geo. Backup via pg_basebackup plus WAL archiving (PITR-capable). Cost: CHF 25-50/month incl. backup storage.

Startup or MVP with tight engineering time: Supabase EU region. Auth, storage, realtime ready-configured, Frankfurt hosting for GDPR. Pro plan USD 25/month for 8 GB storage and medium load. Migration to self-host possible since Postgres-based.

Single-tenant apps, embedded software: SQLite plus Litestream. Fairlane and Realty51 run that way -- same server as app, one file, backup via Litestream replication to S3. Scales per file up to 1 TB without server overhead.

Sessions, rate limiting, job queues: Redis (or Valkey from 2026 for a pure OSS license). Never as primary DB -- only as cache and queue next to PostgreSQL.

Web analytics, log aggregation, reporting > 100M rows: ClickHouse. Standard for telemetry pipelines. With < 10M rows, prefer Postgres with partitioning -- ClickHouse setup is non-trivial.

Local data analysis, Pandas replacement: DuckDB. In fiduciary reporting on a 5 GB client snapshot, 100x faster than Postgres queries without server setup.

Multi-region consistency (CH + DE + USA): CockroachDB. When a group needs data consistency under 100 ms latency across 3 countries. Self-host free up to 10 GiB, cloud serverless from USD 0.

Flexible schemas, document data: first check Postgres jsonb. If truly schemaless data (e.g. CMS content, sensor data in 50+ variants), then MongoDB Atlas EU or self-host.

Multi-model experiment, greenfield project 2026/2027: SurrealDB as a pilot. Attractive for R&D, not yet mature for regulated industries like fiduciary or law.

When this choice is wrong

MongoDB is the most common mistaken choice in Swiss SMEs. Whoever starts a new system in 2026 and knows nothing but MongoDB should first check whether the schema is truly schemaless -- in 80 percent of cases it is not, and Postgres with jsonb columns offers the same flexibility plus classic SQL power. A later migration from MongoDB to Postgres is a 2-4 week task.

Redis as primary DB is another classic mistake. Whoever holds orders or client data only in Redis loses them on the first crash -- Redis is in-memory-focused, persistence is an option, not the default. Redis is a sidekick to Postgres, not a replacement.

ClickHouse for OLTP (small, frequent writes) is wrong -- columnar is built for aggregates, not for single inserts. Whoever writes 10 new bookings per second in a fiduciary system belongs in Postgres.

CockroachDB for a single-region Swiss application is overkill -- the overhead of distributed operations pays off only with real multi-region requirements. Until then, a Postgres instance with good backups does the job.

Trade-offs

FAQ

Related topics

Sources

1. PostgreSQL 17 Release Notes · 2026-04
2. Supabase Pricing 2026 · 2026-05
3. SurrealDB License FAQs · 2026-04
4. CockroachDB Licensing Update 2024-2026 · 2026-03
5. ClickHouse Apache 2.0 Documentation · 2026-04
6. Valkey -- Linux Foundation OSS fork of Redis · 2026-03