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

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

#211

Post by vaughan » Mon, Sep 06, 2021, 3:58 AM UTC

charlysl wrote: post #145

Chyzwar wrote: post #109

1. High performance read/write of Scylla/Cassandra with high availability[1]. It has some limitations for OLTP workloads and require careful planning. Postgres without Citus is not really HA and even then companies would often invent custom sharding/cluster management system. 2. Powerful graph query language like Cypher. It might not perform well in real life, but my personal experience left me amazed[2]. There is a…

> Zero impedance mismatch between database and application representation Does this include informacion hiding/encapsulation? (to prevent saved objects' internal representation from being exposed). Traditional databases don't have an encapsulation mechanism AFAIK, which is one of the reasons for impedance mismatch. This is important because it is a good practice for client code to make no assumptions about the intern…

> good practice for client code to make no assumptions about the internal representation

If your internal representation and API start to differ then it adds complexity fast. Its far better to have as close to a 1-1 mapping for your backend and frontend data models as possible.

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

#212

Post by iaml » Mon, Sep 06, 2021, 4:06 AM UTC

vaughan wrote: post #205

1. Streaming SQL Real-time queries should be the default. Some kind of smart query invalidation (similar to incremental view maintenance) as a result of DML statements. 2. Can run in a web browser / mobile phone Your client-side cache is usually a messy denormalization of your SQL database. For offline/local-first apps you essentially need to run your entire backend API in your browser. So you should be running an SQ…

A lot of the points you mention are really interesting to me, as I've been coming to similar conclusions recently. What are good choices that solve these particularly in the context of js/clientside apps?

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

#213

Post by vaughan » Mon, Sep 06, 2021, 4:08 AM UTC

eitland wrote: post #207

zinclozenge wrote: post #22

1. Automatic backups to an S3 compatible storage, out of the box. 2. Progressive automatic scalability. As load increases or storage runs out, the DB should be able to automatically. NewSQL databases do this already. 3. Tiered storage. 4. Support streaming, stream processing, in memory data structures, etc. I feel like this is one of those weird things but I keep wishing this were possible when I work on side project…

> 3. Tiered storage. Do you mean tablespaces? https://www.postgresql.org/docs/10/manage-ag-tablespaces.htm... They'll allow you to have some parts on database on faster devices for instance.

Very cool - I had no idea this existed. I wonder if this could be useful for using ramdisks when working with temp tables.

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

#214

Post by eitland » Mon, Sep 06, 2021, 4:14 AM UTC

natmaka wrote: post #165

aserafini wrote: post #30

One thing I find really interesting from Redis that I would love in a relational database is the concept of ‘blocking’ queries, which block until they get a result. For example: https://redis.io/commands/BLPOP If I could do something like: Select id, msg From job Where id > 1224 And have that query block until there actually was a job with an id > 1224, it would open up some interesting use cases.

I would love to be able to "nice" queries, a high "nice" attribute meaning, for the engine, "work on this whenever it does not slow down anything else", especially on the I/O side (à la Linux "ionice"). A "niced" query blocks (and its working set may even be "swapped out") as long as: - the read buffer does not contain anything pertinent for it, - there are "too much" pending I/O requests ( submitting an I/O request…

Not exactly what you are asking for but worth mentioning anyway: https://wiki.postgresql.org/wiki/Priorities

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

#215

Post by vaughan » Mon, Sep 06, 2021, 4:15 AM UTC

zinclozenge wrote: post #22

1. Automatic backups to an S3 compatible storage, out of the box. 2. Progressive automatic scalability. As load increases or storage runs out, the DB should be able to automatically. NewSQL databases do this already. 3. Tiered storage. 4. Support streaming, stream processing, in memory data structures, etc. I feel like this is one of those weird things but I keep wishing this were possible when I work on side project…

> 4. Support streaming

I feel the same. Streaming is so lacking in most dbs today. RethinkDB's `.changes()` was really cool. I wonder if PSQL will eventually do it, or whether a new DB will take over. Everyone went to Mongo then ran back to PSQL, but maybe after lessons-learned there is room for a new db optimized for in-memory usage and with great first-class streaming support.

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

#216

Post by anaganisk » Mon, Sep 06, 2021, 4:15 AM UTC

reilly3000 wrote: post #17

At least speaking for Postgres, it _is_ modern in that it’s very actively developed with all kinds of innovations layered on top of the core system. It can be a timeseries db, a real-time db, horizontally sharded, a self-contained REST/graphql API server, a graph db, an interface to many APIs via foreign data wrappers, and much more. In itself it has many analytics functions, and most cloud OLAP dbs use its syntax ov…

My opinion is Postgres is Jack of many trades, but master of none. I would be more afraid of a DB that tries to be everything. Every feature adds a baggage.

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

#217

Post by mikewarot » Mon, Sep 06, 2021, 4:25 AM UTC

majewsky wrote: post #94

mikewarot wrote: post #57

Relationships between tables, as supported by Microsoft Access.. it did cascaded deletes and things automagically. Persistent queries, where any change to the answer is propagated, like a subscription to a feed.

ON DELETE CASCADE has been in SQL since approximately forever.

Ok, so this is a new thing since 2003, thanks for letting me know.

Of course, it's best to do both, to avoid orphan records

ON DELETE CASCADE ON UPDATE CASCADE

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

#218

Post by [deleted] » Mon, Sep 06, 2021, 4:27 AM UTC

vaughan wrote: post #211

charlysl wrote: post #145

Earlier quoted context omitted.

> Zero impedance mismatch between database and application representation Does this include informacion hiding/encapsulation? (to prevent saved objects' internal representation from being exposed). Traditional databases don't have an encapsulation mechanism AFAIK, which is one of the reasons for impedance mismatch. This is important because it is a good practice for client code to make no assumptions about the intern…

> good practice for client code to make no assumptions about the internal representation If your internal representation and API start to differ then it adds complexity fast. Its far better to have as close to a 1-1 mapping for your backend and frontend data models as possible.

[deleted]

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

#219

Post by 7kmph » Mon, Sep 06, 2021, 4:29 AM UTC

Make you think that there is hope for humanity after all

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

#220

Post by vaughan » Mon, Sep 06, 2021, 4:33 AM UTC

alerighi wrote: post #147

nicoburns wrote: post #35

Subscriptions. Databases like Firebase will automatically push changes to query results down to clients. You can add this to Postgres with tools like Hasura, but it's poll based and not very efficient. It's a super-useful feature for keeping UIs in sync with database state.

Why can't you do that with triggers? By the way subscriptions seems a good idea in theory, in practice polling is often the best solution. There are not a lot of applications where you need the data that real time (I mean with a latency that is less of a couple of seconds), and for that applications you should build something custom. Subscriptions to work are based on websockets that have its problems, and also requi…

> and for that applications you should build something custom.

Given the spirit of the original post, I think avoiding "build something custom" is the point.

It should be a toggle in the database.

> you don't have a 1:1 mapping between the database schema and the API (and if you do, you shouldn't, since a change of the internal database representation will require a change of the API and will break clients)

I'd argue this is a huge problem. When DB and API get out of sync it creates so much complexity, especially when working in a relational model, and things become impossible to change, or make client caching really difficult.

> The application when updates the data can publish it to whatever subscription system it wants to notify clients.

This is so much complexity though, compared with the experience of Firebase. Good for keeping more devs employed though.