Skills

Do NOT activate this skill for purely application logic design, frontend rendering, or API contract design that has no data modeling or storage component.

Instructions

Phase 1: Data Requirements Discovery and Access Pattern Analysis

1. Identify the data domain and its purpose. Before any modeling or technology selection, explicitly state what data this system manages and why. If the user has not clearly stated this, ask directly: "What are the core data entities this system manages, and what are the primary operations performed on them?" Do not proceed until the data domain is understood. State it in one to two sentences. Example: "This system manages e-commerce order data, including orders, line items, payments, and fulfillment records. The primary operations are order creation, status tracking, customer order history retrieval, and operational reporting."

2. Catalog the access patterns exhaustively. This is the single most important step in database architecture — technology selection, schema design, indexing, and partitioning all flow from access patterns. For each identified access pattern:

   • Name the operation descriptively (e.g., "Look up order by ID," "List orders for a customer sorted by date," "Search products by name and category," "Aggregate daily revenue by region").
   • Classify the operation: Read or Write. For reads: point lookup, range scan, full-text search, aggregation, or graph traversal. For writes: insert, update, upsert, delete, or append.
   • Estimate frequency: How often does this operation occur? Requests per second at current scale and at projected scale (1 year, 3 years).
   • Estimate data volume: How many rows/documents are read or written per operation? What is the expected result set size?
   • Identify the lookup keys and filter conditions: What fields are used to find the data? (e.g., "by customer_id and status, sorted by created_at descending").
   • Determine latency requirements: What is the acceptable response time? (e.g., "< 10ms for point lookups, < 100ms for customer order history, < 5s for monthly revenue report").
   • Determine consistency requirements: Must this operation see the latest write immediately (strong consistency), or is a delay of seconds/minutes acceptable (eventual consistency)?
   • Classify criticality: Is this on the critical user-facing path, a background process, or an analytical query?

   Produce a numbered access pattern catalog. This catalog is the foundation for every subsequent decision. Reference specific access pattern numbers when justifying design choices.

3. Characterize the data profile. Establish concrete facts about the data:

   • Read-to-write ratio: Is this system read-heavy (100:1), write-heavy (1:10), or balanced (1:1)? This fundamentally affects technology choice and optimization strategy.
   • Data growth rate: How many new records per day/month? What is the expected total data volume in 1 year, 3 years, 5 years?
   • Record size: Average and maximum size of individual records. Are there large text fields, binary data, or deeply nested structures?
   • Data relationships: Are relationships simple (foreign keys between a few tables) or complex (deep hierarchies, many-to-many, graph-like traversals)?
   • Data mutability: Is data mostly immutable after creation (append-only logs, events, transactions) or frequently updated (user profiles, inventory counts)?
   • Data temperature: What percentage of data is "hot" (accessed frequently), "warm" (accessed occasionally), or "cold" (rarely accessed, archival)?
   • Temporal characteristics: Is there a strong time-based component (time-series, event logs)? Do queries primarily filter by time ranges?
   • Cardinality: For key fields, what is the expected number of distinct values? (Affects index selectivity and partition distribution.)

4. Identify constraints and requirements. Establish:

   • Regulatory and compliance requirements: GDPR (right to deletion, data residency), HIPAA (audit logging, encryption), PCI-DSS (cardholder data isolation), SOC 2, or industry-specific regulations. These constrain technology choices, encryption requirements, and data lifecycle design.
   • Team expertise: What databases does the team have production experience operating? Introducing a new technology has a steep operational learning curve — factor this into every recommendation.
   • Infrastructure constraints: Cloud provider (AWS, GCP, Azure), existing managed services, VPC networking, region availability.
   • Budget constraints: Managed service costs, license costs for commercial databases, storage costs at projected volume.
   • Existing systems: What databases are already in production? Is there a mandate to consolidate or a preference to reuse existing infrastructure?
   • Operational capacity: Does the team have dedicated DBAs, or do application engineers manage the database? This affects the choice between self-managed and fully managed services.

Phase 2: Database Technology Selection

