Database Indexes and Idempotency: What They Fix, and How to Get Them Wrong

Two things quietly decide whether a small application survives being used: whether its reads
are indexed, and whether its writes can be repeated safely. They are usually taught as separate
subjects - one belongs to databases, the other to API design. They are the same subject seen
twice. An index is how reads survive scale. Idempotency is how writes survive retries.

Every example below is from the application that serves this page, including a failure that
happened while it was being written.

Part one: what an index actually is

The mental model that gets people into trouble is "an index makes the table faster". It does
not. An index is a second, ordered copy of some of your columns, with a pointer back to the
row. Adding one gives the query planner a new route to the data. It does not change the table.

Two consequences follow immediately, and both matter more than the speedup:

• Every index is paid for on write. An insert into a table with four indexes is five writes, not one. An index nobody reads is a pure tax.
• An index only helps if the query can use it. Wrapping the indexed column in a function - WHERE YEAR(PublishedAt) = 2026 - throws the ordering away and the planner scans anyway. So does a leading wildcard in a LIKE.

The problem an index solves, concretely

This site lists blog posts. The query behind that list is, in effect:

SELECT ... FROM BlogPosts
WHERE IsPublished = 1 AND PublishedAt IS NOT NULL
ORDER BY PublishedAt DESC;

Without a useful index the database reads every row to find the published ones, then sorts
the survivors. At a hundred rows nobody notices. At fifty thousand you are reading fifty thousand
rows and sorting most of them to return ten.

With the right index the database walks a structure that is already filtered and already in
the right order, takes the first ten entries, and stops.

Composite indexes, and why column order is the whole game

These three indexes shipped with this application:

IX_BlogPosts_IsPublished_PublishedAt             (IsPublished, PublishedAt)
IX_BlogPosts_IsPublished_IsFeatured_PublishedAt  (IsPublished, IsFeatured, PublishedAt)
IX_BlogPosts_AuthorId_IsPublished                (AuthorId, IsPublished)

Look at the first one. The order is not arbitrary and it is not alphabetical. A composite
index is sorted by its first column, then within that by its second - like a phone book sorted by
surname, then first name.

(IsPublished, PublishedAt) puts all the published rows together, and inside that
group they are already in date order. The query above filters on the first column and sorts on
the second, so the database seeks to the published section and reads straight down it. No sort
step at all.

Reverse it to (PublishedAt, IsPublished) and the same index is nearly useless
for the same query. The rows are now ordered by date first, with published and unpublished
interleaved throughout. There is no contiguous block of published rows to seek to.

Same two columns. Same table. One arrangement answers the query without a sort; the other
does not. The rule worth memorising: columns you filter by with equality come first,
the column you sort or range-scan on comes last.

Why the third index exists separately

(AuthorId, IsPublished) could look redundant next to the others. It is not,
because of the leftmost-prefix rule: an index on (A, B) can serve queries filtering
on A, or on A and B - but not queries filtering on
B alone. The author page filters by author first, so it needs an index that leads
with AuthorId. No amount of indexes leading with IsPublished will help
it.

When not to add one

Indexes are not free and the instinct to add one per column is how tables get slow at writing
without getting faster at reading. Skip them on small tables, on columns with very few distinct
values, and on anything you have not actually seen in a slow query. Add them from evidence -
an execution plan, a slow query log - rather than from intuition.

Part two: idempotency, and the problem it actually solves

An operation is idempotent when doing it twice leaves the same result as doing it once.
Reading is naturally idempotent. Setting a value is idempotent. Appending is not. Charging a card
is emphatically not.

The problem it solves is narrower and more common than "duplicate clicks". It is this:
a network gives you no way to tell a request that failed from a response that was
lost. Your client sends a request, waits, and gets a timeout. Did the server do the
work? You cannot know from the client. And the moment you retry - and every HTTP client, load
balancer and message queue retries - you are betting the outcome on that unanswerable
question.

Idempotency removes the question. If the operation is safe to repeat, the retry is safe. You
stop needing to know whether the first attempt landed.

The pattern, in SQL

The simplest form is to make the write describe the destination rather than the change. This
is not idempotent:

UPDATE Posts SET ViewCount = ViewCount + 1 WHERE Id = 42;   -- runs twice, counts twice

This is:

UPDATE Posts SET ViewCount = 981 WHERE Id = 42;             -- runs twice, same result

When you genuinely need an increment, the idempotency has to come from somewhere else -
usually a record of which events you have already counted.

For inserts, the equivalent is to check before writing. The script that published this post
runs as an upsert, so running it a second time edits the post rather than creating a duplicate:

