LabHub

Blog

Database Engines 2026 Deep-Dive — Postgres Won the API, ClickHouse and DuckDB Won Analytics

2026-05-16 · 17 min read · #database#postgresql#mysql#clickhouse#duckdb#sqlite#tidb#cockroachdb#yugabytedb#cassandra#scylla#neon#supabase#olap#oltp

한국어English日本語

The database landscape in 2026 can be summarized in two propositions. First, Postgres has won the API. It is the default for new SaaS projects, the default dialect that AI coding agents emit, the catchphrase of serverless, and the first-class target of every ORM. Neon, Supabase, Prisma Postgres, Xata, Render Postgres, Fly Postgres, Vercel Postgres (which is Neon), Crunchy Bridge, Tembo, and even Convex SQL all either speak the Postgres wire protocol or host it directly. Second, ClickHouse and DuckDB have won analytics. ClickHouse is the de facto standard for petabyte-scale cloud OLAP, and DuckDB has cemented the "embedded Snowflake" position that runs on laptops, edge runtimes, browsers, Lambda, and Workers. In the gaps StarRocks, Doris, Druid, and Pinot own real-time OLAP; Cassandra, Scylla, Aerospike, and FoundationDB own ultra-high-QPS workloads; and TiDB, CockroachDB, YugabyteDB, and Spanner own distributed SQL.

This post unfolds the entire 2026 database engine map across OLTP, OLAP, NewSQL, NoSQL, embedded, ORM, and serverless Postgres economics. We cover the real features of PostgreSQL 18 RC, MySQL 9.x, MariaDB 12, ClickHouse 24.x, and DuckDB 1.x, with operational stories from Korean and Japanese big-tech operators sprinkled throughout.

The big picture in 2026

ACID and isolation levels revisited

Standard SQL defines four isolation levels, but real engines implement more variants.

CockroachDB and Spanner provide external consistency or strict serializability using TrueTime or hybrid logical clocks (HLC).

MVCC and replication topologies

PostgreSQL's MVCC keeps tuple versions inside the same page and determines visibility via the xmin/xmax transaction IDs. Consequently VACUUM is essential.

MySQL InnoDB stores prior versions separately in the undo log. Unlike Postgres, a dedicated purge thread runs in the background.

Replication topologies:

-- PostgreSQL 18 logical replication v2: bidirectional + sequence sync
CREATE PUBLICATION sales_pub
  FOR TABLE orders, customers, products
  WITH (publish = 'insert, update, delete, truncate', publish_via_partition_root = true);

CREATE SUBSCRIPTION sales_sub
  CONNECTION 'host=primary.internal dbname=app user=replicator'
  PUBLICATION sales_pub
  WITH (copy_data = true, streaming = 'parallel', origin = 'any', two_phase = true);

-- Sequences also auto-sync starting in 18
ALTER SUBSCRIPTION sales_sub REFRESH PUBLICATION SEQUENCES;

Column vs row storage

OLTP traffic is dominated by row-wise access, so row storage (InnoDB, Postgres heap) wins. OLAP requires per-column compression so columnar formats (ClickHouse MergeTree, Parquet, Apache ORC, DuckDB native, Snowflake micro-partition) dominate. ClickHouse supports LZ4/ZSTD plus time-series codecs like Gorilla, Delta, and DoubleDelta.

Vectorized execution processes 1024 (or more) tuples at a time using SIMD. ClickHouse, DuckDB, Photon (Databricks), Velox, and Snowflake all follow this pattern.

Query optimizers

The PostgreSQL planner is a CBO (cost-based optimizer) with some RBO heuristics layered in. The operational trio: pg_stat_statements, pg_hint_plan, and auto_explain.

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON, SETTINGS)
SELECT c.id, c.name, sum(o.total)
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= now() - interval '7 days'
GROUP BY c.id, c.name
ORDER BY 3 DESC
LIMIT 100;

MySQL added hash join in 8.0; MariaDB had it earlier. ClickHouse 24.x's new optimizer (enable_analyzer = 1 by default) dramatically improves distributed JOIN rewrites.

Partitioning and sharding

-- PostgreSQL pg_partman for time-series auto-partitioning
CREATE TABLE events (
  id bigserial,
  occurred_at timestamptz NOT NULL,
  payload jsonb NOT NULL
) PARTITION BY RANGE (occurred_at);

SELECT partman.create_parent(
  p_parent_table => 'public.events',
  p_control => 'occurred_at',
  p_type => 'native',
  p_interval => 'daily',
  p_premake => 7
);

-- pg_cron: nightly partition creation + 60-day detach
SELECT cron.schedule('events-maint', '0 2 * * *',
  $$CALL partman.run_maintenance_proc()$$);

