Choosing the Right Database: A Practitioner's Framework

Database selection is one of the few engineering decisions that compounds over years. Get it right and the system fades into the background, doing its job quietly. Get it wrong and you are rewriting application logic, explaining migration timelines to stakeholders, and carrying the context tax of working around a mismatch every single sprint.

This article is part of the data architecture series. If you have not read the overview of modern data architecture patterns, that is a reasonable starting point.

The honest framing: most teams reach for a new database because it appeared in a conference talk, not because they exhausted what their existing system could do. This guide is about reversing that reflex.

The Questions That Actually Matter#

Before evaluating any database, answer these three questions as precisely as you can.

What are the query patterns? Transactional workloads (OLTP) read and write individual rows or small row sets with high concurrency. Analytical workloads (OLAP) scan millions of rows, aggregate across columns, and favour throughput over latency. Most real systems are a mix, but the dominant pattern drives the decision. Get this wrong and no amount of tuning recovers it.

What is the data model? Relational data with referential integrity, known schema, and joins belongs in a relational database. If you are building around access patterns (e.g. "all orders for a customer, sorted by date, paginated") and the schema is volatile, a document or key-value store may be appropriate. If you have embeddings or need similarity search, vector-native storage enters the picture.

What are the operational constraints? Team expertise, cloud provider lock-in tolerance, licensing cost, compliance requirements (GDPR, SOC2, PCI-DSS), and whether you have a DBA on staff all constrain the feasible set before technical merit enters the conversation. A team of three engineers without PostgreSQL expertise should probably use RDS rather than self-hosting on EC2 with custom autovacuum tuning.

The Framework Table#

| Workload | Primary Pattern | Default Choice | |---|---|---| | OLTP, relational | Read/write rows, joins | PostgreSQL | | OLTP, key-value at scale | Known access patterns, single-digit ms | DynamoDB | | OLAP, cloud | Column scans, aggregations | Snowflake / BigQuery / Redshift | | OLAP, embedded | Local analytics, data engineering | DuckDB | | HTAP | Transactional + real-time analytics on same data | TiDB / SingleStore | | Distributed transactional | Global OLTP, active-active | CockroachDB / YugabyteDB | | OLTP + Vector search | Semantic search under 10M vectors | pgvector on PostgreSQL | | Vector search at scale | Over 10M vectors, filtering, speed | Qdrant / Pinecone | | Edge / serverless | Sub-millisecond local reads, global replication | Turso / Neon / D1 |

When PostgreSQL Is the Answer#

PostgreSQL is the answer more often than the industry narrative suggests. Not because it is perfect, but because its surface area is large enough to cover cases that look like they need a specialist tool.

The typical progression goes: team builds on PostgreSQL, hits a pain point, reaches for a new database, discovers that the new database has different pain points and less tooling, and eventually misses PostgreSQL. This has happened with full-text search (Elasticsearch), with JSON documents (MongoDB), and increasingly with vector similarity (Qdrant/Pinecone). PostgreSQL grew native solutions for all of these.

Hidden capabilities#

Logical replication is PostgreSQL's change data capture primitive. With wal_level = logical and a replication slot, you can stream row-level changes to Kafka, a data warehouse, or a secondary application without polling. Debezium uses this mechanism. It is production-grade and does not require a third-party CDC tool for straightforward use cases.

pg_partman automates partition creation and retention. Declare a partitioned table, configure a background worker, and it creates monthly or daily partitions ahead of time and drops old ones according to a retention policy. This matters at scale because a DELETE FROM events WHERE created_at < NOW() - INTERVAL '90 days' on a 500GB table takes minutes and causes a write spike. Dropping a partition is metadata-only and near-instant.

pg_cron runs SQL jobs on a schedule inside the database. Simple maintenance tasks, rolling up aggregates, refreshing materialised views, these do not need an external scheduler in many cases.

pgaudit generates detailed audit logs at the session or object level. For PCI-DSS or SOC2 workloads, this replaces a significant amount of application-level audit logging and is harder to bypass.

