Hacker News Reader

Comment by cactusplant7374 | Hacker News Reader

Indexes are such an easy thing to add. I don't get it. Seems like under optimization when you consider the tradeoffs.

They add overhead to updates and inserts. If you don't need them, why take that hit. Know your data.

If the table is small enough that indexes aren't critical for reads, they probably don't impact the rate of writes either.

Of course, if the object is large scale bulk inserts, with only very occasional selects, then yes.

Wait a second, the whole thing is you don't mind O(N) overhead in searching on every request, but you mind O(log N) overhead for updates and inserts?

Well, my guess is that updates and inserts are much more frequent than searches in their use case. You're assuming a balanced frequency for these operations and it hardly ever happens.

This is a read-heavy workload per the OP: https://news.ycombinator.com/item?id=36071799

It was neither read-heavy nor write-heavy.

Ah, thanks for the correction; I’m guessing I read your comment too literally.

Would strikethrough my prior comment if it weren’t past the edit window…

Considering how much data is written to support redo/undo/wal logs, a single index for user id is very little overhead.

My experience is that it's uaually better to add indices where it's expected to be needed beforehand. Adding indices on large production tables will millions of rows can bring down databases for hours or even days worst case. It's tricky to manage.

Consider the example from the post of searching your shell history. If I don't need indexes I can just have it all in one flat file and use grep. Switching to a tool that supports indexing adds a lot of potential complexity and ways things could go wrong.

Or consider the example of a developer querying frontend logs: queries are many orders of magnitude less common than writes, and (at the scale I used to work at) an index would be incredibly expensive.

I take the point of your examples as written in the post, but I think both of those are a bad comparison to the Reddit Premium subscriber table being discussed, because:

- We’re already using a database, so there’s very minimal added complexity

- This is an extremely hot table getting read millions of times a day

- The scale of the data isn’t well-bounded

- Usage patterns of the table are liable to change over time

It wasn't an extremely hot table, and the scale of the data was well-bounded insofar as the only way for it to become non-negligible would be for us to have had revenue beyond our then-wildest dreams.

If you have nothing to optimize yet, how will you know if you’re optimizing it right?

Typically, you create a large number of test records and see how your expected access patterns perform.

> Indexes are such an easy thing to add.

Indexes can have catastrophic consequences depending on the access patterns of your table. I use indexes very sparingly and only when I know for sure the benefit will outweigh the cost.

Reply on news.ycombinator.com