# [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*.

<!--
Guidance: How to use this template
- Choose one problem and one audience. An article that tries to serve
  beginners and experts at once serves neither.
- Every code sample must run as shown. Test it in a clean environment
  and pin versions in the prerequisites.
- Show evidence. A benchmark table or before-and-after numbers make an
  article worth sharing; claims without data do not.
- Cut ruthlessly. Most technical articles read best at 1,200 to 2,500
  words plus code.
-->

| Field | Value |
|---|---|
| Author | [@author] |
| Status | [Draft / In review / Published] |
| Target publication | [Company engineering blog / dev.to / personal site] |
| Reviewers | [Technical reviewer], [Editor] |
| Tags | [postgresql, performance, go] |
| Estimated reading time | [8 minutes] |
| Published | YYYY-MM-DD |
| Canonical URL | [https://example.com/blog/slug] |

## Summary

<!-- Guidance: Two to four sentences: the problem, what you did, the headline result. Many readers decide from this paragraph whether to continue. Put the most interesting number here. -->

[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

<!-- Guidance: Say who the article is for and what they need to know or install. Pin versions so readers can reproduce your results. -->

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].
- [Sample data set or repository]: [link].
- [Familiarity with concept]; if new to it, read [link] first.

## The problem

<!-- Guidance: Describe the situation concretely, with the symptoms and the cost. Show the original code, query or configuration and a measurement. Explain why the obvious fixes did not work, if you tried them. -->

[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:

```sql
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

<!-- Guidance: Explain the idea before the steps: what you decided to do, why, and which alternatives you rejected. A short diagram helps when several components are involved. -->

[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.

| Option | Effort | Expected gain | Decision |
|---|---|---|---|
| [Option] | [Low / Medium / High] | [Gain] | [Chosen / Rejected, reason] |
| *Example:* Read replica | Medium | None for a single slow query | Rejected |
| *Example:* Search engine | High | Large, but adds a system to run | Rejected for now |
| *Example:* Partial covering index + keyset pagination | Low | Large | Chosen |

## Step-by-step

<!-- Guidance: Numbered steps, each with one action, the code, and what the reader should see. Explain non-obvious lines after the code block, not in long comments inside it. Use the real language tag on every code fence so the editor highlights it. -->

### Step 1: [Measure the baseline]

[What to do and why.]

```sql
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.]

```sql
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.]

```go
// 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

<!-- Guidance: Report measured results with the environment, data size, method and number of runs, so readers can judge and reproduce them. Use the same units throughout. Include the downside (index size, write cost) as well as the gain. -->

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

| Metric | Before | After | Change |
|---|---|---|---|
| [Metric] | [Value] | [Value] | [%] |
| *Example:* p50 latency | 860 ms | 12 ms | -98.6% |
| *Example:* p95 latency | 2,400 ms | 45 ms | -98.1% |
| *Example:* Rows read per request | 41,000,000 | 50 | n/a |
| *Example:* Index size | 0 GB | 9.2 GB | +9.2 GB |
| *Example:* Insert latency p95 | 3.1 ms | 3.4 ms | +9.7% |

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

## Pitfalls and troubleshooting

<!-- Guidance: The problems readers are most likely to hit, with symptoms and fixes. These often get the most comments and search traffic. -->

| Symptom | Cause | Fix |
|---|---|---|
| [Symptom] | [Cause] | [Fix] |
| *Example:* Planner still uses a sequential scan | Query predicate does not match the index `WHERE` clause exactly | Use the same predicate text, or run `ANALYZE orders` |
| *Example:* `CREATE INDEX CONCURRENTLY` leaves an invalid index | Build failed or was cancelled | Drop the invalid index and rebuild |
| *Example:* Duplicate or skipped rows when paging | Sort key not unique | Add `id` as a tie-breaker in the sort and the keyset |

## Conclusion

<!-- Guidance: Restate the result and the general lesson the reader can apply elsewhere. Invite feedback or point to the next article. -->

[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

<!-- Guidance: Three to six high-quality links: official documentation first, then deeper articles or talks. Say in a few words why each is worth reading. -->

- [Official documentation page]: [why it is useful].
- [Article or talk]: [why it is useful].
- [Repository with the full example code]: [link].

## Publishing checklist

<!-- Guidance: Work through this list before publishing, then delete the section. -->

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.