Database
Database design and migration best practices
Best Practices
Expand and Contract Database Migration Pattern
A zero-downtime schema change technique that adds new structures, migrates reads and writes in phases, then removes the old structures once nothing depends on them.
by Martin Fowler / Pramod SadalageProducts & Technologies
PostgreSQL
Powerful, open source object-relational database system
MySQL
Popular open-source relational database management system
MariaDB
Community-developed fork of MySQL
MongoDB
Document-oriented NoSQL database for modern applications
Redis
In-memory data structure store and cache
Elasticsearch
Distributed search and analytics engine
Apache Cassandra
Distributed NoSQL database for high availability
Apache CouchDB
Document-oriented database with HTTP API
Neo4j
Native graph database for connected data
InfluxDB
Time series database for metrics and events
ClickHouse
Column-oriented database for real-time analytics
TimescaleDB
Time-series database built on PostgreSQL
CockroachDB
Distributed SQL database for global applications
PlanetScale
Serverless MySQL-compatible database platform
Neon
Serverless PostgreSQL with autoscaling
Turso
Edge-hosted distributed database based on libSQL
Patterns
Sharding
Horizontally partitions a data store into independent shards so capacity and load scale beyond a single node.
Index Table
Create secondary index tables over data stores queried by non-key fields to speed up lookups that would otherwise scan.
Materialized View
Precompute and store read-optimized views of data so expensive queries become fast lookups against ready-made results.
Materialized View
A precomputed, stored result set that trades storage and refresh cost for fast reads of expensive queries, aggregations, or denormalized projections.
Star Schema
A dimensional data-modeling pattern with a central fact table linked to denormalized dimension tables, optimized for fast, intuitive analytical queries.
Optimistic Concurrency Control
A concurrency strategy that lets transactions proceed without locking and validates at commit, retrying on conflict, assuming conflicts are rare.
Pessimistic Locking
A concurrency strategy that acquires locks on data before use to prevent concurrent modification, ensuring correctness under high contention at the cost of throughput.
Two-Phase Commit (2PC)
A distributed-transaction protocol that coordinates multiple participants to commit or abort atomically through a prepare phase and a commit phase.
Tutorials
Migrating from MySQL to PostgreSQL
Complete guide to migrating databases from MySQL to PostgreSQL
Database Management with Prisma
Set up Prisma ORM for type-safe database access
How to build a CRUD API on Amazon DynamoDB
Model data and implement create, read, update, and delete operations against Amazon DynamoDB from Node.js.
How to build a CRUD API on Azure Cosmos DB
Model documents and perform CRUD operations against Azure Cosmos DB for NoSQL from a .NET application.
How to build a CRUD app on Google Cloud Firestore
Model collections and perform CRUD operations and queries against Google Cloud Firestore from a web app.
How to provision a managed PostgreSQL database on Cloud SQL
Provision, secure, and connect to a managed PostgreSQL instance on Google Cloud SQL with private IP.
How to design and tune PostgreSQL indexes for query performance
Find slow queries with EXPLAIN, choose the right index types, and verify gains so reads stay fast without bloating writes.
How to manage versioned database migrations with Flyway
Set up Flyway, write versioned SQL migrations, and apply them repeatably across environments with validation and history tracking.
How to manage database migrations with Liquibase changelogs
Define schema changes in declarative Liquibase changesets, apply and roll them back, and track state across environments.
How to run SQLAlchemy database migrations with Alembic
Generate, edit, and apply Alembic migrations for a Python SQLAlchemy project, including autogeneration and safe downgrades.
How to evolve a database schema with Prisma Migrate
Use Prisma Migrate to turn schema.prisma changes into versioned SQL migrations and apply them in dev and production safely.
How to perform a zero-downtime database schema migration
Use the expand-and-contract pattern to change a live schema without downtime, keeping old and new code compatible during rollout.
How to set up MySQL primary-replica replication
Configure binary log replication from a MySQL primary to a replica for read scaling and high availability, then verify it.
How to design an effective MongoDB document schema
Model data for MongoDB by choosing embedding vs referencing based on access patterns, then index and validate the result.
How to implement the cache-aside pattern with Redis
Add a Redis read-through cache with TTLs and safe invalidation to reduce database load, including stampede protection.
How to partition a large PostgreSQL table by range
Use declarative range partitioning to split a large table by time, improving query pruning, maintenance, and data retention.
How to shard a MongoDB collection for horizontal scale
Enable sharding on a MongoDB cluster, choose a shard key, and distribute a collection across shards with verified balancing.
How to set up vector similarity search with pgvector
Install pgvector in PostgreSQL, store embeddings, build an ANN index, and run nearest-neighbor queries for semantic search.
How to store and query time-series data with TimescaleDB
Turn a PostgreSQL table into a TimescaleDB hypertable, add continuous aggregates, and apply retention for time-series workloads.
How to migrate a PostgreSQL database using logical replication
Move a PostgreSQL database to a new server with near-zero downtime using publications, subscriptions, and a final cutover.
How to design Elasticsearch index mappings and analyzers
Define explicit Elasticsearch mappings, configure analyzers for text search, and index documents for fast, accurate queries.
How to model an analytics table in ClickHouse
Create a MergeTree table in ClickHouse with the right ordering key and partitioning, then load data and run fast aggregations.
Checklists
Database Migration Checklist
Detailed checklist for safely migrating databases with minimal downtime
Database Migration Cutover Checklist
Step-by-step verification for safely switching production traffic from a source database to a migrated target with minimal risk.
Oracle to PostgreSQL Pre-Flight Checklist
Pre-migration readiness checks for moving an Oracle database to PostgreSQL, covering data types, PL/SQL, and feature gaps.
Zero-Downtime Database Migration Checklist
Checks for migrating a production database with no service interruption using dual-write, CDC, and gradual cutover techniques.
Schema Change Safety Checklist
Safety checks for applying database schema changes to production without locking tables or breaking running applications.
Backup and Restore Verification Checklist
Verification checks confirming database and data backups are complete, secure, and reliably restorable within recovery targets.
MongoDB to PostgreSQL Migration Checklist
Readiness checks for migrating a document database on MongoDB to a relational PostgreSQL model.
NoSQL Data Modeling Review Checklist
A review checklist for validating NoSQL data models against access patterns, partitioning, and scalability before migration.
Database Version Upgrade Checklist
Checks for upgrading a database engine to a new major version with minimal risk to data integrity and availability.
SQL Server to PostgreSQL Migration Checklist
Pre-migration readiness checks for moving a Microsoft SQL Server database to PostgreSQL, covering T-SQL, data types, and feature gaps.
FAQs
What is the difference between SQL and NoSQL databases?
SQL (relational) databases store data in tables with fixed schemas and use SQL for queries, offering strong consistency and powerful joins; examples include PostgreSQL, MySQL, and SQL Server. NoSQL databases trade rigid schemas for flexibility and horizontal scale, and come in families such as document (MongoDB), key-value (Redis), wide-column (Cassandra), and graph (Neo4j). Use SQL when you need complex relationships, transactions, and ad-hoc queries; reach for NoSQL when you need schema flexibility, very high write throughput, or scale-out across many nodes. Many systems use both, choosing the right store per workload.
What does ACID mean in databases?
ACID is a set of guarantees that make database transactions reliable: Atomicity (a transaction either fully completes or fully rolls back), Consistency (a transaction moves the database from one valid state to another), Isolation (concurrent transactions do not interfere with each other), and Durability (committed changes survive crashes). These properties are the foundation of relational databases and are essential for workloads like financial transfers. The opposing model, often called BASE, relaxes some of these guarantees for higher availability and scale.
What is the CAP theorem?
The CAP theorem states that a distributed data store can provide at most two of three guarantees at the same time: Consistency (every read sees the latest write), Availability (every request gets a non-error response), and Partition tolerance (the system keeps working despite network failures between nodes). Because network partitions are unavoidable in real distributed systems, the practical choice is between consistency and availability during a partition. CP systems reject some requests to stay consistent, while AP systems stay available but may return stale data. In practice, partition tolerance is a given, so the trade-off is really CP versus AP.
What is database sharding?
Sharding is a horizontal partitioning technique that splits a large dataset across multiple database instances, each holding a subset of the rows. A shard key (such as a user ID) determines which shard a record lives on, letting the system spread storage and query load across many servers. It enables scale beyond what a single machine can handle but adds complexity: cross-shard queries, joins, and transactions become harder, and choosing a poor shard key can create hot spots. Sharding is typically a last resort after vertical scaling, read replicas, and caching are exhausted.
What is the difference between normalization and denormalization?
Normalization organizes data to eliminate redundancy by splitting it into related tables, which reduces anomalies and keeps a single source of truth for each fact. Denormalization deliberately reintroduces redundancy, duplicating data across tables to avoid expensive joins and speed up reads. Normalized schemas favor write integrity and storage efficiency, while denormalized schemas favor read performance at the cost of more complex updates. OLTP systems lean toward normalization, whereas analytics and read-heavy systems often denormalize.
What is a database index and why does it matter?
An index is a separate data structure, usually a B-tree, that lets the database find rows matching a query without scanning the entire table. It dramatically speeds up reads on indexed columns, much like the index in the back of a book. The trade-off is that indexes consume storage and slow down writes, since every insert, update, or delete must also maintain the index. Choosing the right columns to index, and avoiding redundant indexes, is one of the most impactful database tuning tasks.
What is eventual consistency?
Eventual consistency is a model in distributed systems where replicas may temporarily hold different values, but if no new writes occur they will all converge to the same value over time. It trades the immediate, strong consistency of ACID systems for higher availability and lower latency, which is the AP side of the CAP theorem. It is well suited to use cases that tolerate brief staleness, such as social feeds, shopping carts, or DNS, but poorly suited to operations like bank balances that demand strict accuracy. Techniques like read-your-writes consistency and conflict resolution help manage the trade-offs.
What is the difference between a primary key and a foreign key?
A primary key uniquely identifies each row in a table and cannot be null or duplicated; it is the column (or set of columns) the database uses to address a specific record. A foreign key is a column in one table that references the primary key of another table, establishing a relationship and enforcing referential integrity so you cannot reference a row that does not exist. For example, an orders table might have a customer_id foreign key pointing to the customers table's primary key. Together they model relationships and keep the data consistent across tables.
What is a materialized view?
A materialized view is a database object that stores the precomputed result of a query physically on disk, unlike a regular view which runs its query every time it is accessed. This makes reads of expensive aggregations or joins very fast, at the cost of stale data and the need to refresh. Refreshes can be manual, scheduled, or in some databases incremental, updating only what changed. Materialized views are common in analytics and reporting where the same heavy query is run repeatedly and slight staleness is acceptable.
When should I use Redis?
Redis is an in-memory key-value data store known for very low latency, making it ideal as a cache in front of a slower primary database. Beyond caching, it is widely used for session storage, rate limiting, leaderboards, real-time counters, pub/sub messaging, and simple queues, thanks to rich data structures like sorted sets and hashes. Because data lives in memory, it is fast but capacity is bounded by RAM and durability requires configuring persistence or replication. Use it to offload read-heavy or latency-sensitive workloads, not as the system of record for critical data unless durability is carefully configured.
What is a vector database?
A vector database stores and searches high-dimensional vector embeddings, the numeric representations of text, images, or other data produced by machine learning models. Instead of exact matching, it finds the nearest vectors by similarity using approximate nearest neighbor (ANN) algorithms such as HNSW, enabling semantic search. They are central to AI applications like retrieval-augmented generation (RAG), recommendation systems, and image search. Options include dedicated stores like Pinecone, Milvus, Qdrant, and Weaviate, as well as vector extensions for existing databases such as pgvector for PostgreSQL.
What is database replication?
Replication keeps copies of a database on multiple servers, typically a primary that accepts writes and one or more replicas that receive the changes. It improves read scalability by spreading queries across replicas, increases availability through failover if the primary fails, and can place data closer to users geographically. Replication can be synchronous, where a write waits for replicas to confirm (stronger consistency, higher latency), or asynchronous, where replicas lag slightly behind (faster writes, possible stale reads). It differs from backups, which protect against data loss but are not live copies serving traffic.
Benchmarks
TPC-C
The classic OLTP benchmark simulating an order-entry warehouse workload, measuring transactions per minute (tpmC) and price/performance.
TPC-H
An ad-hoc decision-support benchmark of 22 complex analytical queries over a star-like schema, reporting query throughput and power at fixed scale factors.
TPC-DS
A modern decision-support benchmark with 99 queries over a multi-snowflake retail schema, designed to stress complex analytics, data loading, and concurrency.
TPC-E
An OLTP benchmark modeling a brokerage firm with realistic data and read-heavy transactions, reporting tpsE as a more representative successor to TPC-C.
YCSB
The Yahoo! Cloud Serving Benchmark for NoSQL and key-value stores, defining standard read/write workload mixes to compare throughput and latency.
sysbench
A scriptable, multi-threaded benchmark tool widely used for MySQL/PostgreSQL OLTP tests as well as CPU, memory, and file I/O microbenchmarks.
pgbench
PostgreSQL's built-in benchmarking tool that runs a TPC-B-like transaction load and custom scripts to measure transactions per second and latency.
HammerDB
An open-source database load-testing tool implementing TPROC-C and TPROC-H (TPC-C/TPC-H-derived) workloads across many relational engines.
BenchBase
An extensible Java framework (successor to OLTPBench) for benchmarking relational databases with many built-in workloads via a common JDBC harness.
ClickBench
An open benchmark for analytical databases using a single wide web-analytics table and 43 queries to compare cold and hot OLAP query latency.
Star Schema Benchmark (SSB)
A simplified TPC-H derivative using a classic star schema and 13 queries in four flights to measure data-warehouse query performance.
OLTP vs OLAP Latency Benchmark
A comparative benchmark category contrasting transactional (OLTP) point-operation latency with analytical (OLAP) scan-and-aggregate latency to evaluate HTAP systems.
ANN-Benchmarks
The standard open benchmark for approximate nearest neighbor search, plotting recall against queries-per-second across vector index libraries and databases.
BigANN Benchmark
A billion-scale approximate nearest neighbor benchmark testing vector search algorithms on large data sets with constraints on memory, throughput, and recall.
Time-Series Ingestion Benchmark
A benchmark category measuring write throughput, query latency, and compression for time-series databases under high-cardinality metric and event ingestion.
In-Memory Store Benchmark (memtier/redis-benchmark)
Benchmarks for in-memory data stores like Redis and Memcached, measuring operations per second and sub-millisecond latency under varied key/value and pipeline settings.
LDBC Social Network Benchmark
The standard benchmark for graph databases, measuring interactive transactional and analytical query performance over a realistic, correlated social-network graph.