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

141–150 of 326 posts

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

#141

Post by zozbot234 »

FpUser wrote: post #108
mirekrusin wrote: post #33

Earlier quoted context omitted.

It always rubs me the wrong way - all those paxos/raft approaches (which are great, but...) simply elect the leader to pick writes. In that sense there is no distribution of computation at all. It's still single target that has to cruch through updates. Replication is just for reads. Are we going to have something better anytime soon? Like real distribution, when you add more servers writes distribute as well?

You are asking for a miracle or just simply - breach of the physics laws. The only way to horizontally scale write speed is to shard your data to be written somehow. You can easily do it but then your reading queries have to go to multiple servers to assemble single result. You can't scale both at the same time. There are some in between solutions like eventual read consistency that are relying on traffic at some poi…

> There are some in between solutions like eventual read consistency that are relying on traffic at some point easing enough so that the conductor can actually synchronize servers.

You can use CRDT's to give a formally correct semantics to these "inconsistent" scenarios. And they might well be something that's best explored in a not-purely-relational model, since the way they work is pretty unique and hard to square with ordinary relational db's.

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

#142

Post by jsperson »

I’m surprised no one has mentioned Snowflake. The biggest feature here is no worries. It scales in every dimension, is quite stable, and has few knobs to turn. I’m on a Redshift project now, and the differences are significant. It’s not hard to overload the cluster with a complex query. With Snowflake I would just create my own cluster with the same data shared on the back end. While it’s possible to do something similar with redshift, in practice the configuration and DBA culture make it much more difficult.

Edit - reread the question and it mentions OLTP workloads. Snowflake and Redshift are specialized analytics DBs so they don’t meet that criteria.

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

#143

Post by Aeolun »

vvern wrote: post #138
Supermancho wrote: post #89

Earlier quoted context omitted.

CDB was great until we started doing table creation on the minute and hundreds of inserts on those tables. When we tried to drop tables on a schedule, CDB never could catch up and would generally just crash (3 nodes). I really don't like the magic you have to do with CDB for things that your commodity DBs can be expected do.

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.

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

#144

Post by zozbot234 »

topspin wrote: post #120
DanHulton wrote: post #60

Earlier quoted context omitted.

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.

"Consider using Postgres views!" The minute you create a view modifying the underlying table usually gets blocked, in many cases even for changes that have zero relevance to the view. You have to drop the view(s) to get the ALTER TABLE to go through. Absolute nightmare. Oracle, for example, does a far better job of handing this type of evolution and dependency management.

> You have to drop the view(s) to get the ALTER TABLE to go through.

So? With Postgres you can use transactions in your DDL. So it's possible to seamlessly drop the view, alter the underlying table and recreate it, all in one step once the transaction is commited.

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

#145

Post by charlysl »

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 internal representation, accessing data only via the a public interface.

If it happens to be exposed by the database, the clients can use it in their queries. If the internal representation changed later on, such clients would be broken.

Of course, this can be solved by only allowing data access via, say, well designed restful apis (that don't expose internal details), but this would still provide no guarantees.

How about another reason for impedance mismatch, that of storing objects that belong to a class hierarchy?

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

#146

Post by nicoburns »

pornel wrote: post #93
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.

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".

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

#147

Post by alerighi »

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 requires a constant connection to the server. The application then needs to handle the incoming data properly, another thing that is not entirely obvious.

Another thing is that most of the times 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). You have in practice an application server in between. Well is that application server that can send subscriptions to clients. And it can do that without subscribing to updates on database tables (assuming that the tables are only changed by the application itself, that seems reasonable). The application when updates the data can publish it to whatever subscription system it wants to notify clients.

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

#148

Post by psanford »

laurent92 wrote: post #78
gkoberger wrote: post #73

Oh I've been thinking a lot about this! It might not be the answer you're looking for since they're all very UX-related and not particularly database-specific, however I have a few ideas. Sometimes I feel like databases were created by people who never built a website before. Most websites are pretty similar, and databases historically have never felt (to me at least) "modern". I always feel like I'm fighting against…

What I would like best about a database would be to be in a file, in the data directory of the website. Snapshot the filesystem, snapshot the db. SSH the filesystem and the db exists elsewhere. I wouldn’t have to deal with the db at all, since it would be part of the website files. Most Wordpress sites would fit in SQLite easily. Using Wordpress would be like opening a Word document. Postgresql can’t do that, since t…

There's a bunch of projects that have implemented this. I wrote a SQLite VFS in Go that lets you query a read-only SQLite db over http (including from s3) [0].

The VFS API offers the possibility for weirder storage solutions, if thats the type of thing you're into. Recently I've been moving some of my personal websites hosted on AWS Lambda over to use a read/write sqlite db backed by DynamoDB[1]. There are a bunch of limitations to this type of thing (like it uses a global write lock), but it works nicely for DBs that have low write frequency.

[0]: https://github.com/psanford/sqlite3vfshttp

[1]: https://github.com/psanford/donutdb

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

#149

Post by brainbag »

Some thing I've encountered in practically every project in the last 10 years is a large number of calculated attributes that have to be in code or in triggers. I know MySQL and PostgreSQL both have generated/virtual columns but they are in-table only, and this can be accomplished with triggers, but I want to be able to simply define my table schemas based on schemas from other tables, and have the database system handle it automatically.

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

#150

Post by np_tedious »

Supermancho wrote: post #89
dvtkrlbs wrote: post #84

Earlier quoted context omitted.

Not a database expert but here is my thoughts after using Cockroach DB for a mid sized: - Single cluster mode is really nice for local deployment - WebUI is a cool idea but I wish it had more info - The biggest problem is Postgres compatibility there are some quirks that was annoying examples being. The default integer types having different sizes, cockroachdb having a really nice if not exists clause for create type…

CDB was great until we started doing table creation on the minute and hundreds of inserts on those tables. When we tried to drop tables on a schedule, CDB never could catch up and would generally just crash (3 nodes). I really don't like the magic you have to do with CDB for things that your commodity DBs can be expected do.

Can you tell me more about the use case where you'd create a new table every minute?
Post reply on HN