KAHIBARO
Discord Login Register ☰
☰

26.4 Database Optimization

Table of Contents

Why Database Optimization Matters

As your application grows, the database usually becomes one of the main bottlenecks. Every request that reads or writes data has to pass through it. Poor queries and schema design can turn a fast API into a slow one, even if your code is efficient.

Database optimization is about:

You will see some overlap with other chapters like Indexes, Query Optimization, and Connection Pooling, but here the focus is on how to think about database performance as a whole, in a backend application.

Typical Symptoms of Database Problems

Before changing anything, you need to recognize when the database is the real problem.

Common symptoms

Measure first

You should never “optimize blindly”. Always measure.

Useful measurements:

For example, in PostgreSQL you can:

Important rule
Always measure and understand where the time is spent before optimizing. Do not guess which query is slow. Use logs, query plans, and metrics.

Schema and Data Modeling for Performance

The way you design your tables affects performance long before you write a single query.

Choosing appropriate data types

Use the smallest and most appropriate types that can correctly represent your data.

Examples:

DataBad choiceBetter choiceNotes
Primary key idBIGINTINTUse BIGINT only if you truly need more than 2B rows.
Monetary amountsFLOATNUMERIC(12,2)Floats are not exact, bad for money.
Short status valuesTEXTVARCHAR(20) or enumRestrict length or use enum for clarity and indexing.
Boolean flagsINT (0/1)BOOLEANUse the native boolean type if available.

Smaller types mean less disk, less memory, and faster scans.

Normalization vs denormalization

You learned normalization and relationships earlier. For performance, you need to balance between:

Example: user and post counts

Normalized:

sql 
SELECT u.id, u.name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.name;

This is fine for small datasets, but for millions of posts it can be slow.

Denormalized option: add post_count column on users and update it on each post create/delete.

Tradeoff:

Important rule
Normalize by default, then selectively denormalize to solve specific performance problems that you can measure.

Avoiding unbounded growth in single tables

Some tables grow much faster than others. Examples: logs, events, analytics.

Very large tables can cause:

Common strategies:

Example: keep only 90 days of audit logs in the main table, older logs go into audit_logs_archive.

Query Design and N+1 Problems

The query itself often matters more than which database you use.

Only fetch what you need

Avoid “SELECT *” for high traffic endpoints. Fetch only the columns you actually use.

Bad:

sql 
SELECT * FROM orders WHERE customer_id = 123;

Better:

sql 
SELECT id, created_at, total_amount
FROM orders
WHERE customer_id = 123;

If the table has many columns (for example large JSON, text, or blobs), selecting only required columns can drastically reduce I/O.

Use filtering and limits

When showing lists, never return unbounded results.

Bad API pattern:

http 
GET /orders    # returns all orders

Better:

http 
GET /orders?limit=50&offset=0

SQL example:

sql 
SELECT id, created_at, total_amount
FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 50 OFFSET 0;

You will see pagination in more depth elsewhere, but from a database perspective it is essential for performance.

Understanding the N+1 query problem

The N+1 query problem is one of the most common backend performance bugs.

Example scenario

python 
posts = db.query("SELECT * FROM posts ORDER BY created_at DESC LIMIT 20")
for post in posts:
    author = db.query("SELECT * FROM users WHERE id = ?", post.user_id)
    post.author_name = author.name

Queries executed:

Fix: use a join

sql 
SELECT p.id,
       p.title,
       p.created_at,
       u.id   AS author_id,
       u.name AS author_name
FROM posts p
JOIN users u ON u.id = p.user_id
ORDER BY p.created_at DESC
LIMIT 20;

Now only 1 query returns posts with authors.

Fix: use an IN clause

Sometimes you cannot join directly, but you can fetch related data in bulk.

Step 1: fetch posts.

sql 
SELECT id, user_id, title
FROM posts
ORDER BY created_at DESC
LIMIT 20;

Step 2: fetch all authors in one query:

sql 
SELECT id, name
FROM users
WHERE id IN (list_of_all_user_ids_from_step_1);

Then match in your application code.

