Database Best Practices for Scalable Applications

Database scalability is rarely solved by one optimization. Production systems scale through a combination of correct schemas, bounded queries, selective indexes, short transactions, controlled concurrency, caching, partitioning, replication, observability, and safe operational workflows.

The strongest database design is not the one that handles the highest synthetic throughput. It is the one that preserves correctness, predictable latency, recoverability, and manageable cost as traffic, data volume, tenant count, and engineering teams grow.

Table of Contents

• Design for Access Patterns and Invariants
• Build Efficient Schemas and Indexes
  • Choose Stable Data Types
  • Create Purpose-Built Indexes
  • Avoid Unbounded Record Growth
• Control Queries and Concurrency
  • Require Bounded Queries
  • Keep Transactions Short
  • Protect the Database with Admission Control
• Scale Reads and Writes Deliberately
  • Use Caching for the Right Data
  • Use Replicas with Explicit Consistency
  • Partition Before Sharding
• Design Safe Write Paths
  • Make Writes Idempotent
  • Use Optimistic Concurrency
  • Separate Transactions from Asynchronous Work
• Production Design Example
  • Architecture
  • Request and Data Flows
  • Failure Scenarios
• Ready-to-Use Example
  • PostgreSQL Schema
  • FastAPI Write Service
  • Bounded Pagination
  • Cache-Aside Reader
  • Database Monitoring Queries
• Operational Practices
• Common Mistakes
• Production Checklist
• Conclusion
  • More Articles to Read

Design for Access Patterns and Invariants

A scalable database design starts by defining what must remain correct and how the data will be accessed. Tables, indexes, caches, replicas, and partitions should follow those requirements.

For each important workflow, document:

• the business invariant
• the required input keys
• the expected result size
• the sort order
• the maximum acceptable latency
• the required consistency level
• the peak concurrency
• the retry and failure behavior

Correctness requirements should be enforced as close to the data as possible. Application validation is useful, but it cannot replace unique constraints, foreign keys, checks, and atomic updates under concurrency.

CREATE TABLE customer_orders (
    order_id UUID PRIMARY KEY,
    account_id BIGINT NOT NULL,
    external_reference TEXT NOT NULL,
    status TEXT NOT NULL,
    total_amount_cents BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT customer_orders_reference_unique
        UNIQUE (account_id, external_reference),

    CONSTRAINT customer_orders_status_check
        CHECK (status IN (
            'pending',
            'confirmed',
            'cancelled',
            'completed'
        )),

    CONSTRAINT customer_orders_amount_check
        CHECK (total_amount_cents >= 0)
);

The unique constraint remains correct even when several application instances process the same request concurrently.

Build Efficient Schemas and Indexes

Schema and index design determine how much data the database must read, write, lock, replicate, cache, back up, and maintain. Small design choices become expensive at scale because they affect every row and request.

Choose Stable Data Types

Use data types that reflect the business domain and remain stable as the system grows.

• Use integers for monetary amounts when fixed precision is sufficient.
• Use timestamps with time zones for global systems.
• Use constrained text values or database enums for stable state machines.
• Use UUIDs when distributed identifier generation is required.
• Keep frequently filtered values in typed columns instead of hiding everything in JSON.
• Use JSON for flexible metadata, not for core relational ownership and constraints.

A hybrid schema can keep stable queryable fields in columns and variable provider data in JSON:

CREATE TABLE carrier_events (
    event_id UUID PRIMARY KEY,
    account_id BIGINT NOT NULL,
    shipment_id UUID NOT NULL,
    carrier_code TEXT NOT NULL,
    event_type TEXT NOT NULL,
    occurred_at TIMESTAMPTZ NOT NULL,
    received_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    provider_payload JSONB NOT NULL,

    CONSTRAINT carrier_events_event_type_check
        CHECK (event_type IN (
            'picked_up',
            'in_transit',
            'delayed',
            'delivered',
            'exception'
        ))
);

This keeps operational filters efficient while preserving the original provider payload for debugging and reconciliation.

Create Purpose-Built Indexes

An index should support a measured query. Every additional index increases insert latency, update cost, replication volume, storage, vacuum work, and backup size.

For an account shipment timeline:

CREATE INDEX shipments_account_created_idx
ON shipments (
    account_id,
    created_at DESC,
    shipment_id DESC
)
INCLUDE (
    status,
    carrier_code,
    tracking_number
);

