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

81–90 of 326 posts

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

#81

Post by _3u10 »

johnchristopher wrote: post #67
zz865 wrote: post #58

My biggest problem with databases is always versioning. IE renaming a column will break old clients. If there was a way you could have multiple schema versions so you could upgrade database then clients later it would be the best. EDIT: yes thanks for the comments, views and creating and API layers and adding instead of subtracting do all work, but I believe they're all workarounds for the underlying problem. Fixing…

Besides using an ORM that would hide the renaming or views, why not build an API layer ?

Because Postgres views enforce API versioning in ways that don’t break older clients. Pretty much any schema modification that would break an older client is forbidden by REPLACE VIEW. It is certainly possible to break older clients but you have to work at it.

Also… you don’t want to build an API layer because postgrest builds one for you. One that is close to on par with GraphQL (arbitrary joins / filters)

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

#82

Post by skyde »

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?

All those noSQL or newSQL have more than one leader per table they have a distinct leader for each partition.

So if you have 6 server and 2 partition you could have 3 servers for partition number #1 and 3 different servers for partition number #2.

If you want extra performance you could make the server simply store key->value mapping using quorum write like Cassandra is doing but to keep data consistency you still have 2 choice

#1 use Optimistic concurrency (an app performing an update will verify if the data has changed since the app last read that data).

#2 using some kind of Lease, elect one machine to be the only one allowed to write to that partition for some time period.

Option #1 do not give faster transaction throughput but could offer lower tail latency.

Option #2 bring you back to square one of having a leader so you better just use (Paxos/Raf)

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

#83

Post by throwawayboise »

hpeinar wrote: post #44
tdhz77 wrote: post #7

Does your data model change often? PostgreSQL and MySQL well be very difficult to make fundamental changes. If you need to make changes to your data model, the structure of relational databases will make it difficult for you to move fast. Linear relationships must be defined. Your database doesn't do much for you. You must define every relationship between tables.

As some of the commenters here have already voiced, I also struggle a bit to understand the issue with using relational databases if the data is actually relational. Which, for probably like 95% projects it is. The argument that "it will slow us down" seems so vague, I can't really see how defining the schemas and relationships slow you down. You need to know what data you are dealing with anyway even when prototypin…

> You need to know what data you are dealing with anyway

Exactly. At some point, somewhere, you need to understand your data and how different attributes or objects relate to one another. You can do this in a variety of ways, but if you don't understand this you don't understand what you are building.

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

#84

Post by dvtkrlbs »

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…

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 but having no way to maintain a Postgres compatible way of doing it (i think this one is on postgres only being able to do it with plsql scripts is cumbersome) - For me the neutral one. IT HAS ZERO DOWNTIME SCHEMA CHANGES. But if you are just coming from Postgres and just using a regular migration tool and transactions and schema changes having serious limitations and could end up your database in inconsistent state is scary. (Docs really document the behavior but still I would expect a runtime warning for it.

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

#85

Post by nextaccountic »

truenindb wrote: post #75
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…

There is absolutely no reason at all that using a Model View Controller architecture should say anything about your persistence layer. Model View Controller is an abstraction for managing code that generates things that are put on a screen. It says that you should have code that represents underlying data separate from code that represents different ways to show the data to the user and also separate from code that r…

What's being discussed is MVCC, or multiversion concurrency control

https://en.wikipedia.org/wiki/Multiversion_concurrency_contr...

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

#86

Post by [deleted] »

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.

[deleted]

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

#87

Post by rad_gruchalski »

alberth wrote: post #80
rad_gruchalski wrote: post #69

Earlier quoted context omitted.

That’s a huge oversimplification. Mnesia is distributed but that’s about it. There’s so much more to sql than selecting data. Transactions, user defined functions, stored procedures, custom types…

None of what you detail though is included in what the GP describes as a modern database. I’m not trying to nitpick but what GP describes aligns to what Mnesia provides.

The GP mentioned CockroachDB though. There’s a lot of implicit functionality behind that name. Even just behind mentioning Postgres.

The distributed part of modern sql is fairly recent.

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

#88

Post by someelephant »

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…

Make sure you thoroughly test your multi-region deploys. Last time we tried the system was not stable when a region went down. Also beware of this anti pattern: https://www.cockroachlabs.com/docs/stable/topology-patterns....

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

#89

Post by Supermancho »

dvtkrlbs wrote: post #84
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…

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.

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

#90

Post by adrianmonk »

karmajunkie wrote: post #56
iudqnolq wrote: post #55

Earlier quoted context omitted.

Business usage is observed over time instead of known up front though. In a way this is sort of like the query planner. Couldn't your usage be observed and used to determine appropriate indexes?

i’d be more interested in an automatic index “suggester” based on observation and slow query analysis. there’s also the matter of new use cases where you’d absolutely want to be able to create them manually.

It has been a million years since I've used it, but I think MS SQL Server has this.

Here's some documentation I just found (at https://docs.microsoft.com/en-us/sql/relational-databases/pe...):

> The Missing Indexes report shows potentially missing indexes that the Query Optimizer identified during query compilation. However, these recommendations should not be taken at face value. Microsoft recommends that indexes with a score greater than 100,000 should be evaluated for creation, as those have the highest anticipated improvement for user queries.

> Tip

> Always evaluate if a new index suggestion is comparable to an existing index in the same table, where the same practical results can be achieved simply by changing an existing index instead of creating a new index. For example, given a new suggested index on columns C1, C2 and C3, first evaluate if there is an existing index over columns C1 and C2. If so, then it may be preferable to simply add column C3 to the existing index (preserving the order of pre-existing columns) to avoid creating a new index.

Post reply on HN