Database Design 2025 FAQ & Answers

PostgreSQL provides 7 index types: (1) B-tree - default, handles equality and range queries (=, <, >, BETWEEN), works on sortable data, most common, (2) BRIN - Block Range INdexes, stores min/max summaries per block range, best for large time-series tables with natural ordering, (3) GiST - infrastructure for complex types (spatial, full-text, similarity), supports nearest-neighbor searches, (4) SP-GiST - space-partitioned GiST for non-balanced structures, (5) GIN - inverted indexes for arrays, JSONB, full-text search, (6) Hash - equality only (=), rarely used since B-tree improved, (7) Bloom - multi-column queries (extension). Choice depends on query patterns and data types. 2025 recommendation: Start with B-tree, use BRIN for large ordered tables, GiST for spatial data, GIN for JSONB/arrays.

B-tree is PostgreSQL's default index type - use for most queries. Best for: (1) Equality queries - WHERE id = 123, (2) Range queries - WHERE created_at BETWEEN date1 AND date2, (3) Sorting - ORDER BY name, (4) Pattern matching - WHERE name LIKE 'prefix%' (prefix only, not '%suffix'), (5) Null checks - IS NULL/IS NOT NULL. Supports: <, <=, =, >=, >, BETWEEN, IN, IS NULL operators. Performance: Log(N) lookup time. Works on any sortable data (numbers, strings, dates, UUID). 2025 usage: 90%+ of indexes are B-tree. Create by default: CREATE INDEX idx_users_email ON users(email). Don't use for: Full-text search (use GIN), spatial queries (use GiST), very large tables with sequential inserts (consider BRIN).

BRIN (Block Range INdexes) for very large tables with naturally ordered data. How it works: Stores only min/max value ranges per block (not individual values), tiny index size (1000x smaller than B-tree for large tables). Best for: (1) Time-series data with timestamp-based queries, (2) Auto-incrementing IDs, (3) Append-only tables (logs, events, sensors), (4) Tables >100GB where data correlates with physical order. Example: Logs table with created_at always increasing. Benefits: Minimal storage, fast inserts (no index maintenance overhead), efficient range scans on ordered data. Don't use: Random data order, frequent updates, small tables (<1M rows), exact equality lookups. 2025 pattern: BRIN for logs/events (WHERE created_at > now() - interval '1 day'), B-tree for user lookups.

GiST (Generalized Search Tree) is extensible indexing framework for complex data types beyond simple equality/range. Acts as template for arbitrary indexing schemes. Use cases: (1) Spatial/geometric queries - PostGIS with R-tree for coordinates (find nearby, polygon overlap, contains operators), (2) Full-text search - tsvector for ranking (GIN faster for lookups, GiST better for dynamic data with <100k lexemes), (3) Range types - daterange/int4range overlap/containment (@>, <@, &&), (4) Nearest-neighbor - ORDER BY <-> LIMIT N for proximity searches, (5) Similarity - pg_trgm for fuzzy matching. Example: CREATE INDEX idx_locations ON places USING gist(coordinates). Trade-offs: Larger than B-tree, slower updates, excellent for specialized queries. PostgreSQL 18 (2025) maintains GiST as core infrastructure. Choose GIN for JSONB/arrays, GiST for spatial/similarity. Essential for location-based apps, mapping services.

Covering index contains all columns needed by query, enabling index-only scans without table access. INCLUDE clause adds payload columns (not part of search key). Syntax: CREATE INDEX idx_users_email ON users(email) INCLUDE (name, created_at). Query covered: SELECT name, created_at FROM users WHERE email = 'x' reads only index. Benefits: (1) Index-only scans avoid heap access, (2) Suffix truncation removes INCLUDE columns from upper B-tree levels (smaller, cache-friendly), (3) Faster queries on slow-changing tables. INCLUDE advantages: Unique constraint on prefix only (CREATE UNIQUE INDEX ON tab(x) INCLUDE (y)), supports non-orderable types. Trade-offs: Larger index size, slower writes, bloat if overused. 2025 best practice: Conservative with wide columns (duplicate data, slow searches), effective when table changes slowly. PostgreSQL 18 (Nov 2025) B-tree only. Example: user_id INCLUDE (status, role) for dashboards. Don't include TEXT/JSONB unless essential.