This index supports tenant filtering, stable ordering, keyset pagination, and index-only reads for common summary fields.

Column order should follow the query:

SELECT
    shipment_id,
    status,
    carrier_code,
    tracking_number,
    created_at
FROM shipments
WHERE account_id = 4812
  AND (created_at, shipment_id) < (
      TIMESTAMPTZ '2026-08-02 15:00:00+00',
      'c9b37146-e8ac-4e76-8b10-b2226aa1cc77'
  )
ORDER BY created_at DESC, shipment_id DESC
LIMIT 100;

The account identifier comes first because it is an equality predicate. The ordered cursor fields follow because they control range filtering and sorting.

Use partial indexes when only a small subset of rows is operationally important:

CREATE INDEX shipments_active_exception_idx
ON shipments (
    account_id,
    updated_at DESC
)
WHERE status IN ('delayed', 'exception');

This is smaller and cheaper than indexing the status of every completed historical shipment.

Avoid Unbounded Record Growth

Rows and documents should not grow indefinitely. Unbounded arrays, histories, comments, events, and embedded collections eventually create large writes, lock contention, storage amplification, and record-size limits.

Store unbounded collections separately:

Shipment
   |
   +-- fixed shipment metadata
   +-- current status
   +-- current carrier
   |
   +--> Shipment Events table
   +--> Shipment Documents table
   +--> Shipment Comments table

The aggregate keeps only current state. Historical and repeating entities receive their own rows, indexes, retention rules, and pagination.

Control Queries and Concurrency

Database overload is often caused by uncontrolled work rather than insufficient hardware. One unbounded query, connection storm, lock queue, or retry loop can consume the capacity intended for thousands of normal requests.

Require Bounded Queries

Every high-volume list endpoint should require:

• a tenant or ownership filter
• a maximum page size
• a stable sort order
• a cursor or bounded time range
• a query timeout

Avoid offset pagination for deep datasets:

-- Avoid for large offsets.
SELECT shipment_id, status, created_at
FROM shipments
WHERE account_id = 4812
ORDER BY created_at DESC
OFFSET 500000
LIMIT 100;

The database still walks through the skipped rows. Keyset pagination reads from a known position:

SELECT shipment_id, status, created_at
FROM shipments
WHERE account_id = 4812
  AND (created_at, shipment_id) < (:cursor_time, :cursor_id)
ORDER BY created_at DESC, shipment_id DESC
LIMIT 100;

Keyset pagination provides stable performance as the dataset grows.

Keep Transactions Short

Long transactions retain row versions, hold locks, delay cleanup, increase deadlock probability, and make failure recovery more expensive.

Do not call external services inside a database transaction:

Bad flow:

BEGIN
  INSERT payment
  call external payment provider
  wait for network response
  UPDATE invoice
COMMIT

Problems:
- locks remain held during network latency
- provider timeout extends transaction duration
- retries become ambiguous
- deadlock risk increases

Commit local state and an outbox event, then perform external work asynchronously:

BEGIN
  INSERT payment attempt
  INSERT outbox event
COMMIT
       |
       v
Payment worker
       |
       +--> call provider
       +--> update result in a new short transaction

Protect the Database with Admission Control

Increasing the connection count does not automatically increase throughput. Too many concurrent queries increase context switching, memory use, lock contention, and storage queue depth.

Application connection pools should be bounded:

from __future__ import annotations

from sqlalchemy.ext.asyncio import AsyncEngine, create_async_engine


def create_database_engine(database_url: str) -> AsyncEngine:
    return create_async_engine(
        database_url,
        pool_size=20,
        max_overflow=10,
        pool_timeout=5,
        pool_recycle=1_800,
        pool_pre_ping=True,
        connect_args={
            "server_settings": {
                "statement_timeout": "5000",
                "lock_timeout": "1500",
                "idle_in_transaction_session_timeout": "10000",
            }
        },
    )

Pool and timeout values must be tested against database capacity and the number of application instances. One hundred containers with thirty connections each can overwhelm a database configured for several hundred active sessions.

Admission control should also exist at the request level. Expensive exports, backfills, and analytics jobs should have separate concurrency limits from customer-facing requests.

Scale Reads and Writes Deliberately

Scaling mechanisms solve different bottlenecks. Caches reduce repeated reads, replicas distribute read traffic, partitioning reduces scanned and maintained data, while sharding distributes storage and writes across independent databases.

Use Caching for the Right Data

