Showdown: MySQL 8 vs. PostgreSQL 10
Great to see MySQL adding this stuff!  There&#x27;s still a ton of reasons to choose Postgres and more and more silicon valley startups seem to be choosing pg - I don&#x27;t remember the last time I met a startup choosing MySQL.<p>DBMSs are giant complex pieces of software with a million features - it&#x27;s really hard to compare them.  But if I had to sum it up, you can dump freaking line noise into Postgres and then hide the nastiness and manage and query it like a well-designed database.  If you have a pretty database, good for you, but today&#x27;s app writers have gotten lazy with NoSQL record stores and their databases resemble vomitoriums - and let&#x27;s not talk about what people used to do in the 80s and early 90s.<p>Without further ado, Postgres vomitorium cleanup features:<p>- user defined functions&#x2F;aggegrates&#x2F;windowfuncs <i></i>in your favorite language<i></i> incl JavaScript, which means you can write tricky business logic once and run it where the data is, vs pulling out millions of records from the database.  Language list: <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;10&#x2F;static&#x2F;external-pl.html" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;10&#x2F;static&#x2F;external-pl.html</a><p>- foreign data wrappers with hundreds of connectors AND a 5 minute toolkit for authoring new wrappers in python and other scripting languages.
<a href="https:&#x2F;&#x2F;wiki.postgresql.org&#x2F;wiki&#x2F;Foreign_data_wrappers" rel="nofollow">https:&#x2F;&#x2F;wiki.postgresql.org&#x2F;wiki&#x2F;Foreign_data_wrappers</a>
<a href="http:&#x2F;&#x2F;multicorn.org&#x2F;" rel="nofollow">http:&#x2F;&#x2F;multicorn.org&#x2F;</a><p>- index goddamned anything.  Postgres has the most array of index types of any open source database AND if you need, you can easily write a function (in javascript or python!!!) and create an index that&#x27;s the result of that function call.  Postgres even has a full range of partial indices and block range indices, which make it practical to index massive and sparse datasets. <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;current&#x2F;static&#x2F;indexes-partial.html" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;current&#x2F;static&#x2F;indexes-parti...</a>
<a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;current&#x2F;static&#x2F;indexes-expressional.html" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;current&#x2F;static&#x2F;indexes-expre...</a>
<a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;current&#x2F;static&#x2F;brin-intro.html" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;current&#x2F;static&#x2F;brin-intro.ht...</a><p>EXAMPLE: I was once handed a MySQL database of IoT signals where timestamps were in seconds since the epoch and asked to report on this data without changing the database. No biggie: 15 minutes to replicate this OLTP database to a read-only Postgres replica (dumb schema mapping) - then a Postgres VIEW to hide this nastiness and a function index on the timestamp column (to_timestamp).<p>(from memory)
CREATE VIEW foo AS SELECT *, to_timestamp(mysql_ts_col) as ts_timestamp FROM replicated_mysql_table;
CREATE INDEX foo_ts_inx ON replicated_mysql_table(to_timestamp(mysql_ts_col));<p>At another company, we got dumped a load of JSON and weren&#x27;t sure how we&#x27;d need to parse it.  No biggie, I just created indices using function calls that parsed the JSON.<p>- tons of native datatypes and extensible datatypes. In cases where you&#x27;re handed complex structures or &quot;weird&quot; data that doesn&#x27;t behave like most programmers expect, you can define new datatypes, then create a library of user defined functions around them.  <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;9.5&#x2F;static&#x2F;xtypes.html" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;9.5&#x2F;static&#x2F;xtypes.html</a><p>- sampling.  Postgres has native, low-level support for queries that sample the data, which makes it super fast to explore data while preserving some semblance of statistics.  <a href="https:&#x2F;&#x2F;blog.2ndquadrant.com&#x2F;tablesample-in-postgresql-9-5-2&#x2F;" rel="nofollow">https:&#x2F;&#x2F;blog.2ndquadrant.com&#x2F;tablesample-in-postgresql-9-5-2...</a><p>- EXPLAIN.  The Postgres planner&#x2F;optimizer is still the king at explaining why your query is taking forever and what you can do about it.  Admittedly, this stuff quickly gets arcane, but you can post your EXPLAIN output to a forum and guys like me will tell you how to override the JOIN order, update statistics, etc.  <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;current&#x2F;static&#x2F;using-explain.html" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;current&#x2F;static&#x2F;using-explain...</a><p>Finally, no conversation about Postgres vs &lt;x&gt; is complete without mentioning that ALMOST EVERY FEATURE IN POSTGRES WORKS WITH EVERY OTHER FEATURE, which means you don&#x27;t waste hours investing in something and then &quot;oops&quot; your carefully written user defined function can&#x27;t be invoked in some obscure place - with Postgres, you can assume everything just works and will keep working.  There&#x27;s few pieces of software that can claim this.