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

221–230 of 326 posts

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

#221

Post by eitland »

vaughan wrote: post #213
eitland wrote: post #207

Earlier quoted context omitted.

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

Very cool - I had no idea this existed. I wonder if this could be useful for using ramdisks when working with temp tables.

Very possible.

I learned about the possibility a good decade ago when working on Oracle. For high performance use we used to have two areas:

- one small SSD-backed area where new inserts happened (Server class SSD was new and relatively expensive back then.)

- one (or more) areas for permanent storage, based on ordinary disks with good storage/price

Then we had processes that processed data from the SSD Pool and wrote them to long term storage asynchronously.

Edit: I should say I was very happy to find the same feature on Postgres, Oracle is a real hassle in more than one way.

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

#222

Post by vaughan »

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.

I feel like Rethink would have done a lot better if it were started closer to today. Funnily enough, in 2009 it was originally an SSD-optimized storage engine for MySQL.

The dev experience and API were great. It was the "relational mongodb".

Though no one feels daring enough to part from PSQL as a backend these days. With all the cloud deployment options and optimizations in PSQL, it's hard to justify writing your own db engine, but for a great real-time experience, that's what needs to happen.

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

#223

Post by vaughan »

nicoburns wrote: post #68
spiffytech wrote: post #51

Earlier quoted context omitted.

Hasura evaluated Postgres' listen/notify feature to power their subscriptions, but chose polling instead: https://github.com/hasura/graphql-engine/blob/master/archite... > Listen/Notify: Requires instrumenting all tables with triggers, events consumed by consumer (the web-server) might be dropped in case of the consumer restarting or a network disruption. It was substantially non-trivial for them to implement subscri…

The Superbase ( https://github.com/supabase/realtime ) approach is really interesting. It listens to the logical replication stream. Makes a lot of sense to me. Unfortunately our postgres instances hosted on heroku don't expose this, so I've been unable to try it out.

Hasura discusses why they chose polling here (https://github.com/hasura/graphql-engine/blob/master/archite...).

> WAL: Reliable stream, but LR slots are expensive which makes horizontal scaling hard, and are often not available on managed database vendors. Heavy write loads can pollute the WAL and will need throttling at the application layer.

Would like to hear Supabase's response to this.

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

#224

Post by musingsole »

rc_mob wrote: post #111
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…

i mean it took me just a couple hours to write script to backup postgres db to s3. not a lot of work

And it's work that gets replicated again and again

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

#225

Post by 5e92cb50239222b »

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…

> moving old records to separate cold storage

FWIW this is available in ClickHouse (which is an analytics database, though)

https://clickhouse.tech/docs/en/engines/table-engines/merget...

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

#226

Post by jasfi »

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…

Postgres has many HA solutions, and it's getting better all the time. Postgres has good performance, benchmarks need to show the various trade-offs systems make so that informed decisions can be made. Feel free to post a link.

The Graph model can be made available through extensions. See AGE: https://age.incubator.apache.org/ They plan to support OpenCypher.

The JSONB type allows for No-SQL like development if that's what you really want.

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

#227

Post by Upitor »

CharlesW wrote: post #70
Upitor wrote: post #52

Earlier quoted context omitted.

What? I wouldn’t want this. How would you evne automate it? Creating the right indexes is the same as creating the right tables and columns in your data model: It depends on the business purpose and usage of the data.

> Creating the right indexes is the same as creating the right tables and columns in your data model: It depends on the business purpose and usage of the data. Right, so the way this works is that the database collects instrumentation data and, over time, automatically applies strategies (indexing improvements being one of them) to improve its performance. https://arxiv.org/abs/2007.14244

As another user wrote, some db systems already can provide suggestions for missing indexes based on stats. But automatically creating them? Should all new indexes just add up (that would fill the db pretty fast) or replace prevoius ones (what about users depending on these indexes?)

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

#228

Post by cwp »

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…

+1 to the application/database mismatch. GemStone is really amazing. I never got to do commercial work with it, but damn is it a great environment. I've never seen anything like it.

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

#229

Post by takeda »

charlysl wrote: post #145
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 intern…

> Traditional databases don't have an encapsulation mechanism AFAIK, which is one of the reasons for impedance mismatch.

It actually does, those are views and functions.

The real problem with impedance mismatch is that SQL is declarative (you say what you want and database figures out how to get it) when most programming languages are iterative (you say what should be done).

The issue is that you have two very different languages. For one you have powerful IDE with type checking auto completion and refactoring capabilities, the SQL often is sent as a string and don't have these benefits. The various ORM are attempts to use iterative and object oriented language to access relational objects using a declarative language.

I think JetBrains is addressing the problem the right way. They added Data Grip functionality to their IDEs like PyCharm for example. What it does is that if you connect the IDE to a database and let it download the schema you get the same functionality for the data. Basically it will detect SQL statements in the string and offer the same capability for it as you have with the primary language.

At that point the impedance mismatch no longer feels like a mismatch. You basically have two languages, one to obtain data you need and another to process/present it. You can get database to return exact fields you need for your projects and even the object mapping starts feeling unnecessary.

Why data is stored in a relational way? Because that's most optimal way to store the data and the way it is stored allows multiple applications access the same data differently.

For example with NoSQL you need to know how the data will be used so you correctly plan how it will be stored. If application changes you might need to restructure the entire data.

Ultimately the data is the most important thing businesses and it stays, while applications that use it come and go.

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

#230

Post by reilly3000 »

anaganisk wrote: post #216
reilly3000 wrote: post #17

At least speaking for Postgres, it _is_ modern in that it’s very actively developed with all kinds of innovations layered on top of the core system. It can be a timeseries db, a real-time db, horizontally sharded, a self-contained REST/graphql API server, a graph db, an interface to many APIs via foreign data wrappers, and much more. In itself it has many analytics functions, and most cloud OLAP dbs use its syntax ov…

My opinion is Postgres is Jack of many trades, but master of none. I would be more afraid of a DB that tries to be everything. Every feature adds a baggage.

It is a master of plain old rbdms, but able to accomplish more with specific extensions. Sure, it probably makes more sense to use Elastic than Postgres for full-text search, but there is a solid extension for it. In a lot of operational contexts it’s easier to host another Postgres instance with a specialized extension than to try deploy an entirely new DB.
Post reply on HN