5. Select the primary database technology based on the access pattern catalog. Match the technology to the dominant access patterns — not to trends, not to familiarity, and not to a single feature. Evaluate each candidate against the full access pattern catalog from step 2.

   Relational databases (PostgreSQL, MySQL, SQL Server):

   • Select when: Data has well-defined structure and relationships. Access patterns include complex joins, multi-table transactions, aggregations, and ad-hoc queries. ACID transactions are required for core operations. The data model is unlikely to change radically. Most access patterns involve structured queries with known predicates.
   • PostgreSQL (default recommendation for relational): Superior feature set — JSONB for semi-structured data within a relational model, partial indexes, expression indexes, CTEs, window functions, full-text search (adequate for moderate search needs), excellent extension ecosystem (PostGIS, pg_trgm, timescaledb). Choose PostgreSQL unless there is a specific reason not to.
   • MySQL/Aurora MySQL: When the team has deep MySQL expertise, when Aurora's architecture provides needed read scaling, or when specific MySQL ecosystem tools are required. MySQL 8.0+ has closed many feature gaps with PostgreSQL.
   • SQL Server: When the organization is Microsoft-ecosystem committed, or when specific SQL Server features (SSRS, SSIS, in-memory OLTP) are required.
   • Managed service recommendation: Amazon RDS/Aurora, Google Cloud SQL/AlloyDB, Azure Database for PostgreSQL/SQL. Always prefer managed unless regulatory or latency requirements mandate self-managed.

   Document databases (MongoDB, Amazon DocumentDB, Firestore, Cosmos DB):

   • Select when: The data model is document-oriented — self-contained objects that are read and written as a unit. Access patterns are primarily key-based lookups or simple queries on top-level fields. The schema evolves frequently and different records may have different structures. Relationships between documents are minimal — if you frequently need to join across collections, a document database is the wrong choice.
   • MongoDB: When you need flexible schema, rich query capabilities within documents, aggregation pipeline, and the operational maturity of Atlas (managed).
   • Do NOT select document databases because: "We might change the schema later" (relational schema migrations are a solved problem), "JSON is easier" (PostgreSQL JSONB gives you JSON within a relational model), or "NoSQL scales better" (this is not inherently true and depends on access patterns).

   Key-value stores (Redis, Memcached, DynamoDB, Valkey):

   • Select when: Access patterns are overwhelmingly key-based lookups and writes with known keys. Extremely low latency is required (sub-millisecond). The data model is simple — key → value with no complex querying.
   • Redis / Valkey: For caching, session storage, rate limiting, leaderboards, real-time counters, distributed locks, and pub/sub. Use as a caching layer or ephemeral data store, not as a primary database for durable data (unless using Redis with AOF persistence and accepting its operational characteristics).
   • DynamoDB: When you need a fully managed, serverless key-value/document store with predictable single-digit-millisecond latency at any scale, and your access patterns are well-defined at design time. DynamoDB requires you to design your data model around access patterns upfront — retrofitting new access patterns is expensive. Select DynamoDB when access patterns are stable, scale is high, and operational simplicity is paramount.

   Wide-column stores (Apache Cassandra, ScyllaDB, Google Bigtable):

   • Select when: Write throughput is extremely high (tens of thousands to millions of writes per second). Data is append-heavy or time-series in nature. Queries are limited to partition key lookups and range scans within a partition. Multi-region, active-active replication is required natively. Tolerance for eventual consistency exists.
   • Do NOT select when: You need complex queries, joins, aggregations, or secondary indexes on arbitrary fields. Cassandra's query model is restrictive by design — every query must be satisfied by the table's partition and clustering key structure.

   Search engines (Elasticsearch/OpenSearch):

   • Select when: Full-text search, fuzzy matching, faceted search, log analytics, or complex filtering and aggregation over semi-structured data are primary access patterns.
   • Never use as the sole primary data store: Search engines can lose data during failures and are not ACID-compliant. Always maintain a source-of-truth data store and feed the search engine via CDC, events, or dual-write with reconciliation.
   • Consider PostgreSQL full-text search first: For moderate search needs (< 10M documents, simple text matching), PostgreSQL's built-in full-text search with GIN indexes may be sufficient and eliminates the operational overhead of a separate search cluster.

   Graph databases (Neo4j, Amazon Neptune, Memgraph):

   • Select when: The core query patterns involve multi-hop traversals across complex relationships — social networks (friends of friends), permission hierarchies (does user X have access to resource Y through group membership?), fraud detection (find circular transaction patterns), recommendation engines (users who bought X also bought Y via shared attributes).
   • Do NOT select because: "Our data has relationships." All relational databases handle relationships. Graph databases are justified only when the query pattern is fundamentally about traversing relationships of variable or unknown depth.

   Time-series databases (TimescaleDB, InfluxDB, QuestDB, Amazon Timestream):

   • Select when: The primary data model is timestamped measurements — IoT sensor data, application metrics, financial tick data, user activity events. Queries are dominated by time-range scans, downsampling, and time-windowed aggregations. Data retention policies with automatic expiration are important.
   • TimescaleDB: Preferred when you want time-series capabilities as a PostgreSQL extension — you get time-series optimizations while retaining full SQL, joins with relational tables, and the PostgreSQL ecosystem.

   Vector databases (Pinecone, Weaviate, pgvector, Milvus, Qdrant):

   • Select when: The system performs similarity search over high-dimensional embeddings — semantic search, recommendation systems, image/audio similarity, RAG (retrieval-augmented generation) for LLMs.
   • pgvector: Preferred when vector search is a secondary access pattern alongside relational data, and the embedding dataset is moderate (< 10M vectors). Keeps the operational footprint simple.
   • Dedicated vector database: When vector search is the primary access pattern, the dataset is large (100M+ vectors), and advanced features (filtering during ANN search, real-time index updates, sharding) are required.

   Object storage (S3, GCS, Azure Blob Storage):

   • Select for: Files, media, backups, data lake raw storage, large binary objects. Never store large blobs (> 1MB) in a relational database — store a reference (URL/key) in the database and the blob in object storage.

6. Justify the selection explicitly. For every technology choice, state:

   • Which access patterns from the catalog (by number) this technology satisfies.
   • What the technology gives you (specific capabilities matched to specific requirements).
   • What the technology costs you (operational complexity, limitations, learning curve).
   • What alternatives were considered and why they were rejected for this specific system.
   • Under what conditions you would reconsider this choice.

7. Design polyglot persistence when justified. If no single database satisfies all access patterns, design a multi-database architecture:

   • Define which database handles which access patterns. Each access pattern must have exactly one primary data store.
   • Define the system of record for each entity — which database holds the authoritative version.
   • Design the synchronization mechanism between databases (CDC, events, dual-write with reconciliation). State the consistency model — how stale can secondary stores be?
   • State the operational cost of polyglot persistence: more systems to monitor, more failure modes, more expertise required. Only adopt when the access pattern mismatch genuinely cannot be solved by a single technology.

Phase 3: Logical Data Modeling

8. Build the logical data model. Before any physical schema design, model the data conceptually:

   • Identify all entities in the domain. For each entity:
     • Name it clearly and specifically.
     • State its definition in one sentence.
     • List its attributes with data types, nullability, and business constraints.
     • Classify it: is this an independent entity (e.g., Customer), a dependent entity (e.g., OrderLineItem, which cannot exist without an Order), or a reference/lookup entity (e.g., Country, Currency)?
   • Identify all relationships between entities:
     • State the relationship type: one-to-one, one-to-many, many-to-many.
     • State the cardinality with specificity: "One Customer has zero to many Orders. One Order belongs to exactly one Customer."
     • State the ownership/lifecycle dependency: "When an Order is deleted, its LineItems are deleted (cascade). When a Customer is deleted, their Orders are retained (no cascade, set customer reference to null or anonymized)."
   • Identify entity lifecycle states. For entities with state machines (Orders: created → confirmed → shipped → delivered → returned), define the valid states and valid transitions. This informs status field design and constraint enforcement.
   • Produce an entity-relationship description or diagram. This model is technology-agnostic — it represents the business domain, not the physical storage.

9. Identify aggregate boundaries (for document and DDD-oriented models). An aggregate is a cluster of entities that are always read and written together as a consistency boundary:

   • Define which entities belong to the same aggregate. Example: Order + LineItems + ShippingAddress form an aggregate; Customer is a separate aggregate.
   • The aggregate root (e.g., Order) is the only entity directly accessible from outside — LineItems are accessed through the Order.
   • Transactions should not span multiple aggregates. If a business operation touches multiple aggregates, use eventual consistency (events, sagas) rather than distributed transactions.
   • Aggregate boundaries directly inform document structure (in document databases) and table decomposition (in relational databases).

10. Design for data integrity at the model level. Before physical implementation, define:

    • Uniqueness constraints: Which fields or combinations must be unique? (e.g., email per tenant, SKU per merchant.)
    • Referential integrity: Which relationships must be enforced at the database level vs. application level? In relational databases, use foreign keys for critical relationships. In document databases, accept that referential integrity is the application's responsibility and design for it.
    • Business rule constraints: CHECK constraints, valid value ranges, conditional requirements (e.g., "if status = 'shipped', tracking_number must not be null").
    • Temporal integrity: If the model tracks time (created_at, updated_at, deleted_at), define the timestamp source (database server time via now() vs. application time) and the timezone standard (UTC always — never local time).

Phase 4: Physical Schema Design (Relational Databases)

