The big tech companies (ie. FANG) all have huge demand for principal+ engineers and know how to scope and support them properly. The size means they have lots of them which reduces the snowflake nature of the role and makes career development, salary support, etc… more standardized. Since they are large, there can be some variance, but overall they are a good option if you are interested. I don’t know where in EU you are, but most of FANG has roles in EU and some even do remote as well.
Smaller companies tend to want principal engineers in theory, but as you are finding they can often struggle with how to utilize them properly. Often you see them playing a Chief Architect role or something similar which is more hybrid with PM than pure tech. So getting PM skills could help set you up for this direction as well.
Finally, you have said you did management for a bit before, so you should know if this is an interesting path or not. This is a very different role, so I would recommend you do this only if you have the passion and desire to do this full time. Most principal engineers I know have done management for a bit and gained a lot of skills related to it, but ultimately wasn’t what they wanted to do.
There is nothing that prevents these from being implemented for PostgreSQL. In fact, they have already done a lot of optimizations that close the gap, such as covering indexes. Before covering indexes, InnoDB was even further ahead of them.
Of the ones mentioned, I would guess compression is the most complex. For InnoDB this took many years to get the point of it being usable and efficient. Many naive implementations can cause huge overheads for CPU which makes it unusable.
Right, PG calls the REDO log the WAL (for most purposes they are the same thing). I did not know that 9.4 can do partial page writes to the WAL now. Guess I will have more reading to do, thanks for pointing it out! A nice blog post by a colleague recently showing how large writes to redo logs matter is (not about PG, but why it is significant in the context of size of entries):
As far as when clustering a table is useful, see the CLUSTER command in PG. It is roughly the same places you would want to do it, except it is automatically maintained. You do need to realize what is going on to minimize impact on inserts, but in a lot of cases, data in inserted in generally ascending order so it mostly 'free'. This does make GUIDs really bad for PKs in InnoDB.
Clustered indexes are like covering indexes (which PG got recently). You don't quite realize how useful they are until you get access to it ;)
InnoDB compression for us is primarily for non-lob objects, so TOAST is quite a bit different than the cross-row compression that we get. We will normally do compression of large objects outside of the DB whenever possible.
I'm not saying that InnoDB is always better than PG, but in a lot of cases that I have tested it with, it is indeed better. PG has come a long way recently, including options such as covering indexes to close the gap.
(I am a manager of the Data-Perf team at FB which deals with lots of different DB technologies)
MySQL (specifically InnoDB) is extremely efficient as a storage backend compared to PostgreSQL.
There are a few features that make InnoDB better in many cases:
1. Change buffering for IO bound workloads: If you are IO bound, then InnoDB change buffering is a huge, huge win. It basically is able to reduce IO required for secondary index maintenance by a huge amount.
2. InnoDB compression: When you are space constrained (say using flash storage), then being able to compress your data is a big win. In our case, it reduces space by around 40% which translates directly to 40% less servers required. While you could do something like run PG on ZFS with compression, for an OLTP workload, you want the compression in the DB so that it can do a lot of work to minimize the compressing and decompressing of data.
3. Clustered index: The InnoDB PK is a clustered index. This makes a lot of query patterns (such as range scans of the PK) very cheap. Combined with covering indexes (which PG now has too!), you can really minimize the IO required by properly tuned queries.
There are a variety of smaller things as well, such as InnoDB doing logical writes to the redo log vs. PG doing full page writes, so on very high write systems, the REDO log bytes written will be dramatically less. Also MySQL replication has traditionally been more flexible than PG, but PG has made some great strides recently, so I don't know if I would maintain that position still.
There is nothing that precludes public contributions to the project. The important part is that the change be useful to 'webscale' type applications.
Prior to this announcement, the only people involved have been these companies to get it bootstrapped, so the work so far looks a bit slanted to these companies.
"We’ll be doing everything in the open, so everyone in the MySQL community will be able to take what they want from what we do and contribute however they like."
Yes, we are still very active users of MySQL. Most of the primary portions of facebook.com are still served from a backend MySQL system (with lots of caching and many other services involved). Some data is not stored in MySQL, but in other systems such as HBase (messages being a big one).
Doing compression on the ZFS level is significantly worse than InnoDB compression.
InnoDB has a lot of really smart optimizations which make it much better than just zipping things up. Included are the modification log (so you only have to re-compress occasionally) and a dynamic scaling ability to keep compressed pages in memory rather than always decompressing. These optimizations are really only possible with an understanding of the data.
I would only consider ZFS for something like an append-only data warehouse type system.
In addition, when you are dealing with many thousand application servers and many hundred database connections, the database will not end up happy having to maintain > 10k connections.
So you will end up needing to add a proxy or connect every time. As you indicated, since MySQL connections are so cheap (ie. you can do > 50k per second), it is faster and easier to create a new connection each time.
The easiest way is to avoid running into the problem.
The main reason the central table space grows is due to rollback segments. To avoid collecting a lot of them there are two tips (in addition to file per table you mentioned):
1. Use multi-threaded purge.
2. Avoid very long running transactions (ie. 12+ hours)
Once we have done that, we don't really see this problem any longer.
This really reminds me a lot of the progression of MySQL.
Originally it was used with ISAM/MyISAM and it was pretty popular. Then InnoDB came around and it quickly revolutionized the MySQL world, allowing MySQL to grow to the next level. Now InnoDB is by far the most commonly used storage engine and the default on several distributions.