Caching is useful when data is read frequently, changes less often, and can tolerate a defined staleness window.

Good cache candidates:

• configuration
• reference data
• permissions with short expiration
• computed summaries
• frequently requested entity details

Poor cache candidates:

• highly volatile records
• security decisions without controlled invalidation
• financial balances requiring authoritative reads
• large values with low reuse
• queries whose keys are not stable

Use expiration even when invalidation exists. TTL provides eventual cleanup if an invalidation event is lost.

Use Replicas with Explicit Consistency

Read replicas improve read capacity and isolate reporting traffic, but asynchronous replicas can return stale data.

Route reads based on consistency requirements:

• Use the primary for read-after-write workflows.
• Use replicas for timelines, dashboards, exports, and noncritical reads.
• Fall back to the primary when replica lag exceeds a threshold.
• Do not assume successful writes are immediately visible on replicas.

from __future__ import annotations

from dataclasses import dataclass
from typing import Protocol


class ShipmentRepository(Protocol):
    async def get(self, shipment_id: str) -> dict[str, object] | None:
        ...


@dataclass(frozen=True)
class ReplicaHealth:
    lag_seconds: float


class ShipmentReadRouter:
    def __init__(
        self,
        primary: ShipmentRepository,
        replica: ShipmentRepository,
        maximum_replica_lag_seconds: float = 2.0,
    ) -> None:
        self._primary = primary
        self._replica = replica
        self._maximum_replica_lag_seconds = maximum_replica_lag_seconds

    async def get(
        self,
        shipment_id: str,
        require_fresh: bool,
        replica_health: ReplicaHealth,
    ) -> dict[str, object] | None:
        if require_fresh:
            return await self._primary.get(shipment_id)

        if replica_health.lag_seconds > self._maximum_replica_lag_seconds:
            return await self._primary.get(shipment_id)

        return await self._replica.get(shipment_id)

This makes replica consistency an explicit application decision.

Partition Before Sharding

Partitioning can improve large-table pruning, retention, maintenance, and index size while keeping transactions inside one database cluster. Sharding distributes data across separate database instances but adds routing, cross-shard query, transaction, migration, and operational complexity.

Use partitioning when:

• large tables have natural time or tenant boundaries
• retention requires removing old data
• queries can prune unrelated ranges
• one database still has sufficient total write capacity

Consider sharding when:

• the primary write workload exceeds one cluster
• storage cannot fit one database safely
• tenant isolation requires separate failure domains
• connection and CPU demand cannot be reduced or scaled vertically

Sharding should solve a measured single-cluster limit, not anticipated future popularity.

Design Safe Write Paths

Scalable write paths must handle retries, concurrent updates, network timeouts, duplicate messages, and partial failures without corrupting state.

Make Writes Idempotent

Clients retry requests when responses are lost. Workers retry messages after crashes. A scalable system must treat retries as normal behavior.

Use a business key or idempotency key with a unique constraint:

CREATE TABLE request_idempotency (
    account_id BIGINT NOT NULL,
    idempotency_key TEXT NOT NULL,
    request_hash TEXT NOT NULL,
    status TEXT NOT NULL,
    response_payload JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMPTZ NOT NULL,

    PRIMARY KEY (account_id, idempotency_key),

    CONSTRAINT request_idempotency_status_check
        CHECK (status IN ('processing', 'completed', 'failed'))
);

The request hash prevents reuse of the same key for different request bodies.

Use Optimistic Concurrency

Optimistic concurrency prevents stale requests from overwriting newer state without holding long locks.

UPDATE shipments
SET status = 'in_transit',
    version = version + 1,
    updated_at = CURRENT_TIMESTAMP
WHERE shipment_id = :shipment_id
  AND version = :expected_version
  AND status = 'confirmed'
RETURNING shipment_id, status, version;

If no row is updated, the caller must reload current state and decide whether to retry, reject, or merge the change.

from __future__ import annotations

from dataclasses import dataclass

from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncConnection


class ConcurrentUpdateError(RuntimeError):
    pass


@dataclass(frozen=True)
class ShipmentState:
    shipment_id: str
    status: str
    version: int


