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…
Two critical factors determine whether a small application can thrive: whether its reads are indexed and whether its writes can be safely repeated. Usually, these concepts are taught separately - one from databases and the other from API design. However, they are fundamentally the same subject viewed from different angles. An index enables reads to scale, while idempotency ensures writes survive retries.
An index is essentially a second, ordered copy of some columns, with a pointer back to the original row. While it might seem to make the table faster, that's not the case. An index is a separate structure that provides an alternative route to the data. However, it comes at a cost: every write operation incurs a penalty. Adding an index increases the number of writes, which can become a tax if the indexed column is never used.
For an index to be effective, the query must be able to utilize it. Wrapping the indexed column in a function or using a leading wildcard in a LIKE operation can render the index useless, as the query planner must perform a scan anyway. The primary benefit an index provides is the ability to read only the necessary rows and skip the rest, significantly reducing the amount of work required for large datasets.
Composite indexes offer even greater potential. They are sorted first by the first column, then by the second within that group. Choosing the right order for columns in a composite index is crucial. For example, (IsPublished, PublishedAt) is far more efficient than (PublishedAt, IsPublished) because it allows the database to directly access the published rows in date order without any sorting required.
Idempotency, on the other hand, deals with the challenge of handling retries in a networked environment. When a request fails, there's no way to determine if the server actually processed it or if the response was lost. This issue is more common than simply dealing with duplicate clicks. Idempotent operations are those that can be repeated without changing the outcome beyond the initial execution.
For example, reading a value is inherently idempotent, as well as setting a value or appending data. However, charging a card is not idempotent, as it would result in multiple charges.
Idempotency is essential for ensuring retries are safe. If an operation is idempotent, a retry does not compromise the final result, eliminating the need to verify whether the initial attempt succeeded. This concept is easily implemented in SQL by describing the destination rather than the change. Instead of updating a view count, you could set it to a specific value.
This approach ensures that running the same operation multiple times yields the same result, making retries safe and eliminating the need for additional checks.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
