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

What? I wouldn’t want this. How would you evne automate it? Creating the right indexes is the same as creating the right tables and columns in your data model: It depends on the business purpose and usage of the data.

Too much focus in the "scalability" that only matter for a very narrow niche and lateral to the DB engine, so I instead focus in real progress/improvements for RDBMS (one of my dreams is doing this):

- Algebraic data types, removal of NULLs.

- Including a relational language, not just a partial query language (SQL). (I making one at https://tablam.org, just to get the idea)

- So, is full relational (you can store tables in tables, you can model trees with table because above, etc)

- SQL is a interface for compatibility and stuff, but the above is for the rest, because:

- The engine is not a full black box but a composite of blocks so:

-- The inner and only only black box is the full ACID storage layer, that is concerned in manage PAGEs, WALs, etc made in a lang like Rust.

-- The user-facing/high-level storage layer is above this. I think this will allow to code it in the lang above because exist:

-- A pluggable language interface (making a "WASM for database/VM") that others (like SQL) compile to. And probably WASM for stored procedures and/or extend it, THEN

-- This will allow to compile "SELECT field FROM table" CLIENT-SIDE and check it! (after supplied with the schema definition), AND TOO:

- Because it not have a limited query language but one that is full, you can code a new INDEX with it. Note how do it in any language (ignoring the complexity of storage and acid, this is where a high-level interface is needed) is simple, but impossible in current RDBMS.

- Because the DB is truly, fully, relational, you can do "SELECT * FROM Index"

- Then, you can add a cargo-like package manager to shared code to the community

- Then, you can "db-pkg add basic-auth" to install stuff like auth modules that are actually used, not like the security that is included in old database for a use case not many care for

- Allow to make real-time subscriptions to data/schema changes

- Make it HTTP-native, so is already REST/GrapQL/WebSocket/etc endpoint-capable and

- Go extra-mile with the idea of Apache Avro or similar and make the description of the DB-schema integral to it, so you can compile interfaces to the db

- Store the schema changes, so it have in-built MIGRATION support (git-like?)

- Then auto-generate DOCS with something like swagger?

----

In relational to the engine itself:

- The storage is mixed row/columnar (PAX-like) to support mixed-workloads

- The engine, like sqlite, is a single library. Server-support is another exe and the package manager is what install support for operation

- The DB is stored in a single-file?

- We want to store:

-- Rows/Tables: BTrees + PAX like today, nothing out-of-ordinary

-- LOGs/Metrics: is the same as the WAL!. A rdbms already have it, but is buried: Allow to surface that, so you can do 'SELECT * FROM my_wal"

-- Vectors: Is the same as a PAX storage but one where is only 1 column

-- Trees: Is something you can do if the DB is truly relational and allow to store tables/algebraic types on it

IF the storage have a high-level interface and exist a full-featured language ("WASM-like") interace to it, you can add the optimizations to the query planner and the code that manipulate the data without demand to get into the deeps of the engine.

This mean that people that want to disable the query planner, INSTEAD NEED to improve it! IF the query planner is a component of the engine that is surfaced, and can tweak it.

i’d be more interested in an automatic index “suggester” based on observation and slow query analysis. there’s also the matter of new use cases where you’d absolutely want to be able to create them manually.

My biggest problem with databases is always versioning. IE renaming a column will break old clients. If there was a way you could have multiple schema versions so you could upgrade database then clients later it would be the best.

EDIT: yes thanks for the comments, views and creating and API layers and adding instead of subtracting do all work, but I believe they're all workarounds for the underlying problem. Fixing versioning would make everyone's lives easier.

Consider using Postgres views! You can create a view into your data and use that for reading and writing. Then, when you need to change the underlying data model, you create a new view with the renamed column. Old clients will target the old view, new clients will target the new view. Then, when all old clients are removed, you can remove the old view safely.