Distributed transactions — 2PC, Calvin, Raft, Spanner

Why Postgres won the API

  1. Extensibility: The extension interface absorbs nearly every workload. pgvector (vectors), timescaledb (time-series), citus (distributed), postgis (geo), pg_cron (scheduler), pgmq (queues), pgaudit (audit), pglogical (logical replication), pg_partman (partitioning), hypopg (virtual indexes).
  2. JSONB: Effectively absorbed NoSQL.
  3. MERGE/RETURNING refinements: 17 added RETURNING to MERGE, 18 added WHEN NOT MATCHED BY SOURCE.
  4. Logical replication v2: Bidirectional, parallel, sequence-syncing.
  5. AIO (asynchronous I/O): io_uring/libaio backend in 18 RC.
  6. Block-level incremental backup: 17's pg_combinebackup.
-- Postgres 17/18 MERGE — the canonical UPSERT expression
MERGE INTO inventory AS t
USING (
  SELECT sku, qty FROM staging.new_stock
) AS s
ON t.sku = s.sku
WHEN MATCHED AND s.qty = 0 THEN DELETE
WHEN MATCHED THEN UPDATE SET qty = t.qty + s.qty, updated_at = now()
WHEN NOT MATCHED THEN INSERT (sku, qty, created_at) VALUES (s.sku, s.qty, now())
WHEN NOT MATCHED BY SOURCE AND t.updated_at < now() - interval '90 days' THEN DELETE
RETURNING merge_action(), t.sku, t.qty;

MySQL 9 / MariaDB 12

Oracle MySQL 9 introduced the VECTOR data type (GA in 2024), JS stored functions (developer preview), and HeatWave Lakehouse. MariaDB 12 further accelerated divergence from Oracle MySQL — Galera Cluster 4, ColumnStore integration, and enhanced JSON Path.

New adoption is slowing, but Korean and Japanese giants like Toss, Woowa Brothers, and Rakuten still run MySQL as their core OLTP. Vitess (used in part by Coupang and LINE) was decoupled from PlanetScale and now sits as a CNCF graduated project.

Serverless Postgres economics

Serverless Postgres rests on two pillars.

  1. Compute/storage separation: Neon's Pageserver/Safekeeper/Compute three-tier architecture, Aurora's shared grain storage, AlloyDB's columnar accelerator.
  2. Scale-to-zero: At zero traffic, compute stops and only storage is billed. Neon, Supabase Branches, Xata, and Cloudflare Hyperdrive all follow this model.

Cold-start costs: Neon ~300-700ms, Supabase pooler always-on, PlanetScale Postgres always-on. The more burst-heavy/dev-leaning your workload, the more Neon shines; steady-state workloads favor Supabase or RDS.

# Neon Branch + Vercel Preview pattern (conceptual)
neon:
  project: my-app
  default_branch: main
  preview_strategy:
    on_pr_open: create_branch_from_main
    branch_name: pr-${PR_NUMBER}
    compute_endpoint:
      autoscaling: { min_cu: 0.25, max_cu: 4 }
      suspend_timeout: 300
  protections:
    main:
      allowed_writers: [migrations-bot, app-prod]
      enable_logical_replication: true

ClickHouse — the de facto cloud OLAP standard

ClickHouse is a column store centered on the MergeTree engine family. The essentials:

