I was reading an article comparing PostgreSQL and MySQL recently.
PostgreSQL: cool features, lots of knobs, but index writes are expensive.
MySQL: simpler to pick up, fewer knobs, cheaper index writes, but not as fast as PostgreSQL.
That got me thinking — I use both of these, so let me add SQL Server too, since that's the one I use the most.
SQL Server has two kinds of indexes: clustered and non-clustered.
Clustered index: wherever the index is, the data is right there. No extra step needed.
Non-clustered index: it first finds the clustered index's position, then goes to get the data. Or if there's no clustered index, it goes straight to a physical row locator, then gets the data. Either way, that's one extra step.
MySQL's InnoDB forces every table to have an index.
If there's a primary key, it automatically becomes the (clustered) index. No primary key? It picks a UNIQUE column for you. Nothing UNIQUE either? It generates a hidden row ID. Same as SQL Server: the clustered index is one step, but any non-clustered index still takes one extra step.
PostgreSQL is a completely different idea.
Every row has an address pointer called a TID. No matter what index you build, what gets stored is index value → TID. So querying an index always means: find the index entry, get the TID, then go fetch the data. That's one extra step too.
So why does PostgreSQL's write cost get bigger? Say this row has two indexes — one on ID, one on date+order number. You update the order number. The TID changes.
Now every index that was pointing at the old TID is stale, so anything tied to that TID has to get updated too.
Remember what I said above — a PostgreSQL index's value points at a TID. The TID just changed, so every index has to update along with it: ID → new TID, date+order-number → new TID.
So PostgreSQL ends up doing: update the row itself, plus update the ID index, plus update the date+order-number index. One logical update, three writes.
MySQL and SQL Server, though? They only update the row itself, and whichever index actually got touched. If nothing got touched, it's just the one row write.
In this example the update was to order number, which happens to be part of the date+order-number index — so it's: one write for the row, one for that index. Two writes total.
If the update had been to some unrelated notes field instead, it would just be the one row write.
That's why PostgreSQL's index write cost blows up the way it does.
Top comments (0)