Database Optimization Techniques

Optimization techniques are not universally applicable — each addresses a specific class of problem under specific conditions. This document groups techniques by the scenario where they apply.

---

Hot-Row Contention Under High Concurrency

When this applies: Many concurrent transactions compete to update the same rows — flash sales, ticket drops, popular inventory items, counters. The symptom is rising lock-wait times and throughput collapse despite low CPU usage.

Source context: Shopify — Scaling Inventory Reservations — Shopify's inventory system handles Black Friday traffic ($5.1M/min in sales). During flash sales, thousands of concurrent buyers attempt to reserve the same item simultaneously. Originally MySQL 8 / InnoDB and Redis.

One Row Per Unit

When many transactions decrement a single quantity column, they all serialize on that one row's lock. Instead, create one row per sellable unit. Reserving 3 units means selecting 3 separate rows — contention is distributed.

-- Problem: every concurrent buyer locks this single row
UPDATE inventory SET quantity = quantity - 1 WHERE item_id = 42;

-- PostgreSQL: one row per available unit
CREATE TABLE inventory_units (
  id            BIGINT GENERATED ALWAYS AS IDENTITY,
  shop_id       BIGINT NOT NULL,
  item_id       BIGINT NOT NULL,
  location_id   BIGINT NOT NULL,
  status        TEXT NOT NULL DEFAULT 'available',
  PRIMARY KEY (shop_id, item_id, location_id, id)
);

-- Seed 10 units for item 42 at location 1
INSERT INTO inventory_units (shop_id, item_id, location_id)
SELECT 1, 42, 1 FROM generate_series(1, 10);

-- Reserve 3 units: each row gets its own lock
DELETE FROM inventory_units
WHERE id IN (
  SELECT id FROM inventory_units
  WHERE shop_id = 1 AND item_id = 42 AND location_id = 1
    AND status = 'available'
  LIMIT 3
  FOR UPDATE
)
RETURNING id;

Trade-offs: Table size grows proportionally with inventory (10,000 units = 10,000 rows). Requires a bounded pool strategy (below) to keep the table manageable. More complex schema than a simple counter.

SELECT ... FOR UPDATE SKIP LOCKED with Bounded Pool

Even with row-per-unit, traditional FOR UPDATE blocks until a locked row is released. Under flash-sale concurrency this creates cascading waits. SKIP LOCKED tells the database: if a row is already locked, skip it and return the next available one. No transaction ever waits on a row being processed by another.

This is paired with a bounded pool — a fixed set of pre-allocated rows (e.g., 1,000 per item/location). Reservations consume from the pool; a replenishment process refills from the inventory ledger. The 1,000-row size is based on observed peak reservation rates — enough headroom so replenishment keeps up under sustained load.

-- PostgreSQL (SKIP LOCKED supported since 9.5)
BEGIN;

DELETE FROM inventory_units
WHERE id IN (
  SELECT id FROM inventory_units
  WHERE shop_id = 1 AND item_id = 42 AND location_id = 1
    AND status = 'available'
  ORDER BY id
  LIMIT 3
  FOR UPDATE SKIP LOCKED
)
RETURNING id;

INSERT INTO reserved_quantities (shop_id, item_id, location_id, unit_id, reserved_at)
VALUES (1, 42, 1, <returned_id>, NOW());

COMMIT;

-- Replenishment: refill pool when it drops below threshold
-- Advisory lock prevents thundering herd (only one process replenishes at a time)
SELECT pg_try_advisory_xact_lock(hashtext('replenish:1:42:1'));

INSERT INTO inventory_units (shop_id, item_id, location_id)
SELECT 1, 42, 1 FROM generate_series(1, 500)
WHERE (
  SELECT COUNT(*) FROM inventory_units
  WHERE shop_id = 1 AND item_id = 42 AND location_id = 1 AND status = 'available'
) < 500;

Trade-offs: Pool exhaustion during extreme spikes triggers synchronous replenishment, briefly serializing requests. The advisory lock ensures single-threaded replenishment — others wait rather than stampeding.

Composite Primary Key to Reduce Lock Count

MySQL/InnoDB-specific. This addresses InnoDB's clustered index architecture. PostgreSQL uses heap storage and does not have the same dual-lock behavior.

In InnoDB, the primary key is the clustered index. Querying by a secondary index and locking a row requires locking both the secondary index entry and the clustered PK entry — 2 locks per row. Changing the PK to a composite that matches the query pattern makes the lookup go directly through the clustered index: 1 lock per row.

