ScreenshotNeo
Sign in Get a free key

BlogEngineering

Database Design Best Practices for High-Performance Applications

Design databases for the workload first, then improve speed with measured indexes, selective partitioning, caching, and continuous query-plan analysis.

By the ScreenshotNeo team1 October 20269 min read

Direct answer: Start with a correct logical model based on your application’s workload. Separate data into subject-based tables, define keys and integrity constraints, normalize transactional data, and add a small set of indexes that match measured query patterns. Partition or shard only when query volume, data size, or operational requirements justify the added complexity. Then tune with execution plans, production-like measurements, caching, and monitoring.

There is no universally fastest database design. The right design depends on read/write mix, transaction scope, consistency, latency, durability, availability, geographic access, growth, retention, and team capabilities. AWS summarizes the platform decision as a trade-off among availability, consistency, partition tolerance, latency, durability, scalability, and query capability (AWS Well-Architected Framework, c008).

1. Define the workload before designing tables

Write down the operations your system must support before choosing indexes or a database engine. Include:

  • Read/write ratio and peak concurrency
  • Transaction boundaries and consistency requirements
  • Latency objectives for critical requests
  • Expected row count, growth rate, retention, and archival policy
  • Geographic access and data-residency requirements
  • Availability, recovery-point, and recovery-time objectives
  • Most frequent queries and the queries that must remain fast

Capture representative query shapes, parameters, result sizes, and write patterns. Azure’s partitioning guidance starts with observed slow and frequent queries and application requirements (Microsoft Learn, c002). A schema optimized for a dashboard is different from one optimized for high-volume order writes.

2. Model entities, relationships, and constraints

Divide information into subject-based tables to reduce duplication and protect correctness. Microsoft describes this as a core property of good database design (Microsoft Support, c001). Define:

  • A primary key for every entity.
  • Foreign keys for relationships.
  • Unique constraints for business identifiers such as email addresses or order numbers.
  • NOT NULL, CHECK, and domain constraints for valid values.
  • Explicit delete and update behavior for related rows.
