On Mon, Jul 13, 2020 at 12:28 PM Michael Lewis <mlewis@entrata.com> wrote:
Is this an insert only table and perhaps not being picked up by autovacuum? If so, try a manual "vacuum analyze" before/after each batch run perhaps. You don't mention updates, but also have been adjusting fillfactor so I am not not sure.
It is mostly an insert table. Only queries I need to run on it are to aggegate the count of IDs inserted per hour.
I did the vacuuming of the table; Didn't help. I tried both vacuum(analyze) and vacuum(full) ... took a looooong time and no improvements.
I adjusted the `fillfactor` because the documentation didn't make it too clear if by `updates to the table` it meant updating the value of existing rows, or updating the table itself (which in my understanding would mean that adding new data into it would cause the table to be updated). I just started messing with the `fillfactor` to see if that would give me any improvements. It seems to me it did since the first time I created the table, I didn't change the fillfactor and stumbled upon the performance issue after 12 hours; I then recreated the table with a fillfactor of 30 and was good again for about 12 hours more. Could be a coincidence though. I tried to recreate the table using fillfactor 10, but it was taking too long to add the data to it (12+ hours running and it wasn't done yet and the WRITE speed on iotop was around 20K/s .... I ended up just canceling it).
As of now, the table has about 280 million records in it.