-- MySQL/InnoDB: Before — auto-increment PK + secondary index = 2 locks per row
CREATE TABLE inventory_units (
  id               BIGINT AUTO_INCREMENT PRIMARY KEY,
  shop_id          BIGINT NOT NULL,
  inventory_item_id BIGINT NOT NULL,
  inventory_group_id BIGINT NOT NULL,
  INDEX idx_lookup (shop_id, inventory_item_id, inventory_group_id)
);

-- MySQL/InnoDB: After — composite PK matches query pattern = 1 lock per row
CREATE TABLE inventory_units (
  id               BIGINT NOT NULL,
  shop_id          BIGINT NOT NULL,
  inventory_item_id BIGINT NOT NULL,
  inventory_group_id BIGINT NOT NULL,
  PRIMARY KEY (shop_id, inventory_item_id, inventory_group_id, id)
);

In PostgreSQL, composite PKs still help by enabling index-only scans and removing secondary index lookups, but the lock-count halving is InnoDB-specific:

-- PostgreSQL: composite PK for efficient lookups
CREATE TABLE inventory_units (
  id               BIGINT GENERATED ALWAYS AS IDENTITY,
  shop_id          BIGINT NOT NULL,
  inventory_item_id BIGINT NOT NULL,
  inventory_group_id BIGINT NOT NULL,
  PRIMARY KEY (shop_id, inventory_item_id, inventory_group_id, id)
);

-- One-time physical reorder (not maintained automatically; use pg_repack for online reclustering)
CLUSTER inventory_units USING inventory_units_pkey;

READ COMMITTED to Avoid Gap Locks

Under MySQL's default REPEATABLE READ, a SELECT ... FOR UPDATE SKIP LOCKED on an empty result set takes gap locks on the index range, blocking INSERT in that range. This creates a deadlock-like loop: reserves can't proceed (pool empty), replenishment can't insert (gap locked by the empty-result reserve). Switching to READ COMMITTED for these transactions removes gap locks.

PostgreSQL: READ COMMITTED is the default isolation level — this problem does not occur. No configuration needed.

-- PostgreSQL: just works under default isolation
BEGIN;
  SELECT id FROM inventory_units
  WHERE shop_id = 1 AND item_id = 42 AND status = 'available'
  FOR UPDATE SKIP LOCKED
  LIMIT 3;
COMMIT;

If you've explicitly set a stricter level and need to override per-transaction:

BEGIN;
SET LOCAL transaction_isolation = 'read committed';
-- SET LOCAL reverts automatically after the transaction
  SELECT id FROM inventory_units
  WHERE shop_id = 1 AND item_id = 42 AND status = 'available'
  FOR UPDATE SKIP LOCKED
  LIMIT 3;
COMMIT;

MySQL requires explicit override since its default is REPEATABLE READ:

-- MySQL: set isolation before the transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
  SELECT id FROM inventory_units
  WHERE shop_id = 1 AND item_id = 42 AND status = 'available'
  FOR UPDATE SKIP LOCKED
  LIMIT 3;
COMMIT;

---

Deadlocks and Data Consistency Across Multi-Step Transactions

When this applies: Multiple code paths (reserve, claim, cancel, refund) touch overlapping tables in different orders, or related data spans multiple data stores that can't share a transaction. Symptoms are intermittent deadlock errors or data drift between systems.

Source context: Shopify — Scaling Inventory Reservations — Shopify's checkout has separate reserve (hold inventory) and claim (payment confirmed, deduct permanently) paths. These ran concurrently and touched the same tables. Reservations originally lived in Redis while the ledger was in MySQL, making atomic claim impossible.

Lock Ordering to Prevent Deadlocks

When two code paths acquire locks on the same tables in different orders, circular waits occur — a deadlock. The fix is to standardize a canonical lock acquisition order across all code paths. If every transaction acquires locks in the same sequence, circular waits are impossible.

-- PostgreSQL
-- RULE: All paths acquire locks in this order:
-- 1. inventory_units  2. reserved_quantities  3. inventory_ledger

-- Reserve path
BEGIN;
  DELETE FROM inventory_units
  WHERE id IN (
    SELECT id FROM inventory_units
    WHERE shop_id = 1 AND item_id = 42
    FOR UPDATE SKIP LOCKED
    LIMIT 3
  );

  INSERT INTO reserved_quantities (shop_id, item_id, unit_id, reserved_at)
  VALUES (1, 42, 101, NOW()), (1, 42, 102, NOW()), (1, 42, 103, NOW());
COMMIT;