CREATE TABLE customers (
  customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
  status TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
  total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX orders_customer_created_idx
  ON orders (customer_id, created_at DESC);

Choose data types that represent the values accurately and support the access pattern. MySQL identifies table structure, column types, and appropriate indexes as central performance concerns (MySQL, c004). Store monetary values as integer minor units or a suitable exact numeric type, not floating point.

3. Normalize by default, denormalize deliberately

Keep each fact in one authoritative place for transactional workloads. Third-normal-form-style modeling reduces update anomalies and keeps writes consistent. MySQL recommends nonredundancy for normal workloads while allowing duplicated data or summary tables where read speed is more important than storage and maintenance cost (MySQL, c009).

Denormalize only after identifying a measured bottleneck. Common choices include:

  • Materialized or summary tables for reporting aggregates.
  • Read models tailored to a stable query shape.
  • Cached counters such as item counts or balances.
  • Duplicated lookup fields to avoid an expensive join on a critical path.

For each duplicated field, document its source of truth, refresh mechanism, lag tolerance, backfill process, and behavior during retries or failures. If a value must be exact inside a transaction, keep it in the transaction’s authoritative tables.

4. Design indexes from real queries

Indexes should follow predicates, joins, sort orders, and uniqueness rules in the queries that matter. Microsoft warns that missing, excessive, or poorly designed indexes are major sources of performance problems and recommends beginning high-throughput OLTP systems with a few narrow indexes targeted at critical queries (Microsoft Learn, c003).

Index checklist

  • Index foreign keys used in joins or parent-to-child lookups.
  • Put equality predicates before range predicates in a composite index when that matches the query.
  • Match the index order to frequent ORDER BY clauses.
  • Use unique indexes for business uniqueness.
  • Consider covering included columns only after measuring the trade-off.
  • Remove indexes that are unused, redundant, or too expensive to maintain.
-- Query pattern: one customer's newest paid orders
SELECT order_id, total_cents, created_at
FROM orders
WHERE customer_id = $1
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

-- PostgreSQL partial index tailored to that pattern
CREATE INDEX orders_paid_customer_created_idx
  ON orders (customer_id, created_at DESC)
  WHERE status = 'paid';

Always inspect the plan with your database’s plan tool, such as PostgreSQL EXPLAIN (ANALYZE, BUFFERS) or the equivalent in your engine. Compare estimated and actual row counts, scan type, join order, sort or spill operations, buffer reads, and total time. An index that helps one query can slow inserts, updates, vacuuming, replication, and backups.

5. Write queries that preserve plan quality

  • Select only columns needed by the caller.
  • Paginate with a stable indexed key; keyset pagination usually avoids increasingly expensive offsets.
  • Pass typed parameters instead of constructing SQL strings.
  • Avoid functions on indexed columns in predicates unless you have a matching functional index.
  • Return bounded result sets and enforce server-side limits.
  • Batch writes where atomicity permits, but keep transactions short.
-- Keyset pagination using the composite ordering key
SELECT order_id, total_cents, created_at
FROM orders
WHERE customer_id = $1
  AND (created_at, order_id) < ($2, $3)
ORDER BY created_at DESC, order_id DESC
LIMIT 50;

6. Partition only when it solves a measured problem

Partitioning divides a logical table into physically separate portions. It can reduce the data examined by a query, support partition pruning, improve retention operations, and isolate hot data. It also adds routing, maintenance, cross-partition query, and rebalancing complexity.

Choose a partition key that appears in critical filters and lets the application target one or a few partitions. Azure warns against designs that force scans across every partition (Microsoft Learn, c002). PostgreSQL notes that a sequential scan of a large fraction of one partition can beat scattered index reads, so partitioning is workload-dependent (PostgreSQL, c005).

-- Illustrative PostgreSQL range partitioning by event month
CREATE TABLE events (
  event_id BIGINT NOT NULL,
  tenant_id BIGINT NOT NULL,
  occurred_at TIMESTAMPTZ NOT NULL,
  payload JSONB NOT NULL
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2026_01 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE INDEX events_2026_01_tenant_time_idx
  ON events_2026_01 (tenant_id, occurred_at DESC);

Plan partition creation, retention, indexes on every partition, default or late-arriving rows, statistics, backups, and rebalancing. Test queries that omit the partition key: they may still scan every partition.

7. Shard only with a clear routing strategy

Sharding distributes data across independent database nodes. It can increase horizontal capacity but makes transactions, joins, unique identifiers, reporting, failover, and migrations harder. Select a shard key with even distribution and high probability of being present in requests, such as tenant ID. Avoid keys that create a hot shard or require scatter-gather queries.

Define how the application handles a missing or moved shard, how resharding works, and which operations are allowed to cross shards. If most important operations need data from many shards, revisit the model before adopting sharding.

8. Use caching with explicit consistency rules

Caching can reduce repeated reads, but it does not repair inefficient queries or an underspecified consistency model. Decide whether each value is cache-aside, write-through, refreshed asynchronously, or immutable. Define TTLs, invalidation events, stale-read tolerance, stampede protection, and behavior when the cache is unavailable.

Cache bounded, high-read values such as product metadata or expensive aggregates. Avoid caching rapidly changing authorization or balance data unless invalidation is exact. Measure hit rate, origin load, eviction rate, and added latency.

9. Choose SQL, NoSQL, or a managed service by requirements

Option Often fits Questions to answer
Relational SQL Integrity-heavy OLTP, joins, transactions, flexible queries Can one primary and its replicas meet write and availability needs?
Key-value or document Known access patterns, very large horizontal scale, flexible records How will relationships, transactions, and new query patterns work?
Wide-column or distributed SQL Partitioned workloads with high scale requirements Can every critical request route to a small partition set?
Managed database Teams that want backups, patching, monitoring, and failover operated for them What are the service limits, recovery guarantees, network costs, and lock-in implications?

Compare consistency and transaction scope, read latency, write throughput, query flexibility, partition routing, storage and cache cost, backup and recovery, observability, and team expertise. AWS states that the optimal database solution varies with availability, consistency, partition tolerance, latency, durability, scalability, and query capability (AWS, c008).

10. Measure continuously with production-like data

Performance work is an iteration:

  1. Collect latency percentiles, throughput, errors, lock waits, connection-pool usage, CPU, memory, storage latency, cache hit rate, replication lag, and deadlocks.
  2. Find slow and frequent queries by normalized query shape.
  3. Capture execution plans with representative parameters and data distribution.
  4. Change one schema, query, index, cache, or storage setting at a time.
  5. Load-test read and write paths together, including failover and recovery exercises.
  6. Keep or roll back the change based on measured user-facing and database impact.

Azure recommends profiling data, analyzing query plans, monitoring metrics, and iterating on schema, indexes, caching, and storage configuration (Microsoft Learn, c006). Do not optimize from averages alone; inspect tail latency and behavior during bursts.

11. Reliability, operations, and cost

  • Backups: Test restoration, point-in-time recovery, and cross-region recovery; a successful backup job is not proof of a usable restore.
  • Transactions: Keep locks short, set timeouts, retry only safe operations, and use idempotency keys for retried writes.
  • Connections: Use bounded pools and monitor exhaustion; opening a connection per request can overload the database.
  • Replication: Define whether reads may be stale and route consistency-sensitive reads to an appropriate replica.
  • Schema changes: Prefer backward-compatible expand, migrate, contract steps for rolling deployments.
  • Cost: Account for storage, IOPS, replicas, backups, cross-zone or cross-region traffic, cache nodes, and operational labor. An index or replica that lowers latency can increase write and storage cost.

12. Troubleshooting common performance failures

Symptom Likely cause Fix
Full table or partition scan Missing or mismatched predicate index; query omits partition key Inspect the plan, add a targeted index, rewrite the predicate, or route by partition key.
Writes became slower after tuning Too many or wide indexes Measure index usage and write cost; remove redundant indexes and keep critical ones narrow.
Fast query becomes slow over time Changed data distribution or stale statistics Refresh statistics, inspect actual row counts, and retest with current data.
High lock waits or deadlocks Long transactions or inconsistent update order Shorten transactions, access rows in a consistent order, add timeouts, and retry safe operations.
Pagination slows on later pages Large offset requires discarding many rows Use keyset pagination with a stable composite index.
Partitioning did not improve latency Queries scan many partitions or a large fraction of one partition Verify pruning in the plan, reconsider the key and granularity, and compare with a well-indexed table.
Cache serves incorrect values Unclear invalidation or TTL policy Define ownership and consistency rules, invalidate on writes where required, and bound staleness.

13. A practical review checklist

  • Every table represents a clear subject or relationship.
  • Primary, foreign, unique, and domain constraints protect correctness.
  • Critical queries and transaction boundaries are documented.
  • Indexes map to measured predicates, joins, and sort orders.
  • Write overhead and index usage are monitored.
  • Denormalized fields have a source of truth and refresh process.
  • Partitioning or sharding has a routing, retention, and recovery plan.
  • Backups and restores have been exercised.
  • Latency, errors, waits, saturation, and replication lag have alerts.
  • Capacity and cost are reviewed as data and traffic grow.

Or skip the browser setup

If you publish database architecture diagrams, schema pages, or operational dashboards, ScreenshotNeo can capture a clean image through one request instead of maintaining browser automation. See the ScreenshotNeo API documentation.

curl -G "https://api.screenshotneo.com/v1/shot" -d access_key=YOUR_API_KEY --data-urlencode url=https://stripe.com -o shot.webp
import requests
r = requests.get("https://api.screenshotneo.com/v1/shot", params={"access_key": "YOUR_API_KEY", "url": "https://stripe.com"}, timeout=90)
open("shot.webp", "wb").write(r.content)
const q = new URLSearchParams({ access_key: 'YOUR_API_KEY', url: 'https://stripe.com' });
const res = await fetch(`https://api.screenshotneo.com/v1/shot?${q}`);

Cookie banners, newsletter popups, and chat widgets are removed before the shot. Bot checks, blank pages, failed loads, timeouts, and cache hits are not billed, and response headers identify the page verdict and billing status. Its MCP server lets Claude, Cursor, and other MCP clients take screenshots. The free plan includes 1,000 screenshots each month with no card; paid plans start at $5 for 3,000. Create a free ScreenshotNeo account.

FAQ

Should every table be fully normalized?

Normalize transactional data first. Denormalize only for a measured read benefit, with a documented refresh and consistency policy.

How many indexes should a table have?

Only as many as measured query coverage requires. Each index consumes storage and adds write and maintenance work.

Is partitioning the same as sharding?

No. Partitioning usually keeps one logical database table within a database system; sharding distributes data across independent nodes and requires application-level routing.

When should I replace SQL with NoSQL?

Use explicit workload requirements. A different model is justified when its scalability or access pattern benefits outweigh the loss of relational constraints, joins, or transaction flexibility.

What is the most reliable tuning method?

Measure representative workloads, inspect actual execution plans and resource metrics, make one change, and compare user-facing latency and operational cost.

Keep reading