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

#121

Post by hamburglar » Sun, Sep 05, 2021, 11:08 PM UTC

JohnBooty wrote: post #29

thom wrote: post #8

I realise I'm straying a bit from core OLTP stuff but also I think removing the historical need for a separate OLAP database is something modern systems should address. Off the top of my head: 1) Incremental materialized view maintenance, à la Materialize (bonus points for supporting even gnarly bits of SQL like window functions). 2) Really ergonomic and scalable pub/sub of some sort, à la RethinkDB. 3) Fine tuned co…

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 mos…

> The optimum query plan depends on a lot of dynamically changing factors

Except … I’m part of a large organization with a very thorough incident post-mortem process and I’ve read a lot of analyses that end up blamed on the dreaded “query plan flip.” This ends up being an unplanned change that has an unexpected perf cost (and no real “rollback”) and causes an outage. The lesson I’ve seen learned over and over is to have very good understanding (and constant re-evaluation) of your hot queries’ perf , and to lock the query plan so it can only change when you intend it to.

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

#122

Post by [deleted] » Sun, Sep 05, 2021, 11:09 PM UTC

tmh88j wrote: post #105

kixiQu wrote: post #100

Earlier quoted context omitted.

The title seems fair to me. Anything that's actually used has certain commitments it made earlier in its lifecycle from which it now can't deviate, even if later developments made the commitments problematic.

>Anything that's actually used has certain commitments it made earlier in its lifecycle from which it now can't deviate Perhaps I'm misunderstanding your comment, but MySQL has definitely deprecated and removed features over the years. https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html

[deleted]

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

#123

Post by zamalek » Sun, Sep 05, 2021, 11:09 PM UTC

Write-expensive, read-cheap[1]: the exact opposite of the mentioned.

Once your developers have completed an iteration, your DB will see the same queries over and over again (if it doesn't, then it should be an OLAP aggregate). These databases optimize for writes, and defer complexity to reads and, considering that you could see millions more reads than writes, makes no sense whatsoever.

[1]: https://github.com/mit-pdos/noria

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

#124

Post by pas » Sun, Sep 05, 2021, 11:10 PM UTC

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…

> independence mismatch

you probably mean impedance mismatch, right?

https://en.wikipedia.org/wiki/Object%E2%80%93relational_impe...

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

#125

Post by ozim » Sun, Sep 05, 2021, 11:10 PM UTC

threeseed wrote: post #102

mamcx wrote: post #54

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 ta…

Scalability and high availability are not niche. Everyone wants this and most production environments need it.

I would argue that everyone wants this for the wrong reasons.

Then I would argue that most production environments don't need it.

Unfortunately I do not have numbers but my rough estimation is that average application is maybe 10k users. Where my mobile phone would be overkill to host such application.

My estimation is based on that most of the web-apps are not even close to Alexa top 500 - where top 50 is insane and they need scalability and availability - where I expect amount of people visiting sites is governed by "power law" so those top 50 get 80% of traffic and the rest gets 20%. Don't even start on all those intranet applications that are having maybe 100 to 500 users at all and I would expect there are much more of that kind of applications in the world than there is google search engines :)

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

#126

Post by bigblind » Sun, Sep 05, 2021, 11:20 PM UTC

It's always been insane to me that databases aren't better at migrations. There are so many blog posts out there on how to do zero downtime migrations, and I feel like a lot more of that could probably be abstracted away.

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

#127

Post by jasonhansel » Sun, Sep 05, 2021, 11:28 PM UTC

lifepillar wrote: post #47

One thing PostgreSQL would likely not be able to adapt to, at least without significant effort, is dropping MVCC in favor of more traditional locking protocols. While MVCC is fashionable nowadays, and more or less every platform offers it at least as an option, my experience, and also opinions I have heard from people using SQL Server and similar platforms professionally, is that for true OLTP at least, good ol’ lock…

Personally I wish Postgres would add support for optimistic concurrency control (for both row-level and "predicate" locks), which can be a big win for workloads with high throughput and low contention.

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

#128

Post by estebarb » Sun, Sep 05, 2021, 11:31 PM UTC

Materialize (https://materialize.com/) is capable of performing SQL operations over a stream, using incremental calculation based on differential dataflow.

CockroachDB is a distributed SQL database, with strong consistency. I think that Yugabyte is similar.

Support for realtime changes, including queries over those changes, is better in other databases, like RethinkDB, OVSDB or Firebase.

Relational is not always the best way to store your data. Sometimes a graph model is better. Or for full text search something specialized is better. In that sense dgraph or elastic search may be better OLTP databases in some cases.

Columnar storage, like used in Vertical or BigQuery have several advantages in processing and data compression. But implementing it in PG or MySQL I think that would require almost a full rewrite.

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

#129

Post by atombender » Sun, Sep 05, 2021, 11:31 PM UTC

lifepillar wrote: post #47

One thing PostgreSQL would likely not be able to adapt to, at least without significant effort, is dropping MVCC in favor of more traditional locking protocols. While MVCC is fashionable nowadays, and more or less every platform offers it at least as an option, my experience, and also opinions I have heard from people using SQL Server and similar platforms professionally, is that for true OLTP at least, good ol’ lock…

This is already being developed. zheap [1] is based on the undo/redo log system that databases such as Oracle use, and will be one of the options once Postgres supports pluggable storage backends.

[1] https://www.cybertec-postgresql.com/en/postgresql-zheap-curr...

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

#130

Post by jasonhansel » Sun, Sep 05, 2021, 11:33 PM UTC

Better consistency and transaction isolation models. The ANSI transaction isolation levels are ambiguous and confusing, and the different consistency models supported by NoSQL databases are often poorly-specified. A database with clearer abstractions around consistency and isolation would be a big win for applications where correctness and scalability are both high priorities.