Index types beyond B-tree#

Most engineers know B-tree indexes. Very few use the others, and that is where real query performance gains hide.

GIN (Generalised Inverted Index) is built for columns where a single row contains multiple values: JSONB documents, arrays, and full-text search. If you query JSONB columns frequently, a GIN index on the column is often the difference between a 2-second query and a 5-millisecond one.

-- Full-text search index on a documents table
CREATE INDEX idx_documents_fts
ON documents USING GIN (to_tsvector('english', title || ' ' || body));

-- Query using the index
SELECT id, title
FROM documents
WHERE to_tsvector('english', title || ' ' || body) @@ plainto_tsquery('english', 'vector database production');

-- JSONB containment index
CREATE INDEX idx_events_payload ON events USING GIN (payload jsonb_path_ops);

-- Query that uses the GIN index
SELECT * FROM events WHERE payload @> '{"event_type": "payment_failed"}';

BRIN (Block Range INdex) is ideal for append-only tables where row physical order correlates with query predicates. Event tables, log tables, time-series data loaded in chronological order. A BRIN index on a 200GB events table is 128KB on disk, compared to a B-tree which would be several gigabytes. Queries are not as fast as a B-tree, but for range scans on ordered data the performance is acceptable and the storage and maintenance cost is near-zero.

-- BRIN index for a high-volume event log table
-- Only useful when rows are inserted in approximately created_at order
CREATE INDEX idx_events_created_brin
ON events USING BRIN (created_at)
WITH (pages_per_range = 64);

Partial indexes are the most underused optimisation in PostgreSQL. A partial index covers only rows that satisfy a WHERE clause. If 95% of queries only touch WHERE status = 'pending' rows and pending rows are 2% of the table, an index on (user_id, created_at) WHERE status = 'pending' is tiny, fast, and never needs to cover historical completed rows.

-- Without partial index: index covers millions of historical rows
CREATE INDEX idx_orders_user_id ON orders (user_id, created_at);

-- With partial index: index covers only active rows, fraction of the size
CREATE INDEX idx_orders_pending ON orders (user_id, created_at)
WHERE status = 'pending';

-- This query uses the partial index
SELECT * FROM orders
WHERE user_id = 12345
  AND status = 'pending'
ORDER BY created_at DESC;

Reading EXPLAIN ANALYZE#

EXPLAIN (ANALYZE, BUFFERS) is the single most valuable diagnostic tool in PostgreSQL. Three metrics decide most of the story.

When actual rows differ significantly from estimated rows, the statistics are stale. Run ANALYZE table_name or check that autovacuum is running. A planner estimating 100 rows and finding 50,000 will choose the wrong join strategy and the wrong index. This is the root cause of a surprising number of production slowdowns that appear after data migrations.

A sequential scan on a large table where a filter condition exists usually means a missing index, an index that PostgreSQL cannot use (e.g. a function wrapped around an indexed column), or statistics so wrong that the planner calculated a seq scan was cheaper. Fix the statistics first, then look at the index.

High Buffers: shared hit means pages are coming from shared memory (good). High Buffers: shared read means pages are coming from disk. If shared read is consistently high for a query you care about, either shared_buffers is too small for the working set or the data access pattern is too random for buffer cache to help.

Autovacuum and table bloat#

PostgreSQL uses MVCC for concurrency. Every UPDATE creates a new row version and marks the old one dead. Every DELETE marks a row dead. Dead rows accumulate until autovacuum removes them. On high-write tables, the default autovacuum settings are often too conservative, and dead tuple accumulation causes table bloat, which causes sequential scans to read more pages than necessary, which causes everything to slow down.

Per-table autovacuum tuning:

-- For a high-write table with frequent updates
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,    -- vacuum when 1% of rows are dead (default 0.2)
  autovacuum_vacuum_cost_delay = 2,          -- reduce vacuum throttling (default 20ms)
  autovacuum_vacuum_threshold = 100          -- minimum dead tuples before triggering
);