async def mark_in_transit(
    connection: AsyncConnection,
    shipment_id: str,
    expected_version: int,
) -> ShipmentState:
    result = await connection.execute(
        text(
            """
            UPDATE shipments
            SET status = 'in_transit',
                version = version + 1,
                updated_at = CURRENT_TIMESTAMP
            WHERE shipment_id = :shipment_id
              AND version = :expected_version
              AND status = 'confirmed'
            RETURNING shipment_id, status, version
            """
        ),
        {
            "shipment_id": shipment_id,
            "expected_version": expected_version,
        },
    )
    row = result.mappings().one_or_none()

    if row is None:
        raise ConcurrentUpdateError(
            "Shipment changed before the update was applied"
        )

    return ShipmentState(
        shipment_id=str(row["shipment_id"]),
        status=str(row["status"]),
        version=int(row["version"]),
    )

Separate Transactions from Asynchronous Work

Use the transactional outbox pattern when a committed database change must produce a message.

CREATE TABLE outbox_events (
    event_id UUID PRIMARY KEY,
    aggregate_type TEXT NOT NULL,
    aggregate_id UUID NOT NULL,
    aggregate_version BIGINT NOT NULL,
    event_type TEXT NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    published_at TIMESTAMPTZ,

    CONSTRAINT outbox_event_version_unique
        UNIQUE (
            aggregate_type,
            aggregate_id,
            aggregate_version
        )
);

CREATE INDEX outbox_events_pending_idx
ON outbox_events (created_at)
WHERE published_at IS NULL;

The business write and outbox insert occur in one transaction. A publisher retries until the event is delivered. Consumers must still be idempotent because delivery can happen more than once.

Production Design Example

Consider a multi-tenant logistics platform processing shipment creation, tracking updates, customer timelines, search, and reporting.

The platform must support:

• transactional shipment creation
• high-volume tracking event ingestion
• fast recent shipment lists
• read-after-write consistency for shipment details
• eventually consistent search and analytics
• thousands of concurrent tenant requests

Architecture

Clients
   |
   v
API Gateway
   |
   v
Application Services
   |
   +--------------------+
   |                    |
   v                    v
Redis Cache        PostgreSQL Primary
                        |
              +---------+---------+
              |                   |
              v                   v
        Read Replicas        Outbox Publisher
                                      |
                                      v
                                Message Broker
                                      |
                    +-----------------+----------------+
                    |                 |                |
                    v                 v                v
               Search Index     Timeline Cache    Analytics Store

PostgreSQL owns authoritative shipment state. Redis caches frequently read summaries. Replicas handle noncritical reads and reporting. Search and analytics remain asynchronous projections.

Request and Data Flows

Shipment creation:

1. The API validates the account and idempotency key.
2. A short PostgreSQL transaction creates the shipment and outbox event.
3. The API commits before publishing external messages.
4. The outbox publisher sends the event asynchronously.
5. Search, cache, and analytics consumers update their projections.

Shipment details:

1. The service checks the cache for noncritical requests.
2. A request requiring current state reads from the primary.
3. Normal historical reads use a healthy replica.
4. The cache receives a short TTL and is invalidated by shipment events.

Tracking event ingestion:

1. The API validates and normalizes provider data.
2. Events are written in batches where possible.
3. The event table is partitioned by ingestion time.
4. A selective index supports shipment timeline queries.
5. Old partitions are archived and detached according to retention rules.

Failure Scenarios

The cache fails. Reads fall back to the database with request coalescing and strict concurrency limits. The database must not receive an uncontrolled cache-miss storm.

A replica lags. Requests needing freshness use the primary. Reporting traffic may pause if lag exceeds the operational limit.

The publisher crashes after sending an event. The event is published again. Consumers ignore duplicates using event IDs and aggregate versions.

A slow query deployment reaches production. Statement timeouts stop unbounded execution. Query monitoring identifies the new fingerprint, and the deployment can be rolled back.

The database reaches its connection limit. Application pools reject or queue requests instead of opening additional sessions. Noncritical work is shed first.

Storage approaches capacity. Retention jobs remove verified historical partitions, nonessential maintenance pauses, and alerts provide sufficient time to scale storage safely.

Ready-to-Use Example

The following examples combine schema constraints, idempotent writes, bounded pagination, cache-aside reads, and monitoring for a scalable shipment service.

PostgreSQL Schema

CREATE TYPE shipment_status AS ENUM (
    'confirmed',
    'in_transit',
    'delivered',
    'cancelled',
    'exception'
);

