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

Yes! I would see this tied in with temporal support throughout the database, so you can query the schema and data as it was at 2021-09-03 11:53:28.

This avoids some big problems with temporal data but I can't see it ever being efficient to do.

Two things stick out here that I don't fully understand.

Declarative schemas, and a well-defined update process (that isn't human-written DDL), are essential at any sort of organizational scale.

Isn't this impossible because some schema changes require data migration? A data migration cannot be declaratively automated as far as I know.

Queue support

Why not use a dedicated and feature rich queue such as Rabbit MQ or, if you want to get really fancy, Kafka?

For example with NoSQL you need to know how the data will be used so you correctly plan how it will be stored. If application changes you might need to restructure the entire data.

Honestly, SQL has this problem too, but it presents itself not in the way you store, but in the way you query. There are simple schemas and complex ones, and irrespective of that there are obvious query sets and unplanned ones (i.e. written at runtime as part of the data analysis process). SQL and its autoplanning is required only for complex+unplanned cases, in my opinion. In all other cases I know my data and I’d better walk through the indexes myself rather than writing 4-story queries to satisfy the planner. At the end of the day, nested loops through the indexes is what RDBMS does. There is no declarative magic at the fetch-and-iterate level.

Iow, it would be nice to have “extql” a direct access to indexes and rows, in sort of a way EXPLAIN works, and skip SQL completely.

  function get_items(store_id) {
    for (var item in items.id) {
      var res = item.{name, code}
      var item_id = i.id
      res.price = prices.any({item_id, store_id})?.price
      if (!res.price) continue
      res.props = todict(props.all({item_id}).{name, value})
      yield res // or collect for a bigger picture
    }
  }

This query could be an equivalent of “select from items inner join prices on (store_id, item_id) left join props on (item_id)” but saving space for many props and being much more programmable. Also, it would be nice to have the same engine (sql+extql) at the “client” side, where the inverse problem exists – all your data is nosql, no chance to walk indexes or declare relations.

> 1. Horizontal scaling. PG can do sharding or replication, but that's not the same thing. Manual resharding is something that, once you've experienced it, you don't want to do it ever again, especially when things like Spanner/Cockroach exist. Database-level liquid sharding is such a dramatically superior solution that it makes any system that depends on an enumerated set of master instances seem completely obsolete.

It feels like the industry has largely failed at this in regards to the traditional RDBMSes. Solutions like TiDB attempt to remedy this in a transparent way, but there are simply too many limitations for it to be a feasible alternative for many projects out there: https://docs.pingcap.com/tidb/stable/mysql-compatibility#uns...

I've only ever seen PostgreSQL/MySQL/MariaDB/Oracle be deployed successfully as single instances in prod that are scaled vertically, because attempts to do otherwise either create issues with performance and resiliency, or make the entire system only have eventual consistency, which is as problematic as it is inevitable when it comes to horizontally scaled and distributed systems.

I don't think that there is a good answer to this, honestly. You will get either horizontal scalability OR data consistency - pick one.

> 3. Interface abstraction. Databases that ship language-support drivers should also ship a fake in-memory implementation for testing purposes that supports the same interfaces and options. There's no reason why I should have to start a subprocess and emulate a network connection to tell whether a query functions properly or not, or whether I've set isolation properties correctly for a pipeline.

That's a good idea, but personally i've seen that almost all abstractions are leaky in one way or another. In the day of containers and highly customizable software that can scale from tens or hundreds of megabytes of memory with a fraction of a CPU core to an entire server with a hundred gigabytes of memory and dozens of CPU cores, it feels somewhat unnecessary to risk these abstractions.

Just make sure that your process for initializing the DB schema and seeding it with data for testing is fully automated and reproducible, and then spin up a disposable database container as a part of your CI process. Either that, or just don't test the actual DB implementation, which i've also seen be done to pretty horrendous results.

Edit: Perhaps my past experience makes me have certain biases - i've also seen manually set up DB instances which eventually leads to there being no good way to test N parallel feature branches, each of which needs a different schema, because that also necessitates N DB instances to be present, half or more of which could in theory break. I don't see any good options apart from containers here, especially when you need extensions which these in-memory implementations wouldn't play nicely with.

>Isn't this impossible because some schema changes require data migration?

In the most general case, sure - although there are workarounds for some specific cases (e.g., including previously-known-as names in the declarative schema to allow automatically planning renames). But 99% of the time, you're adding and removing tables and columns in a way that's very well defined. This is one of those areas where the best solution is to legislate away the hard problems - a tool that covers 98% of schema changes automatically (the provably safe ones), and then fails/requires a human to approve the last 2% is still dramatically better than having humans manually write and sequence change plans all the time.

Data migrations will require human effort, but you can sequence the changes and isolate the parts that need different kinds of work. If you're changing a string to an integer, for instance, you can make it clear in your change history that you (1) add the new column, (2) start dual writes, (3) backfill, (4) move readers to the new column, (5) stop the dual write, (6) drop the old column. You can do that with checked-in migration code, but think about what you end up with at the end - with imperative migrations, you have the clutter from all of that history; with declarative schema definitions, you just have the final state, just like how it works for code.

Declarative schemas also usually come with nice ancillary properties - for instance, they can give you automatic rollbacks, because you can pack up the whole schema definition as a static artifact.

>Why not use a dedicated and feature rich queue such as Rabbit MQ or, if you want to get really fancy, Kafka?

Atomicity. It's really, really powerful to be able to write a row and send or receive a message as a single exactly-once transaction. Imagine a queue of events. The writer writes the event data, and sends a message as a notification that it exists; the consumer will keep some sort of aggregate (count, filtered total, etc.). With a separate queueing system, you have to explicitly manage the timing and error cases bridging different atomicity domains, usually reimplementing some sort of idempotency log on your own. If it's all in one place, you just write a transaction that reads the old value, consumes the queue message, and writes a new value.