Decision tree: (1) Simple equality/range on sortable data? → B-tree (default, 90% of cases), (2) Very large table (>100GB) with natural ordering (timestamps, IDs)? → BRIN (1000x smaller index), (3) JSONB/array containment queries? → GIN (for @>, ?, ?& operators), (4) Spatial/geographic queries? → GiST with PostGIS, (5) Full-text search? → GIN for tsvector (faster) or GiST for ranking, (6) Nearest-neighbor/similarity? → GiST with <-> operator. Start with B-tree by default. Measure query performance with EXPLAIN ANALYZE. Switch to specialized index only if: (1) Query is slow with B-tree, (2) Workload matches specialized type, (3) Index size is concern (BRIN). 2025 recommendation: B-tree for OLTP, BRIN for time-series, GIN for JSONB, GiST for spatial. Don't prematurely optimize - profile first.

Horizontal partitioning splits table by rows into multiple tables (partitions) with same schema. Each partition contains subset of rows based on partition key. Methods: (1) Range - partition by date ranges (e.g., orders_2024, orders_2025), (2) Hash - partition by hash of key for uniform distribution, (3) List - partition by specific values (e.g., region='US', region='EU'). Benefits: (1) Query performance - scan only relevant partitions (partition pruning), (2) Maintenance - drop old partitions instead of DELETE, (3) Parallelism - query multiple partitions concurrently. PostgreSQL native partitioning: CREATE TABLE orders PARTITION BY RANGE (created_at). Use when: Large tables (>100M rows), queries filter on partition key, time-series data. 2025 pattern: Partition by date for events/logs, by tenant_id for multi-tenant apps. Don't partition small tables (<10M rows).

Vertical partitioning splits table by columns into multiple tables sharing primary key. Goes beyond normalization by dividing columns based on access patterns. Pattern: Separate frequently accessed (hot) columns from rarely accessed (cold) or large columns. Example: users → users_core (id, email, name), users_profile (id, bio, preferences, avatar_url). Benefits: (1) Reduced I/O - query loads only needed columns (faster for hot columns), (2) Better cache utilization - hot data fits in memory, (3) More rows per page - smaller row size improves sequential scans. Use cases: Static vs dynamic data separation (static faster to access), frequency-based splitting (login fields vs profile settings), large column isolation (TEXT/JSONB slow down common queries). 2025 Azure recommendation: Divide fields by access pattern for optimal indexing and I/O reduction. Trade-off: Joins add complexity. Don't overuse - materialized views or column projection (SELECT specific columns) may suffice.

Sharding is horizontal partitioning across multiple database servers (shards). Unlike partitioning (one database), sharding distributes data across separate physical databases. Each shard holds subset of data, independent schema, hardware, scaling. Partition key (shard key) determines which shard stores each row. Example: Users sharded by user_id % 4 across 4 database servers. Benefits: (1) Horizontal scalability - add more shards for more data/traffic, (2) Isolation - shard failure doesn't affect others, (3) Geographical distribution - shard by region for latency. Challenges: Cross-shard queries expensive, complex application logic (shard routing), rebalancing when adding shards. Use when: Single database can't handle load (>10TB data, >100k QPS), need geographic distribution. 2025 tools: Vitess, Citus, application-level sharding. Don't shard prematurely - vertical scaling first.

Hash-based sharding uses hash function on shard key to determine shard placement. Formula: shard = hash(shard_key) % num_shards. Example: user_id = 12345, hash(12345) % 4 = 2 → User stored in shard 2. Benefits: (1) Uniform distribution - hash ensures even data spread across shards, (2) Predictable - same key always routes to same shard, (3) Simple implementation. Challenges: (1) Range queries difficult - users 1000-2000 spread across all shards, (2) Rebalancing - adding shards changes hash, requires data migration (use consistent hashing), (3) No natural grouping - related records may be on different shards. Use when: Need even distribution, mostly key-based lookups (SELECT * FROM users WHERE id = X), no range query requirements. 2025 pattern: Hash for user data, range for time-series. Algorithm: Use consistent hashing (e.g., jump hash) to minimize rebalancing.