Monitor bloat with pgstattuple or the pg_stat_user_tables view. If n_dead_tup is consistently high relative to n_live_tup, autovacuum is not keeping up. If VACUUM FULL is your regular maintenance, something is wrong with the autovacuum configuration.

When to Choose DynamoDB#

DynamoDB is the right answer for a specific type of problem, and the wrong answer for almost everything else.

The type of problem: you need single-digit millisecond latency at arbitrary scale, your access patterns are known and stable, and the data fits a key-value or document model. User sessions, shopping carts, device state, feature flags at scale. DynamoDB delivers on this reliably and with zero operational overhead.

The wrong answer: financial ledgers, complex reporting, workloads where ad-hoc queries are the norm, or anything where access patterns are still being discovered. DynamoDB's GSI (Global Secondary Index) model lets you project access patterns you did not anticipate at table design time, but that is not the same as SQL flexibility. Every new access pattern that does not fit an existing GSI is a table scan or a new index with real cost implications.

Design for DynamoDB from the beginning. Single-table design, where related entity types coexist in one table with composite keys, is the idiomatic approach. Retrofitting relational patterns onto DynamoDB after the fact produces slow queries, expensive scans, and significant application complexity.

The Data Warehouse Decision#

For analytical workloads, the cloud-native options have converged enough that the decision is mostly about existing infrastructure and commercial preferences.

Snowflake separates compute and storage. You pay for storage continuously and for compute per second when it runs. Virtual warehouses (compute clusters) scale independently. The operational model is simple and the SQL coverage is complete. It is the default choice for teams that want a managed analytical database without cloud provider lock-in.

BigQuery is serverless. No warehouses to size, no clusters to manage. You pay per byte scanned (or with flat-rate reservations). Performance is excellent on large scans. The tight integration with Google Cloud makes it compelling for GCP-native teams.

Redshift is the AWS-native option. Redshift Serverless reduces the operational burden significantly. The key advantage is tight integration with the AWS ecosystem: S3, Glue, Lambda, Kinesis. If your data pipeline is already AWS-native, Redshift reduces friction.

The practical guidance: if you are already on AWS and your data is in S3, start with Redshift Serverless. If you want cloud-agnostic, use Snowflake. If you are Google Cloud-native, BigQuery. The performance differences between these three are marginal at typical enterprise data volumes and are dominated by how well the data models and queries are designed.

Vector Databases: The 2026 Decision#

The AI workload surge created a new category of database selection questions. The mistake most teams make is reaching immediately for a dedicated vector database before trying what they already have.

Start with pgvector#

pgvector is a PostgreSQL extension that adds vector storage, indexing, and similarity search. For most teams building their first production AI feature, it is all they need.

-- Install the extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Create a table with an embedding column
CREATE TABLE documents (
  id BIGSERIAL PRIMARY KEY,
  content TEXT,
  metadata JSONB,
  embedding vector(1536)  -- OpenAI ada-002 dimension
);

-- HNSW index: faster queries, more memory, better for latency-sensitive workloads
CREATE INDEX idx_documents_embedding_hnsw
ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- IVFFlat index: less memory, better for throughput-sensitive workloads
CREATE INDEX idx_documents_embedding_ivfflat
ON documents USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

-- Similarity search
SELECT id, content, metadata,
       1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

Choose HNSW when you need consistent low-latency queries and can afford the memory (roughly 8 bytes per dimension per vector for the index). Choose IVFFlat when memory is constrained. The rule of thumb: HNSW is the better default in 2026 because memory is cheaper than engineering time spent tuning IVFFlat recall.

pgvector becomes the wrong answer around 5 to 10 million vectors when you start noticing query latency degradation or need complex metadata filtering during vector search (not post-hoc filtering). Also, each vector similarity query against a pgvector table uses a single CPU core, which limits throughput under concurrent load.

When to move to Qdrant, Pinecone, or Weaviate#

