Physical Database Design, Indexing, and Performance Model

1. Core Idea

Logical data model menjelaskan entity, relationship, invariant, and lifecycle.

Physical database design menjawab:

Bagaimana model itu disimpan, diindex, dipartisi, dikunci, diquery, diarsipkan, dan dioperasikan pada volume production tanpa merusak correctness?

Dalam CPQ / Quote / Order / Billing / Telco BSS/OSS, data model bisa benar secara konseptual tetapi gagal di production karena:

• query lambat,
• index salah,
• table terlalu besar,
• locking berlebihan,
• pagination tidak stabil,
• JSONB dipakai tanpa strategi,
• audit/outbox tumbuh tanpa partition,
• status dashboard scan full table,
• reconciliation query membebani OLTP,
• migration DDL lock table,
• FK/index tidak align dengan access pattern.

Mental model:

Physical design is where business modelling meets production workload.

2. Why Physical Design Matters

Enterprise systems punya data dengan lifecycle dan access pattern berbeda:

• quote draft frequently updated,
• accepted quote immutable/read-heavy,
• order item frequently updated during fulfillment,
• audit append-only,
• outbox polled constantly,
• invoice historical but queried by account/date,
• usage event high-volume,
• product inventory current-state lookup,
• characteristics effective-dated,
• approval task queue assigned by group/user,
• data quality results operational backlog,
• reporting read models aggregated.

Satu indexing strategy tidak cocok untuk semua.

3. Start from Query Patterns

Index design starts from query patterns, not from entity diagram.

For each table, ask:

• How is row created?
• How is row updated?
• How is row read by ID?
• How is row listed?
• Which filters are common?
• Which sort order is common?
• Which joins are common?
• Which queries are high QPS?
• Which queries are operational dashboards?
• Which queries are batch/reconciliation?
• Which fields are frequently updated?
• How many rows are expected?
• What is retention/archival policy?

Example:

product_order
- get by id
- get by order_number
- list by customer/status/updated_at
- find by source_quote_id/source_quote_version
- operational dashboard by status/created_at

Indexes should support these.

4. Primary Key Strategy

Common options:

Common pattern:

id = technical primary key
quote_number/order_number = unique business key
external_id = external system reference

Do not expose sequential IDs externally if security/business policy prohibits enumeration.

5. Business Number

Business number is for human/reference:

Q-2026-000123
O-2026-000456
INV-2026-000789

It should usually have unique index.

create unique index uq_quote_number on quote (quote_number);

Business number generation can be tricky:

• sequence gap allowed or not,
• per tenant/account/year prefix,
• concurrency,
• retry,
• rollback behavior,
• legal invoice numbering rules.

Do not use business number semantics casually for invoice if legal numbering applies.

6. Composite Index Design

Composite index order matters.

Example query:

select *
from product_order
where customer_id = ?
  and status = ?
order by updated_at desc
limit 50;

Index:

create index idx_order_customer_status_updated
on product_order (customer_id, status, updated_at desc);

General idea:

• equality filters first,
• range/sort columns later,
• align with order by,
• avoid too many unused indexes,
• test with actual query plan.

7. Partial Index

Partial index is useful for active/open states.

Example:

create index idx_order_open_by_customer
on product_order (customer_id, updated_at desc)
where status in ('CAPTURED', 'SUBMITTED', 'IN_PROGRESS', 'FALLOUT');

Use cases:

• active products,
• open orders,
• pending approvals,
• unpublished outbox events,
• unresolved data quality issues,
• active recurring charges,
• current effective-dated rows.

Partial index reduces size and improves hot queries.

8. Unique Index for Invariants

Use unique index for business invariant.

Examples:

create unique index uq_order_source_quote_version
on product_order (source_quote_id, source_quote_version)
where source_quote_id is not null;

create unique index uq_inbox_event_subscriber
on inbox_message (event_id, subscriber_name);

create unique index uq_current_product_characteristic
on product_instance_characteristic (product_instance_id, characteristic_name)
where effective_to is null;

Do not rely only on application logic for uniqueness under concurrency.

9. Foreign Key Strategy

Foreign keys help integrity inside one owned database.

Use FKs for:

• aggregate child tables,
• status history referencing entity,
• invoice lines to invoice,
• quote items to quote,
• order items to order.

Be careful for:

• cross-service references,
• huge high-write tables,
• asynchronous projections,
• archive/purge constraints,
• bulk migration.

If FK not possible, use logical reference validation and reconciliation.

10. Table Width

Wide tables hurt performance and maintainability.

Examples of fields that may not belong in main hot table:

• large JSON payload,
• long comments,
• audit snapshots,
• raw external payload,
• rarely used details,
• sensitive encrypted fields,
• large text notes,
• historical evidence.

Split:

quote
quote_item
quote_snapshot
quote_audit
quote_external_payload

Hot operational table should stay focused.

11. JSONB Tradeoff

JSONB is useful for flexible characteristics/configuration.

Good use cases:

• product configuration snapshot,
• external payload reference/metadata,
• sparse optional attributes,
• audit evidence snapshot,
• versioned schema where relational query is limited.

