News for hackers. Stuff that matters.
Re:The State of Vacuum in Postgres by rosser on Friday January 05, 2018 @02:57PM (#16081153)
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).

Re:The State of Vacuum in Postgres by warent on Friday January 05, 2018 @03:16PM (#16081293)
Lol. If that's true (and I'm not disputing it) then how would switching to MySQL fix that problem?
Re:The State of Vacuum in Postgres by rosser on Friday January 05, 2018 @03:19PM (#16081316)
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?)

Re:The State of Vacuum in Postgres by blaisio on Friday January 05, 2018 @09:32PM (#16083513)
That's correct.
Re:The State of Vacuum in Postgres by jtc331 on Friday January 05, 2018 @07:29PM (#16083008)
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.

Re:The State of Vacuum in Postgres by dragonwriter on Friday January 05, 2018 @04:57PM (#16082080)
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.
Re:The State of Vacuum in Postgres by anarazel on Friday January 05, 2018 @06:04PM (#16082566)
> 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...

Re:The State of Vacuum in Postgres by dragonwriter on Friday January 05, 2018 @06:12PM (#16082612)
Edited the comment to correct the error in my original understanding; thanks for pointing it out!
Re:The State of Vacuum in Postgres by noncoml on Friday January 05, 2018 @03:19PM (#16081313)
> 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/

Re:The State of Vacuum in Postgres by rosser on Friday January 05, 2018 @03:21PM (#16081330)
Uber's explanation of the problem neglects to consider their poor usage of the tool as contributory to the problem.
Re:The State of Vacuum in Postgres by dozzie on Friday January 05, 2018 @03:27PM (#16081377)
That's why there were so many rebuttals to their arguments.
Re:The State of Vacuum in Postgres by Thaxll on Friday January 05, 2018 @07:18PM (#16082938)
Even Postgres dev ack the problem they had, don't try to dismiss the issue it's real.
Re:The State of Vacuum in Postgres by rosser on Friday January 05, 2018 @08:25PM (#16083253)
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.

Re:The State of Vacuum in Postgres by jtc331 on Friday January 05, 2018 @07:30PM (#16083012)
There is a real issue, yes, but it’s heavily lessened by using sane scheme design, while Uber basically used the worst possible design.