-- Claim path: touches reserved_quantities then ledger (same order, never reversed)
BEGIN;
  DELETE FROM reserved_quantities
  WHERE shop_id = 1 AND item_id = 42 AND order_id = 999
  RETURNING unit_id;

  INSERT INTO inventory_ledger (shop_id, item_id, change, reason, created_at)
  VALUES (1, 42, -3, 'claimed', NOW());
COMMIT;

Trade-offs: Requires discipline across every code path — a single path that violates the order reintroduces deadlock risk. Document the canonical lock order prominently.

Atomic Cross-System Consolidation

When related data lives in different systems (e.g., reservations in Redis, ledger in MySQL), multi-step operations can't be wrapped in a single transaction. Depending on which step fails:

• Store A updated, Store B fails -> Overselling (item sold but never deducted)
• Store B updated, Store A fails -> Underselling (deducted but still marked reserved)

The fix is to move the data into the same database so ACID transactions cover the entire operation. No two-phase commit, no eventual consistency.

-- PostgreSQL: single atomic transaction covers both steps
BEGIN;
  DELETE FROM reserved_quantities
  WHERE shop_id = 1 AND item_id = 42 AND order_id = 999
  RETURNING quantity;

  INSERT INTO inventory_ledger (shop_id, item_id, change, reason, order_id, created_at)
  VALUES (1, 42, -3, 'order_claimed', 999, NOW());
COMMIT;
-- Both succeed or both fail.

Trade-offs: Gives up the raw throughput of in-memory stores like Redis. Increases load on the relational database — requires the contention-reduction techniques above to compensate. Simplifies operations: one fewer data store to maintain and keep consistent.

---

Reducing Latency for Batch Operations

When this applies: A single user action requires multiple independent database operations — multi-item carts, bulk updates, batch imports. Latency scales linearly with the number of round trips.

Source context: Shopify — Scaling Inventory Reservations — A checkout cart with multiple line items required separate reservation queries per item, multiplying round-trip latency under load.

Query Batching with UNION ALL

Combine multiple independent queries into a single database round trip. Each sub-query handles one item but they're sent as one statement.

