0

We ran a migration on a table which contain a big jsonb.

"Just" one new attribute was added inside the jsonb: "someAttribute": null was added on each row.

After this migration, the "table_size" increase a lot: from 20GB to 50GB

It is normal?

These graphes show table size, dead-rows and autovacuums: table size, dead-rows and autovacuums

Tried to look at TOAST behavior, vaccuming, or physical storage working.

4
  • 3
    Yes, this is normal.
    – jjanes
    Commented Apr 20, 2023 at 14:18
  • thank you for your answer. Can you please elaborate a bit more? Even involved key concept names are appreciated.
    – Slim
    Commented Apr 20, 2023 at 15:13
  • For example, will it double again if I add another attribute in the jsonb, or was it "this time only" because of some raised threshold that won't happen again?
    – Slim
    Commented Apr 20, 2023 at 15:18
  • 1
    If you did such an update again before the old space becomes available for reuse, you would expect the table size to become a little more than 3 times its original size (not another doubling which would be to 4 times the original size). I say a little more, because obviously adding a key to a JSON make it take more space, as the key itself has some size. How space is reclaimed for reuse is described here: postgresql.org/docs/current/…
    – jjanes
    Commented Apr 20, 2023 at 18:42

1 Answer 1

2

When you update a row in PostgreSQL, the old version of the row remains in the table, and a new version is added. So if you update all rows, the table size will approximately double.

There are two approaches:

  1. After the mass update, run

    VACUUM (FULL) tab;
    

    But beware that the table is inaccessible during that operation.

  2. Run the update in batches like

    UPDATE tab SET ... WHERE id BETWEEN 1 AND 10000;
    

    After each batch, run

    VACUUM tab;
    

    to remove the old data from the table. That VACUUM will not reduce the size of the table, but it will free the old row versions, and that space can be reused by the next batch.

5
  • Ok Laurentz, so I will run a full VACUUM when possible. Is it normal that autovacuums did not reduce the table size? (There were 3 autovacuums during migration, please see the bottom right graph)
    – Slim
    Commented Apr 21, 2023 at 7:31
  • 1
    Yes, normal VACUUM (which is triggered by autovacuum) usually will not reduce the size of the table. Commented Apr 21, 2023 at 7:51
  • Thank you very much. full vacuum took 10 minutes on a staging database. We manage to run it on a low used moment. Next times we'll plan to use solution 2. Regards
    – Slim
    Commented Apr 21, 2023 at 8:07
  • A subsidiary stupid question: our day to day activity updates many records. Did our disk usage increase due to this? There should be a mechanism that prevent it. Isn't the role of autovacuum? The seen effect of autovacuum is reducing "dead rows" only from what I am able to see.
    – Slim
    Commented Apr 21, 2023 at 8:39
  • That leads too far and would warrant its own investigation by a consultant. Commented Apr 21, 2023 at 10:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.