5 ms·

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

• SoftTalker 3y ago
  They add overhead to updates and inserts. If you don't need them, why take that hit. Know your data.
  • toast0 3y ago
    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.
  • EGreg 3y ago
    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?
    • titaniczero 3y ago
      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.
      • wswope 3y ago
        This is a read-heavy workload per the OP: https://news.ycombinator.com/item?id=36071799 https://news.ycombinator.com/item?id=36071799
        • raldi 3y ago
          It was neither read-heavy nor write-heavy.
          • wswope 3y ago
            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…
  • tomnipotent 3y ago
    Considering how much data is written to support redo/undo/wal logs, a single index for user id is very little overhead.
  • holoduke 3y ago
    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.
• raldi 3y ago
  If you have nothing to optimize yet, how will you know if you’re optimizing it right?
  • wswope 3y ago
    Typically, you create a large number of test records and see how your expected access patterns perform.
• jefftk 3y ago
  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.
  • wswope 3y ago
    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
    • raldi 3y ago
      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.
• VWWHFSfQ 3y ago
  > 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.