| Dimension | Qdrant | Pinecone | Weaviate | |---|---|---|---| | Hosting | Self-hosted (also cloud) | Fully managed | Self-hosted or managed | | Scale | Billions of vectors | Billions of vectors | Hundreds of millions | | Filtering | Native payload filtering during search | Metadata filtering | Rich filtering with its object model | | Best for | Teams who want control and OSS | Teams who want zero ops | Multi-modal workloads, hybrid BM25+vector | | Cost model | Infrastructure cost | Per-write and per-query pricing | Infrastructure or managed |

Qdrant is the recommendation for self-hosted production vector search. The HNSW implementation is well-optimised, filtering happens during the graph traversal rather than post-retrieval, and the operational model (single binary, Rust) is simple. If your vectors exceed 5 million and you are hitting pgvector latency limits, Qdrant is the migration target.

Pinecone suits teams who have no interest in operating infrastructure and are building on a budget that accommodates per-query pricing. The serverless tier handles billions of vectors without provisioning anything.

Weaviate is the choice for multi-modal workloads (text, image, audio in the same index) or when hybrid search combining BM25 keyword matching with vector similarity is a first-class requirement, not an afterthought.

DuckDB VSS for batch similarity#

DuckDB now has a vector similarity search extension (VSS) that works on local Parquet or CSV files. This is not for production serving, but it is extremely useful for data engineering tasks: generating embeddings for a new corpus, computing similarity between pairs, building training datasets, validating embedding quality before deploying to production.

-- DuckDB VSS for batch offline similarity
INSTALL vss;
LOAD vss;

CREATE TABLE embeddings AS
SELECT * FROM read_parquet('embeddings.parquet');

-- Find top-5 similar documents for each query embedding
SELECT q.id AS query_id, e.id AS doc_id,
       array_distance(q.embedding, e.embedding) AS distance
FROM query_embeddings q, embeddings e
ORDER BY distance
LIMIT 5;

DuckDB in Production#

DuckDB is the embedded analytics database. It runs in-process, reads Parquet and CSV directly, and executes analytical queries on hundreds of millions of rows on a laptop in under a second. It outperforms many dedicated OLAP databases on single-node workloads.

The use case is data engineers and analysts who need fast local analytics without standing up infrastructure: processing S3 files, transforming data before loading to a warehouse, running ad-hoc queries during incident investigation, building lightweight ETL pipelines.

DuckDB does not replace PostgreSQL. It has no concurrent writer support, no row-level locking, and no server mode suitable for multi-tenant workloads. The comparison is with Pandas and Spark for local batch processing, not with PostgreSQL or Snowflake for serving transactional or multi-user analytical queries.

If you are writing Pandas code that reads a CSV, applies filters, and aggregates, the same logic in DuckDB SQL is typically 10 to 50 times faster and uses less memory. That is the practical pitch.

The AI Era Changes Database Selection#

Production AI systems introduce three database selection questions that did not exist five years ago.

Feature store or no feature store. Training-serving skew is the problem where model features computed at training time differ from features computed at serving time, causing models to perform well in evaluation and poorly in production. Feature stores (Feast, Hopsworks) solve this by centralising feature computation and making the same features available for training and online serving. Most teams do not need a feature store. If your ML team is small and your feature set is simple, a PostgreSQL table with pre-computed features is adequate. When feature reuse across multiple models becomes real and the engineering cost of consistency grows, revisit Feast.

Operational analytics and the read replica trap. When query latency increases and connection errors appear, read replicas look like the obvious fix. If the root cause is max_connections exhaustion, the replica adds infrastructure cost without solving the problem. Read replicas are the answer when read throughput genuinely exceeds primary capacity. Connection pooling is the answer when you have too many application connections exhausting the connection limit. Diagnose before provisioning.

Embedding storage. Under 10 million vectors: pgvector. Beyond that threshold, or when you need filtering-during-search rather than post-retrieval filtering: Qdrant or Pinecone. This is not a political statement. It is the breakeven point where the pgvector query overhead exceeds what most latency budgets tolerate. See the data observability guide for monitoring vector search quality drift over time.

