Database

Repository

Mediates between the domain and data mapping layers via a collection-like interface for accessing domain objects, hiding persistence details.

Unit of Work

Tracks objects affected by a business transaction and coordinates writing out changes and resolving concurrency as a single commit.

Data Mapper

Moves data between objects and a database while keeping them independent, so domain objects carry no persistence knowledge.

Active Record

Wraps a database row in an object that carries both the data and the methods to load, save, and delete itself.

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.

Write-Through Cache

A caching strategy where every write goes to the cache and the backing store synchronously, keeping the two consistent at the cost of write latency.

Materialized View

A precomputed, stored result set that trades storage and refresh cost for fast reads of expensive queries, aggregations, or denormalized projections.

Polyglot Persistence

An architecture that uses multiple, purpose-fit data stores within one system, matching each store's strengths to each data access pattern.

Star Schema

A dimensional data-modeling pattern with a central fact table linked to denormalized dimension tables, optimized for fast, intuitive analytical queries.

Slowly Changing Dimension (SCD)

Techniques for handling changes to dimension attributes over time in a data warehouse, ranging from overwriting to preserving full historical versions.

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.

Pagination

Splits a large result set into smaller pages so APIs and UIs can return and traverse data incrementally instead of loading everything at once.

Anti-Patterns21

Shared Database Integration

Multiple services directly sharing the same database tables

N+1 Query Problem

Loading a list, then firing one extra query per row to fetch related data, turning a single page load into hundreds of round-trips.

God Table

A single table accumulating dozens or hundreds of unrelated columns for many concepts, becoming a contention and maintenance bottleneck.

Entity-Attribute-Value (EAV) Abuse

Storing arbitrary attributes as rows of name/value pairs to fake a schemaless model, sacrificing type safety, integrity, and query performance.

One True Lookup Table (OTLT)

Cramming every reference list into one generic lookup table keyed by category, defeating foreign keys and mixing unrelated domains.

SELECT * Everywhere

Querying all columns by default instead of naming the ones you need, wasting I/O and creating brittle coupling to schema order and shape.

Implicit Columns in INSERT

Writing INSERT statements without naming columns, relying on positional order so a schema change silently misaligns or corrupts data.

Premature Denormalization

Duplicating data across tables for speed before any measured need, creating update anomalies and integrity bugs to solve a problem you may not have.

Over-Normalization

Splitting data into so many tables that every read requires a sprawl of joins, hurting performance and readability with no integrity benefit.

Storing Everything as JSON Blobs

Dumping structured data into opaque JSON text columns by default, abandoning constraints, indexing, and queryability the database would provide.

Natural Primary Keys Misuse

Using mutable, business-meaningful values like email or SSN as primary keys, so a real-world change cascades breakage through every referencing row.

Soft Delete Everywhere

Adding an is_deleted flag to every table by default, so all queries must filter it, integrity decays, and tables bloat with dead rows.

Polling the Database

Repeatedly querying a table on a tight loop to detect changes instead of using events or notifications, wasting resources and adding latency.

Chatty Data Access

Making many fine-grained round-trips to the database for one logical operation, so network latency dominates and throughput collapses under load.

No Connection Pooling

Opening a fresh database connection per request or query and closing it after, paying high handshake cost and exhausting connection limits under load.

Timestamp Without Time Zone

Storing instants in local time without zone information, so values are ambiguous across regions and DST shifts, corrupting ordering and reporting.

Floating-Point for Money

Storing monetary amounts in binary floating point, so rounding errors accumulate and totals fail to reconcile to the cent.

Schemaless Sprawl

Treating a schemaless store as license to skip data design, so documents drift into inconsistent shapes that no consumer can reliably read.

Hot Partition (Hot Shard)

A partitioning key that sends a disproportionate share of traffic to one shard, overloading it while the rest sit idle and capping the system at one node's throughput.

SQL Injection via String Concatenation

Building SQL queries by concatenating untrusted input into the query string, letting attackers alter query logic and access or destroy data.

Leaky API Abstraction

An API that exposes internal database schemas, implementation details, or storage structures, coupling clients to internals and blocking safe evolution.

Tutorials24

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 run stateful workloads with a Kubernetes StatefulSet

Deploy databases and other stateful apps with stable network identities and per-pod persistent storage.

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.

