Re: Uber migrated from Postgres to MySQL

Just a few points on reading this.

First, the timeline bugs regarding replication (particularly iirc in the 9.1 days).  I remember accidentally corrupting a (fortunately only demonstration!) database cluster in the process of demonstrating promotion at least once.  Iirc last time I tried to reproduce these problems, they had been fixed (by 9.3?).

The replication section made me wonder though if they were using the right replication solution for the job.  If you don't want an on-disk copy, don't use physical replication.  This being said there is one serious issue here that is worth mentioning, which is that since autovacuum on the master has no knowledge of autovacuum on the slave, it is easy to have longer-running queries on a slave that have rows they need to see removed by autovacuum and replication.  This can of course be easily fixed (if your query takes 30 sec to run, every 30 sec open a minute-long transaction on the master, which means that autovacuum can never clean rows that are older than 30 sec) but such is not a very robust solution and may cause more problems than it is worth (the real solution is going to a logical replication system where that is a problem).  As I usually put it, streaming replication is for cases where you need to guarantee an exact replica of everything, while logical replication is where you need a copy of data for use.

Finally, if I were trying to create something like schemaless, there is one major limitation of PostgreSQL that is not mentioned here, which is TOAST overhead.  I have seen people try to do things like this and TOAST overhead can be a real problem in these cases.  If your data for a row won't easily fit in significantly less than a page, then every read of that data and every write can effectively do an implicit nested loop join.  And if you want to talk about write amplification......  But this is also very well hidden and not easy to measure unless you know to look for it specifically so it is possible that they ran into it and didn't know it but I don't have any knowledge of what they did or tried so I could be totally off base here.  I would say I have seen more than one project run into this and because explain analyze select * does not detoast....

All of the above being said, there are solutions to all the major problems.  But you have to know about them, where to look, and what to do.  And with higher scale, one very important aspect is that attention to detail starts to matter a whole lot.  I agree that there are some good points raised but I wonder what the solutions are.  There is room for some improvement in the backend (it would really be nice to instrument and measure toasting/detoasting overhead in explain analyze) but for a lot of these  I wonder if that is secondary.   PostgreSQL is very well optimized for a certain series of tasks, and one can build well optimized solutions well outside that.  At a certain point (including a certain scale) therewill be no substitute for a teamof people who really know the db backend inside and out who can design around limitations and I think that is true for all databases I have worked with.