Connection Pooling Deep Dive#

This is where junior and senior database engineers diverge. Most engineers know connection pooling exists. Few can explain the trade-offs between modes, calculate pool sizes correctly, or explain why RDS Proxy has a latency cost.

Why PostgreSQL has a hard connection limit#

Each PostgreSQL connection is a forked OS process. A connection in idle state consumes roughly 5 to 10MB of RAM for process overhead alone. At 500 connections, that is 2.5 to 5GB of RAM before a single query runs. PostgreSQL does not use a thread-per-connection model. The max_connections setting is therefore a hard RAM budget, not a soft guideline.

The practical consequence: an application server with 100 threads, each holding a connection, will run fine. A serverless function that opens a new connection per invocation at 1,000 concurrent requests will exhaust max_connections and crash.

PgBouncer transaction mode vs session mode#

PgBouncer in transaction mode multiplexes thousands of application connections to a small number of PostgreSQL connections. Each transaction borrows a server connection from the pool, and when the transaction commits, the connection returns. 10,000 application connections can route through 100 PostgreSQL connections.

The cost: you cannot use session-level PostgreSQL features. SET LOCAL, temporary tables, advisory locks, and prepared statements with named bindings do not survive across transactions because the session is not persistent.

PgBouncer in session mode maps one application connection to one server connection for the lifetime of the session. Session-level features work correctly. But session mode does not solve the connection limit problem. If you have 500 application connections in session mode, you have 500 PostgreSQL connections.

Pool size formula:

max_pool_size = (max_connections - superuser_reserved_connections - monitoring_connections)
                / number_of_application_instances

For a PostgreSQL instance with max_connections = 200, 3 reserved for superusers, 2 for monitoring, and 4 application server instances: (200 - 3 - 2) / 4 = 48 connections per instance.

A minimal PgBouncer configuration:

[databases]
myapp = host=postgres.internal port=5432 dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/users.txt
pool_mode = transaction
default_pool_size = 48
max_client_conn = 5000
server_idle_timeout = 600
client_idle_timeout = 0
log_connections = 0
log_disconnections = 0

RDS Proxy#

RDS Proxy is Amazon's managed connection pooler for RDS and Aurora. It adds approximately 1 to 2 milliseconds of latency per query — the cost of the additional network hop through the proxy layer.

Use RDS Proxy when your application is Lambda-based or when you want Aurora failover handled transparently within 30 seconds without application-level reconnection logic. Skip it for persistent application servers where PgBouncer gives you more control over pool behaviour without the latency cost.

Zero-Downtime Schema Migrations#

ALTER TABLE acquires an ACCESS EXCLUSIVE lock that blocks all reads and writes for the duration. On a large table with a large change (adding a column with a DEFAULT that requires a table rewrite, building an index), this can mean minutes of downtime on a production system.

PostgreSQL 11 eliminated the table rewrite for ADD COLUMN ... DEFAULT with a non-volatile default. Adding a column with a constant default is now metadata-only and instant. Adding a column with DEFAULT gen_random_uuid() still rewrites the table in older versions. Know your PostgreSQL version and test this.

The expand/contract pattern#

Phase 1 (expand): add the new column as nullable, alongside the old one.

ALTER TABLE payments ADD COLUMN payment_reference_v2 TEXT;

Phase 2 (backfill): populate the new column in batches.

DO $$
DECLARE
  batch_size INT := 10000;
  last_id BIGINT := 0;
  max_id BIGINT;
BEGIN
  SELECT MAX(id) INTO max_id FROM payments;
  WHILE last_id < max_id LOOP
    UPDATE payments
    SET payment_reference_v2 = 'REF-' || payment_reference
    WHERE id > last_id AND id <= last_id + batch_size;
    last_id := last_id + batch_size;
    PERFORM pg_sleep(0.05);
  END LOOP;
END $$;

Phase 3 (contract): add the NOT NULL constraint using NOT VALID to avoid a full table scan.