11. Determine the normalization level and justify it. Start with Third Normal Form (3NF) as the default and deviate intentionally:

    • 3NF (default): Eliminates data redundancy, ensures data integrity, and simplifies updates. Use for the system of record and any data that is frequently updated.
    • Controlled denormalization: Introduce only when a specific, measured access pattern requires it and the performance benefit is demonstrated. For each denormalization:
      • State which access pattern it serves (by number from the catalog).
      • State what data is duplicated and where.
      • Define the update propagation strategy — how is the denormalized copy kept in sync? (Trigger, application code, event-driven update, materialized view.)
      • State the risk: data inconsistency if the sync mechanism fails.
    • Common justified denormalizations:
      • Storing a computed count (e.g., order_count on Customer) to avoid COUNT queries on large tables. Update via trigger or application code.
      • Storing a snapshot of related data at a point in time (e.g., product name and price on OrderLineItem at the time of purchase — this is not denormalization, it is intentional snapshotting because the source data may change).
      • Creating a read-optimized table or materialized view for a specific reporting query.

12. Design the table structure. For each table:

    • Table name: Plural, snake_case (e.g., orders, order_line_items, customer_addresses).
    • Primary key: Define the PK strategy:
      • UUID (uuid/uuid_v7): Recommended for distributed systems, avoids sequential ID enumeration, safe for external exposure. UUIDv7 is preferred over UUIDv4 because it is time-ordered, which provides better index locality and insert performance in B-tree indexes.
      • BIGSERIAL (auto-increment): Simpler, smaller storage, better index performance than random UUIDs. Use when the system is single-database, IDs are not externally exposed, and the simplicity benefit outweighs the distribution limitation.
      • Natural key: Use only when a stable, immutable, globally unique natural identifier exists (e.g., ISO country code). Rarely appropriate for primary entities.
      • State the choice and justification per table.
    • Columns: Define each column with:
      • Name (snake_case).
      • Data type (be specific: VARCHAR(255) not just "string"; NUMERIC(12,2) for money, not FLOAT; TIMESTAMPTZ not TIMESTAMP).
      • Nullability (NOT NULL by default — make columns nullable only when null has a defined semantic meaning).
      • Default value if applicable.
      • Constraints (UNIQUE, CHECK, FOREIGN KEY).
    • Audit columns: Include created_at TIMESTAMPTZ NOT NULL DEFAULT now() and updated_at TIMESTAMPTZ NOT NULL DEFAULT now() on every table. Use a trigger or application code to maintain updated_at.
    • Soft delete: If the system requires soft delete, add deleted_at TIMESTAMPTZ NULL. Define whether queries automatically filter deleted records (application-level concern or database view). State the tradeoff: soft delete complicates queries (every query must filter WHERE deleted_at IS NULL) but enables recovery and audit trails. If the primary motivation is audit trail, consider an audit log table instead of soft delete.

13. Design foreign key relationships. For each relationship:

    • Define the FK column (e.g., customer_id BIGINT NOT NULL REFERENCES customers(id)).
    • Define the ON DELETE behavior:
      • CASCADE: Child records are deleted when parent is deleted. Use for strong ownership (Order → LineItems).
      • RESTRICT / NO ACTION: Prevent parent deletion if children exist. Use when children should outlive the parent relationship or deletion should be explicit.
      • SET NULL: Set FK to NULL when parent is deleted. Use when the relationship is optional and historical reference should be preserved without the parent.
    • Define the ON UPDATE behavior (usually CASCADE or NO ACTION).
    • Index every foreign key column. Unindexed FKs cause full table scans on parent deletes and join operations. This is one of the most common performance mistakes.

14. Design enum and status fields. Choose one approach consistently:

    • PostgreSQL ENUM type: Type-safe, compact storage. Drawback: adding values requires ALTER TYPE, which acquires a lock. Acceptable for small, rarely changing enums.
    • VARCHAR with CHECK constraint: More flexible — adding values requires altering the CHECK constraint, which is simpler. Recommended for enums that may grow.
    • Lookup/reference table: For enums that have additional attributes (e.g., status table with status code, display name, description, sort order). Use FK from the main table to the lookup table.
    • Integer codes: Avoid. They are unreadable in query results and debugging.
    • State the convention and apply it across all tables.