Range-based sharding assigns contiguous value ranges to shards. Example: Shard 1 (user_id 1-1M), Shard 2 (1M-2M). Respects natural sort order. Benefits: (1) Range queries efficient - WHERE user_id BETWEEN 1000 AND 2000 hits one shard, (2) Time-series optimized - recent data co-located (WHERE created_at > now() - 1 day), (3) Natural grouping - related records stay together. Challenges: (1) Hotspots - sequential inserts overload newest shard (latest user_id range), (2) Uneven distribution - data skew causes imbalance. 2025 hotspot prevention: (1) Application-level sharding - index as (shard_id HASH, timestamp ASC) distributes via hash, (2) Hash-sharded indexes - index itself hash-distributed while maintaining order, (3) Dynamic rebalancing - automated shard splitting. Best for: Analytics/reporting (date ranges), acceptable skew. Use hash sharding for transactional OLTP. Monitor shard sizes, split proactively. CockroachDB/YugabyteDB handle automatic rebalancing.

Use partitioning when: Single database can handle load, need query performance benefits (partition pruning), easier maintenance (drop old partitions), simpler operations (no distributed queries). Partitioning = multiple tables, one database server. Use sharding when: Single database can't scale (>10TB, >100k QPS), need horizontal scalability, geographical distribution required, willing to handle complexity. Sharding = multiple databases across servers. 2025 progression: (1) Start unpartitioned, (2) Add partitioning when table >100M rows or queries slow, (3) Shard when single server exhausted. Hybrid: Partition within each shard (e.g., shard by tenant, partition each shard by date). Trade-offs: Partitioning simpler, sharding more scalable. Don't shard prematurely - partition first. Most apps never need sharding (read replicas + caching often sufficient).

EXPLAIN ANALYZE actually executes query and shows real vs estimated stats. Read innermost (bottom) to outermost (top). Key metrics: (1) Cost - startup..total (arbitrary units, compare relative values), (2) Rows - estimated row count, (3) Actual time - real milliseconds (startup..total) per node, (4) Actual rows - true row count, (5) Loops - how many times node executed, (6) Buffers - 8KB blocks read/written (with BUFFERS option). Node types: Seq Scan (full table, slow >10k rows), Index Scan (fast), Index Only Scan (fastest, covering index), Nested Loop (small datasets), Hash Join (large datasets), Sort (memory vs disk). Red flags: Seq Scan on large tables, estimated ≠ actual rows (stale statistics), disk-based sorts. PostgreSQL 18 (Nov 2025) recommendation: EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) shows 8KB block I/O (essential). Visualize: explain.dalibo.com, pgAdmin. Fix: indexes, ANALYZE, increase work_mem.

Cardinality estimate predicts row count at each query operation step. Planner uses estimates to choose execution plan (join order, index selection). Collected via ANALYZE into pg_stats. PostgreSQL 17 (2024) significantly improved statistics model for persistent estimation problems. Accurate estimates critical - wrong estimates cause catastrophic plans (Nested Loop instead of Hash Join). Statistics include: ranges, distinct value counts (ndistinct), equi-depth histograms, heavy hitters. Assumes uniformity, independence, inclusion. Common issues: (1) Outdated statistics (table changed since ANALYZE), (2) Correlated columns (city/zip_code) - planner assumes independence, multiplies selectivity, massive underestimation. Fix: (1) Regular ANALYZE (autovacuum handles automatically), (2) Increase default_statistics_target from 100 to 1000 for skewed columns (99% rows share one value), (3) Extended statistics for correlated columns (CREATE STATISTICS). Monitor: estimated vs actual rows in EXPLAIN ANALYZE. Essential for optimal query performance.

Systematic approach: (1) Identify slow queries - pg_stat_statements, auto_explain, application logs, (2) Run EXPLAIN ANALYZE - understand execution plan, (3) Check for missing indexes - Seq Scan on large tables indicates missing index, (4) Verify index usage - Index Scan vs Seq Scan, (5) Update statistics - run ANALYZE table if estimates wrong. Common fixes: Add B-tree indexes for WHERE/JOIN columns, add covering indexes for SELECT columns, rewrite queries (avoid SELECT *, use EXISTS not IN), denormalize for read-heavy workloads, partition large tables. Advanced: Increase work_mem for sorts, tune random_page_cost, use materialized views, add connection pooling. 2025 tools: pgBadger analyzes logs, pg_stat_statements tracks queries, hypopg tests indexes without creating. Measure before/after - aim for <100ms response time. Don't: Add indexes blindly (slows writes), over-normalize.

