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.