How to add semantic search with embeddings and a vector database

Add meaning-based search to an app by generating text embeddings and querying a vector database for nearest neighbors.

Blueprints9

MySQL to PostgreSQL Blueprint

Complete migration guide from MySQL to PostgreSQL

Oracle to PostgreSQL Migration Blueprint

Migrate an Oracle Database workload to PostgreSQL, converting PL/SQL, data types, and schema while preserving data integrity.

SQL Server to PostgreSQL Migration Blueprint

Migrate Microsoft SQL Server to PostgreSQL, converting T-SQL, data types, and schema to remove licensing cost.

MySQL to Amazon Aurora Migration Blueprint

Migrate self-managed MySQL to Amazon Aurora MySQL-Compatible for managed scaling, HA, and reduced operational toil.

On-Prem Database to Cloud Managed Database Blueprint

Move an on-premises relational database to a cloud managed service (RDS, Cloud SQL, or Azure Database) for elasticity and reduced ops.

Relational to MongoDB Migration Blueprint

Re-model a normalized relational schema into MongoDB document collections for flexible schemas and scale-out reads.

Relational to Apache Cassandra Migration Blueprint

Migrate a relational workload to Apache Cassandra with query-first data modeling for write-heavy, globally distributed scale.

Elasticsearch to OpenSearch Migration Blueprint

Migrate an Elasticsearch cluster to OpenSearch to stay on an Apache 2.0-licensed, community-driven search engine.

Self-Managed Elasticsearch to Amazon OpenSearch Service Blueprint

Move a self-managed Elasticsearch cluster to the managed Amazon OpenSearch Service for reduced operational overhead.

Migrations16

Ad-hoc SQL to Flyway Migration

Adopt Flyway for repeatable database schema migrations and environment parity

Ad-hoc SQL to Liquibase Migration

Adopt Liquibase changelogs for auditable, repeatable schema migrations

DynamoDB to PostgreSQL Migration

Migrate DynamoDB single-table designs to relational PostgreSQL with new schema and access patterns

Entity Framework 6 to EF Core Migration

Migrate Entity Framework 6 data access to EF Core with updated mappings and migrations

Hibernate ddl-auto to Flyway Migration

Replace Hibernate schema auto-generation with explicit Flyway migrations

Liquibase to Flyway Migration

Move from Liquibase changelogs to Flyway versioned SQL (or Java) migrations

MySQL to Aurora PostgreSQL Migration

Migrate MySQL to Aurora PostgreSQL including schema translation and cutover

MySQL to PostgreSQL Migration

Migrate MySQL schema and data to PostgreSQL, translating queries and tuning indexes

Oracle Database to SQL Server Migration

Migrate Oracle schemas and PL/SQL workloads to SQL Server with T-SQL translation and operational redesign

Oracle to PostgreSQL Migration

Migrate Oracle Database to PostgreSQL with PL/SQL conversion

PostgreSQL 12 to PostgreSQL 16 Migration

Major-version upgrade PostgreSQL 12 → 16 with extension checks, replication/cutover, and performance validation

PostgreSQL to Aurora PostgreSQL Migration

Move PostgreSQL to managed Aurora PostgreSQL with minimal downtime using replication

SQL Server to Aurora PostgreSQL Migration

Migrate SQL Server to Aurora PostgreSQL with T-SQL rewrites and managed Postgres operations

SQL Server to Azure SQL Migration

Move SQL Server workloads to Azure SQL with compatibility checks and operational runbooks

SQL Server to PostgreSQL Migration

Migrate from SQL Server to PostgreSQL including stored procedures

SQLite to PostgreSQL Migration

Migrate an app from embedded SQLite to PostgreSQL for concurrency, HA, and operational tooling

Products14

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

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

PlanetScale

Serverless MySQL-compatible database platform

Neon

Serverless PostgreSQL with autoscaling

Turso

Edge-hosted distributed database based on libSQL

Prisma

Next-generation Node.js and TypeScript ORM

Reference Architectures5

Modern Data Warehouse on Snowflake

A cloud data warehouse on Snowflake with ELT ingestion, virtual warehouses, and governed marts feeding BI across Azure.

BigQuery Analytics Platform

A serverless analytics platform on Google BigQuery with streaming ingestion, dbt transforms, and Looker for enterprise BI.

