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

201–210 of 326 posts

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

#201

Post by josephg »

mirekrusin wrote: post #33
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…

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?

> Like real distribution, when you add more servers writes distribute as well?

There's a really interesting suggestion in The Mythical Man Month. He suggests that instead of hiring more programmers to work in parallel, maybe we should scale teams by keeping one person writing all the code but have a whole team supporting them.

I don't know how well that works for programming, but with databases I think its a great idea. CPUs are obnoxiously fast. Its IO and memory which are slow. I could imagine making a single CPU core's job to be simply ordering all incoming writes relative to each other using strict serialization counters (in registers / L1 cache). If the stream of writes coming in (and going out) happened over DPDK or something, you could probably get performance on the order of 10m-100m counter updates per second.

Then a whole cluster of computers sit around that core. On one side you have computers feeding it with writes. (And handling the retry logic if the write was speculatively misordered.) And on the other side you have computers taking the firehose of writes, doing fan-out (kafka style), updating indexes, saving everything durably to disk, and so on.

If that would work, you would get full serializable database ordering with crazy fast speeds.

You would hit a hard limit based on the speed of the fastest CPU you can buy, but I can't think of much software on the planet which needs to handle writes at a rate faster than 100m per second. And doesn't have some natural sharding keys anyway. Facebook's analytics engine and CERN are the only two which come to mind.

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

#202

Post by dkh »

gigatexal wrote: post #189
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.

Alas RethinkDB was supposed to do this but the project imploded. It’s been OSSed I think though.

Yes, and as a happy early adopter with many projects running off it, I was very happy about this. However the pace of development since then has been absolutely glacial and seemingly without direction. It's truly a shame.

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

#203

Post by strken »

gigatexal wrote: post #188
strken wrote: post #172

Earlier quoted context omitted.

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.

I wonder if Materalize gets you close?

Materialize excites me, but probably can't be used for the current problems I'm working on, because of the operational overhead we'd be putting on our clients. Streaming SQL queries inside an existing well-understood database would be the killer feature.

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

#204

Post by hchasestevens »

strken wrote: post #172
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.

https://ksqldb.io/ is great for this, assuming you're already using Kafka.

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

#205

Post by vaughan »

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 SQL database in the browser. For this to be seamless you need your database targetting JS during design stage.

3. Syncing

To support offline/local/mobile use cases, your database needs to be able to sync changes efficiently.

4. Logical-first data schema

You should be able to give your db a logical schema instead of a physical one, and the database can take care of the necessary denormalizations for performance reasons automatically.

5. Lower-level api for query planner

Instead of having SQL as your only interface, there should be a way to interact with the query tree and the query plan directly. People are always creating new query languages hacked over SQL (like Google's Logica).

6. Graph-based queries

Support a query language like Cypher. Provide better solutions for tree-based data structures - the queries get too unwieldy today.

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

#206

Post by dkh »

tlack wrote: post #101
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…

Great list. For #4, you should take a look at Erlang or Elixir - they have "just enough" concurrency with streaming primitives and a functional style that makes it easy to use. They make a lot of "bolt on" stuff you need for a regular startup (Redis, Celery, etc) superfluous.

Could you briefly elaborate on this? Are you suggesting the right structures within Elixir/Erlang are both concurrent and safe enough to negate the need for these things at some level? (And are you referring to things like OTP, or more general than that?)

EDIT: For context, I'm familiar with the concurrency model and with how fairly bulletproof "processes" are in their context, but had never considered putting these to use in lieu of a Redis cache or certain other datastore use-cases. (My brief foray into Elixir was, however, when looking to improve reliability of a high-volume messaging system and various task queues attached to it, so that use-case I am at least aware of)

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

#207

Post by eitland »

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.

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

#208

Post by vaughan »

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

I see the problem here being that too many manual optimizations need to be done when implementing a schema.

You start with a logical schema (ERD diagram) and then implement it via a physical schema with denormalizations added for efficiency (usually because the relational model has scaling limits with number of joins, or because db migrations are too difficult, or handling unstructured data without introducing a ton of new tables). The db should do the denormalization automatically, allowing the user to interface with their logical schema directly.

Another reason is we can't use SQL in the browser - we have to go through many caching layers and API layers which complicate things.

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

#209

Post by flowerlad »

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…

> good ol’ locking-based protocols in practice outperform MVCC-based protocols

Doesn't make sense to me. Oracle supports MVCC. SQL Server doesn't scale as well as Oracle.

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

#210

Post by Glyptodon »

This is pretty stupid, but I'd be happy to see a SQL dialect where a statement can start with a FROM clause. This is also pretty stupid, but there are also some cases where I wouldn't hate some sort of "JOIN AS CHILD" feature along with results that aren't 100% tabular.
Post reply on HN