![]() In the former, administration of Postgres has gotten much easier since Heroku got involved and Andy Pavlo started talking about self-driving databases. That is, turn-key cloud Postgres is not Y2K Postgres, and MySQL post-Maria is not Y2K MySQL. I'd say that today, it's much muddier than it was maybe ten years ago, before the data science explosion and cloud explosion. 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. 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. 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). It depends: the projects make different trade offs and support different points of extension. Personally I'd just go for PostgreSQL/MySQL/MariaDB in most cases because of the ease of use. Even if you can launch Oracle XE or something like it in containers, provided that you get your hands on such containers, there will still be challenges in regards to startup time, resource usage and various other quirks.īut hey, for people that don't need to use that approach, I guess nobody ever got fired for picking IBM, Microsoft or even Oracle. However, in practice it's easier to work with PostgreSQL and MySQL/MariaDB, because of how easy it is to launch either, be it locally or on a server, thanks to the availability of container images. Admittedly, their automatic indexing and a few other features in the recent releases are useful. Over here in Latvia, I've seen orgs that run Oracle and are still starting new projects in it, because that's what they know best and presumably have the licensing all figured out. > In the last ten years I don't think I've seen a single org that still runs Oracle. I'm not sure which is worse, though, because gradual degradation doesn't demand fixing. But it usually degrades gradually rather than suddenly. OTOH you have to be more careful writing queries and you sometimes end up adding hints or encoding a query structure which locks in a query plan which might not be the best one long term. MySQL's query planner is more stupid, more predictable, and less likely to give you this kind of pain. It can be tedious forcing it to do the right thing, and it does sometimes switch strategy resulting in queries taking 100x or more longer than they used to, just because some stats changed. Postgres's query planning is mercurial and sometimes quixotic. The troubles each database gives you do have different flavours. Otherwise, I recommend Postgres.įor anything other than a data-oriented startup, I'd pick Postgres all day long. If you're going to scale up to 10s of millions of inserts a day and 100s of billions of rows, I recommend MySQL as a step on a journey towards Vitess. Support of complex types (arrays, JSON, specialized types like ISBN.) is also years ahead in Postgresql, and so are many advanced features. Now for the bad side of MySQL: it has so many footguns, like the various compatibility modes (silent truncation when inserting is still the default behavior, I think), or the transactions breaking when a DDL starts. Obtaining the same result with Postgres requires much more work. ![]() With MySQL, I can declare `description TEXT collate utf8mb4_general_ci`, and then `LIKE '%de toutes façons%'` will match `De toutes facons`. ![]() The last time I used a Pg DB that was queried by a web app, PgPool was installed because Pg could not handle many concurrent connections, but I've also heard this may not be required with recent Pg.Ĭlient side, in many cases, a "poor man's search" with LIKE is enough or even suitable. ![]() Replication has been easier with MySQL, though I've heard Pg has recently matured on this side. My last upgrade from Pg12 to 14 was not that easy. In my experience, the main differences are that many simple things are simpler with MySQL, but Postgres is much more rigorous and complete.įor instance, a major upgrade of a MySQL server is usually painless (Debian even upgrades automatically). But nowadays, I'm not sure MySQL is generally faster. I also believe these were the original philosophies, since MySQL emerged from practical issues and Postgresql has more academic origins.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |