4 ms·

So satisfying to do a huge upgrade like this and then see the actual proof in the pudding with all the reduced latencies and query times.

hu3 2y ago
Yeah some numbers caught my attention like ~94% reduction in overall database lock time. And to think they never have to worry about VACUUM. Ahh the peace.
- anonzzzies 2y ago
  Yeah, until vacuum is gone, i'm not touching postgres. So many bad experiences with our use cases over the decades. I guess most people don't have our uses, but i'm thinking Uber does.
  - RedShift1 2y ago
    Maybe just vacuum much more aggressively? Also there have been a lot of changes to the vacuuming and auto vacuuming process these last few years, you can pretty much forget about it.
    - anonzzzies 2y ago
      Not in our experience; for our cases it is still a resource hog. We discussed it even less than a year ago with core devs and with a large postgres consultancy place; they said postgres doesn't fit our use case which was already our conclusion, no matter how much we want it to be. Mysql is smooth as butter. I have nothing to win from picking mysql just that it works; I rather use postgres as features / not oracle but... Edit; also, as can be seen here in responses, and elsewhere on the web when discussing this, the fans say it's no problem, but many less religious users feel it's a massive design flaw (perfectly logical at the time, not so logical now) that sometimes will stop users from using it, which is a shame
      - yeswecatan 2y ago
        What is your use case?
        anonzzzies 2y ago
        We have 100000s tables per database (1000s of those) (think sensor/iot data with some magic sauce that 0 of our competitors offer) that are heavy on the changes. And yes, maybe it's the wrong tool (is it though if it works without hickups?) for the job (but migrating would be severe so we would only attempt that if we are 100% sure it will work and if the endresult would be cheaper; remember; we are talking decades here, not a startup), but mysql has been taking this without any issues for decades with us (including the rapid growth of the past decade) now while far smaller setups with postgres have been really painful and all because of vacuum. We were postgres in 1999 when we ran many millions of records through it, but that was when we could do a full vacuum at night without anyone noticing. The internet grew a little bit, so that's not possible anymore. Vacuum improved too like everyone says here, and i'm not spreading the gospel or whatever; just fans (... what other word is there) blindly stating it can do loads 'now' they never considered is, well weird.
        dhoe 2y ago
        I'd generally call this amount of tables an antipattern - doing this basically implies that there's information stored in the table names that should be in rows instead, like IDs etc. -- But I'll admit that sensor related use cases have a tendency to stress the system in unusual ways, which may have forced this design.
        anonzzzies 2y ago
        Especially back when we started. Now we would've done it differently, but still think postgres wouldn't really work. Guess we will never now as even far smaller data sets do not work in the way we need them.
  - leishman 2y ago
    Postgres 17 tremendously improves vacuum performance
    - mannyv 2y ago
      Vacuuming is a design decision that may have been valid back in the day, but is really a ball and chain today. In a low-resource environment deferring work makes sense. But even in low-resource environment the vacuum process would consume huge amounts of resources to do its job, especially given any kind of scale. And the longer it's deferred the longer the process will take. And if you actually are in a low-resource environment it'll be a challenge to have enough disk space to complete the vacuum (I'm looking at you, sunos4) - and don't even talk about downtime. I don't understand how large pgsql users handle vacuuming in production. Maybe they just don't do it and let the disk usage grow unbounded, because disk space is cheap compared to the aggravation of vacuuming?
      - wongarsu 2y ago
        You run VACUUM often enough that you never need a VACUUM FULL. A normal VACUUM doesn't require any exclusive locks or a lot of disk space, so usually you can just run it in the background. Normally autovacuum does that for you, but at scale you transition to running it manually at low traffic times; or if you update rows a lot you throw more CPUs at the database server and run it frequently. Vacuuming indices is a bit more finicky with locks, but you can just periodically build a new index and drop the old one when it becomes an issue
        sgarland 2y ago
        People not realizing you can tune autovacuum on a per-table basis is the big one. Autovacuum can get a lot done if you have enough workers and enough spare RAM to throw at them. For indices, as you mentioned, doing either a REINDEX CONCURRENTLY (requires >= PG12), or a INDEX CONCURRENTLY / DROP CONCURRENTLY (and a rename if you’d like) is the way to go. In general, there is a lot more manual maintenance needed to keep Postgres running well at scale compared to MySQL, which is why I’m forever upset that Postgres is touted as the default to people who haven’t the slightest clue nor the inclination to do DB maintenance. RDS doesn’t help you here, nor Aurora – maintenance is still on you.
- InsideOutSanta 2y ago
  As somebody who has always used MySQL, but always been told that I should be using Postgres, I'd love to understand what the issues with VACUUM are, and what I should be aware of when potentially switching databases?
  - evanelias 2y ago
    For an in-depth read on the differences in MVCC implementations, this post is pure gold: https://www.cs.cmu.edu/~pavlo/blog/2023/04/the-part-of-postgresql-we-hate-the-most.html https://www.cs.cmu.edu/~pavlo/blog/2023/04/the-part-of-postg...
  - vbezhenar 2y ago
    When postgres deletes a row, it marks a disk space unused. If you delete a lot of rows, there will be plenty of unused space, but that space will not be released to OS. To release that space to OS, you need to run vacuum full or perform backup/truncate/restore. In normal circumstances it's not needed. The unused space is reused for further inserts and if your database maintains steady rate of inserts/deletes, this won't be an issue. However I experienced situation when database starts to grow uncontrollably and it was happening for weeks (when actual workload didn't change). I don't know what causes that behaviour. The solution was to run `vacuum full`.
  - djbusby 2y ago
    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.
- brightball 2y ago
  There are always tradeoffs.
- tomnipotent 2y ago
  MySQL indexes can contain references to rows in the undo log and has a periodic VACUUM-like process to remove those references, though no where near as impactful.

hu3 2y ago

Yeah some numbers caught my attention like ~94% reduction in overall database lock time. And to think they never have to worry about VACUUM. Ahh the peace.

anonzzzies 2y ago
Yeah, until vacuum is gone, i'm not touching postgres. So many bad experiences with our use cases over the decades. I guess most people don't have our uses, but i'm thinking Uber does.
- RedShift1 2y ago
  Maybe just vacuum much more aggressively? Also there have been a lot of changes to the vacuuming and auto vacuuming process these last few years, you can pretty much forget about it.
  - anonzzzies 2y ago
    Not in our experience; for our cases it is still a resource hog. We discussed it even less than a year ago with core devs and with a large postgres consultancy place; they said postgres doesn't fit our use case which was already our conclusion, no matter how much we want it to be. Mysql is smooth as butter. I have nothing to win from picking mysql just that it works; I rather use postgres as features / not oracle but... Edit; also, as can be seen here in responses, and elsewhere on the web when discussing this, the fans say it's no problem, but many less religious users feel it's a massive design flaw (perfectly logical at the time, not so logical now) that sometimes will stop users from using it, which is a shame
    - yeswecatan 2y ago
      What is your use case?
      - anonzzzies 2y ago
        We have 100000s tables per database (1000s of those) (think sensor/iot data with some magic sauce that 0 of our competitors offer) that are heavy on the changes. And yes, maybe it's the wrong tool (is it though if it works without hickups?) for the job (but migrating would be severe so we would only attempt that if we are 100% sure it will work and if the endresult would be cheaper; remember; we are talking decades here, not a startup), but mysql has been taking this without any issues for decades with us (including the rapid growth of the past decade) now while far smaller setups with postgres have been really painful and all because of vacuum. We were postgres in 1999 when we ran many millions of records through it, but that was when we could do a full vacuum at night without anyone noticing. The internet grew a little bit, so that's not possible anymore. Vacuum improved too like everyone says here, and i'm not spreading the gospel or whatever; just fans (... what other word is there) blindly stating it can do loads 'now' they never considered is, well weird.
        dhoe 2y ago
        I'd generally call this amount of tables an antipattern - doing this basically implies that there's information stored in the table names that should be in rows instead, like IDs etc. -- But I'll admit that sensor related use cases have a tendency to stress the system in unusual ways, which may have forced this design.
        anonzzzies 2y ago
        Especially back when we started. Now we would've done it differently, but still think postgres wouldn't really work. Guess we will never now as even far smaller data sets do not work in the way we need them.
- leishman 2y ago
  Postgres 17 tremendously improves vacuum performance
  - mannyv 2y ago
    Vacuuming is a design decision that may have been valid back in the day, but is really a ball and chain today. In a low-resource environment deferring work makes sense. But even in low-resource environment the vacuum process would consume huge amounts of resources to do its job, especially given any kind of scale. And the longer it's deferred the longer the process will take. And if you actually are in a low-resource environment it'll be a challenge to have enough disk space to complete the vacuum (I'm looking at you, sunos4) - and don't even talk about downtime. I don't understand how large pgsql users handle vacuuming in production. Maybe they just don't do it and let the disk usage grow unbounded, because disk space is cheap compared to the aggravation of vacuuming?
    - wongarsu 2y ago
      You run VACUUM often enough that you never need a VACUUM FULL. A normal VACUUM doesn't require any exclusive locks or a lot of disk space, so usually you can just run it in the background. Normally autovacuum does that for you, but at scale you transition to running it manually at low traffic times; or if you update rows a lot you throw more CPUs at the database server and run it frequently. Vacuuming indices is a bit more finicky with locks, but you can just periodically build a new index and drop the old one when it becomes an issue
      - sgarland 2y ago
        People not realizing you can tune autovacuum on a per-table basis is the big one. Autovacuum can get a lot done if you have enough workers and enough spare RAM to throw at them. For indices, as you mentioned, doing either a REINDEX CONCURRENTLY (requires >= PG12), or a INDEX CONCURRENTLY / DROP CONCURRENTLY (and a rename if you’d like) is the way to go. In general, there is a lot more manual maintenance needed to keep Postgres running well at scale compared to MySQL, which is why I’m forever upset that Postgres is touted as the default to people who haven’t the slightest clue nor the inclination to do DB maintenance. RDS doesn’t help you here, nor Aurora – maintenance is still on you.
InsideOutSanta 2y ago
As somebody who has always used MySQL, but always been told that I should be using Postgres, I'd love to understand what the issues with VACUUM are, and what I should be aware of when potentially switching databases?
- evanelias 2y ago
  For an in-depth read on the differences in MVCC implementations, this post is pure gold: https://www.cs.cmu.edu/~pavlo/blog/2023/04/the-part-of-postgresql-we-hate-the-most.html https://www.cs.cmu.edu/~pavlo/blog/2023/04/the-part-of-postg...
- vbezhenar 2y ago
  When postgres deletes a row, it marks a disk space unused. If you delete a lot of rows, there will be plenty of unused space, but that space will not be released to OS. To release that space to OS, you need to run vacuum full or perform backup/truncate/restore. In normal circumstances it's not needed. The unused space is reused for further inserts and if your database maintains steady rate of inserts/deletes, this won't be an issue. However I experienced situation when database starts to grow uncontrollably and it was happening for weeks (when actual workload didn't change). I don't know what causes that behaviour. The solution was to run `vacuum full`.
- djbusby 2y ago
  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.
brightball 2y ago
There are always tradeoffs.
tomnipotent 2y ago
MySQL indexes can contain references to rows in the undo log and has a periodic VACUUM-like process to remove those references, though no where near as impactful.