3 ms·

I did not (yet) deep-dive into the article, but this: > This was observed in a specific table, that had ~20000000 rows and 23 indexes. The DB will update all

I did not (yet) deep-dive into the article, but this: > This was observed in a specific table, that had ~20000000 rows and 23 indexes. The DB will update all the indizes before committing its transaction and that could be one of the reason why it‘s still „slow“. Many indexes and many rows is not always a good option. Indexes makes insert slow and select fast.

hinkley 5y ago
Every place I’ve worked we seem to have forgotten that insert times are going to go up logarithmically as the data grows. So all your little queries that represent the sequential fraction of Amdahl’s law slowly creep up, while page weight increases and expectations of response time ratchet down. The exact same query that used to take 5% of your time budget now takes 8% when you need it to take 3%. And it has friends... We talk about economies of scale like we’re a manufacturing concern, and I don’t know why because I have literally never seen it work out that way for multiuser software, which has been taking over since the mid-late 90’s, about 25 years now. It’s a bimodal distribution. Too expensive to build for 10 people, more expensive to build (per user) for 10 million than for 1 million. And now that they’re pushing the idea that the real network effect probably only has a logarithmic payoff, then essentially the creators do logarithmically more work just to keep the wheels on, so the users enjoy a logarithmic improvement in the experience.
AdrianB1 5y ago
Does Postgres have includes for indexes? 23 indexes seems either a very rare case or bad design.
- chrisandchris 5y ago
  It does [1]. However I‘m not going to judge the Index count without knowing the exact use case. If it is r/w 95/5 percent, the amount of indices might be ok. If its 5/95, it‘s obviously not. [1] https://www.postgresql.org/docs/11/sql-createindex.html https://www.postgresql.org/docs/11/sql-createindex.html
  - s3rg4fts 5y ago
    Sadly, in this specific case we update almost 1/10th of the table daily and we have also based our entire searching functionality on indexes in this table. Bad news: our product relies a lot on having so many access patterns so it's hard to limit the indexes at this point Good news: most of the overhead (as you can see from the table with the timings) seems to be caused by the GIN index. We'll most likely move full text search to ElasticSearch and drop this so things will most likely get better. Once the move is done, we'll try dropping indexes and using ElasticSearch more for searches. I've seen this work tremendously well in the past so I have high hopes!

hinkley 5y ago

Every place I’ve worked we seem to have forgotten that insert times are going to go up logarithmically as the data grows. So all your little queries that represent the sequential fraction of Amdahl’s law slowly creep up, while page weight increases and expectations of response time ratchet down. The exact same query that used to take 5% of your time budget now takes 8% when you need it to take 3%. And it has friends... We talk about economies of scale like we’re a manufacturing concern, and I don’t know why because I have literally never seen it work out that way for multiuser software, which has been taking over since the mid-late 90’s, about 25 years now. It’s a bimodal distribution. Too expensive to build for 10 people, more expensive to build (per user) for 10 million than for 1 million. And now that they’re pushing the idea that the real network effect probably only has a logarithmic payoff, then essentially the creators do logarithmically more work just to keep the wheels on, so the users enjoy a logarithmic improvement in the experience.

AdrianB1 5y ago

Does Postgres have includes for indexes? 23 indexes seems either a very rare case or bad design.

chrisandchris 5y ago
It does [1]. However I‘m not going to judge the Index count without knowing the exact use case. If it is r/w 95/5 percent, the amount of indices might be ok. If its 5/95, it‘s obviously not. [1] https://www.postgresql.org/docs/11/sql-createindex.html https://www.postgresql.org/docs/11/sql-createindex.html
- s3rg4fts 5y ago
  Sadly, in this specific case we update almost 1/10th of the table daily and we have also based our entire searching functionality on indexes in this table. Bad news: our product relies a lot on having so many access patterns so it's hard to limit the indexes at this point Good news: most of the overhead (as you can see from the table with the timings) seems to be caused by the GIN index. We'll most likely move full text search to ElasticSearch and drop this so things will most likely get better. Once the move is done, we'll try dropping indexes and using ElasticSearch more for searches. I've seen this work tremendously well in the past so I have high hopes!