Run ANALYZE to update planner statistics for cardinality estimates. Collects: row count, column distributions (histograms), most common values (MCVs), null fraction, correlation, ndistinct values. When to run: (1) After bulk data changes (INSERT/UPDATE/DELETE), (2) EXPLAIN shows estimate errors, (3) Post-migration, (4) Autovacuum handles automatically (triggers when >10% rows change). Syntax: ANALYZE table_name; or ANALYZE; (all tables). For complex columns: ALTER TABLE users ALTER COLUMN email SET STATISTICS 1000; (default 100, higher = more accurate but slower ANALYZE). 2025 best practices per official docs: (1) Autovacuum enabled by default (sufficient for most), (2) Manual ANALYZE for partitioned tables (keeps hierarchy current), (3) Daily ANALYZE during low-usage for read-heavy databases. Monitor freshness: pg_stat_user_tables.last_analyze. PostgreSQL generates statistics automatically; autovacuum maintains regularity. Essential for optimal join orders and index selection.

Connection pooling maintains pool of reusable database connections instead of creating new connection per request. Application borrows connection from pool, uses it, returns it. Benefits: (1) Performance - connection creation expensive (handshake, SSL, auth), pooling reuses (10-100x faster), (2) Resource efficiency - limit concurrent connections to database (prevent overload), (3) Scalability - handle more requests with fewer database connections. Without pooling: Each HTTP request creates new DB connection, overhead kills performance at scale. With pooling: 1000 HTTP requests share 20 DB connections. Implementations: Server-side (PgBouncer, pgpool), client-side (HikariCP, node-postgres pool). Use when: Web applications (essential), microservices, any multi-threaded app. 2025 pattern: Client pool (HikariCP) per app instance + server pool (PgBouncer) for massive scale. Don't: Skip pooling in production (performance/stability disaster).

PgBouncer is lightweight server-side connection pooler for PostgreSQL. Sits between application and database, multiplexes thousands of client connections over ~20-100 actual database connections. Written in C, uses <1MB memory per connection. Current stable: v1.23.1 (2024). Benefits: (1) Reduce DB connections 50x-100x (1000 clients → 20 DB connections), (2) Lower PostgreSQL memory (each connection = ~10MB saved), (3) Handle connection spikes without overwhelming database, (4) Transparent to applications (no code changes). Key features: Three pool modes (transaction/session/statement), rolling restarts (zero downtime), TLS/SSL support, auth methods (md5, scram-sha-256, cert). Configuration: max_client_conn=1000, default_pool_size=25, pool_mode=transaction. Deploy: Same host as PostgreSQL or separate server via Unix socket or TCP. Use when: >100 concurrent clients, hitting max_connections limit, microservices, serverless functions. Essential for production PostgreSQL.

PgBouncer has three pool modes controlling when server connections are returned to pool. (1) Session pooling: Connection held for entire client session until disconnect. Supports all PostgreSQL features (prepared statements, temp tables, advisory locks). Most compatible but least efficient (lowest connection multiplexing). (2) Transaction pooling: Connection returned after COMMIT/ROLLBACK. Best balance - high efficiency while supporting transactions. Breaks session features (no prepared statements, temp tables). Recommended for most applications. (3) Statement pooling: Connection returned after each SQL statement. Highest multiplexing but forces autocommit mode (no multi-statement transactions). Rarely used. Set with: pool_mode=transaction in pgbouncer.ini or per-database. 2025 recommendation: Transaction mode for 90% of workloads (serverless, web apps, microservices). Session mode only if you need prepared statements or session-level features.

Decision tree: Use transaction pooling when: (1) Web applications (REST APIs, GraphQL servers), (2) Microservices, (3) Serverless functions (Lambda, Cloud Functions), (4) Don't use prepared statements or temp tables, (5) Need high connection multiplexing (1000+ clients → 20 DB connections). This is 90% of use cases. Use session pooling when: (1) Application uses prepared statements (PREPARE/EXECUTE), (2) Need temporary tables, (3) Use advisory locks (pg_advisory_lock), (4) LISTEN/NOTIFY required, (5) Can tolerate lower connection efficiency. Use statement pooling when: (1) Simple key-value lookups only, (2) All queries in autocommit mode, (3) Need absolute maximum connection multiplexing. Rarely needed. 2025 best practice: Start with transaction mode, switch to session only if you hit feature limitations. Never use statement mode unless extreme connection pressure. Configuration: pool_mode=transaction in pgbouncer.ini.

