3 ms·

VACUUM and VACUUM FULL (and/or with ANALYZE) can lock tables for a very long time, especially when the table is large. Incantation may also require 2x the space

VACUUM and VACUUM FULL (and/or with ANALYZE) can lock tables for a very long time, especially when the table is large. Incantation may also require 2x the space for the table being operated on. In short: it's slow.

williamdclt 2y ago
Only FULL takes a serious lock (normal vacuum only takes a weak lock preventing things like other vacuums or table alterations iirc). Aside: I wish Postgres forced to make explicit the lock taken. Make me write “TAKE LOCK ACCESS EXCLUSIVE VACUUM FULL my_table”, and fail if the lock I take is too weak. Implicit locks are such a massive footgun that have caused countless incidents across the world, it’s just bad design.
- luhn 2y ago
  `TAKE LOCK ACCESS EXCLUSIVE VACUUM FULL` is just an incantation that will be blindly copy-pasted. I don't see how it would stop anyone from shooting themselves in the foot.
  - immibis 2y ago
    Imagine two footguns. One shoots your foot off when you open the window. The second requires you to point a gun at your foot and pull the trigger before the window unlocks. Far fewer people will suffer accidental foot injuries from the latter.
sgarland 2y ago
`VACUUM` (with or without `ANALYZE`) on its own neither locks tables nor requires additional disk space. This is what the autovacuumdvaemon is doing. `VACUUM FULL` does both, as it's doing a tuple-by-tuple rewrite of the entire table.
gomoboo 2y ago
pg_repack gets rid of the need to lock tables for the duration of the vacuum: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.pg_repack.html https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appen... It is an extension though so downside there is it not being included in most Postgres installs. I’ve used it at work and it felt like a superpower getting the benefits of a vacuum full without all the usual drama.
- take-five 2y ago
  pg_repack can generate a lot of WAL, which can generate so much traffic that standby servers can fall behind too much and never recover. We've been using https://github.com/dataegret/pgcompacttable https://github.com/dataegret/pgcompacttable to clean up bloat without impacting stability/performance as much as pg_repack does.
cooljacob204 2y ago
This is sorta mitigated by partitioning or sharding though right? Too bad it's sorta annoying to do on plain old pg.

williamdclt 2y ago

Only FULL takes a serious lock (normal vacuum only takes a weak lock preventing things like other vacuums or table alterations iirc). Aside: I wish Postgres forced to make explicit the lock taken. Make me write “TAKE LOCK ACCESS EXCLUSIVE VACUUM FULL my_table”, and fail if the lock I take is too weak. Implicit locks are such a massive footgun that have caused countless incidents across the world, it’s just bad design.

luhn 2y ago
`TAKE LOCK ACCESS EXCLUSIVE VACUUM FULL` is just an incantation that will be blindly copy-pasted. I don't see how it would stop anyone from shooting themselves in the foot.
- immibis 2y ago
  Imagine two footguns. One shoots your foot off when you open the window. The second requires you to point a gun at your foot and pull the trigger before the window unlocks. Far fewer people will suffer accidental foot injuries from the latter.

sgarland 2y ago

`VACUUM` (with or without `ANALYZE`) on its own neither locks tables nor requires additional disk space. This is what the autovacuumdvaemon is doing. `VACUUM FULL` does both, as it's doing a tuple-by-tuple rewrite of the entire table.

gomoboo 2y ago

pg_repack gets rid of the need to lock tables for the duration of the vacuum: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.pg_repack.html https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appen... It is an extension though so downside there is it not being included in most Postgres installs. I’ve used it at work and it felt like a superpower getting the benefits of a vacuum full without all the usual drama.

take-five 2y ago
pg_repack can generate a lot of WAL, which can generate so much traffic that standby servers can fall behind too much and never recover. We've been using https://github.com/dataegret/pgcompacttable https://github.com/dataegret/pgcompacttable to clean up bloat without impacting stability/performance as much as pg_repack does.

cooljacob204 2y ago

This is sorta mitigated by partitioning or sharding though right? Too bad it's sorta annoying to do on plain old pg.