Ask HN: What could a modern database do that PostgreSQL and MySQL can't

I can't argue against that iff the team has this capability.

Unfortunately, I saw too many teams that end up implementing an informally-specified, bug-ridden, slow implementation of half of PostgreSQL.

> It has broad PGSQL language compatibility

Depends how you define broad. :)

Many key features are missing, including but not limited to:

    - UDFs and sprocs.
    - Useful datatypes such as TSTZRANGE
    - More limited constraints

I was recently looking at Cockroach as a PGSQL replacement because of its distributed nature. But the equivalence featureset is still lagging badly, unfortunatley.

Postgres was first to the party but MySQL supports that too since 5.6 or 5.7, I can't remember.

When building YugabyteDB, we reuse the "upper half" of PostgreSQL just like Amazon Aurora PostgreSQL and hence support most of the functionality in PG (including advanced ones like triggers, stored procedures, full suite of indexes like partial/expression/function indexes, extensions, etc).

We think a lot about this exact question. Here are some of the things YugabyteDB can do as a "modern database" that a PostgreSQL/MySQL cannot (or will struggle to):

* High availability with resilience / zero data loss on failures and upgrades. This is because of the inherent architecture, whereas with traditional leader-follower replication you could lose data and with solutions like Patroni, you can lose availability / optimal utilization of the cluster resources.

* Scaling the database. This includes scaling transactions, connections and data sets *without* complicating the app (like having to read from replicas some times and from the primary other times depending on the query). Scaling connections is also important for lambdas/functions style apps in the cloud, as they could all try to connect to the DB in a short burst.

* Replicating data across regions. Use cases like geo-partitioning, multi-region sync replication to tolerate a region failure without compromising ACID properties. Some folks think this is far fetched - its not. Examples: the recent fire on an OVH datacenter and the Texas snowstorm both caused regional outages.

* Built-in async replication. Typically, async replication of data is "external" to DBs like PG and MySQL. In YugabyteDB, since replication is a first-class feature, it is supported out of the box.

* Follower reads / reads from nearest region with programmatic bounds. So read stale data for a particular query from the local region if the data is no more than x seconds old.

* We recently enhanced the JDBC driver to be cluster aware, eliminating the need to maintain an external load balancer because each node of the cluster is "aware" of the other nodes at all times - including node failures / add / remove / etc.

* Finally, we give users control over how data is distributed across nodes - for example, do you want to preserve ASC/DESC ordering of the PKs or use a HASH based distribution of data.

There are a few others, but this should give an idea.

(Disclosure: I am the cto/co-founder of Yugabyte)

    3) Fine tuned control over query plans if I want it.

I feel like when most people say this, what they really want is a better query planner.

The optimum query plan depends on a lot of dynamically changing factors: system load, free RAM, and of course the data in the tables themselves. Any hints we give the query planner are going to help at certain times and be somewhere between "suboptimial" and "disasterous" at most other times.

It's certainly true that the query planners in major RDBMS could be better or, at least, give insight into why they made the choices they did.

It would be cool if EXPLAIN ANALYZE also perhaps showed other query plans the planner considered but discarded, and why. Imagine if the planner tried multiple plans and adjusted itself accordingly.

For Postgres in particular, I think the user-specified costs in pg.conf like `seq_page_cost` and `random_page_cost` feel like one obvious area for improvement: why am I guessing at these values? Postgres should be determining and adjusting these costs on the fly. But, I could be wrong.