Re:Why Uber Engineering Switched from Postgres to MySQL
by Negitivefrags on Tuesday July 26, 2016 @09:31PM
(#12169937)
None of what you said addresses the issue that I (or they) are talking about.
On Postgres an update requires a rewrite of every index of the row.
On MySQL it only requires an update of the indexes that were touched by the update.
If you have a table with 10 indexes then this means doing 10 extra writes physically to the disk.
Re:Why Uber Engineering Switched from Postgres to MySQL
by dialogbox on Tuesday July 26, 2016 @11:12PM
(#12170249)
As I know, not every updates rewrite indexes in PG. It has single page clean up and HOT (heap only tuple) update optimization.
Please refer to 64~ page of https://momjian.us/main/writings/pgsql/mvcc.pdf.
Re:Why Uber Engineering Switched from Postgres to MySQL
by continuational on Wednesday July 27, 2016 @02:55AM
(#12171015)
10 indexes on one table seems a bit much. It sounds like a table that hasn't been normalized.
Re:Why Uber Engineering Switched from Postgres to MySQL
by nsxwolf on Wednesday July 27, 2016 @03:54AM
(#12171216)
Though that is quite proper for a data warehouse.
Re:Why Uber Engineering Switched from Postgres to MySQL
by dikei on Wednesday July 27, 2016 @02:30PM
(#12175359)
A data warehouse should use much fewer transactions though.
Re:Why Uber Engineering Switched from Postgres to MySQL
by stubish on Wednesday July 27, 2016 @05:13AM
(#12171449)
Both vacuuming and logical replication are discussed in the article. In particular, vacuuming is easier with InnoDB since the changed records all exist in the redo log whereas PostgreSQL needs to scan the whole table. pglogical is mentioned for people running PG9.4+ as a way of doing minimal downtime cross version upgrades, which wasn't an option back with PG9.2 unless you go with something like slony1 or londiste.
Re:Why Uber Engineering Switched from Postgres to MySQL
by RedCrowbar on Wednesday July 27, 2016 @08:47AM
(#12172272)
> vacuuming is easier with InnoDB ... PostgreSQL needs to scan the whole table
There's been quite a few improvements to VACUUM in 9.6 [1], including avoiding full-table scans.
[1] https://www.postgresql.org/docs/9.6/static/release-9-6.html#...
Also they didn't mention anything about the auto vacuumer, which mostly solved the issue they are talking about.
Their lack of mention of the vacuumer and not seeming to know that Postgres supports statement level replication makes me wonder if they took a deep dive into the wrong part of the technology.