3 ms·
It depends: the projects make different trade offs and support different points of extension. I find it useful to remember that MySQL has various storage engin
by voganmother42 4y ago
It depends: the projects make different trade offs and support different points of extension.
I find it useful to remember that MySQL has various storage engines (that make different trade offs, and may themselves have different options such as various row formats), when using for instance the default of InnoDB knowing that it uses index organized (meaning stores the data in a btree as opposed to heap) tables can influence what workloads perform well (is the primary key used heavily/load bearing).
Postgres often excels when queries are more complex, for instance being able to use partial indexes (include a subset of a table, a “where” clause in the index) can be very powerful.
One other point of differentiation that has been useful for me to consider for various projects is also the programming within the database(stored programs): specifically doing things like writing stored procedures/functions, Postgres has many high quality procedural languages(some are shipped in core like PL/Perl, or others like PL/R that are external) that can useful to express these with.
- voganmother42 4y agoAs far as making/performing modifications to the schema/data, as others have stated, transactional ddl is a huge feature for Postgres and makes migrations much more straightforward in my experience.
- barrkel 4y agoAny DDL worthy of the name (table rewrite needed) in MySQL needs to be done with pt-online-schema-change or gh-ost. With DDL, you want to e.g. have it tune the I/O load of the migration so it doesn't interfere too much with production load. In practice, pt-osc / gh-ost means you have a kind of transaction, because the rewritten table doesn't take the place of the old one until the rename at the end. That's essentially an atomic commit.
- anonzzzies 4y agoI think this is a good answer. Our case is weird; millions of tables with billions (not sure how many in total even) of rows and no joins. Mysql handles this far better in our testing.