8 ms·

The State of Vacuum in Postgres

• firasd 9y ago
  Related: Why Uber Engineering Switched from Postgres to MySQL https://eng.uber.com/mysql-migration/ https://eng.uber.com/mysql-migration/ Edit: This is above my technical paygrade so I'll let others be the judge of whether these are in fact related! I believed so, from my earlier readings about the issue. Here's an article from the same blog in the OP about the Uber migration: http://rhaas.blogspot.in/2016/08/ubers-move-away-from-postgresql.html http://rhaas.blogspot.in/2016/08/ubers-move-away-from-postgr...
  • rosser 9y ago
    Vacuuming is not why Uber flounced off PostgreSQL. They were naïvely using it as a KV store, and indexing every column, which caused a write-amplification cascade. Edit, re: the parent's edit: Quoting that article, "Perhaps the thorniest problem which Uber raises is that of write amplification caused by secondary index updates." The word "vacuum" is mentioned exactly once, in the context of keeping up with index insertions (writes).
    • warent 9y ago
      Lol. If that's true (and I'm not disputing it) then how would switching to MySQL fix that problem?
      • noncoml 9y ago
        > Lol. If that's true (and I'm not disputing it) then how would switching to MySQL fix that problem? It's explained in the blog the great-grandparent linked: https://eng.uber.com/mysql-migration/ https://eng.uber.com/mysql-migration/
        • rosser 9y ago
          Uber's explanation of the problem neglects to consider their poor usage of the tool as contributory to the problem.
          • dozzie 9y ago
            That's why there were so many rebuttals to their arguments.
          • Thaxll 9y ago
            Even Postgres dev ack the problem they had, don't try to dismiss the issue it's real.
            • jtc331 9y ago
              There is a real issue, yes, but it’s heavily lessened by using sane scheme design, while Uber basically used the worst possible design.
            • rosser 9y ago
              Can you point out where I dismissed anything? PostgreSQL has a write-amplification issue — especially when you index every column on a table. My point is: if you don't do that, it doesn't hurt.
      • rosser 9y ago
        Writes in postgres touch all indexes. When you index every column, you have to touch every column's index for every write. Writes in MySQL (using InnoDB) just touch the pkey index, to which all other indexes point, or something like that. (Can someone who knows MySQL innards better — I avoid it like it's contagious — please confirm or clarify?)
        • blaisio 9y ago
          That's correct.
      • dragonwriter 9y ago
        Postgres’s MVCC implementation with immutable tuples means every write to a table affecting any index touches every index on that table. Excessive indexing will have a performance hit in any DB, but combined with Postgres’s architecture it’s particularly bad.
        • anarazel 9y ago
          > Postgres’s MVCC implementation with immutable tuples means every write (even updates to a column not in any indexes) to a table touches every index on that table. The parenthetical comment is not true. If no indexed columns are updated, and there's space on the same page, a so called "HOT" update is performed. Basically a redirection is inserted triggering index scans to follow the update chain to the newest version: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/heap/README.HOT&hb=HEAD https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f...
          • dragonwriter 9y ago
            Edited the comment to correct the error in my original understanding; thanks for pointing it out!
        • deleted 9y ago
          [deleted]
      • jtc331 9y ago
        The other thing here they downplay in the article is that they didn’t switch to MySQL per se; they implemented a columnar storage backend within MySQL. It’d have been more accurate to say they abandoned traditional relational databases entirely.