-- ClickHouse time-series + distributed + MV pattern
CREATE TABLE events_local ON CLUSTER prod (
  ts        DateTime CODEC(DoubleDelta, ZSTD(3)),
  user_id   UInt64,
  event     LowCardinality(String),
  props     Map(String, String),
  amount    Decimal(18, 4) CODEC(Gorilla, ZSTD(3))
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
PARTITION BY toYYYYMM(ts)
ORDER BY (user_id, event, ts)
TTL ts + INTERVAL 90 DAY DELETE,
    ts + INTERVAL 7 DAY TO VOLUME 'cold'
SETTINGS index_granularity = 8192;

CREATE TABLE events ON CLUSTER prod
AS events_local
ENGINE = Distributed('prod', 'default', 'events_local', rand());

CREATE MATERIALIZED VIEW events_daily_mv ON CLUSTER prod
ENGINE = SummingMergeTree
ORDER BY (event, day)
AS SELECT
  event,
  toDate(ts) AS day,
  count() AS cnt,
  sum(amount) AS revenue
FROM events_local
GROUP BY event, day;

ClickHouse Cloud offers fully separated compute/storage, multi-region replication, and autoscaling. Korea's Toss runs ClickHouse in its analytics pipeline, and Japan's LINE has publicly described using it for messaging analytics.

DuckDB — the embedded Snowflake

DuckDB is often called the OLAP version of SQLite. Single binary, embedded, scans Parquet/CSV/JSON/Arrow as external tables on the fly, vectorized execution, multi-threaded.

# DuckDB scanning S3 Iceberg/Parquet directly
duckdb << 'SQL'
INSTALL httpfs; LOAD httpfs;
INSTALL iceberg; LOAD iceberg;

SET s3_region='ap-northeast-2';
SET s3_access_key_id='AKIA...';
SET s3_secret_access_key='...';

CREATE VIEW events AS
  SELECT * FROM iceberg_scan('s3://lake/warehouse/events');

SELECT
  date_trunc('day', ts) AS day,
  event,
  count(*) AS cnt
FROM events
WHERE ts >= current_date - INTERVAL 30 DAY
GROUP BY 1, 2
ORDER BY 1 DESC, cnt DESC
LIMIT 50;
SQL

DuckDB 1.x guarantees stability, and variants are proliferating: MotherDuck (managed cloud), DuckDB-Wasm (browser), and DuckDB on Lambda/Workers.

StarRocks, Apache Doris, Druid, Pinot

Druid vs Pinot vs ClickHouse — picking a tool

NewSQL — TiDB, CockroachDB, YugabyteDB, Spanner

Distributed SQL aims to deliver "horizontally scalable ACID OLTP".

-- TiDB EXPLAIN ANALYZE — verify coprocessor pushdown
EXPLAIN ANALYZE
SELECT user_id, sum(amount)
FROM orders
WHERE created_at >= '2026-05-01'
GROUP BY user_id
HAVING sum(amount) > 1000;

Cassandra 5, ScyllaDB, Aerospike

The leaderless wide-column stores.

-- Cassandra CQL: TTL + LWT
CREATE TABLE sessions (
  user_id uuid,
  session_id timeuuid,
  ip inet,
  payload text,
  PRIMARY KEY (user_id, session_id)
) WITH default_time_to_live = 86400
  AND compaction = { 'class' : 'TimeWindowCompactionStrategy', 'compaction_window_size' : 1, 'compaction_window_unit' : 'HOURS' };

INSERT INTO sessions (user_id, session_id, ip, payload)
VALUES (uuid(), now(), '203.0.113.1', '{"agent":"chrome"}')
IF NOT EXISTS;

SQLite, libSQL, Turso, rqlite, Cloudflare D1

Embedded SQL is all about "runs anywhere".

Embedded → edge trend

The keyword of serverless v2 is "edge database". Place data at the PoP closest to the user, and relax consistency. Turso's embedded replica, Cloudflare D1, PlanetScale Boost, and Vercel Edge Config all follow this pattern.

Mongo, DynamoDB, Cosmos DB

Neon, Supabase, PlanetScale, Xata, Convex

Fauna shut down in 2025, with migration guides pointing to Convex/Neon.

EdgeDB → Gel, SurrealDB

Adoption is slow, but AI agents find the abstraction nicely tractable, so they're getting a second look.

Postgres extensions — the operational top 7

ExtensionPurpose
pg_stat_statementsQuery statistics
pg_repackOnline VACUUM FULL replacement
pgvectorVector search
timescaledbTime-series hypertables
citusDistributed sharding
pg_partmanPartition automation
pg_cronIn-DB scheduler

Plus pgaudit (audit), hypopg (virtual indexes), pg_stat_kcache, auto_explain, and external pgbouncer.

Connection pooling — PgBouncer, Pgpool-II, ProxySQL, Vitess

# pgbouncer.ini — transaction pooling + auth delegation
[databases]
app = host=primary.internal port=5432 dbname=app

[pgbouncer]
listen_port = 6432
pool_mode = transaction
default_pool_size = 50
reserve_pool_size = 10
reserve_pool_timeout = 5
max_client_conn = 5000
server_idle_timeout = 600
auth_type = scram-sha-256
auth_user = pgb_auth
auth_query = SELECT u.usename, u.passwd FROM pg_shadow u WHERE u.usename = $1
ignore_startup_parameters = extra_float_digits
admin_users = pgb_admin

PostgreSQL 18 has work-in-progress on a native connection pooler (not yet GA). Supavisor (Supabase), Hyperdrive (Cloudflare), and AlloyDB poolers are the cloud-pooler heavyweights.

DB observability — pganalyze, Datadog DBM, OtterTune, EverSQL

ORM landscape 2026

LanguageTier 1RisingNotes
TypeScriptDrizzle, PrismaKysely, MikroORMDrizzle leads thanks to RSC alignment
PythonSQLAlchemy 2.xSQLModel, TortoiseSQLAlchemy 2.0 async stable
Gosqlc, GORMBun, entsqlc trending toward standard
RustDiesel, SeaORMsqlxsqlx + macros most common
RubyActiveRecordSequelStable era
Java/KotlinjOOQ, Spring DataExposedjOOQ near-universal praise

Prisma Postgres was announced in 2024 — Prisma now offers its own managed Postgres. Drizzle Studio is a zero-config local GUI that draws fans.

Korea and Japan operating stories

Comparison table — one-page summary

EngineModelConsistencyDistributionLicense
PostgreSQLrowRC/RR/SI/SSIsingle node (+logical rep)PostgreSQL
MySQLrowRR/RCsingle node (+Group Replication)GPLv2
ClickHousecolumneventual (replica)shard + replicaApache 2.0
DuckDBcolumnn/a (embedded)noneMIT
Cassandrawide-coltunable quorumleaderless ringApache 2.0
ScyllaDBwide-coltunable quorumleaderless ringAGPL/Enterprise
TiDBrow+colsnapshot (HLC)Raft shardedApache 2.0
CockroachDBrowsnapshot (HLC)Raft rangeCCL
YugabyteDBrow+colsnapshotRaft tabletApache 2.0
SpannerrowexternalPaxosproprietary
Mongodoctunablesharded replica setSSPL
DynamoDBKV/docstrong/eventualhash shardedproprietary
Neonrow (PG)RC (PG)separated storageApache 2.0 + proprietary
SQLiterowserializablen/apublic domain
DuckDBcolumnn/an/aMIT

SQL on the data lake — Iceberg + DuckDB + ClickHouse

The hottest pattern in 2026: the warehouse is just compute, and the truth lives in Iceberg tables.

# Terraform: AWS Glue Iceberg + S3 + Athena/ClickHouse/DuckDB all query the same table
resource "aws_s3_bucket" "lake" {
  bucket = "yj-blog-lake-prod"
}

resource "aws_glue_catalog_database" "warehouse" {
  name = "warehouse"
}

resource "aws_glue_catalog_table" "events" {
  database_name = aws_glue_catalog_database.warehouse.name
  name          = "events"
  table_type    = "EXTERNAL_TABLE"

  parameters = {
    "table_type"               = "ICEBERG"
    "format"                   = "iceberg/parquet"
    "metadata_location"        = "s3://yj-blog-lake-prod/warehouse/events/metadata/v1.metadata.json"
  }

  storage_descriptor {
    location = "s3://yj-blog-lake-prod/warehouse/events/"
    columns {
      name = "ts"
      type = "timestamp"
    }
    columns {
      name = "user_id"
      type = "bigint"
    }
    columns {
      name = "event"
      type = "string"
    }
  }
}

ClickHouse has iceberg/deltalake table functions, DuckDB has iceberg_scan, Snowflake has external iceberg tables, and BigQuery has BigLake — all reading the same data.

Data modeling — is normalization dead?

Answer: In OLTP, stay close to 3NF; in OLAP, star schema or wide tables. Use JSONB/Map only where it earns its place (variable attributes, user-defined fields) and flatten the rest into columns where you can control with PKs, FKs, and indexes.

Migrations and schema evolution

Security — RLS, row-level encryption, queryable encryption

PostgreSQL's RLS is a real weapon for SaaS multi-tenancy.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

-- Application sets context at transaction start
SET LOCAL app.tenant_id = '11111111-2222-3333-4444-555555555555';

MongoDB 8's queryable encryption lets you compare equality over ciphertext. CockroachDB and YugabyteDB also ship client-side encryption SDKs.

Backup and PITR

Cost model comparison

CategoryRepresentativesPricing modelStrengths
Managed OLTPRDS Postgres, Aurorahourly + I/Ostability
Serverless PGNeon, Aurora Serverless v2CU-hour + storagescale-to-zero
Data warehouseSnowflake, BigQuerycompute (credits) + storageisolation, governance
Self-hosted OLAPClickHouse, DruidCPU/RAM/diskcost advantage
EmbeddedSQLite, DuckDB$0operational simplicity

Which engine, when — a decision tree

Conclusion — the simple 2026 recommendation

Default to Postgres (managed: Neon/Supabase/RDS); for analytics, start with DuckDB and graduate to ClickHouse. If multi-region ACID is a business requirement, CockroachDB or Spanner. Ultra-high QPS: Scylla. Embedded: SQLite/libSQL. ORMs: Drizzle for TS, sqlc for Go, SQLAlchemy 2.x for Python, sqlx + Diesel/SeaORM for Rust.

Database choice is no longer a religious war. The workload pattern, what your team can actually operate, and most importantly — which questions you need answered within one second over this data — determine everything.

References

Comments

No comments yet.

Sign in to leave a comment