ALTER TABLE payments
  ADD CONSTRAINT payments_payment_reference_v2_not_null
  CHECK (payment_reference_v2 IS NOT NULL)
  NOT VALID;

ALTER TABLE payments VALIDATE CONSTRAINT payments_payment_reference_v2_not_null;

Phase 4 (drop old): once the old column is no longer referenced in application code.

ALTER TABLE payments DROP COLUMN payment_reference;
ALTER TABLE payments RENAME COLUMN payment_reference_v2 TO payment_reference;

CREATE INDEX CONCURRENTLY#

Building an index with CREATE INDEX CONCURRENTLY allows reads and writes to continue during the build. It takes roughly 2 to 3 times longer than a regular index build but does not block production traffic.

-- Regular: locks the table
CREATE INDEX idx_payments_user_id ON payments (user_id);

-- Concurrent: does not block
CREATE INDEX CONCURRENTLY idx_payments_user_id ON payments (user_id);

If the build fails partway through, it leaves an invalid index. Drop it explicitly and retry.

-- Drop invalid index and retry
DROP INDEX CONCURRENTLY idx_payments_user_id;

pgroll is a newer PostgreSQL-native migration tool that implements the expand/contract pattern as its default behaviour. For teams starting fresh with migrations, it is worth evaluating. For teams already using Flyway or Liquibase, the migration tool choice is less critical than the migration discipline: always test migrations against a production-size dataset in staging. A migration that takes 5 seconds on 100MB dev data will take 50 minutes on a 50GB production table.

The Decision I Regret#

Several years into building data infrastructure at a fintech, the team needed to track user payment methods. The product team said the schema would change frequently as the product evolved. The engineers said a document database was flexible and did not require migrations. Both statements were true and both were bad reasons to use a document database for a payment ledger.

The problems emerged over eighteen months. First, the queries. The product needed to audit all payment method changes for a user across a date range. In a relational database, this is a simple query with an index. In the document store, the query required loading documents and filtering in the application layer for some edge cases because the query planner could not use the index efficiently with the chosen document structure.

Second, transactions. One particular flow needed to update a user record, insert a payment method document, and update a wallet balance atomically. Multi-document transactions exist in most document databases, but the performance cost at scale was significant: roughly 3 to 5 times slower than single-document operations under load. The team built a compensating-transaction pattern in the application layer to avoid it, which added complexity that created bugs.

Third, the migration. Eighteen months later, the schema changes the product team predicted never arrived. The schema had been stable for a year. The team spent three months migrating the payment method data to PostgreSQL. Application code changed, dual-write patterns were needed during cutover, and two incidents occurred during the migration weekend. If the initial analysis had asked "do we actually expect the schema to change, or are we assuming it will?", the answer would have been honest: we do not know. That uncertainty was not a good reason to leave ACID transactions and joins behind.

NewSQL for Distributed Transactional Workloads#

Standard PostgreSQL on a single primary with read replicas covers most OLTP workloads up to several terabytes. When you genuinely need active-active multi-region writes, horizontal sharding, or a 99.99% SLA backed by automatic regional failover, NewSQL databases enter the picture.

CockroachDB offers the strongest consistency guarantee: serialisable isolation, PostgreSQL wire compatibility, and active-active multi-region writes. The trade-off is latency: multi-region consensus rounds add latency relative to a single-region PostgreSQL deployment. For financial services workloads where data integrity across regions is non-negotiable and latency budgets allow for 20 to 50 milliseconds, CockroachDB is a reasonable choice.

YugabyteDB is open-source and PostgreSQL-compatible. The latency profile is typically better than CockroachDB because it uses a different consensus and replication approach. For latency-sensitive global OLTP workloads where open-source licensing matters, YugabyteDB is the alternative to evaluate.

Google Spanner is the managed option: external consistency, unlimited horizontal scale, and no operational burden. The cost per node-hour is substantially higher than self-managed alternatives. For teams already in Google Cloud building globally distributed systems where the engineering cost of operating CockroachDB or YugabyteDB exceeds the Spanner premium, it is worth the price.

