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?
> 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.
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.
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!
"use AV worker items infrastructure for GIN pending list's cleanup"
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". """
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.