-- PostgreSQL: batch 3 item reservations into one round trip
(
  SELECT id, 42 AS item_id FROM inventory_units
  WHERE shop_id = 1 AND item_id = 42 AND status = 'available'
  FOR UPDATE SKIP LOCKED
  LIMIT 2
)
UNION ALL
(
  SELECT id, 77 AS item_id FROM inventory_units
  WHERE shop_id = 1 AND item_id = 77 AND status = 'available'
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UNION ALL
(
  SELECT id, 99 AS item_id FROM inventory_units
  WHERE shop_id = 1 AND item_id = 99 AND status = 'available'
  FOR UPDATE SKIP LOCKED
  LIMIT 3
);

Trade-offs: Reduces round trips and helps latency under load. Query complexity increases — harder to handle per-item errors (if item 2 has insufficient stock, you still get results for items 1 and 3).

---

Database Resource Saturation

When this applies: The database has CPU and memory headroom, but connections are exhausted or internal concurrency caps prevent full utilization. All services sharing the database degrade, not just the one causing the bottleneck. Symptoms: connection pool exhaustion, low CPU despite high latency, timeouts across unrelated services.

Source context: Shopify — Scaling Inventory Reservations — Shopify's checkout, cart, payment, and reservation systems share a database. Connection attribution revealed that reservations were not the throughput ceiling — other checkout components holding connections too long were the real bottleneck. Thread concurrency settings, unchanged for years, capped CPU utilization.

Connection Attribution and Monitoring

Without visibility into which business process holds each connection and for how long, you can't find the bottleneck. The technique: tag every SQL query with its business process, then measure connection hold time per caller.

-- PostgreSQL: tag connections via application_name
SET application_name = 'checkout_completion';

-- Or via SQL comments (aggregatable with pg_stat_statements)
/* conn_tag:checkout_completion */
SELECT * FROM reserved_quantities WHERE order_id = 999;

-- Query connection hold time per caller
SELECT
  application_name,
  state,
  COUNT(*) AS connections,
  MAX(NOW() - state_change) AS longest_hold_time,
  AVG(NOW() - state_change) AS avg_hold_time
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY application_name, state
ORDER BY longest_hold_time DESC;

For connection pooling, PostgreSQL uses PgBouncer (analogous to MySQL's ProxySQL):

# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 dbname=mydb

[pgbouncer]
pool_mode = transaction          # release connection after each transaction
max_client_conn = 1000
default_pool_size = 50
stats_period = 60
application_name_add_host = 1    # append client host to application_name

What this reveals: In Shopify's case, cleanup after attribution removed 50% of reads and 33% of transactions on the primary database. The bottleneck was not where they expected.

Thread / Worker Concurrency Tuning

CPU underutilized despite connection exhaustion means internal concurrency limits are capping how many operations run simultaneously. Conservative defaults from years ago may no longer fit the workload.

MySQL/InnoDB-specific: The original technique tunes innodb_thread_concurrency.

-- MySQL: check and adjust
SHOW VARIABLES LIKE 'innodb_thread_concurrency';
SET GLOBAL innodb_thread_concurrency = 0;  -- 0 = let InnoDB manage

PostgreSQL equivalent — concurrency is governed by multiple settings:

SHOW max_connections;                   -- total connection slots
SHOW max_worker_processes;              -- background worker processes
SHOW max_parallel_workers;              -- max workers for parallel queries
SHOW max_parallel_workers_per_gather;   -- max workers per parallel scan

-- Tune in postgresql.conf:
-- max_connections = 200
-- max_worker_processes = 16
-- max_parallel_workers = 8
-- max_parallel_workers_per_gather = 4

Trade-offs: Increasing concurrency without sufficient CPU/memory causes thrashing. Monitor both metrics side by side — the goal is higher CPU utilization without saturation. After Shopify's tuning: writer CPU stayed under 50%, reader CPU under 16%.

---

Zero-Downtime Data Store Migration

When this applies: Replacing one data store with another in a live production system (e.g., Redis to PostgreSQL, MySQL to PostgreSQL, monolith to microservice). You need to validate the new system handles real traffic before trusting it, but can't afford downtime or data loss to find out.

Source context: Shopify — Scaling Inventory Reservations — Shopify migrated inventory reservations from Redis to MySQL. They ran dual-write shadow mode during the transition, validating correctness and performance on production traffic before flipping the source of truth. Rollout was pod-by-pod, low-traffic first.

Dual-Write Shadow Mode

Run both systems in parallel with the old system as source of truth. Every write goes to both. Compare outputs to validate correctness. Once confidence is established, flip the source of truth. The old system stays writable as a kill switch until decommissioned.

Phase 1 — Shadow writes
  Old system = source of truth (reads + writes)
  New system = receives all writes, results discarded
  Compare: correctness validation on every operation

Phase 2 — Flip source of truth
  New system = source of truth (reads + writes)
  Old system = still receives writes (kill switch / rollback path)

Phase 3 — Decommission
  Old system writes stopped
  Old system decommissioned

-- Validation query: detect drift between old and new system
-- Run periodically during shadow mode
SELECT
  o.shop_id,
  o.item_id,
  o.reserved_count AS old_system_count,
  n.reserved_count AS new_system_count,
  CASE
    WHEN o.reserved_count != n.reserved_count THEN 'DRIFT'
    ELSE 'OK'
  END AS status
FROM old_system_snapshot o
JOIN new_system_counts n
  ON o.shop_id = n.shop_id AND o.item_id = n.item_id
WHERE o.reserved_count != n.reserved_count;

Trade-offs: Write latency increases (dual writes). No in-flight data migration needed — the new system is populated throughout the transition. Rollback is a config change, not a data migration. Gradual rollout recommended: start with low-traffic segments, progress to highest-volume.

---

Summary

Scenario	Techniques	DB Support
Hot-row contention	One Row Per Unit, SKIP LOCKED + Bounded Pool	PostgreSQL 9.5+, MySQL 8+
	Composite PK lock reduction	MySQL/InnoDB only
	READ COMMITTED isolation	PostgreSQL (default), MySQL (explicit)
Deadlocks & consistency	Lock ordering	Any (application-level)
	Atomic cross-system consolidation	Any ACID DB
Batch latency	UNION ALL batching	Any (standard SQL)
Resource saturation	Connection attribution	PostgreSQL (pg_stat_activity), MySQL (ProxySQL)
	Thread/worker tuning	MySQL (innodb_thread_concurrency), PostgreSQL (max_parallel_workers)
Live migration	Dual-write shadow mode	Architectural (DB-agnostic)

References

• Shopify Engineering — Scaling Inventory Reservations at Shopify — Source for all techniques in this document. Shopify migrated inventory reservations from Redis to MySQL to achieve ACID guarantees, tested at Black Friday 2025 scale ($5.1M/min). Originally MySQL 8 / InnoDB and Redis.

---