IF EXISTS (SELECT 1 FROM BlogPosts WHERE Slug = @slug)
    UPDATE BlogPosts SET Title = @title, Content = @content WHERE Slug = @slug;
ELSE
    INSERT INTO BlogPosts (Title, Slug, Content) VALUES (@title, @slug, @content);

That is a small habit with a large payoff: any script written this way can be re-run after a
failure without anyone having to work out how far it got.

Idempotency keys, and when you need one

Checking before writing works when the row has something naturally unique - a slug, an email,
an order reference. When it does not, the client supplies the uniqueness instead: a key generated
per intended operation, sent with the request, and stored by the server alongside the result.

The server's logic becomes: if this key has been seen, return the stored response and do
nothing. If not, do the work and record the key with the outcome, in the same transaction. Stripe
popularised the pattern with its Idempotency-Key header, and it is the right answer
for anything that moves money or sends a message.

Two details decide whether an implementation works. The key must be stored in the same
transaction as the work - store it afterwards and a crash in between gives you the duplicate
you were preventing. And the stored response has to be returned on a repeat, not just a bare
200, or the caller cannot tell what happened.

Where the two ideas meet: EF Core's migration history

Entity Framework's __EFMigrationsHistory table is an idempotency ledger, though
nobody calls it that. It exists so that running dotnet ef database update twice does
not apply the same migration twice. Each applied migration's ID is the key.

Which makes its failure mode instructive - and it happened on this project while this article
was being drafted. A migration had been applied under one generated ID. Later the migration file
was regenerated and picked up a new timestamp in its name. The change was already in the database;
the ledger held the old key. EF compared files to history, saw an ID it had no record of,
and tried to apply it again:

Column names in each table must be unique.
Column name 'ResumeUpdatedAt' in table 'SiteSettings' is specified more than once.

The schema was correct. Only the bookkeeping was wrong. The fix is to record the key that
should have been there - itself written idempotently, so re-running it is harmless:

IF NOT EXISTS (SELECT 1 FROM __EFMigrationsHistory
               WHERE MigrationId = '20260920154332_AddResumeToSiteSettings')
    INSERT INTO __EFMigrationsHistory (MigrationId, ProductVersion)
    VALUES ('20260920154332_AddResumeToSiteSettings', '9.0.0');

The lesson generalises past EF. An idempotency mechanism is only as good as the stability of
its key. Change how the key is derived and the ledger stops protecting you - silently, until
something tries to run twice.

What to take from this

Indexes answer a question about reading: can the database find these rows without looking at
all of them? Get the column order right and most of the work disappears; get it backwards and
the index is decoration you pay for on every insert.

Idempotency answers a question about writing: if this runs twice, is the result the same?
Make it so, and the entire category of bug that begins "the request timed out and then
everything was duplicated" stops existing.

Neither is advanced. Both are the difference between an application that works in testing and
one that works in production.

FAQ

What is a database index, in simple terms?

A second, ordered copy of some of your columns with pointers back to the rows. It gives the query planner a faster route to the data without changing the table itself. The cost is that every insert and update has to maintain it as well.

Why does column order matter in a composite index?

A composite index is sorted by its first column, then by its second within that - like a phone book by surname then first name. An index on (IsPublished, PublishedAt) lets a query filter on IsPublished and read the results already in date order. Reverse the columns and the same query gains almost nothing.

When should I not add an index?

On small tables, on columns with very few distinct values, and on anything you have not seen in a slow query. Every index is paid for on every write, so one nobody reads is a pure tax. Add them from an execution plan, not from intuition.

What does idempotent mean?

Doing the operation twice leaves the same result as doing it once. Setting a value is idempotent; incrementing one is not. It matters because a network cannot tell you whether a request failed or the response was simply lost.

What problem do idempotency keys actually solve?

They remove the need to know whether a timed-out request landed. The client sends a key with the operation; the server stores it with the result. A repeat of the same key returns the stored response instead of doing the work again - so a retry cannot charge a card or send an email twice.

How do I make an SQL script safe to re-run?

Write the destination rather than the change, and check before inserting. An upsert - IF EXISTS then UPDATE, ELSE INSERT - can be run any number of times without creating duplicates, which means a failed run can simply be repeated.

---

Originally published on knowledgemarkg.com.

Comments

[Kashif Manzer]

One detail worth adding to the idempotency key section: checking for the key and then doing the work is still racy if two requests with the same key arrive at the same time, which double submits do. Both can read 'key not seen' before either writes. The fix is a UNIQUE constraint on the key column and letting the second insert fail, so the database, not your code, arbitrates. That turns the check into a guarantee even under concurrency.