15. Design JSON/JSONB columns judiciously. JSONB in PostgreSQL is powerful but must be used intentionally:

    • Appropriate uses: Storing genuinely semi-structured data that varies per record (custom form fields, product attributes that differ by category, third-party webhook payloads, user preferences). Data that is written and read as a unit and rarely queried by internal fields.
    • Inappropriate uses: Storing structured data that you regularly filter, join, or aggregate on. If you are writing queries like WHERE metadata->>'status' = 'active' frequently, that field should be a proper column.
    • If using JSONB: Define the expected schema (even though the database doesn't enforce it — document it and validate in application code). Create GIN indexes on JSONB columns only for fields that are actually queried. Use expression indexes (CREATE INDEX idx ON table ((data->>'field'))) for specific field lookups.

16. Design multi-tenant data architecture (if applicable). Choose and justify:

    • Shared tables with tenant_id column (recommended for most SaaS systems):
      • Add tenant_id to every table. Include tenant_id in every query's WHERE clause and every index.
      • Use Row-Level Security (RLS) in PostgreSQL to enforce tenant isolation at the database level — prevents accidental cross-tenant data access even if application code omits the tenant filter.
      • Tradeoff: simplest operationally, but noisy neighbor risk (one tenant's heavy queries affect others) and compliance concerns (data is commingled).
    • Schema-per-tenant (PostgreSQL schemas):
      • Each tenant gets their own schema with identical table structures. Application sets search_path per request.
      • Tradeoff: better isolation, easier per-tenant backup/restore, but schema migrations must be applied to all schemas (operational complexity grows linearly with tenant count). Viable up to ~1000 tenants.
    • Database-per-tenant:
      • Maximum isolation, independent scaling, per-tenant backup/restore. Required for strict compliance (HIPAA, certain financial regulations).
      • Tradeoff: highest operational complexity, connection management challenges, migration coordination across all databases. Use only when compliance or customer contracts require it.

Phase 5: Physical Schema Design (Non-Relational Databases)

17. Design document schemas (MongoDB, DynamoDB, Firestore). For document databases, the schema design is driven entirely by access patterns:

    • Embed vs. Reference decision for each relationship:
      • Embed (denormalize into the parent document) when: the child data is always read with the parent, the child data is owned by the parent and has no independent lifecycle, the cardinality is bounded and small (1:few), and the child data doesn't change independently of the parent.
      • Reference (store a foreign ID and fetch separately) when: the related data is accessed independently, the cardinality is unbounded (1:many with many > 100), the related data changes frequently and independently, or the related data is shared across multiple parents.
    • Design for the primary query, not for normalization. In document databases, you optimize the write path and document structure for the most common read pattern. If you need to read customer + orders + line items in one call, embed or structure accordingly.
    • Avoid unbounded arrays in documents. A document that grows indefinitely (e.g., an array of all orders embedded in a customer document) will cause performance degradation and eventually hit document size limits (16MB in MongoDB). Use a separate collection with a reference when the array can grow unboundedly.

18. Design DynamoDB table and key structures (if DynamoDB is selected). DynamoDB requires access-pattern-first design:

    • Define the partition key (PK) based on the highest-cardinality, most-queried attribute. The partition key must distribute data evenly — avoid hot partitions.
    • Define the sort key (SK) to enable range queries within a partition. Use composite sort keys for hierarchical access: SK = ORDER#2024-01-15#ord_123.
    • Design single-table or multi-table: Single-table design (storing multiple entity types in one table) is recommended when: entities are frequently accessed together, and the access patterns are well-defined. Use multi-table when entities are unrelated and accessed by different services.
    • Design Global Secondary Indexes (GSIs) for each access pattern that cannot be satisfied by the base table's key structure. Each GSI has its own partition and sort key. Minimize GSI count — each GSI duplicates data and consumes additional write capacity.
    • Design the item structure: Overload PK/SK attributes with prefixed entity types (e.g., PK: CUSTOMER#cust_123, SK: ORDER#2024-01-15). Document the key schema exhaustively — DynamoDB single-table design is unreadable without documentation.

19. Design wide-column schemas (Cassandra/ScyllaDB, if selected). Cassandra requires one-table-per-query design:

    • Define the partition key to ensure even data distribution and to satisfy the query's equality predicates. A partition should not exceed 100MB.
    • Define clustering columns to satisfy the query's range predicates and sort order within a partition.
    • Create a separate table for each distinct query pattern. Duplicate data across tables — this is by design in Cassandra, not a mistake. Data is written redundantly to serve each query efficiently.
    • Design the compaction strategy based on the workload: Size-Tiered (write-heavy, default), Leveled (read-heavy, space-efficient), Time-Window (time-series data with TTL).

Phase 6: Indexing Strategy

20. Design indexes based on the access pattern catalog. Every index must be justified by a specific access pattern from step 2. Do not create speculative indexes — unused indexes waste storage, slow down writes, and consume memory.

    For each access pattern that involves a query:

    • Identify the WHERE clause predicates: These are the index candidates.
    • Identify the ORDER BY clause: The index should support the sort order to avoid in-memory sorts.
    • Identify the SELECT fields: Consider covering indexes to avoid heap lookups.

21. Apply indexing rules (relational databases):

    • Single-column indexes: For simple equality lookups on high-selectivity columns. Example: CREATE INDEX idx_orders_customer_id ON orders(customer_id) for "find orders by customer."
    • Composite (multi-column) indexes: For queries that filter on multiple columns. Column order matters — place equality predicates first, then range predicates, then sort columns. Follow the "equality, range, sort" (ERS) principle.
      • Example: For query WHERE customer_id = ? AND status = ? ORDER BY created_at DESC, create index (customer_id, status, created_at DESC).
    • Covering indexes: Include frequently selected columns in the index using INCLUDE to avoid heap lookups entirely. Example: CREATE INDEX idx ON orders(customer_id, status) INCLUDE (total, created_at).
    • Partial indexes: Index only a subset of rows. Dramatically reduces index size for queries that always filter by a condition. Example: CREATE INDEX idx_active_orders ON orders(customer_id, created_at) WHERE status != 'cancelled' — only indexes non-cancelled orders.
    • Expression indexes: Index computed values. Example: CREATE INDEX idx_lower_email ON users(LOWER(email)) for case-insensitive email lookups.
    • GIN indexes: For JSONB fields, array containment, full-text search (tsvector), and trigram similarity (pg_trgm).
    • BRIN indexes: For large tables where the indexed column is naturally correlated with physical row order (e.g., created_at on an append-only table). Much smaller than B-tree indexes.
    • Unique indexes: For enforcing uniqueness constraints. Prefer unique indexes over application-level uniqueness checks — they prevent race conditions.

22. Analyze index impact on writes. For every index added:

    • State the write amplification cost: each insert and relevant update must update every index on the table.
    • For write-heavy tables, limit the total number of indexes. If a table has > 5-6 indexes, review whether all are necessary or whether some access patterns can be served differently (materialized views, denormalized read tables, search engine offload).
    • Monitor index usage in production. Remove indexes with zero or near-zero scans — they are pure cost with no benefit.

23. Plan index maintenance. Define:

    • Index bloat monitoring and remediation: In PostgreSQL, B-tree indexes accumulate bloat from updates and deletes. Monitor bloat ratio and reindex periodically (REINDEX CONCURRENTLY for zero-downtime reindexing).
    • Statistics and query planner: Ensure ANALYZE runs regularly (autovacuum handles this in PostgreSQL, but verify settings). Stale statistics cause the query planner to choose suboptimal plans.
    • Index creation strategy for production: Always use CREATE INDEX CONCURRENTLY on PostgreSQL production databases to avoid locking the table during index creation. Plan for the extra time and disk space this requires.

Phase 7: Partitioning and Sharding

24. Design table partitioning for large tables. Partitioning improves query performance and operational management for tables that grow continuously. Apply when a single table is projected to exceed 50-100M rows or when data lifecycle management (retention, archival) needs differ by partition.

    • Range partitioning (most common): Partition by time range (monthly, weekly, or daily based on ingestion rate). Example: orders partitioned by created_at month. Queries that filter by time range scan only relevant partitions (partition pruning).
    • List partitioning: Partition by a discrete value (tenant_id, region, status). Use when queries almost always filter by the partition key.
    • Hash partitioning: Partition by hash of a column for even distribution when there is no natural range or list key. Useful for distributing load but does not enable range-based partition pruning.
    • Define the partition key: Must align with the most frequent query filter. If 90% of queries filter by created_at, partition by time. If most queries filter by tenant_id, partition by tenant.
    • Define the partition granularity: Too few partitions (yearly) provide little benefit. Too many partitions (per-minute) cause overhead in planning and metadata management. Choose based on data volume per partition — target 1M-100M rows per partition as a guideline.
    • Define partition lifecycle: Automate partition creation (create future partitions ahead of time) and partition detachment/archival (detach old partitions, move to cold storage, or drop after retention period).
    • Index each partition: In PostgreSQL declarative partitioning, indexes are created on each partition individually. Unique indexes must include the partition key.

25. Design database sharding (horizontal scaling across database instances) only when necessary. Sharding is the strategy of last resort after exhausting:

    • Vertical scaling (bigger instance).
    • Read replicas for read scaling.
    • Table partitioning for data management.
    • Caching for read reduction.
    • CQRS for separating read and write paths.

    If sharding is genuinely required:

    • Choose the shard key: Must distribute data evenly (avoid hot shards), must be present in every query (to enable shard routing), and should align with the primary access pattern. Common shard keys: tenant_id (for multi-tenant SaaS), user_id (for user-scoped data), geographic region.
    • Define the sharding strategy:
      • Application-level sharding: Application determines which shard to query. Most control, most application complexity. Use routing configuration or consistent hashing.
      • Proxy-based sharding: A proxy layer (Vitess, Citus, ProxySQL) handles shard routing transparently. Reduces application complexity but introduces infrastructure complexity.
      • Managed sharding: Some managed databases handle sharding natively (DynamoDB, Cosmos DB, CockroachDB, TiDB). Prefer these if the technology fits the access patterns.
    • Address cross-shard queries: Queries that span shards are expensive and complex. Design the shard key to minimize cross-shard queries. If a query cannot avoid spanning shards, design a separate aggregation layer (materialized views, analytics database).
    • Address cross-shard transactions: Distributed transactions across shards are extremely complex. Avoid them — design aggregate boundaries to fit within a single shard. If cross-shard coordination is needed, use saga/event-driven patterns.
    • Plan resharding: As data grows, you may need to split shards. Design the sharding scheme to support resharding (consistent hashing, virtual shards mapped to physical shards) and plan the operational procedure.

Phase 8: Replication, High Availability, and Disaster Recovery

26. Design the replication topology. Based on availability and read-scaling requirements:

    • Single primary with synchronous replicas (for HA within a region): The primary replicates to one or more standbys. Synchronous replication ensures zero data loss on failover (RPO = 0) but adds write latency (each write waits for standby acknowledgment). Recommended for data where any loss is unacceptable (financial transactions).
    • Single primary with asynchronous replicas (most common): Replicas lag behind the primary by milliseconds to seconds. Provides HA with near-zero data loss and enables read scaling. Recommended as the default for most systems. State the expected replication lag and the consequence: reads from replicas may return slightly stale data.
    • Read replicas for scaling: Route read-heavy access patterns (reports, dashboards, search, customer-facing reads that tolerate slight staleness) to replicas. Keep writes on the primary. Define which access patterns can read from replicas and which must read from the primary (e.g., "after creating an order, subsequent reads must use the primary to avoid read-your-own-write inconsistency, or use session affinity to route to the replica that received the write").
    • Multi-region replication: For global applications or disaster recovery:
      • Active-passive: Primary region handles all writes, secondary region has a replica for failover. RTO depends on failover automation (manual: minutes-hours; automated: seconds-minutes).
      • Active-active: Both regions handle writes. Requires conflict resolution strategy (last-write-wins, application-level merge, CRDTs). Significantly more complex. Recommend only when latency requirements demand writes from multiple regions.
    • Define the failover procedure: automatic (managed service handles it) or manual (runbook with steps, responsible team, estimated RTO).

27. Design the backup and recovery strategy. Define:

    • Backup types:
      • Automated daily snapshots: Full database snapshots stored in a separate region/account. Retention: 7-30 days minimum, longer for compliance.
      • Continuous archiving / Point-in-Time Recovery (PITR): WAL archiving (PostgreSQL) or equivalent. Enables recovery to any point in time within the retention window. Recommended for all production databases.
      • Logical backups (pg_dump, mongodump): For portability, selective restore, and cross-version migration. Run periodically in addition to physical backups.
    • Recovery Point Objective (RPO): Maximum acceptable data loss. PITR provides RPO of seconds. Daily snapshots provide RPO of up to 24 hours. Match RPO to business criticality.
    • Recovery Time Objective (RTO): Maximum acceptable downtime during recovery. Define the restore procedure and test it to measure actual RTO.
    • Backup testing: Schedule regular restore tests (at least quarterly). An untested backup is not a backup. Restore to a non-production environment and verify data integrity.
    • Backup security: Encrypt backups at rest. Store in a separate account or region from the primary database. Restrict access to backup storage.

28. Design for data durability edge cases. Address:

    • Accidental data deletion: Soft deletes, delayed hard deletes (30-day grace period), or audit logging that captures deleted data.
    • Accidental schema changes: Migration rollback procedures, pre-migration backup, and schema change review process.
    • Corruption: Checksum verification, replica comparison, and monitoring for replication divergence.

Phase 9: Consistency, Transactions, and Distributed Data Patterns

29. Design the transaction strategy. For each write operation in the access pattern catalog:

    • Identify the transaction boundary: What set of writes must succeed or fail atomically?
    • Choose the isolation level (for relational databases):
      • Read Committed (PostgreSQL default): Prevents dirty reads. Sufficient for most OLTP workloads. Recommended as default.
      • Repeatable Read: Prevents non-repeatable reads and phantom reads. Use for operations that read data, make a decision, and write based on that decision within the same transaction (e.g., "check balance, then debit").
      • Serializable: Full isolation, prevents all anomalies. Use only for critical financial operations where correctness is paramount and the performance cost is acceptable. Monitor for serialization failures and implement retry logic.
    • Keep transactions short: Long-running transactions hold locks, block other operations, and risk timeouts. If a business operation involves multiple steps with user interaction, break it into multiple transactions with compensating logic rather than holding a transaction open.
    • Avoid distributed transactions (2PC) across databases: They are brittle, slow, and operationally complex. Use saga patterns or event-driven eventual consistency instead.

30. Design optimistic and pessimistic concurrency control. For entities that are concurrently updated:

    • Optimistic concurrency control (OCC) (recommended as default): Add a version column (integer, incremented on each update) or use updated_at timestamp. On update, include WHERE version = expected_version — if no rows are affected, a concurrent update occurred, and the application retries or returns a conflict error. Use OCC when conflicts are rare.
    • Pessimistic locking: Use SELECT ... FOR UPDATE to lock rows during a transaction. Use when conflicts are frequent and the cost of retry is high. Be cautious of deadlocks — always acquire locks in a consistent order.
    • Advisory locks: For application-level coordination that doesn't need row-level locking (e.g., "only one instance should run this migration at a time"). Use PostgreSQL advisory locks or Redis-based distributed locks.

31. Design eventual consistency patterns (for distributed systems). When data must be synchronized across services or databases:

    • Change Data Capture (CDC): Capture changes from the source database's transaction log and publish them as events. Tools: Debezium (Kafka Connect), AWS DMS, PostgreSQL logical replication. CDC is the most reliable way to synchronize databases without dual-write inconsistency.
    • Transactional outbox pattern: Write the event to an outbox table within the same database transaction as the data change. A separate process reads the outbox and publishes events to the message broker. Guarantees at-least-once event delivery without distributed transactions.
    • Event-driven materialization: Consume events from the source and build read-optimized projections in the consumer's database. Define the consistency lag tolerance and the mechanism for rebuilding projections from scratch if they become corrupt.
    • Dual-write avoidance: Never write to two separate data stores in a single application-level operation without a coordination mechanism (outbox, CDC, or saga). Dual writes without coordination will eventually diverge.

32. Design CQRS (Command Query Responsibility Segregation) when justified. CQRS separates the write model from the read model:

    • When to use: When read and write access patterns have fundamentally different shapes (e.g., writes are normalized and transactional; reads require complex joins, aggregations, or denormalized views), and the performance requirements cannot be met with a single model.
    • Command side: Normalized relational model optimized for writes and consistency.
    • Query side: Denormalized read models (materialized views, search indexes, pre-computed aggregations) optimized for specific read access patterns.
    • Synchronization: Events from the command side update the query side. Define the lag tolerance and the rebuild mechanism.
    • Do NOT use CQRS as a default: It doubles the complexity of the data architecture. Use it only when the read/write asymmetry is severe enough to justify the cost.

33. Design event sourcing only when specifically justified. Event sourcing stores state as a sequence of events rather than as current state:

    • When to use: When a complete, immutable audit trail of every state change is a core business requirement (financial ledgers, compliance-heavy systems). When temporal queries ("what was the state at time T?") are a primary access pattern. When the domain genuinely benefits from replaying events to derive state.
    • When NOT to use: As a general-purpose storage pattern. For simple CRUD systems. When "we might want an audit trail someday" — add an audit log table instead.
    • If using event sourcing, design:
      • The event store (append-only, immutable event log).
      • The event schema with versioning (events must be backward-compatible or transformable).
      • Snapshot strategy (snapshot current state every N events to avoid replaying thousands of events on read).
      • Projection/read model design and rebuild mechanism.
      • Compaction/archival strategy for old events.

Phase 10: Data Migration and Schema Evolution

34. Design the schema migration strategy. Define how database schemas evolve safely over time:

    • Choose the migration tool: Flyway (Java ecosystem, SQL-based), Alembic (Python/SQLAlchemy), golang-migrate (Go), Liquibase (multi-platform, XML/SQL), or framework-specific tools (Rails migrations, Django migrations). The tool must support: versioned migrations, checksum verification, migration history tracking, and rollback capabilities.
    • Migration file conventions: Sequential version numbers or timestamps, descriptive names. Example: V20240115_01__add_tracking_number_to_orders.sql.
    • Migration review process: Every migration must be reviewed by at least one other engineer with database expertise. Review for: correctness, backward compatibility, locking implications, index impact, and data integrity.
    • Run migrations in CI: Validate every migration against a fresh database and against a database with the previous migration state. Catch syntax errors and compatibility issues before production.

35. Design zero-downtime migration procedures. Schema changes must not cause application downtime. Apply the expand-and-contract pattern:

    Adding a column:

    1. Add the column as nullable with a default (or no default): ALTER TABLE orders ADD COLUMN tracking_number VARCHAR(100). This is non-blocking in PostgreSQL (if no volatile default).
    2. Deploy application code that writes to the new column.
    3. Backfill existing rows if needed.
    4. Add NOT NULL constraint if required (after backfill): ALTER TABLE orders ALTER COLUMN tracking_number SET NOT NULL.

    Renaming a column:

    1. Add the new column.
    2. Deploy code that writes to both old and new columns.
    3. Backfill the new column from the old column.
    4. Deploy code that reads from the new column.
    5. Deploy code that stops writing to the old column.
    6. Drop the old column.

    Changing a column type:

    1. Add a new column with the target type.
    2. Dual-write to both columns.
    3. Backfill.
    4. Switch reads to the new column.
    5. Stop writing to the old column.
    6. Drop the old column.

    Dropping a column or table:

    1. Deploy code that stops reading from the column/table.
    2. Deploy code that stops writing to the column/table.
    3. Wait for all application versions that reference the column to be fully drained.
    4. Drop the column/table.

    General rules:

    • Never rename or drop a column in the same deployment that changes the code reading it.
    • Never add a NOT NULL column without a default in a single step — this locks the table while rewriting all rows.
    • Test every migration against a production-sized dataset in staging to measure execution time and locking behavior.

36. Design data migration between database technologies. When migrating from one database to another:

    • Define the migration strategy:
      • Dual-write with gradual cutover: Write to both old and new databases. Read from old. Gradually shift reads to new. Compare results for correctness. Cut over writes when confident. Highest safety, highest complexity.
      • CDC-based replication: Use CDC to continuously replicate from old to new. Verify data consistency. Cut over reads, then writes. Most practical for large datasets.
      • Big bang migration: Stop the old system, migrate data, start the new system. Simplest but requires downtime. Acceptable only for small datasets or when downtime is tolerable.
    • Define the verification strategy: Row counts, checksum comparison, sample query comparison between old and new systems.
    • Define the rollback plan: How to revert to the old system if the new system has issues. This must be defined and tested before migration begins.
    • Define the timeline: Schema creation → data migration → verification → shadow traffic → gradual cutover → old system decommission.

Phase 11: Connection Management and Resource Optimization

37. Design connection pooling. Database connections are expensive resources — each connection consumes memory on the database server (PostgreSQL: ~10MB per connection). Mismanaged connections are the #1 cause of database-related production incidents.

    • Application-level connection pool: Every application instance must use a connection pool (HikariCP for JVM, SQLAlchemy pool for Python, pgx pool for Go). Define:
      • Minimum pool size: Keep a small number of idle connections ready (e.g., 2-5).
      • Maximum pool size: Calculate based on max_connections / number_of_instances with headroom. If the database allows 200 connections and you have 10 application instances, each instance gets at most 15-18 connections (reserve some for admin, monitoring, migrations).
      • Connection timeout: How long to wait for a connection from the pool before failing (e.g., 5 seconds). Never wait indefinitely.
      • Idle timeout: Return idle connections after a period (e.g., 10 minutes) to free database resources.
      • Connection validation: Test connections before use (SELECT 1 or equivalent) to handle stale connections after network issues.
    • External connection pooler (recommended for high-instance-count deployments): Use PgBouncer (PostgreSQL) or ProxySQL (MySQL) as an intermediary:
      • Transaction pooling mode (recommended): Connections are returned to the pool after each transaction. Allows many application connections to share few database connections. Caveat: session-level features (prepared statements, temp tables, SET commands) do not work across transaction boundaries. Ensure the application is compatible.
      • Session pooling mode: Connections are dedicated for the duration of the application's session. Less efficient but compatible with session-level features.
    • Serverless/managed connection proxies: RDS Proxy, Cloud SQL Proxy — managed connection pooling that handles IAM authentication and failover. Recommended when using managed databases with auto-scaling application instances.

38. Design query performance management. Establish practices for ongoing query health:

    • Slow query logging: Enable logging of queries exceeding a threshold (e.g., 100ms in production, 50ms in staging). Include query text, execution time, and plan.
    • Query plan analysis: For critical queries, capture and review EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) output. Look for: sequential scans on large tables, nested loop joins on large datasets, high buffer read counts, and inaccurate row estimates.
    • pg_stat_statements (PostgreSQL) or equivalent: Enable query statistics collection to identify the most time-consuming queries in aggregate (total time, call count, mean time). Focus optimization on queries that consume the most total time, not just the slowest individual queries.
    • Connection and lock monitoring: Monitor active connections, waiting connections, and lock waits. Alert on connection count approaching max_connections and on lock waits exceeding a threshold (e.g., 5 seconds).

Phase 12: Data Lifecycle, Retention, and Archival

39. Design data retention policies. For each entity:

    • Define the retention period: How long must data be kept in the primary database? Base this on: business requirements (how far back do users need to access data?), compliance requirements (GDPR: data minimization; financial regulations: 7-year retention), and operational requirements (database performance degrades with unconstrained growth).
    • Define the archival strategy:
      • Partition-based archival: Detach old partitions and move to cold storage (cheaper database instance, S3, or data lake). Old partitions can be re-attached if historical access is needed.
      • Tiered storage: Move old data from hot (SSD) to warm (HDD) to cold (object storage) based on access frequency.
      • Archive tables: Move old records to archive tables within the same database. Simpler but doesn't reduce database size.
    • Define the purging strategy: For data that must be permanently deleted (GDPR right to erasure):
      • Design the deletion procedure: cascade effects, referential integrity handling, audit trail of deletion.
      • For large-scale deletions, batch the deletes to avoid lock contention and replication lag: delete in chunks of 1000-10000 rows with a pause between batches.
    • Implement TTL where supported: DynamoDB TTL, Redis TTL, Cassandra TTL — use database-native TTL for automatic expiration of ephemeral data (sessions, tokens, temporary locks).

40. Design data anonymization and pseudonymization (for compliance). When regulations require:

    • Anonymization: Irreversibly remove identifying information. Used for analytics datasets. Define which fields are anonymized and the technique (hashing, generalization, suppression, noise addition).
    • Pseudonymization: Replace identifying information with reversible pseudonyms. The mapping is stored separately and protected. Enables re-identification when necessary (e.g., for customer support) while protecting data at rest.
    • Implement per-user data export and deletion: Design the query and procedure for extracting or deleting all data associated with a specific user across all tables and services. This is a GDPR requirement and must be tested.

Phase 13: Database Observability and Operational Readiness

41. Design database monitoring. Define metrics to collect and monitor for every production database:

    Health metrics:

    • Connection count (active, idle, waiting) vs. maximum connections.
    • Replication lag (seconds behind primary).
    • Transaction rate (commits/sec, rollbacks/sec).
    • Deadlock count.
    • Database size and growth rate.

    Performance metrics:

    • Query latency percentiles (p50, p95, p99) for top queries.
    • Cache hit ratio (buffer cache, index cache). PostgreSQL: pg_stat_bgwriter, pg_statio_user_tables. Target > 99% cache hit ratio — below this indicates insufficient memory.
    • Disk I/O (read/write IOPS, I/O wait time, throughput).
    • Table and index bloat (wasted space from dead tuples).
    • Vacuum and autovacuum activity (last vacuum, dead tuple count, autovacuum queue length).
    • Temporary file usage (indicates sorts or hash joins spilling to disk — need more work_mem or query optimization).

    Saturation metrics:

    • CPU utilization.
    • Memory utilization (total, used by shared buffers, used by connections).
    • Disk space (total, used, projected time to full at current growth rate).
    • WAL generation rate and WAL disk usage.

42. Design database alerting. Define alerts with actionable thresholds:

    • Critical (page):
      • Replication lag > 30 seconds (data loss risk during failover).
      • Connection count > 80% of maximum (approaching exhaustion).
      • Disk space < 15% free (risk of database halt).
      • Database unreachable / health check failure.
      • Deadlocks > 5 per minute (indicates design problem).
      • Active long-running transactions > 5 minutes (indicates stuck query or missing timeout).
    • Warning (ticket):
      • Cache hit ratio < 99%.
      • Replication lag > 5 seconds.
      • Table bloat > 30%.
      • Autovacuum not completing on large tables (dead tuple count growing).
      • Slow query count increasing trend.
      • Disk space < 30% free.
    • Informational (dashboard):
      • Query patterns shifting (new high-frequency queries appearing).
      • Index usage statistics (identify unused indexes).
      • Connection pool utilization trends.

    Every critical alert must have a documented runbook: what the alert means, how to diagnose, how to mitigate, and how to escalate.

43. Design database dashboards. At minimum:

    • Overview dashboard: Connection count, replication lag, transaction rate, error rate, disk space, and CPU across all database instances.
    • Query performance dashboard: Top queries by total time, slowest individual queries, query count trends, and cache hit ratio.
    • Capacity planning dashboard: Data growth trends, disk usage projection, connection utilization trends, and index size growth.

Phase 14: Database Performance Tuning

44. Design database configuration tuning. For PostgreSQL (adapt for other databases):

    • Memory allocation:
      • shared_buffers: 25% of total RAM (starting point). This is PostgreSQL's internal cache.
      • effective_cache_size: 50-75% of total RAM. Tells the query planner how much OS cache is available.
      • work_mem: Memory per sort/hash operation. Start at 4-16MB, increase for query-heavy workloads. Caution: this is per-operation, not per-connection — high values with many concurrent queries can exhaust memory.
      • maintenance_work_mem: Memory for maintenance operations (VACUUM, CREATE INDEX). Set to 256MB-1GB.
    • WAL configuration:
      • wal_level: replica for replication (or logical if using logical replication/CDC).
      • max_wal_size: Increase for write-heavy workloads to reduce checkpoint frequency.
      • checkpoint_completion_target: 0.9 (spread checkpoint I/O over time).
    • Connection limits:
      • max_connections: Set based on available memory (~10MB per connection) and expected concurrent connections. Use PgBouncer to allow more application connections than database connections.
    • Autovacuum tuning:
      • Increase autovacuum_max_workers for databases with many tables.
      • Decrease autovacuum_vacuum_scale_factor and autovacuum_analyze_scale_factor for large tables so autovacuum triggers more frequently.
      • Set per-table autovacuum settings for tables with unusual write patterns.
    • Do not blindly copy configuration from the internet. Every setting must be justified by the specific workload. Benchmark changes in staging with production-like load before applying to production.

45. Design query optimization procedures. For identified slow queries:

    • Step 1: Get the execution plan (EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)). Identify the most expensive node.
    • Step 2: Check for sequential scans on large tables — add an appropriate index or fix the WHERE clause.
    • Step 3: Check for nested loop joins on large datasets — ensure join columns are indexed, or investigate hash/merge join suitability.
    • Step 4: Check for inaccurate row estimates — run ANALYZE on the relevant tables, or investigate statistics target increases for columns with skewed distributions.
    • Step 5: Check for sorts and hash operations spilling to disk (indicated by disk-based sort/hash in the plan) — increase work_mem for the session or optimize the query.
    • Step 6: If the query cannot be optimized further, consider: restructuring the data access (materialized view, denormalized read table, CQRS), caching the result, or accepting the query is inherently expensive and moving it to a replica or async process.

Phase 15: Database Security

46. Design database access control. Define the security model:

    • Principle of least privilege: Each application connects with a database user that has only the permissions it needs. The application user should NOT be a superuser.
    • Define database roles:
      • app_readwrite: SELECT, INSERT, UPDATE, DELETE on application tables. Used by the application service.
      • app_readonly: SELECT only. Used by read replicas, reporting tools, and analytics queries.
      • migration_admin: DDL permissions (CREATE, ALTER, DROP) on application schemas. Used only by migration tools, not by the running application.
      • monitoring_readonly: SELECT on system catalogs and statistics views. Used by monitoring agents.
    • Row-Level Security (RLS): For multi-tenant systems, enable RLS and define policies that restrict access to rows matching the current tenant. This provides defense-in-depth — even if application code omits a tenant filter, the database rejects cross-tenant access.
    • Network access: Database must not be publicly accessible. Restrict access to application VPC/subnet. Use security groups / firewall rules to allow only known application servers and authorized admin IPs.
    • Audit logging: Enable database audit logging (pgAudit for PostgreSQL) for: DDL changes, privilege changes, and access to sensitive tables. Store audit logs separately from the database with tamper-resistant retention.

47. Design encryption. Define:

    • Encryption at rest: Enable storage-level encryption (AWS RDS encryption, GCP Cloud SQL encryption). This is transparent to the application and protects against physical media theft. Use customer-managed keys (KMS) for compliance requirements.
    • Encryption in transit: Enforce TLS for all database connections. Configure sslmode=verify-full on application connections (verify the database server's certificate) to prevent MITM attacks.
    • Field-level encryption: For highly sensitive data (SSN, credit card numbers, health records) that must be protected even from database administrators: encrypt specific fields in the application before storage. Define the key management strategy (KMS, Vault), key rotation procedure, and the impact on queryability (encrypted fields cannot be indexed or queried at the database level — design access patterns accordingly).
    • Backup encryption: All backups must be encrypted at rest with keys stored separately from the backup storage.

Phase 16: Specialized Patterns and Advanced Topics

48. Design materialized views and precomputed data. When read access patterns require expensive aggregations or joins:

    • PostgreSQL materialized views: CREATE MATERIALIZED VIEW with REFRESH MATERIALIZED VIEW CONCURRENTLY for zero-downtime refresh. Define the refresh trigger (time-based schedule, event-driven, or on-demand). Materialized views are not real-time — define the staleness tolerance.
    • Application-managed denormalized tables: When materialized view refresh is too slow or inflexible, maintain a separate denormalized table updated by triggers, CDC, or application events. More control, more code to maintain.
    • Pre-aggregation tables: For time-based analytics (daily summaries, hourly counts), compute and store aggregates on a schedule rather than computing on every query. Design the aggregation pipeline and backfill procedure.

49. Design full-text search at the database level (if not using a dedicated search engine). For moderate search needs within PostgreSQL:

    • Create a tsvector column with a GIN index.
    • Define the text search configuration (language, stopwords, synonym dictionaries).
    • Design the search ranking strategy (ts_rank, ts_rank_cd).
    • Define the threshold at which PostgreSQL full-text search should be replaced by a dedicated search engine (typically: > 10M searchable documents, complex relevance tuning needs, or faceted search requirements).

50. Design for database testing. Define:

    • Local development database: Use Docker containers running the same database engine and version as production. Never develop against SQLite when deploying to PostgreSQL — SQL dialect differences cause production bugs.
    • Test database management: Each test run should start with a clean, known state. Options: transactional rollback after each test (fast, but tests cannot see committed state), truncation between tests, or fresh database per test suite.
    • Migration testing: Run all migrations from scratch against an empty database to verify they produce the expected schema. Run new migrations against the current production schema to verify compatibility.
    • Performance testing: Maintain a staging database with production-scale data volume (anonymized). Run slow query detection and capacity tests against this environment.
    • Schema drift detection: Use tools (skeema, pgquarrel, or custom scripts) to compare the actual production schema against the expected schema defined by migrations. Alert on drift.

Phase 17: Architecture Output and Deliverables

51. Produce database architecture deliverables. At the conclusion of every database design engagement, produce:
    • Data architecture summary: A concise document stating the data domain, access patterns, technology choices, and key design decisions.
    • Access pattern catalog: The complete numbered list from step 2, with the database technology, table/collection, and index that serves each pattern.
    • Entity-relationship diagram: Visual representation of all entities and their relationships.
    • Physical schema definition: Complete DDL (CREATE TABLE, CREATE INDEX, constraints) or equivalent for the chosen database, ready for implementation.
    • Technology selection ADR: For each database technology chosen, a decision record with context, decision, alternatives considered, and consequences.
    • Capacity estimate: Storage, compute, and connection requirements at current scale and projected growth.
    • Migration plan: If migrating from an existing system, the step-by-step migration procedure with rollback plan.
    • Operational runbook outline: Key monitoring metrics, alerting thresholds, backup/restore procedures, and common troubleshooting steps.
    • Open questions: Areas requiring further investigation, stakeholder input, or production data analysis before finalizing the design.

Cross-Cutting Rules (Apply Throughout All Phases)

52. Access patterns drive everything. Never select a database, design a schema, or create an index without referencing a specific access pattern. If someone asks "should I use MongoDB or PostgreSQL?" without describing their access patterns, the answer is "describe your access patterns first." Technology selection without access pattern analysis is guessing.

53. Start with the simplest architecture that meets requirements. Single PostgreSQL instance with proper indexing handles far more load than most teams realize (easily 10,000+ transactions per second on modest hardware). Add complexity (read replicas, caching layers, sharding, polyglot persistence, CQRS, event sourcing) only when specific, measured requirements demand it. Premature database architecture complexity is one of the most expensive engineering mistakes.

54. Always state tradeoffs explicitly. Never recommend a technology, pattern, or configuration without stating what you gain and what you pay. Format: "Choosing [X] over [Y] gives us [specific benefit] but costs us [specific drawback]. This is acceptable because [justification tied to this system's actual requirements]."

55. Design for the team's operational capacity. A database architecture that the team cannot monitor, tune, backup, restore, migrate, and troubleshoot is a failed architecture. Factor operational expertise into every technology choice. A PostgreSQL database well-operated by an experienced team will outperform a theoretically superior database poorly operated by an inexperienced team.

56. Make concrete recommendations, not technology menus. Do not say "you could use PostgreSQL, MongoDB, or DynamoDB." Say "Use PostgreSQL because [reasons tied to this system's access patterns and constraints]. DynamoDB would be a better choice if [specific condition applies]." When alternatives are genuinely close, present the recommendation with the conditions that would change it.

57. Measure before optimizing. Do not add indexes, caching, or denormalization based on intuition. Identify slow queries from monitoring data, analyze their execution plans, and then apply targeted optimizations. Every optimization adds complexity — it must be justified by measured performance data, not theoretical concern.

58. Treat the schema as a product interface. The database schema, like an API, is a contract. Changing it affects every consumer (application code, reports, integrations, data pipelines). Design for evolution from the start — use the expand-and-contract pattern, maintain backward compatibility, and communicate changes to all stakeholders.38:["$","$L40",null,{"content":"$41","frontMatter":{"name":"database-architecture","description":"A unified, end-to-end database architecture skill that guides the AI agent through the complete lifecycle of database design — from understanding data requirements and access patterns through technology selection, logical and physical data modeling, schema design, indexing, partitioning, replication, performance optimization, migration strategy, data governance, and operational readiness. This skill serves as the agent's core decision framework for all data storage, modeling, and database infrastructure tasks."}}]