Risks:

• weak constraints,
• difficult indexing,
• unclear semantics,
• inconsistent keys,
• slow queries if overused,
• hard API contract governance,
• hidden sensitive fields.

Rule:

Use JSONB for flexible snapshot/config evidence, but extract fields needed for query, constraint, join, or business invariant.

12. Characteristic Modelling Physical Choice

For product/config characteristics:

Relational EAV-like

product_instance_characteristic
- product_instance_id
- name
- value
- effective_from/to

Pros:

• query individual characteristic,
• effective dating,
• constraints possible with governance.

Cons:

• many rows,
• value typing complexity,
• joins/pivot needed.

JSONB snapshot

Pros:

• preserves full config,
• simple write,
• good evidence.

Cons:

• harder validation/query.

Common hybrid:

relational extracted important characteristics
+ JSONB full snapshot

13. Effective-Dated Indexing

As-of queries need indexes.

Pattern:

select *
from recurring_charge
where subscription_id = ?
  and effective_from <= ?
  and (effective_to is null or ? < effective_to);

Index options:

create index idx_recurring_charge_subscription_effective
on recurring_charge (subscription_id, effective_from, effective_to);

For current row:

create index idx_recurring_charge_current
on recurring_charge (subscription_id, charge_type)
where effective_to is null;

Temporal query performance must be tested with realistic data.

14. Status History Tables

Status history is append-heavy and read by entity.

Index:

create index idx_order_status_history_order_time
on order_status_history (order_id, transitioned_at desc);

If support timeline queries by parent entity:

create index idx_status_history_entity_time
on status_history (entity_type, entity_id, transitioned_at desc);

Avoid constantly updating history rows. Append instead.

15. Audit and Event Table Growth

Audit/outbox/inbox/event tables grow quickly.

Strategies:

• time partitioning,
• retention/archive,
• partial indexes for pending/open rows,
• avoid indexing every JSON payload field,
• separate hot operational status from historical body,
• regularly monitor bloat,
• avoid long-running transactions blocking cleanup.

Outbox index:

create index idx_outbox_pending
on outbox_event (status, next_retry_at, created_at)
where status in ('PENDING', 'FAILED');

Published historical outbox may be archived/partitioned.

16. Partitioning

Partitioning is useful for large time-series or lifecycle tables.

Candidates:

• usage_event,
• metered_usage,
• rated_usage,
• audit_event,
• outbox_event historical partitions,
• inbox_message,
• integration_message,
• invoice_line,
• data_quality_result,
• logs/timeline.

Partition by:

• time,
• tenant,
• customer segment,
• hash of ID,
• status/lifecycle rarely.

Time partition is common for append-only data.

Be careful: partitioning adds operational complexity.

17. Hot Table Design

Hot tables are frequently updated/read.

Examples:

• quote draft,
• order item during fulfillment,
• outbox pending,
• approval task queue,
• saga step,
• product current state.

Hot table risks:

• row lock contention,
• index update overhead,
• dead tuples,
• autovacuum pressure,
• dashboard scans.

Mitigation:

• minimize indexes on high-update columns,
• split mutable status from immutable payload if needed,
• use partial indexes for active states,
• avoid large JSON updates,
• batch updates carefully,
• monitor vacuum/bloat.

18. Locking and Concurrency

Common concurrency patterns:

• optimistic locking with version column,
• select for update for aggregate command,
• unique constraint for idempotency,
• advisory lock only when necessary,
• status compare-and-set,
• skip locked for worker queues.

Worker queue pattern:

select id
from outbox_event
where status = 'PENDING'
order by created_at
limit 100
for update skip locked;

Use carefully and test under concurrency.

19. Queue Tables

Outbox, retry, task, and job tables act like queues.

Need:

• status,
• next_retry_at,
• locked_by,
• locked_at,
• retry_count,
• priority,
• created_at,
• dead-letter status.

Indexes:

create index idx_task_ready
on task_queue (priority, next_run_at, created_at)
where status = 'READY';

Avoid scanning all historical completed tasks.

Archive completed rows if volume grows.

20. Pagination Performance

Offset pagination can get slow:

limit 50 offset 500000

Cursor pagination is better for large changing data.

Example:

where updated_at < :last_seen_updated_at
order by updated_at desc
limit 50;

Need deterministic sort:

updated_at desc, id desc

Index:

create index idx_order_customer_updated_id
on product_order (customer_id, updated_at desc, id desc);

API pagination should align with DB design.

21. Search vs Relational Query

Use relational DB for structured operational queries.

Use search index for:

• text search,
• fuzzy search,
• broad customer/product/order lookup,
• support search,
• multi-field search.

Do not implement arbitrary fuzzy search over huge OLTP tables with %like% unless indexed/trigram and volume-safe.

Search index is projection, not source of truth.

22. Reporting Query Isolation

Reporting dashboards can overload OLTP.

Strategies:

• read replicas,
• materialized views,
• read models,
• analytics warehouse,
• event/CDC pipeline,
• cached aggregates,
• operational dashboard tables.