• rosser 9y ago
  We've found that periodic invocations of pg_repack are necessary to keep things from bloating beyond the inflection point where vacuuming just can't keep up. I'd love to see VACUUM somehow able to take care of that, because the WAL volume of repacking multi-terabyte databases — even when you're just targeting the worst culprit tables — is absurd (especially when you're keeping all your WALs around forever, for PITR purposes). I'm just not sure what more it can do.
  • ioltas 9y ago
    There has been in the community for a couple of years now a set of patches to support REINDEX CONCURRENTLY, and there have been discussions for having a CLUSTER CONCURRENTLY, but nothing has materialized. Pushing forward into having such patches would make sense. There are many users asking for the possibility to do tuple reordering without holding an exclusive lock on the relations involved.
• gregw2 9y ago
  Where do things stand with the status of vacuum in Redshift which derived from Postgres? What version of postgres vacuum implementation are they derived from? Have they implemented or incorporated any of these improvements since their fork? (Obviously Redshift doesn't need the btree index ones since it uses distribution keys and zone maps.) I ask because my experience is that Redshift vacuum implementation is awful on large heavily used data warehouses, and I wonder if there is any hope for improvement.
  • bigtones 9y ago
    Amazon Redshift is not actually based in Postgres, it just supports a Postgres interface. It is built on top of technology from the massive parallel processing (MPP) data-warehouse company ParAccel which was acquired by Actian, and licenced by Amazon.
    • anarazel 9y ago
      Which in turn initially was based on postgres. But there indeed isn't much in common anymore...
    • Ralfp 9y ago
      > Amazon Redshift is based on PostgreSQL 8.0.2 Same wikipedia article you are quoting ;) https://en.wikipedia.org/wiki/Amazon_Redshift https://en.wikipedia.org/wiki/Amazon_Redshift
      • scapecast 9y ago
        I can confirm that Redshift is based on Postgres. And I know that straight from the source: Dave Steinhoff, one of the founders of ParAccel, is on our team. https://www.intermix.io/about-us/ https://www.intermix.io/about-us/
  • blaisio 9y ago
    Vacuum in Redshift is doing something entirely different from vacuum in Postgres. You really need to look at the documentation.
• hinkley 9y ago
  A number of GC and other resource reclamation systems, especially soft real-time ones, have worked on accounting the cleanup costs to the parts of the system that make the mess in the first place. In these systems, a function that is very careful with resource allocation will almost never experience a significant slowdown due to the background processing, because they either pay no cost or an amortized cost. For instance, one collector I knew of would garbage collect up to 10 objects on every allocation. If large allocations are front loaded then a full cycle almost never happens. In Swift or C++ or Rust you pay for your allocations as soon as they go out of scope. In the context of an MVCC system, running a long query causes considerably more versioned data to pile up. If part of the cost of retiring that data was payed at the end of the transaction, several things happen. One, other transactions are less impacted by the overhead. Two, you end up throttling the bad actors, which encourages the problem to be fixed, and in the meantime the high water mark for vacuuming is reduced.
  • rosser 9y ago
    Are you legit suggesting tying the cost of GC to transaction commit in a database platform that is very commonly used for OLTP workloads?
    • hinkley 9y ago
      Those are exactly the systems that are falling over already, aren’t they? They’re already tied together whether that’s inconvenient or not. Right now (or at least, recently) the performance simply falls off a cliff instead of degrading. A lot of the tools we have for dealing with down services also can deal with degraded ones. If for instance throttling votes per second keeps the system usable for everyone else then that has been done before and can be done again. But you have to do it before the database has a seizure, not after. [edit: I’m talking about back pressure. This is not a new or crazy concept. Your incredulity and $5 will buy me a cup of coffee]
      • rosser 9y ago
        I wouldn't characterize things as "falling over", except perhaps in cases of operator incompetence. There's certainly room for improvement — The Fine Article wouldn't exist otherwise — and backpressure might be worth exploring as a means of improving things. If that backpressure causes the system to slow down in a way that adversely affects customer or end-user interactions, however, it will often just be turned off, mooting it — or making things worse.
• hinkley 9y ago
  Why isn’t the rate of vacuuming in Postgres determined by the rate of churn instead of things like wall clock time? These weights and heuristics are either going to punish small databases of sabotage large ones, unless the factor in a predictor or how much there is to vacuum.
  • anarazel 9y ago
    > Why isn’t the rate of vacuuming in Postgres determined by the rate of churn instead of things like wall clock time? It is determined by the rate of churn.
• devit 9y ago
  Is it really necessary to have a vacuum procedure? Isn't it possible to just use a data structure to index rows by transaction ids, and on each transaction commit efficiently find all rows that aren't visible to any transaction, and add them to a free list? Seems kind of a bad design to rely on periodic full data scans.
  • al_james 9y ago
    Sadly it's a bit more complex than that. Because currently running different transactions may hold a view of the database as it was before the transaction commits, the actual view of what does can be reclaimed depends on what transactions are currently active. Thus upon commit, it may be that some tuples release by the committed transaction are still visible to others. Vacuum actually works by looking for tuples where no running transactions can see them anymore. Postgres, in effect, maintains a minimum and maximum transaction ID that any tuple is visible for, and vacuum scans over all of those that have a max visible transaction ID (suggesting it's available to be reclaimed) and which is less than the minimum active transaction ID.
    • devit 9y ago
      Sure, but the DB knows what transactions are active. Assuming active transactions {T}_i ordered by id, then if you are committing T_i, any row whose [Tmin, Tmax] visibility interval is fully contained in (T_(i-1), T_(i+1)) (i.e. for which Tmin > T_(i-1) && T_(i+1) < Tmax) is now dead (taking -inf and a large value for the previous/next transaction ids if there are none). I believe this sort of query can be efficiently handled in time O(k polylog n) with several data structures, like an interval tree such as a B-tree augmented with maximum values or several kinds of 2D search trees. It's also possible to use a simple index and just reclaim those rows whose Tmax is lower than the oldest active transaction, although this means that a single never-closed transaction blocks all row reclamation forever, which seems a bad design for a production-quality database.
      • rosser 9y ago
        ...all of which structures you'd somehow need to synchronize writing to, to keep them accurate and fresh. Global write-locks do not scale, no matter how fast it might be in the toy case.