HikariCP (latest 7.0.2 for Java 11+, 4.0.3 for Java 8) is the fastest Java connection pool, default in Spring Boot. Essential config: (1) maximumPoolSize - use formula: (core_count * 2) + effective_spindle_count, typical 9-20 connections (4-core server needs ~10, not 100+), (2) minimumIdle - set equal to maximumPoolSize for fixed-size pool (best performance), (3) maxLifetime - set 30000 (30s) shorter than database timeout (if MySQL wait_timeout=28800s, use 28770000), (4) connectionTimeout - default 30000ms (30s), (5) idleTimeout - default 600000ms (10min). Critical: Pool must be small and saturated - larger pools degrade performance via context switching. Performance: 2-3x faster than DBCP2/Tomcat in JMH benchmarks. Monitor: active connections, wait time (should be ~0). 2025 pattern: Small pool per app instance + PgBouncer for database-side pooling. Don't set huge pools - aim for smallest working size.

Yes, two-tier pooling is recommended 2025 pattern. HikariCP (client-side) pools to PgBouncer, PgBouncer (server-side) pools to PostgreSQL. Benefits: (1) Application efficiency - HikariCP reuses TCP connections (avoid GC churn, handshake latency), (2) Database protection - PgBouncer caps actual PostgreSQL connections, (3) Burst handling - 1000s app connections → hundreds PgBouncer → dozens PostgreSQL. 2025 Kubernetes: PgBouncer centralized, HikariCP per pod with tightened settings. Debate: Some argue redundant (space between HikariCP-PgBouncer is TCP not DB connections, cheaper). Counter: DataSource without pooling causes extreme performance drop - HikariCP+PgBouncer vastly outperforms no client pool. Spring Boot/Java frameworks expect DataSource abstraction. Critical: Applications must return connections properly (avoid leaks). Configuration: HikariCP max 10-20 → PgBouncer → PostgreSQL. Don't skip client pool - creating fresh TCP connections per query kills performance. 2025 consensus: Use both for optimal throughput.

Keep pool size as small as possible, as large as necessary. Formula for HikariCP: connections = (core_count * 2) + effective_spindle_count. Typical: 10-50 connections. Too large: Wastes memory (app + database), increases contention, context switching overhead. Too small: Queries wait for connections, timeouts. Best practice: Start with minimumIdle=5, maximumPoolSize=20. Monitor: Connection wait time (should be near 0), active connections (should be <80% of max during normal load), database CPU/memory. Load test and adjust. For microservices: Each instance has own pool, calculate: total_db_connections = pool_size * num_instances * num_services. Ensure < database max_connections (PostgreSQL default 100). 2025 pattern: Small pools (10-20) per service + PgBouncer for aggregation. Don't: Default to huge pools (100+), set pool size > database connections. Right-size based on concurrency needs.

Connection leak occurs when application borrows connection but never returns it to pool. Eventually exhausts pool. Prevention: (1) Always close connections - use try-with-resources in Java: try (Connection conn = pool.getConnection()) { }, (2) Close ResultSets and Statements - they hold connection references, (3) Set connection timeout - detect leaked connections (HikariCP connectionTimeout), (4) Limit transaction duration - avoid long-running transactions, close quickly, (5) Never call external APIs inside transactions - network calls hold connection. Detection: Monitor active connections, enable leak detection (HikariCP leakDetectionThreshold=60000 warns if connection held >60s). Debugging: Leak detection logs stack trace showing where connection acquired. 2025 tools: HikariCP built-in leak detection, Prometheus metrics for connection pool monitoring. Common cause: Unclosed connections in exception paths. Always use try-finally or try-with-resources. Essential for pool health.