Important rule
Avoid N+1 queries. If you see a loop that runs a separate database query for each item, you likely have an N+1 problem.

Indexing for Faster Queries

You have a dedicated Indexes chapter, so this section will focus on how indexing fits into optimization and how to think about when you need an index.

When you need an index

Indexes help searches, joins, and sorts. Without indexes, the database scans entire tables.

Signs that you need an index:

Example index:

sql 
CREATE INDEX idx_users_email ON users (email);

Now this query is faster:

sql 
SELECT id, name
FROM users
WHERE email = 'alice@example.com';

Composite indexes and query patterns

A composite index is built on multiple columns.

Example:

sql 
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

This can speed up queries like:

sql 
SELECT id, created_at, total_amount
FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 20;

However, it will not fully help if you query only by created_at:

sql 
SELECT * FROM orders
WHERE created_at > '2024-01-01';

For composite indexes (a, b) the index can be used efficiently for:

Important rule
Design indexes for actual query patterns. Think in terms of: “What WHERE and ORDER BY clauses do I use in production?”

Avoiding over-indexing

Indexes are not free:

Too many indexes can make writes slow and increase maintenance.

Guideline:

You can capture slow queries, see which columns they use in conditions or joins, and create indexes only where they bring real benefit.

Reducing Load with Caching

Caching is another dedicated chapter, so here we will only discuss how caching affects database optimization strategy.

Where caching helps

Caching reduces database read load. Common levels:

Typical things to cache:

Example: caching by key

For a query:

sql 
SELECT * FROM products WHERE id = 123;

You can use a cache key like product:123.

Pseudo-code with Redis:

python 
def get_product(product_id: int):
    key = f"product:{product_id}"
    cached = redis.get(key)
    if cached:
        return deserialize(cached)
    product = db.query("SELECT * FROM products WHERE id = ?", product_id)
    redis.setex(key, 300, serialize(product))  # cache for 300 seconds
    return product

Cache invalidation basics

You need a strategy to keep cache and database in sync.

Common approaches:

You will study these in more depth in the Caching chapter, but remember from a database optimization view:

Connection Management and Pooling

Connection management affects both database performance and application reliability. There is a separate Connection Pooling chapter, so here we focus on how it connects to optimization.

Why too many connections are bad

Every database connection consumes memory and CPU on the database server. Thousands of idle or low-traffic connections can exhaust resources.

Problems:

Why creating connections per request is bad

If your API opens a new database connection on every request and closes it afterward, you pay the cost of connection setup each time. This can be very expensive under load.

Using a connection pool

A connection pool keeps a fixed number of open connections and reuses them.

Simplified example in pseudocode:

python 
pool = create_pool(min_size=5, max_size=20)
def handle_request(request):
    with pool.acquire() as conn:
        # use conn to run queries
        ...

Benefits:

Important rule
Use connection pooling for production backends. Do not open or close a new database connection for every request.

Minimizing Contention and Locks

Databases use locks to ensure consistency when multiple clients read and write data at the same time. Too many or long-running locks can block others and cause timeouts.

Keep transactions short

A transaction groups multiple statements into an all-or-nothing unit. But while a transaction is open, it may hold locks on rows or even tables.

Bad pattern (long-running transaction):

sql 
BEGIN;
-- complex report that scans many rows
SELECT ... FROM big_table WHERE ...;
-- now wait for user input, or some other long operation...
UPDATE big_table SET ... WHERE id = 123;
COMMIT;

During this time, other transactions might be blocked on rows or indexes that this transaction locks.

Better:

Choose appropriate isolation levels

Higher isolation levels can mean more locking and lower throughput. Default settings (like PostgreSQL’s READ COMMITTED) are usually a good balance.

Changing isolation levels is an advanced tool. For optimization:

Avoid “hot rows”

A hot row is a single row that is updated very frequently by many clients. For example:

Hot rows cause contention and lock queues.

Strategies:

Read vs Write Optimization Strategies

Workloads differ. Optimizations that help read-heavy systems may hurt write-heavy ones.

Read-heavy workloads

Typical examples:

Strategies:

Write-heavy workloads

