1

I have db table which has around 5-6 Mn entries and it is taking around 20 minutes to perform vacuuming. Since, one field of this table is updated very frequently, thereare a lot of dead rows to deal with.

For an estimate, with our current user base it can have 2 Million dead tuples on daily basis. So, vacuuming of this table requires both:

  1. Read IO: as the whole table is not present in shared memory.
  2. Write IO: as there are a lot of entries to update.

What should be an ideal way to vacuum this table? Should I increase the autovacuum_cost_limit to allow more operations per autovacuum run? But as i can see, it will increase IOPS, which again might hinder the performance. Currently, I have autovacuum_scale_factor = 0.2. Should I decrease it? If I decrease it it will run more often, although write IO will decrease but it will lead to more number of time period with high read IO.

Also, as the user base will increase it will take more and more time as the size of table with increase and vacuum will have to read a lot from disk. So, what should I do?

One of the solution I have thought of:

  1. Separate the highly updated column and make a separate table.
  2. Tweaking the parameter to make it run more often to decrease write IO(as discussed above). How to handle more Read IO, as vacuum will now run more often?
  3. Combine point 2 along with increasing RAM to reduce Read IO as well.

In general what is the approach that people takes, because I assume people must have very big table 10GB or more, that needs to be vacuumed.

hardik24
  • 1,008
  • 1
  • 11
  • 34

2 Answers2

2

Separating the column is a viable strategy but would be a last resort to me. PostgreSQL already has a high per-row overhead, and doing this would double it (which might also remove most of the benefit). Plus, it would make your queries uglier, harder to read, harder to maintain, easier to introduce bugs. Where splitting it would be most attractive is if index-only-scans on a set of columns not including this is are important to you, and splitting it out lets you keep the visibility map for those remaining columns in a better state.

Why do you care that it takes 20 minutes? Is that causing something bad to happen? At that rate, you could vacuum this table 72 times a day, which seems to be way more often than it actually needs to be vacuumed. In v12, the default value for autovacuum_vacuum_cost_delay was dropped 10 fold, to 2ms. This change in default was not driven by changes in the code in v12, but rather by the realization that the old default was just out of date with modern hardware in most cases. I would have no trouble pushing that change into v11 config; but I don't think doing so would address your main concern, either.

Do you actually have a problem with the amount of IO you are generating, or is it just conjecture? The IO done is mostly sequential, but how important that is would depend on your storage hardware. Do you see latency spikes while the vacuum is happening? Are you charged per IO and your bill is too high? High IO is not inherently a problem, it is only a problem if it causes a problem.

Currently, I have autovacuum_scale_factor = 0.2. Should I decrease it? If I decrease it it will run more often, although write IO will decrease but it will lead to more number of time period with high read IO.

Running more often probably won't decrease your write IO by much if any. Every table/index page with at least one obsolete tuple needs to get written, during every vacuum. Writing one page just to remove one obsolete tuple will cause more writing than waiting until there are a lot of obsolete tuples that can all be removed by one write. You might be writing somewhat less per vacuum, but doing more vacuums will make up for that, and probably far more than make up for it.

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • It is not creating any problems as such but the thing I am worried about for being Write IO intensive is that it will transfer the wal files to read replica which in then because of high io will have higher replication. If we talk about Read IO, i just want to ensure that in future it does not affect query performance. – hardik24 Jul 13 '20 at 19:17
  • Your comment that increasing runs will actually increase write, makes perfect sense to me now. It definitely might decrease for single run but overall it will only increase it. In that case I guess Laurenz's answer(using `fillfactor`) might help. – hardik24 Jul 13 '20 at 19:21
  • Just of the information, what do people do when they have very large table with billions of rows that needs to be updated and vacuum needs to be run more often? There it might take a lot of time to actually vacuum the table? One possible might be sharding, so as to split a table/database into multiple machines, each having its own IO bandwidth? – hardik24 Jul 13 '20 at 19:28
  • @hardik24 I think it is very uncommon to have billions of rows in a table, *and* have >30% of them turned over every day. If you do have such a situation, you should be able to throw a lot more IO at the auto-vacs, or turn off IO throttling completely. v13 will also introduce parallel vacuuming (only of tables with multiple indexes). Sharding (or just partitioning) would also help, but I think those would usually be done for other reasons, not just for vacuuming reasons. – jjanes Jul 16 '20 at 16:17
1

There are two approaches:

  1. Reduce autovacuum_vacuum_cost_delay for that table so that autovacuum becomes faster. It will still consume I/O, CPU and RAM.

  2. Set the fillfactor for the table to a value less than 100 and make sure that the column you update frequently is not indexed. Then you could get HOT updates which don't require VACUUM.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • What will happen if that field is indexed? – hardik24 Jul 13 '20 at 15:47
  • 1
    You won't get a HOT update and end up with lots of dead tuples and autovacuum runs. – Laurenz Albe Jul 13 '20 at 15:50
  • This will definitely improve the write io and less wal size to be transferred to read replica. Any way to improve Read IO? Should I seperate the more updated column to a new table? So that less read is done while vacuuming? – hardik24 Jul 13 '20 at 16:01
  • You could consider having the column in a different table. You will still have a lot of vacuuming to do. – Laurenz Albe Jul 13 '20 at 16:18
  • I understand but it will solve a lot of problems. Just for the general knowledge, Postgres is a very widely used database and people must be having billions of rows in a single table that needs to be vacuumed, sometimes more frequently(say once a day). So, will it consume the whole IO of the machine for the time it will run? – hardik24 Jul 13 '20 at 16:22
  • It will consume as much I/O as a single thread that is slowed by occasional sleeps can. – Laurenz Albe Jul 14 '20 at 05:45