Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

news.ycombinator.com

Post reply on HN
Print view View original

171–180 of 326 posts

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

#171

Post by zmmmmm »

singron wrote: post #160
zmmmmm wrote: post #99

Earlier quoted context omitted.

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…

Absolutely ... it needs to be designed from the ground up - why I think it fits the question of something a modern database could do that Postgres would struggle with.

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

#172

Post by strken »

estebarb wrote: post #128

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

I really want to do streaming queries over updates and inserts in a normal SQL database. Imagine writing

    SUBSCRIBE SELECT customers.id, customers.name 
    FROM EVENTS(customer_purchases) AS cpe
    LEFT OUTER JOIN customers ON cpe.row.customer_id = customers.id
    WHERE cpe.row.product_id = '123abc' AND cpe.type IN ('update', 'insert')
in postgres itself and just getting every incoming purchase for a particular product.

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

#173

Post by acjohnson55 »

nicoburns wrote: post #146
pornel wrote: post #93

Earlier quoted context omitted.

Postgres has LISTEN and NOTIFY. People build DIY pub-sub with this.

Sure, but it's a very manual process. I want to be able to write an artbitrary SQL query (or subset of SQL query), and have the subscription aspect "just work".

Materialize.io does something like this, although in a separate system from Postgres, and I'm not sure it's possible to push live queries to a client.

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

#174

Post by overgard »

dheera wrote: post #161

MongoDB: Stashing unstructured JSON data that you don't really know how you might want to query later. Also, getting up and running with an investor demo ASAP with zero technical fuss, because you have a startup idea but you're broke and can't pay your next month's rent unless you either (A) finish this demo and get that investor money next week, or (B) quit working on your idea and take the Google offer. (Yes, I've…

Postgres can store (and query) unstructured JSON quite well (and I don't mean as text, I mean as a mongodb style document)

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

#175

Post by Jemaclus »

masa331 wrote: post #66

This title kind of implies that PG or MySQL aren't modern or modern enough which i think is is very wrong. Look what they bring in every update. I think they are quite modern!

Not sure if you knew this or not, but Phil Eaton (the OP) writes databases for fun[1]. This post isn't saying Postgres and MySQL aren't modern, but is more trying to get ideas for what to play around with next.

[1] https://notes.eatonphil.com/database-basics.html

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

#176

Post by OrvalWintermute »

Postgresql can do everything I need, but not everything I want, and apologies to mysql fans, but I refuse to touch it.

And, I think over the years they have been such an awesome community that has helped me irregularly when I popped into IRC every year or two for a problem I was having. https://github.com/davidfetter ; https://github.com/RhodiumToad ; https://github.com/bmomjian - you are great peeps!

What I would like to see are more security usability enhancements over and above what you find in https://www.crunchydata.com/products/hardened-postgres/

Security Enhancements List:

Attack Resistance

Better protections for the DBMS itself in assume compromised scenarios, or malicious users

Customized implementations per compliance/security regime

Accelerators for various security configurations

Self-audit, self-secure

Functionality Enhancements List:

Continuous Sync Postgres to other serious DB (not a one-way sync, not a DB move)

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

#177

Post by bawolff »

Retric wrote: post #115
jffry wrote: post #106

Earlier quoted context omitted.

I think it's unfair. "Modern" is in no way equivalent to what the thread is really asking, which is what could you do if you built a new database today with all of the knowledge but none of the existing commitments of a large mainstream database.

It’s fair because these databases made trade offs for older ideas presumably for good reasons. The question isn’t what could the have done differently up to now, but rather what new ideas might designers have included if they existed back then.

Although most of the responses have been for ideas that did exist back then, but have significant trade-offs, both then and now. They're mostly just different approaches to db, not something fundamentally "modern".

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

#178

Post by acjohnson55 »

dheera wrote: post #161

MongoDB: Stashing unstructured JSON data that you don't really know how you might want to query later. Also, getting up and running with an investor demo ASAP with zero technical fuss, because you have a startup idea but you're broke and can't pay your next month's rent unless you either (A) finish this demo and get that investor money next week, or (B) quit working on your idea and take the Google offer. (Yes, I've…

You can stash unstructured JSON data in Postgres just fine!

https://scalegrid.io/blog/using-jsonb-in-postgresql-how-to-e...

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

#179

Post by AdamProut »

why-el wrote: post #31
grobbie wrote: post #10

CockroachDB is getting a lot of interest these days. It has broad PGSQL language (and also wire I think) compatibility yet has a clustered peer architecture well suited to running in a dynamic environment like cloud or k8s. Nodes can join dynamically and it can survive them leaving dynamically as long as there's a quorum. Data is distributed across the nodes without administrator needing to make any shard rebalance t…

I don't know if anybody from CockroachDB is reading this but their article[1] on serializable transactions is somewhat questionable, as it compares CockroachDB's Serializable to Postgres's Read Committed, which seems to imply CockroachDB is better. Of course, Postgres has serializable as well. The only novelty in the article is that Cockroach DB runs Serializable by default, so I am not sure what that comparison was…

I don't think CockroachDB supports anything other then serializable isolation level, so of course they are going to be pushing this type of argument. Its an interesting philosophy really. It would be one thing if the performance penalty of running all transactions at serializable isolation level vs weaker isolation levels was reasonably low, but its not (see [1] - yes I know the entire difference in performance here is not due to CDB using serializable transactions for everything, but I'm guessing a very large part of it is). Its kind of like saying the only cars in the world should be ford pintos because we don't trust folks to safely drive faster cars.

[1] https://www.scylladb.com/2021/01/21/cockroachdb-vs-scylla-be...

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

#180

Post by vlasky »

XCSme wrote: post #45
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.

Can't you add something like this to MySQL using triggers or some similar system?

I'm the defacto maintainer of the Meteor MySQL integration and the Node.js package mysql-live-select.

These implement pub/sub and reactive queries using the MySQL binary log as the event source:

https://github.com/vlasky/meteor-mysql

https://github.com/vlasky/mysql-live-select

Post reply on HN