Do not reach for NewSQL because a single-region PostgreSQL instance feels risky. Multi-region replication with automated failover is sufficient for the vast majority of enterprise workloads. NewSQL makes sense when genuine active-active write traffic across multiple regions is required, not just active-passive failover.

HTAP: When OLTP and Analytics Share the Same Data#

Most architectures separate OLTP and OLAP: a transactional database for writes, a warehouse for analytics, and an ETL pipeline connecting them. The delay between a transaction and its visibility in analytics is typically minutes to hours.

When that delay is unacceptable, HTAP databases provide a single system serving both workloads. The key capability is a row-format storage engine for transactional access and a column-format storage engine for analytical queries, with automatic synchronisation.

TiDB is the most mature open-source option. TiFlash provides the columnar replica of TiKV (the row-store), updated with sub-second lag. SQL queries are automatically routed to the appropriate engine. The operational model is more complex than PostgreSQL. The payoff is sub-second analytical queries on data that was just written transactionally.

SingleStore is the commercial option with a stronger performance story for high-concurrency mixed workloads. It is used in financial services, telecommunications, and real-time analytics at large scale.

The honest guidance: most teams do not need HTAP. They need better ETL latency. If your pipeline from PostgreSQL to Snowflake runs in 5 minutes and the business requirement is 15-minute data freshness, you do not need HTAP. If the requirement is sub-minute freshness and the data volumes make a streaming CDC pipeline expensive to maintain, HTAP becomes a serious option.

Serverless and Edge Databases#

Neon is serverless PostgreSQL. It separates compute and storage using a custom storage layer that enables instant point-in-time branching. The killer feature: create a branch of production data for a pull request preview environment in seconds, run migrations and tests against real data shapes, and discard the branch when the PR closes. The minimum compute is 0.25 vCPU with scale-to-zero. For development, CI, and preview environments, Neon is close to the ideal solution.

Turso is SQLite at the edge, built on libSQL. Local reads are sub-millisecond because the database lives on the same machine as the application. A primary-replica model handles writes to a central location and replicates globally. For read-heavy workloads where the data fits in SQLite's capacity envelope and low latency to a globally distributed user base matters, Turso solves a real problem simply.

Cloudflare D1 is SQLite in Cloudflare Workers. If you are building on the Cloudflare Workers platform, D1 is the natural choice. No cross-cloud round trips, no connection management, queries execute in the same edge runtime as the application code.

None of these replace a production PostgreSQL deployment for OLTP. They complement it: Neon for dev environments, Turso and D1 for edge-adjacent read patterns.

Common Mistakes#

Choosing a database to match a conference talk. Someone returns from a conference, proposes the new database they heard about, and the team adopts it without evaluating whether the existing database actually has a problem. This is how teams end up maintaining six different databases.

Using Elasticsearch for primary storage. Elasticsearch is a search and analytics index, not a primary database. It does not have the consistency guarantees of a relational database, schema migrations are operationally complex, and it is expensive to run at scale. Use it as a read replica of relational data for full-text search. Do not put source-of-truth data in it.

Adding read replicas instead of fixing connection pooling. When query latency increases and connection errors appear, read replicas look like the obvious fix. If the root cause is max_connections exhaustion, the replica adds infrastructure cost and operational complexity without solving the problem. Diagnose first.

Normalising too aggressively in analytical systems. Third normal form is correct for OLTP. Denormalised star schemas are correct for OLAP. Joining 15 tables in Redshift or Snowflake because the data was modelled as if it were serving a transactional application produces slow and expensive queries.

Ignoring the migration path. Every technology decision is also a decision about what you will do when you need to change it. Before committing to a proprietary managed service, understand the data export mechanisms, the tooling ecosystem, and the realistic migration path if the pricing or direction changes.

Not running ANALYZE after a large data load. Loading 50 million rows into PostgreSQL and immediately querying does not give autovacuum time to update statistics. Run ANALYZE table_name explicitly after bulk loads.