Examples:

Strategies:

Example of batch insert:

sql 
INSERT INTO events (user_id, event_type, created_at)
VALUES
  (1, 'click', NOW()),
  (2, 'page_view', NOW()),
  (3, 'click', NOW());

This is usually faster than 3 separate INSERT statements.

Application-Level Optimization Around the Database

Sometimes the best database optimization is to do less database work from the application.

Avoid unnecessary queries

Patterns to avoid:

Better patterns:

sql 
SELECT 1
FROM users
WHERE email = 'alice@example.com'
LIMIT 1;

Precompute and materialize

Sometimes you can precompute results and store them in separate tables or materialized views, then refresh them periodically.

Example:

sql 
CREATE TABLE daily_sales (
    day DATE PRIMARY KEY,
    total_amount NUMERIC(12,2)
);

Update it:

Then dashboard queries are fast:

sql 
SELECT * FROM daily_sales
WHERE day >= CURRENT_DATE - INTERVAL '365 days';

A Step-by-Step Optimization Workflow

When you face database performance problems, you can follow a simple process.

  1. Observe
    • Measure request latencies.
    • Enable slow query logging.
    • Collect metrics such as queries per second, CPU, I/O.
  2. Identify
    • Find the slowest queries (top offenders).
    • Use EXPLAIN ANALYZE to see how they run.
    • Look for full table scans, missing indexes, or large row counts.
  3. Optimize queries
    • Rewrite queries to use better filters or fewer joins.
    • Reduce selected columns.
    • Add or adjust indexes for specific patterns.
    • Fix N+1 issues in the application.
  4. Optimize schema
    • Adjust data types.
    • Add or remove indexes based on usage.
    • Consider denormalization or precomputed tables for heavy reports.
    • Manage big tables through partitioning or archiving.
  5. Improve application behavior
    • Implement caching where it has the biggest effect.
    • Introduce connection pooling and tune pool sizes.
    • Shorten transactions and reduce lock contention.
  6. Scale infrastructure
    • Only after software and schema optimizations:
      • Increase resources (vertical scaling).
      • Add read replicas.
      • Use sharding or separate databases for different modules.

Important rule
Optimize in this order: measure, fix queries, adjust schema and indexes, improve application behavior, then scale infrastructure as a last step.

Practical Examples

To make ideas more concrete, here are two typical optimization scenarios.

Example 1: Slow user listing endpoint

Endpoint:

http 
GET /admin/users

Symptoms:

Original query:

sql 
SELECT *
FROM users
ORDER BY created_at DESC;

Problems:

  1. No limit. It tries to return all users.
  2. SELECT * fetches all columns.
  3. ORDER BY created_at without a suitable index.

Step-by-step fixes:

  1. Add pagination and select only needed columns:
sql 
SELECT id, name, email, created_at
FROM users
ORDER BY created_at DESC
LIMIT 50 OFFSET 0;
  1. Add an index:
sql 
CREATE INDEX idx_users_created_at ON users (created_at DESC);

Result:

Example 2: Slow order details with N+1 queries

Endpoint:

http 
GET /users/123/orders

Pseudocode, original:

python 
orders = db.query("SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 50")
for o in orders:
    items = db.query("SELECT * FROM order_items WHERE order_id = ?", o.id)
    o.items = items

Queries:

Fix:

sql 
SELECT *
FROM order_items
WHERE order_id IN (list_of_order_ids);

Or use join:

sql 
SELECT o.id             AS order_id,
       o.created_at     AS order_created_at,
       o.total_amount,
       i.id             AS item_id,
       i.product_id,
       i.quantity,
       i.unit_price
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
WHERE o.user_id = 123
ORDER BY o.created_at DESC;

Then group items by order_id in application code.

Result:

Summary

Database optimization is not a single trick. It is a set of habits and decisions:

In later chapters on Query Optimization, Indexes, Caching, and Connection Pooling you will deepen each of these aspects, but you now have a solid mental model of how they fit together in overall backend performance and scalability.

Views: 7

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3161 / 24h
Impressum / Imprint
Datenschutz / Data Policy