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

#151

Post by CydeWeys » Mon, Sep 06, 2021, 12:35 AM UTC

Aeolun wrote: post #143

vvern wrote: post #138

Earlier quoted context omitted.

CockroachDB dev here. We've gotten a good bit better in the last few versions in terms of schema change stability. We're still not very good at large schemas with more than 10s of thousands of tables but we've got projects under way to fix that which I expect will be in the release in the spring of 22. I'd like to hear more about the magic.

To be fair, while I kind of agree the system should be able to handle it regardless, 10,000s of tables sounds outside the realm of 99.99% of all use cases.

You might be surprised to learn how common the "store the results of the user's query into a temporary table" pattern is.

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

#152

Post by jstgord » Mon, Sep 06, 2021, 12:40 AM UTC

stored procedures/functions in javascript, out of the box ?

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

#153

Post by psanford » Mon, Sep 06, 2021, 12:42 AM UTC

pornel wrote: post #95

I'm missing something as easy to deploy as SQLite, but without its performance problems (like the stop-the-world write lock). Basically, I'd love an embeddable Postgres with a single-file storage.

If you are using SQLite and care about performance, be sure to run in WAL (Write Ahead Log) Mode: `PRAGMA journal_mode=WAL;`[0]. WAL mode is significantly faster and does not have a "stop-the-world write lock" ("Reading and writing can proceed concurrently"). WAL mode is also more efficient with disk I/O, which makes it faster for single user applications as well.

[0]: https://sqlite.org/wal.html

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

#154

Post by bbertelsen » Mon, Sep 06, 2021, 12:48 AM UTC

One really cool thing in the analytics space is the adoption of columnar data stores. PostgreSQL and MySQL don't do this. (If they can please correct me!). This is for embedded loads, situations where you might use SQLite, there are now really interesting alternatives like MonetDB and DuckDB. Paper: https://hannes.muehleisen.org/SIGMOD2019-demo-duckdb.pdf

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

#155

Post by xupybd » Mon, Sep 06, 2021, 12:48 AM UTC

This is a hard question to answer.

You can do most anything with the wrong tool. I'd rather ask the question, when is PostgreSQL or MySQL the wrong tool for the job. I'm not sure I'm qualified to answer this, but I can point you in the direction of a book that has given me a much better understanding of the space. https://www.oreilly.com/library/view/designing-data-intensiv...

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

#156

Post by hestefisk » Mon, Sep 06, 2021, 12:49 AM UTC

jake_morrison wrote: post #77

VoltDB is a good example of rethinking relational databases for for modern technology. Traditional databases assume that everything is stored on disk, with a bit of RAM available for cacheing. VoltDB assumes that everything is stored in RAM first, using checkpointing to disk and replication to get durability. https://www.usenix.org/legacy/events/lisa11/tech/slides/ston... Have a look at Michael Stonebraker, he's a da…

Volt is indeed very cool. Michael Stonebraker wrote part of the original Ingres code, didn’t he?

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

#157

Post by natmaka » Mon, Sep 06, 2021, 12:51 AM 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…

Does "more traditional locking" mean that the clients request locks? Isn't it already offered by PG with table-level and row-level locks, and also in an extremely flexible way (letting the client developer define the semantics) by pg_advisory_lock?

https://www.postgresql.org/docs/current/explicit-locking.htm...

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

#158

Post by sterlinm » Mon, Sep 06, 2021, 12:52 AM UTC

pornel wrote: post #95

I'm missing something as easy to deploy as SQLite, but without its performance problems (like the stop-the-world write lock). Basically, I'd love an embeddable Postgres with a single-file storage.

Check out DuckDB https://duckdb.org/

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

#159

Post by sterlinm » Mon, Sep 06, 2021, 12:55 AM UTC

sam_lowry_ wrote: post #53

refset wrote: post #32

Earlier quoted context omitted.

> Automatic versioning of data More specifically, I would argue for the ability to run arbitrarily complex, non-locking, fully-consistent queries against all historic versions of the database, a.k.a. "the database as a value" (also "transaction time" or "system time" temporal queries)

Isn't this part of the SQL standard already, to some extent?

Temporal queries have been part of the SQL standard for a while but are mostly not supported by the major databases. Some databases have partial support or extensions that add some temporal capabilities.

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

#160

Post by singron » Mon, Sep 06, 2021, 12:56 AM UTC

zmmmmm wrote: post #99

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…

I sort of want the opposite. Except for extremely high velocity mutable data, why do we ever drop an old version of any record? I want the whole database to look more like git commits - completely immutable, versionable, every change attributable to a specific commit, connection, client, user. So much complexity and stress and work at the moment comes from the fear of data loss or corruption. Schema updates, migratio…

If the old versions of records stay where they are, they will start to dominate heap pages and lead to a kind of heap fragmentation. If the records are still indexed, then they will create an enormous index bloat. Both of these will make caches less effective and either require more RAM or IOPS, both of which are scarce in a relational db.

You probably need a drastically different strategy, like moving old records to separate cold storage instead (assuming you might ocassionally want to query it. Otherwise you can just retain your WAL files forever).