Migration Reality#

The best migration is the one that never happens.

Migrations fail on underestimated effort. The data movement itself is rarely the hard part. It is the application code changes, the dual-write period needed for a safe cutover, the edge cases that only appear against production data volumes, and the operational knowledge transfer to a new system. Budget at minimum three times what the technical estimate suggests.

The signal that a migration is genuinely necessary: the team spends more time working around the database than building features. Caching layers that exist to hide read latency, application-level join logic that exists because the database cannot do it efficiently, compensating transactions written in application code to paper over missing ACID guarantees. These are symptoms. Diagnose the cause before prescribing a new database.

Incremental migration beats big-bang migration. Identify the bounded contexts most constrained by the current database. Migrate them first, learn from the process, and carry the lessons into subsequent phases.

My Default Stack 2026#

Primary OLTP: PostgreSQL 17 on managed infrastructure (RDS or Cloud SQL). Not the latest exotic option. The most mature, best-supported, best-tooled relational database available.

Connection pooling: PgBouncer in transaction mode deployed alongside the application, unless the application is Lambda-based, in which case RDS Proxy.

Vector search: pgvector as the first choice, with HNSW indexing. Qdrant when vectors exceed 5 million or filtering-during-search performance becomes a bottleneck.

Analytics: DuckDB for local and data engineering workloads. Snowflake for team-shared analytical queries above 100GB data volumes.

Schema migrations: Flyway for versioned SQL migrations. Expand/contract pattern for all changes that touch live traffic. CREATE INDEX CONCURRENTLY as the default for any index on a table with more than 1 million rows.

Cache: Redis or DragonflyDB. Not a database. Sits in front of the database and handles hot-path reads that PostgreSQL should not be serving under high concurrency.

The Decision Process#

When selecting a database, work through these questions in order.

What does PostgreSQL not do well enough for this workload? If you cannot answer this concretely with a benchmark or a specific operational constraint, start with PostgreSQL.

What are the access patterns, and are they stable enough to specialise for? DynamoDB requires stable access patterns at design time. Vector databases require knowing the embedding dimension and filtering requirements. If the access patterns are still being discovered, a general-purpose relational database preserves options.

What is the team's operational expertise? A technically superior database that the team cannot operate safely is worse than a familiar database they can maintain under pressure.

What is the failure mode? What happens when this database is unavailable, when a query is slow, when disk is full? Familiar failure modes are easier to respond to at 2am.

Is there a regulatory or compliance constraint? Some industries require specific audit logging, data residency, or encryption postures that constrain the feasible set before technical merit enters.

The answers rarely point to an obvious single winner. Database selection is about trade-offs, and the right trade-off depends on constraints specific to your team, your workload, and your organisation.

Frequently Asked Questions#

When should you choose PostgreSQL over other databases?#

PostgreSQL is the right choice for most OLTP workloads where you need ACID transactions, a relational data model, and mature tooling. It also handles more than people expect, including JSON documents via JSONB, full-text search, time-series data with TimescaleDB, and geospatial queries with PostGIS. Before reaching for a specialised database, ask whether PostgreSQL can do the job well enough.

When should you use DynamoDB instead of a relational database?#

DynamoDB is the right choice when you can define access patterns upfront, need single-digit millisecond latency at any scale, your data fits key-value or document patterns, and you want zero operational overhead. Avoid DynamoDB when you need ad-hoc SQL queries, your access patterns are unknown, you require complex cross-item transactions, or you are not on AWS. The key principle is to design for DynamoDB from the start rather than retrofitting relational patterns.

How do you approach database migration in enterprise systems?#

The best database migration is the one you never have to do, which is why choosing carefully upfront matters. Migrations are expensive because they involve data transfer and transformation, application code changes, testing and validation, downtime or complex cutover procedures, and retraining teams. If you find yourself constantly working around database limitations with caching layers or application-level transactions, that is a sign you chose the wrong database and migration may be warranted.