Read replica is copy of primary database used for read-only queries. Primary handles writes, replicas handle reads. Replication is asynchronous - primary writes committed, then copied to replicas with slight delay (lag). Benefits: (1) Scale reads - distribute SELECT queries across multiple replicas, (2) Reduce primary load - offload reporting/analytics to replicas, (3) Geographic distribution - replicas in different regions for low latency, (4) Disaster recovery - promote replica to primary if primary fails. Use cases: Read-heavy workloads (10:1 read/write ratio), reporting/analytics, serving global users. 2025 providers: AWS RDS Read Replicas, Azure SQL Database Read Scale-Out, Supabase Read Replicas. Lag: Typically <1 second, monitor with pg_stat_replication. Application handles: Route reads to replicas, writes to primary. Trade-off: Eventual consistency (replica may be slightly behind). Essential for scaling read traffic beyond single server.

Synchronous replication writes data to primary AND replica(s) before confirming transaction commit. Transaction blocked until replica acknowledges write. Guarantees: (1) Zero data loss - replica always has same data as primary, (2) Immediate consistency - no replication lag, (3) Instant failover - replica can immediately become primary. Trade-offs: (1) Higher latency - every write waits for network round-trip to replica, (2) Availability - if replica unreachable, writes may fail or slow down, (3) Performance - 2-3x slower writes vs asynchronous. Use when: Zero data loss required (financial transactions, critical data), immediate consistency needed, can tolerate write latency (100-500ms). PostgreSQL: synchronous_commit = on, synchronous_standby_names = 'replica1'. 2025 use case: High-end transactional systems (banking, payments). Most systems use async for performance, sync only for critical data.

Asynchronous replication writes to primary, confirms transaction, then replicates to replica(s) later. Primary doesn't wait for replica acknowledgment. Benefits: (1) Fast writes - no network latency, (2) High availability - replica downtime doesn't affect primary, (3) Scalability - add replicas without impacting write performance. Trade-offs: (1) Replication lag - replica behind primary (typically <1s, can be more), (2) Potential data loss - if primary fails before replication, recent transactions lost, (3) Eventual consistency - reads from replica may return stale data. Use when: Performance > zero data loss, read scaling important, can tolerate lag/stale reads. 2025 default: AWS RDS, PostgreSQL, MySQL all use async by default. Monitor lag: pg_stat_replication in PostgreSQL. Pattern: Async for most data, sync for critical transactions. Essential for high-performance replication.

Choose synchronous when: (1) Zero data loss required (financial transactions, orders, payments), (2) Regulatory compliance mandates no data loss, (3) Immediate consistency needed, (4) Can afford write latency (2-3x slower). Choose asynchronous when: (1) Performance critical, (2) Read scaling important (multiple replicas), (3) Acceptable to lose seconds of data on primary failure, (4) Eventual consistency acceptable. Hybrid approach: Synchronous for critical tables (transactions), asynchronous for others (logs, analytics). 2025 pattern: Most systems use async (99% of workloads), sync only for critical data. PostgreSQL supports both: Set synchronous_commit per transaction or table. Monitor: Replication lag (async), write latency (sync). Trade-off matrix: Sync = slow writes + zero loss, Async = fast writes + potential loss. Don't use sync everywhere - kills performance. Reserve for truly critical data.

Replication lag is time from primary commit to replica visibility. Typical <1s, spikes during heavy load or large transactions. 2025 monitoring improvements: Azure SQL Database Replication lag metric (Aug 2025, 1-min frequency, 93-day retention), MariaDB 11.8 LTS (Jun 2025) added Master_Slave_time_diff for consistent tracking. Causes: I/O bottlenecks (slow SSD, cloud storage latency), network congestion (cross-region), single-threaded apply, large transactions. Handling strategies: (1) Monitor - replica_lag and network_lag metrics, pg_stat_replication.replay_lag in PostgreSQL, CloudWatch RDS, alert >5s, (2) Intelligent routing - critical reads from primary, non-critical from replicas, (3) Session pinning - stick user to primary after write, (4) Timestamp-based - route to primary if lag too high. Reduce lag: Tune log capture/apply processes, optimize replica queries, stronger hardware. 2025 tools: ProxySQL, Vitess for automatic routing. SLA: <1s lag 99% time. Don't assume replicas current.