CREATE TABLE accounts (
    account_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_name TEXT NOT NULL,
    status TEXT NOT NULL CHECK (status IN ('active', 'suspended')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE shipments (
    shipment_id UUID PRIMARY KEY,
    account_id BIGINT NOT NULL
        REFERENCES accounts(account_id),
    external_reference TEXT NOT NULL,
    status shipment_status NOT NULL,
    carrier_code TEXT,
    tracking_number TEXT,
    version BIGINT NOT NULL DEFAULT 1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT shipments_account_reference_unique
        UNIQUE (account_id, external_reference),

    CONSTRAINT shipments_tracking_unique
        UNIQUE NULLS NOT DISTINCT (
            carrier_code,
            tracking_number
        )
);

CREATE INDEX shipments_account_created_idx
ON shipments (
    account_id,
    created_at DESC,
    shipment_id DESC
)
INCLUDE (
    status,
    carrier_code,
    tracking_number
);

CREATE INDEX shipments_active_attention_idx
ON shipments (
    account_id,
    updated_at DESC
)
WHERE status IN ('exception', 'in_transit');

FastAPI Write Service

from __future__ import annotations

import hashlib
import json
from dataclasses import dataclass
from uuid import UUID, uuid4

from fastapi import FastAPI, Header, HTTPException, status
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.exc import IntegrityError
from sqlalchemy.ext.asyncio import AsyncEngine


class CreateShipmentRequest(BaseModel):
    account_id: int
    external_reference: str = Field(min_length=1, max_length=100)
    carrier_code: str | None = Field(default=None, max_length=20)
    tracking_number: str | None = Field(default=None, max_length=100)


@dataclass(frozen=True)
class CreatedShipment:
    shipment_id: UUID
    status: str
    version: int


class ShipmentService:
    def __init__(self, engine: AsyncEngine) -> None:
        self._engine = engine

    async def create(
        self,
        request: CreateShipmentRequest,
        idempotency_key: str,
    ) -> CreatedShipment:
        request_hash = hashlib.sha256(
            json.dumps(
                request.model_dump(),
                sort_keys=True,
                separators=(",", ":"),
            ).encode("utf-8")
        ).hexdigest()

        shipment_id = uuid4()
        event_id = uuid4()

        try:
            async with self._engine.begin() as connection:
                idempotency_result = await connection.execute(
                    text(
                        """
                        INSERT INTO request_idempotency (
                            account_id,
                            idempotency_key,
                            request_hash,
                            status,
                            expires_at
                        )
                        VALUES (
                            :account_id,
                            :idempotency_key,
                            :request_hash,
                            'processing',
                            CURRENT_TIMESTAMP + INTERVAL '24 hours'
                        )
                        ON CONFLICT (account_id, idempotency_key)
                        DO NOTHING
                        RETURNING idempotency_key
                        """
                    ),
                    {
                        "account_id": request.account_id,
                        "idempotency_key": idempotency_key,
                        "request_hash": request_hash,
                    },
                )

                if idempotency_result.scalar_one_or_none() is None:
                    existing = await connection.execute(
                        text(
                            """
                            SELECT
                                request_hash,
                                status,
                                response_payload
                            FROM request_idempotency
                            WHERE account_id = :account_id
                              AND idempotency_key = :idempotency_key
                            """
                        ),
                        {
                            "account_id": request.account_id,
                            "idempotency_key": idempotency_key,
                        },
                    )
                    row = existing.mappings().one()

                    if row["request_hash"] != request_hash:
                        raise HTTPException(
                            status_code=status.HTTP_409_CONFLICT,
                            detail="Idempotency key reused with different data",
                        )

                    if row["status"] == "completed":
                        payload = row["response_payload"]
                        return CreatedShipment(
                            shipment_id=UUID(payload["shipment_id"]),
                            status=str(payload["status"]),
                            version=int(payload["version"]),
                        )

                    raise HTTPException(
                        status_code=status.HTTP_409_CONFLICT,
                        detail="Request is already processing",
                    )

                shipment_result = await connection.execute(
                    text(
                        """
                        INSERT INTO shipments (
                            shipment_id,
                            account_id,
                            external_reference,
                            status,
                            carrier_code,
                            tracking_number
                        )
                        SELECT
                            :shipment_id,
                            account_id,
                            :external_reference,
                            'confirmed',
                            :carrier_code,
                            :tracking_number
                        FROM accounts
                        WHERE account_id = :account_id
                          AND status = 'active'
                        RETURNING shipment_id, status, version
                        """
                    ),
                    {
                        **request.model_dump(),
                        "shipment_id": shipment_id,
                    },
                )
                shipment = shipment_result.mappings().one_or_none()

                if shipment is None:
                    raise HTTPException(
                        status_code=status.HTTP_404_NOT_FOUND,
                        detail="Active account not found",
                    )

                payload = {
                    "shipment_id": str(shipment_id),
                    "account_id": request.account_id,
                    "status": shipment["status"],
                    "version": shipment["version"],
                }

                await connection.execute(
                    text(
                        """
                        INSERT INTO outbox_events (
                            event_id,
                            aggregate_type,
                            aggregate_id,
                            aggregate_version,
                            event_type,
                            payload
                        )
                        VALUES (
                            :event_id,
                            'shipment',
                            :shipment_id,
                            :version,
                            'shipment.confirmed',
                            CAST(:payload AS JSONB)
                        )
                        """
                    ),
                    {
                        "event_id": event_id,
                        "shipment_id": shipment_id,
                        "version": shipment["version"],
                        "payload": json.dumps(payload),
                    },
                )

                await connection.execute(
                    text(
                        """
                        UPDATE request_idempotency
                        SET status = 'completed',
                            response_payload = CAST(:payload AS JSONB)
                        WHERE account_id = :account_id
                          AND idempotency_key = :idempotency_key
                        """
                    ),
                    {
                        "account_id": request.account_id,
                        "idempotency_key": idempotency_key,
                        "payload": json.dumps(payload),
                    },
                )

            return CreatedShipment(
                shipment_id=shipment_id,
                status=str(shipment["status"]),
                version=int(shipment["version"]),
            )

        except IntegrityError as error:
            raise HTTPException(
                status_code=status.HTTP_409_CONFLICT,
                detail="Shipment conflicts with an existing record",
            ) from error


app = FastAPI()

The idempotency record, shipment, and outbox event commit in one transaction. A retry receives the previous response instead of creating another shipment.

Bounded Pagination

from __future__ import annotations

from dataclasses import dataclass
from datetime import datetime
from uuid import UUID

from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncConnection


@dataclass(frozen=True)
class ShipmentCursor:
    created_at: datetime
    shipment_id: UUID


@dataclass(frozen=True)
class ShipmentSummary:
    shipment_id: UUID
    status: str
    carrier_code: str | None
    tracking_number: str | None
    created_at: datetime


async def list_shipments(
    connection: AsyncConnection,
    account_id: int,
    limit: int,
    cursor: ShipmentCursor | None = None,
) -> list[ShipmentSummary]:
    safe_limit = min(max(limit, 1), 100)

    cursor_filter = ""
    parameters: dict[str, object] = {
        "account_id": account_id,
        "limit": safe_limit,
    }

    if cursor is not None:
        cursor_filter = """
          AND (created_at, shipment_id)
              < (:cursor_created_at, :cursor_shipment_id)
        """
        parameters.update(
            {
                "cursor_created_at": cursor.created_at,
                "cursor_shipment_id": cursor.shipment_id,
            }
        )

    result = await connection.execute(
        text(
            f"""
            SELECT
                shipment_id,
                status,
                carrier_code,
                tracking_number,
                created_at
            FROM shipments
            WHERE account_id = :account_id
            {cursor_filter}
            ORDER BY created_at DESC, shipment_id DESC
            LIMIT :limit
            """
        ),
        parameters,
    )

    return [
        ShipmentSummary(
            shipment_id=row["shipment_id"],
            status=str(row["status"]),
            carrier_code=row["carrier_code"],
            tracking_number=row["tracking_number"],
            created_at=row["created_at"],
        )
        for row in result.mappings()
    ]

The method enforces tenant filtering, a maximum page size, stable ordering, and keyset pagination.

Cache-Aside Reader

from __future__ import annotations

import asyncio
import json
from typing import Protocol


class CacheClient(Protocol):
    async def get(self, key: str) -> str | None:
        ...

    async def set(
        self,
        key: str,
        value: str,
        expiration_seconds: int,
    ) -> None:
        ...


class ShipmentRepository(Protocol):
    async def get(self, shipment_id: str) -> dict[str, object] | None:
        ...


class ShipmentCachedReader:
    def __init__(
        self,
        cache: CacheClient,
        repository: ShipmentRepository,
        ttl_seconds: int = 30,
    ) -> None:
        self._cache = cache
        self._repository = repository
        self._ttl_seconds = ttl_seconds
        self._locks: dict[str, asyncio.Lock] = {}

    async def get(
        self,
        shipment_id: str,
    ) -> dict[str, object] | None:
        cache_key = f"shipment:{shipment_id}"
        cached = await self._cache.get(cache_key)

        if cached is not None:
            return json.loads(cached)

        # Coalesce concurrent misses inside one application process.
        lock = self._locks.setdefault(cache_key, asyncio.Lock())

        async with lock:
            cached = await self._cache.get(cache_key)
            if cached is not None:
                return json.loads(cached)

            shipment = await self._repository.get(shipment_id)
            if shipment is None:
                return None

            await self._cache.set(
                cache_key,
                json.dumps(shipment, default=str),
                expiration_seconds=self._ttl_seconds,
            )
            return shipment

Request coalescing reduces repeated database reads during cache misses. A distributed lock is not always necessary; short TTLs and database protection may be simpler and safer.

Database Monitoring Queries

Identify expensive query fingerprints:

SELECT
    queryid,
    calls,
    ROUND(mean_exec_time::NUMERIC, 2) AS mean_exec_ms,
    ROUND(total_exec_time::NUMERIC, 2) AS total_exec_ms,
    rows,
    LEFT(query, 300) AS query_sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Find long-running transactions:

SELECT
    pid,
    usename,
    application_name,
    state,
    CURRENT_TIMESTAMP - xact_start AS transaction_age,
    wait_event_type,
    wait_event,
    LEFT(query, 300) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY transaction_age DESC;

Review table and index sizes:

SELECT
    schemaname,
    relname AS table_name,
    pg_size_pretty(
        pg_total_relation_size(
            quote_ident(schemaname) || '.' || quote_ident(relname)
        )
    ) AS total_size,
    n_live_tup,
    n_dead_tup,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(
    quote_ident(schemaname) || '.' || quote_ident(relname)
) DESC
LIMIT 20;

Operational Practices

Scalability depends on how safely the database is changed, monitored, backed up, and recovered.

Use backward-compatible migrations. Add new columns and tables before deploying code that depends on them. Backfill separately, switch reads and writes gradually, then remove old fields later.

Avoid large blocking changes. Build indexes using online or concurrent mechanisms where supported. Validate constraints separately when possible. Break backfills into restartable batches.

Throttle maintenance. Backfills, index builds, exports, and archival jobs should pause when CPU, replica lag, lock waits, or storage latency exceed thresholds.

Test restoration. A backup is not proven until it has been restored, verified, and used to recover the application.

Separate workloads. Transactional requests, analytics, search, exports, and maintenance should not compete without explicit resource controls.

Track capacity trends. Monitor data growth, index growth, connection use, cache hit rates, write amplification, replica lag, and time remaining before storage thresholds.

Common Mistakes

Production Checklist

• Enforce business invariants with database constraints.
• Index measured queries, not hypothetical ones.
• Require tenant filters and bounded pagination.
• Set query, lock, and transaction timeouts.
• Keep database transactions short.
• Bound connection pools and background-job concurrency.
• Make client and worker writes idempotent.
• Route reads according to consistency requirements.
• Use TTLs and invalidation for cached data.
• Monitor slow queries, locks, lag, storage, and connection use.
• Run backfills in restartable batches.
• Use backward-compatible schema migrations.
• Test backups and disaster recovery regularly.
• Separate transactional, search, and analytical workloads.
• Introduce sharding only after measuring a single-cluster limit.

Conclusion

Scalable database architecture begins with correct schemas, bounded access patterns, selective indexes, short transactions, and controlled concurrency. Caching, replicas, partitioning, and sharding should be added to solve specific measured bottlenecks.

Operational discipline matters as much as schema design. Safe migrations, idempotent backfills, replica-aware reads, tested backups, capacity monitoring, and failure recovery determine whether the database remains reliable as the system grows.

Key Takeaway: Build scalability by reducing unnecessary database work, protecting invariants, controlling concurrency, and introducing distributed complexity only when simpler optimizations no longer meet measured production requirements.

More Articles to Read

• Database Scaling Explained: Vertical vs Horizontal Scaling
• Replication and Read Replicas in Distributed Databases
• Database Sharding Strategies and Trade-Offs
• Designing High-Performance Database Schemas
• Partitioning Large Tables for Production Systems
• SQL vs NoSQL: Choosing the Right Database