Do not let BI tools run arbitrary heavy joins on primary production OLTP.

23. Materialized View and Summary Table

Materialized view/summary table can help.

Example:

open_order_summary_by_customer
- customer_id
- open_order_count
- oldest_open_order_at
- fallout_count
- refreshed_at

Use when:

• dashboard query expensive,
• freshness can lag,
• source-of-truth remains elsewhere,
• refresh process monitored.

24. Archival and Table Size

Large historical data should be archived based on lifecycle.

Examples:

• completed orders older than policy,
• old quote drafts,
• published outbox rows,
• processed inbox rows,
• old integration messages,
• audit partitions,
• usage raw events.

Archive strategy should not break:

• audit,
• support lookup,
• reporting,
• reconciliation,
• FK/reference integrity.

Use summary/tombstone in hot DB if needed.

25. Data Type Choices

Important choices:

Avoid using text for everything without governance.

26. Enum Physical Strategy

PostgreSQL enum:

Pros:

• constrained values,
• compact.

Cons:

• changing values requires migration care,
• difficult across independently deployed services.

Text + check/reference table:

Pros:

• easier evolution,
• configurable,
• explicit metadata.

Cons:

• weaker unless constraints enforced.

For fast-changing business statuses/reason codes, text/reference table may be more flexible.

27. Index Cost

Indexes are not free.

Every index adds:

• write overhead,
• storage,
• maintenance,
• vacuum cost,
• migration cost,
• planner complexity.

Remove unused indexes carefully.

Use database metrics to identify:

• unused indexes,
• duplicate indexes,
• bloated indexes,
• missing indexes,
• slow queries.

Do not add index for every column "just in case".

28. Query Plan Review

For critical queries, review:

• EXPLAIN / EXPLAIN ANALYZE,
• rows estimated vs actual,
• index scan vs sequential scan,
• sort cost,
• join type,
• filter pushdown,
• partition pruning,
• buffer usage,
• lock wait,
• query timeout.

Production-like data distribution matters. Small dev DB hides performance problems.

29. Reconciliation Query Design

Reconciliation queries can be heavy.

Best practices:

• run on replica/read model when possible,
• limit scope by time/status,
• use indexes,
• store last check watermark,
• avoid full scans every minute,
• batch by ID/time range,
• persist results,
• track run time.

Example:

Active product without charge

Needs indexes on:

product_instance(status, updated_at)
charge(product_instance_id, charge_type, status)

30. Multi-Tenant Indexing

If tenant-scoped:

• include tenant_id in common indexes,
• ensure queries always filter tenant_id,
• avoid cross-tenant scan,
• tenant-aware unique constraints.

Example:

create unique index uq_quote_number_per_tenant
on quote (tenant_id, quote_number);

Cache/search/read model must also be tenant-aware.

31. Observability

Monitor:

• slow queries,
• lock waits,
• deadlocks,
• index usage,
• table/index bloat,
• autovacuum lag,
• connection pool saturation,
• replication lag,
• long transactions,
• temp file usage,
• queue table age,
• partition growth,
• disk usage.

Database design without observability is incomplete.

32. Failure Modes

33. PR Review Checklist

When reviewing physical design, ask:

• What are the top query patterns?
• What is expected row volume?
• Is this table hot, append-only, historical, or read-mostly?
• Are indexes aligned with filters/sort?
• Are uniqueness invariants enforced?
• Are partial indexes useful?
• Is partitioning needed?
• Are effective-dated queries indexed?
• Are queue/pending rows indexed separately?
• Are large JSON/text payloads separated?
• Are money/time/status data types appropriate?
• Does this affect migration/locking?
• Does this affect archive/retention?
• Can reporting use read model instead?
• Are slow query/lock metrics covered?
• Is tenant/customer scope indexed?

34. Internal Verification Checklist

Verify these in the internal CSG/team context:

• PostgreSQL version and available features.
• Primary key/UUID standard.
• Business number generation standard.
• Index naming and review process.
• Use of partial/concurrent indexes.
• Partitioning standards for audit/usage/outbox/history.
• Query performance review process.
• Whether BI/reporting hits OLTP or warehouse/read replica.
• Existing slow-query monitoring.
• Existing bloat/autovacuum monitoring.
• Use of JSONB and validation conventions.
• Pagination standards.
• Tenant/customer indexing strategy.
• Archive/retention strategy for large tables.
• Production migration constraints and lock timeout standards.
• Incidents involving slow queries, table locks, missing indexes, or queue table growth.

35. Summary

Physical database design turns the logical model into production reality.

A strong model must define:

• query patterns,
• primary/business key strategy,
• composite indexes,
• partial indexes,
• uniqueness constraints,
• effective-dated indexing,
• JSONB tradeoffs,
• table width,
• hot table design,
• partitioning,
• queue table strategy,
• pagination,
• reporting isolation,
• archival,
• data types,
• migration safety,
• observability.

The key principle:

A beautiful conceptual model can still fail in production if physical design does not match workload, volume, lifecycle, concurrency, and operational query patterns.