[Article title: what the reader will learn or achieve]

Technical article and engineering blog post template: a clear problem, the approach, step-by-step instructions with code, measured results, pitfalls and further reading. Based on common practice for engineering blogs and developer publications. Replace every [bracketed] value and every line marked Example.

Summary

[What problem this article solves, the approach in one sentence, and the result with a number.]

Example: Our order-search endpoint took 2.4 seconds at p95 once the orders table passed 300 million rows. This article shows how we used PostgreSQL partial and covering indexes, plus keyset pagination, to bring it down to 45 ms without new infrastructure. You will learn how to find the slow plan, design the right index, and verify the gain safely in production.

Audience and prerequisites

This article is for [backend engineers who know SQL basics and have read an EXPLAIN plan before].

You will need:

• [Tool or language] [version], for example PostgreSQL 16 or later.
• [Library or service] [version].
• [Familiarity with concept]; if new to it, read [link] first.

The problem

[Describe the context and the symptom with a measurement.]

Example: The admin dashboard lets support agents search orders by customer and status. As the table grew, the query below became the top entry in our slow-query log:

SELECT id, customer_id, status, total_cents, created_at
FROM orders
WHERE customer_id = $1 AND status <> 'archived'
ORDER BY created_at DESC
LIMIT 50 OFFSET $2;

EXPLAIN (ANALYZE, BUFFERS) showed a sequential scan of 41 million rows for large customers and a growing cost with every page because of OFFSET.

Approach

[Explain the solution idea and the reasoning in two or three paragraphs.]

Example: We had three options: add a read replica, move search to a dedicated search engine, or fix the access path in PostgreSQL. The query shape was simple and predictable, so we chose to fix the access path: an index that matches the filter and the sort, and pagination that does not re-read skipped rows.

Step-by-step

Step 1: [Measure the baseline]

[What to do and why.]

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, status, total_cents, created_at
FROM orders
WHERE customer_id = 4821 AND status <> 'archived'
ORDER BY created_at DESC
LIMIT 50;

Expected result: [what the reader should see, for example "Seq Scan on orders ... Execution Time: 2390 ms"].

Step 2: [Create the index]

[What to do and why. Note any production safety concerns.]

CREATE INDEX CONCURRENTLY idx_orders_customer_created_active
ON orders (customer_id, created_at DESC)
INCLUDE (status, total_cents)
WHERE status <> 'archived';

CONCURRENTLY avoids blocking writes during the build. The WHERE clause keeps archived orders, which are 70% of the table, out of the index, and INCLUDE lets PostgreSQL answer the query from the index alone.

Step 3: [Change the code]

[What to change in the application and why.]

// Keyset pagination: pass the last seen created_at and id instead of an offset.
rows, err := db.Query(ctx, `
  SELECT id, customer_id, status, total_cents, created_at
  FROM orders
  WHERE customer_id = $1 AND status <> 'archived'
    AND (created_at, id) < ($2, $3)
  ORDER BY created_at DESC, id DESC
  LIMIT 50`, customerID, lastCreatedAt, lastID)

Step 4: [Verify]

[How to confirm the change works and is safe: tests, plan check, monitoring.]

Expected result: [for example "Index Only Scan using idx_orders_customer_created_active ... Execution Time: 0.9 ms"].

Results

Environment: [hardware or instance type, versions, data size, load tool, number of runs].

[One paragraph interpreting the numbers: what improved, what it cost, whether the trade-off is worth it.]

Pitfalls and troubleshooting

Conclusion

[Two or three sentences: what you achieved, the general principle, and when the approach would not fit.]

Example: A single index designed around the exact query shape removed a 2-second bottleneck without new infrastructure. Before adding a system, check whether the database can answer the query from an index alone. If your filters vary widely between requests, a search engine may still be the better tool.

Further reading

• [Official documentation page]: [why it is useful].
• [Article or talk]: [why it is useful].

Publishing checklist

Before you publish, confirm each item:

• A technical reviewer ran every code sample from a clean checkout.
• Versions in the prerequisites match the versions used for results.
• Secrets, internal hostnames and customer data are removed from code, logs and screenshots.
• Every image has alternative text and every code block has a language tag.
• The title and summary state the concrete result, not a vague promise.
• Internal approval (legal, security or communications) is recorded if the article describes company systems.
• The canonical URL is set if the article is cross-posted.

Standard and licence note

Based on common practice for engineering blogs and technical publications. Original text written for this collection; example code and measurements are illustrative. Template licence: MIT.