Isolation level controls what data transaction sees from concurrent transactions. Four standard levels (weakest to strongest): (1) Read Uncommitted - see uncommitted changes from other transactions (dirty reads), rarely used, (2) Read Committed - see only committed changes, default in PostgreSQL/SQL Server, (3) Repeatable Read - queries within transaction see same snapshot, no dirty/non-repeatable reads, default in MySQL InnoDB, (4) Serializable - strongest, transactions execute as if serial (no concurrency), prevents all anomalies. Trade-offs: Higher isolation = more consistency + less concurrency + slower. Lower isolation = more concurrency + faster + potential anomalies. 2025 defaults: PostgreSQL/SQL Server use Read Committed, MySQL uses Repeatable Read. Set per transaction: SET TRANSACTION ISOLATION LEVEL. Choose based on consistency needs vs performance requirements.

Read Committed: Transaction sees only data committed before query started. Default in PostgreSQL, SQL Server. Prevents: Dirty reads (reading uncommitted data). Allows: Non-repeatable reads (same query returns different results if other transaction commits between queries), phantom reads (new rows appear in range queries). How it works: Each statement sees snapshot at statement start. Example: Query 1 reads user balance $100, other transaction commits update to $50, Query 2 reads balance $50 (different result). Use when: Default choice for OLTP, acceptable for queries to see latest committed data, performance important. Don't use when: Need consistent snapshot across multiple queries (use Repeatable Read or Serializable). 2025 usage: 80%+ of transactions use Read Committed. Good balance of consistency and performance. Lost update problem still possible - use SELECT FOR UPDATE for critical updates.

Repeatable Read: Transaction sees snapshot from first query, same snapshot for entire transaction. Default in MySQL InnoDB. Prevents: Dirty reads, non-repeatable reads. Allows: Phantom reads (in some databases, PostgreSQL prevents these too). How it works: Transaction gets consistent snapshot at start, all queries see same data even if other transactions commit changes. Example: Query balance twice, always see same value even if updated elsewhere. Benefits: Consistent view for analytics, reporting. Trade-offs: Longer transactions hold snapshot resources, may conflict with concurrent updates. Use when: Multi-query transactions need consistency (reports, data exports), can tolerate phantom reads. PostgreSQL Repeatable Read actually provides Snapshot Isolation (stricter than standard, prevents phantoms). 2025 usage: Good for batch jobs, complex transactions. Don't use: For long-running transactions (blocks vacuuming in PostgreSQL).

Serializable: Strongest isolation, simulates serial execution (transactions run one-at-a-time). Prevents all anomalies: dirty reads, non-repeatable reads, phantom reads, write skew. PostgreSQL implements Serializable Snapshot Isolation (SSI) - tracks read/write dependencies, detects dangerous conflicts, aborts violating transactions. Example write skew: Two transactions each read data, write based on reads → conflict detected, one aborted with serialization error. Critical requirement: Applications MUST implement retry logic for serialization failures (normal, expected). When to use: Financial ledgers, auditing, compliance-critical systems where zero data loss required. Trade-offs: Increased overhead (dependency monitoring), frequent aborts (especially high concurrency writes), higher latency. PostgreSQL 9.1+ optimization: Read-only transactions never have serialization conflicts, reducing false positives. 2025 reality: Rarely right choice for most apps. Repeatable Read better balance for 99% workloads. SERIALIZABLE feels overkill outside niche domains. Use only for truly critical data.

Decision matrix: Use Read Committed when: (1) Default choice for OLTP web apps, (2) Queries independent (no multi-query consistency needed), (3) Performance important, (4) Lost updates prevented with SELECT FOR UPDATE. Use Repeatable Read when: (1) Multi-query transactions need consistent view (reports, analytics), (2) Can tolerate retry logic for conflicts, (3) Medium contention acceptable. Use Serializable when: (1) Critical correctness (financial, inventory), (2) Complex business rules, (3) Can afford performance cost + retries. Never use Read Uncommitted (dirty reads unacceptable in 2025). 2025 best practice: Start with database default (Read Committed in PostgreSQL, Repeatable Read in MySQL), increase isolation only when needed, measure performance impact. Pattern: Read Committed for 95% of transactions, Serializable for critical 5%. Monitor: Serialization error rate, transaction retry rate. Don't: Use Serializable everywhere (kills performance).