HTAP OLTP and OLAP Platform

A hybrid transactional and analytical platform that serves OLTP and OLAP on one distributed SQL store with columnar replicas.

Columnar OLAP Analytics with ClickHouse

A high-speed columnar OLAP platform on ClickHouse for sub-second aggregation over billions of rows, deployed on Kubernetes.

Graph Analytics Platform

A graph analytics platform on GCP using a property graph database for relationship-heavy queries, fraud detection, and recommendations.

Playbooks5

Oracle to PostgreSQL Migration Program Playbook

Run an enterprise program to migrate Oracle databases to PostgreSQL, covering schema conversion, PL/SQL rewrite, data movement, and cutover.

Zero-Downtime Database Migration Program Playbook

Migrate a production database engine or version with no downtime using dual-write, CDC replication, shadow reads, and staged cutover.

SQL Server to PostgreSQL Migration Program Playbook

Migrate SQL Server databases to PostgreSQL, converting T-SQL, schema, and data with validated cutover to cut licensing costs.

MongoDB to PostgreSQL Migration Program Playbook

Migrate document data from MongoDB to PostgreSQL, modeling collections into relational and JSONB schemas with validated cutover.

Vector Database Adoption Playbook

A program to adopt a vector database for semantic search and RAG, covering embeddings, indexing, scaling, and operations.

Checklists10

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.

Regulations1

Regulation

Israel Protection of Privacy Law, 5741-1981

Israel's foundational privacy law governing databases of personal information, recognized as EU-adequate and modernized by Amendment 13.

Comparisons17

RDS vs Aurora

Amazon RDS runs standard managed database engines; Amazon Aurora is AWS's cloud-native engine with a distributed storage layer offering higher performance and availability.

MongoDB vs DynamoDB

Document database with rich querying versus a fully managed key-value and document store built for predictable scale on AWS.

DynamoDB vs Cassandra

Managed AWS wide-column key-value store versus open-source Apache Cassandra for write-heavy, distributed workloads.

Redis vs Memcached

Feature-rich in-memory data structure store versus a lean, multi-threaded distributed memory cache.

Elasticsearch vs OpenSearch

The original search and analytics engine versus its Apache-licensed open-source fork led by AWS.

ClickHouse vs Druid

Columnar OLAP database for fast analytics versus Apache Druid's real-time analytics datastore for time-series and event data.

Postgres vs CockroachDB

The versatile single-node-first relational database versus a distributed, Postgres-compatible SQL database built for global scale.

MySQL vs MariaDB

The widely deployed relational database now under Oracle versus its community-driven fork with diverging features.

Aurora vs RDS

Amazon's cloud-native, high-performance database engine versus standard managed RDS running stock database engines.

SQLite vs Postgres

An embedded, serverless, single-file database versus a full-featured client-server relational database system.

Neo4j vs ArangoDB

A dedicated native graph database versus a multi-model database supporting graph, document, and key-value in one engine.

pgvector vs Pinecone

A Postgres extension that adds vector search to your existing database versus a fully managed, purpose-built vector database.

PostgreSQL vs Cassandra

A relational ACID database for complex queries versus a distributed wide-column store built for write-heavy linear scale.

Redis vs DynamoDB

An in-memory data store for ultra-low-latency caching and structures versus a durable, managed NoSQL database for scale.

MySQL vs MongoDB

A widely used relational database with structured schemas versus a flexible document database for evolving data.

TimescaleDB vs InfluxDB

A Postgres extension that turns SQL into a time-series database versus InfluxDB, a purpose-built time-series platform.

pgvector vs Dedicated Vector Database

pgvector adds vector search to PostgreSQL; a dedicated vector database is purpose-built. The choice trades operational simplicity against scale and specialized features.

Benchmarks17

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.

FAQs15

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 i...

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), Consistenc...

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 la...

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 s...

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 f...

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 drama...

What is the difference between OLTP and OLAP?

OLTP (Online Transaction Processing) handles many short, concurrent transactions like orders, payments, and updates, optimized for fast writes and row...

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 ...

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 addr...

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 ...

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 cachin...

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 machi...

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....

What is data partitioning?

Partitioning divides a large table or dataset into smaller, manageable pieces based on a key such as date, region, or category. In databases it improv...