5 ms·

I'm still surprised it takes > 1 minute to perform 5000 inserts, even after the optimization was put in place. Is there something else wrong?

chrisandchris 5y ago
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!
anarazel 5y ago
Gin indexes aren't "plain" indexes - they're used for things like full text search. Depending on the specific types/opclasses that can mean that a lot of work has to be done for a single inserted/updated row.
- VWWHFSfQ 5y ago
  The performance table says 5,000/minute even without the GIN index on the table.
  - anarazel 5y ago
    Good point - it's still 22 other indexes though. And it's Aurora, so who knows :). The table is fairly large, so it's quite possible that a lot of the indexes aren't in memory, making the index maintenance more expensive...
    - JoelJacobson 5y ago
      Related thread on pgsql-hackers: "use AV worker items infrastructure for GIN pending list's cleanup" https://www.postgresql.org/message-id/flat/20210405063117.GA2478%40ahch-to https://www.postgresql.org/message-id/flat/20210405063117.GA... """ A customer here has 20+ GIN indexes in a big heavily used table and every time one of the indexes reaches gin_pending_list_limit (because of an insert or update) a user feels the impact. So, currently we have a cronjob running periodically and checking pending list sizes to process the index before the limit get fired by an user operation. While the index still is processed and locked the fact that doesn't happen in the user face make the process less notorious and in the mind of users faster. This will provide the same facility, the process will happen "in the background". """
oulu2006 5y ago
Yes, that is quite slow. I can regularly do 10000 inserts in about 10-15 seconds, they're done in bulk though, so that might be a part of it. Also, I don't have 23 indexes, so that's probably a large reason why it's so slow, especially if the table is massive and therefore the index is also quite large.

chrisandchris 5y ago

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!

anarazel 5y ago

Gin indexes aren't "plain" indexes - they're used for things like full text search. Depending on the specific types/opclasses that can mean that a lot of work has to be done for a single inserted/updated row.

VWWHFSfQ 5y ago
The performance table says 5,000/minute even without the GIN index on the table.
- anarazel 5y ago
  Good point - it's still 22 other indexes though. And it's Aurora, so who knows :). The table is fairly large, so it's quite possible that a lot of the indexes aren't in memory, making the index maintenance more expensive...
  - JoelJacobson 5y ago
    Related thread on pgsql-hackers: "use AV worker items infrastructure for GIN pending list's cleanup" https://www.postgresql.org/message-id/flat/20210405063117.GA2478%40ahch-to https://www.postgresql.org/message-id/flat/20210405063117.GA... """ A customer here has 20+ GIN indexes in a big heavily used table and every time one of the indexes reaches gin_pending_list_limit (because of an insert or update) a user feels the impact. So, currently we have a cronjob running periodically and checking pending list sizes to process the index before the limit get fired by an user operation. While the index still is processed and locked the fact that doesn't happen in the user face make the process less notorious and in the mind of users faster. This will provide the same facility, the process will happen "in the background". """

oulu2006 5y ago

Yes, that is quite slow. I can regularly do 10000 inserts in about 10-15 seconds, they're done in bulk though, so that might be a part of it. Also, I don't have 23 indexes, so that's probably a large reason why it's so slow, especially if the table is massive and therefore the index is also quite large.