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

261–270 of 326 posts

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

#261

Post by yarcob »

GeneralMayhem wrote: post #244

Lots of people are citing cutting-edge bells and whistles for advanced query plans and data types, but after working at $BIGCO and seeing what exists there (and has existed for years), I get frustrated by the lack of ergonomic and operational essentials in the public-cloud options. 1. Horizontal scaling. PG can do sharding or replication, but that's not the same thing. Manual resharding is something that, once you've…

> Horizontal scaling

I think the importance of horizontal scaling is overhyped. 99% of PostgreSQL applications are at a size where a single machine can easily handle the workload.

To enable horizontal scaling, you need to make so many tradeoffs that I don't think it's worth it for most applications.

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

#262

Post by saulrh »

papaf wrote: post #254
GeneralMayhem wrote: post #244

Lots of people are citing cutting-edge bells and whistles for advanced query plans and data types, but after working at $BIGCO and seeing what exists there (and has existed for years), I get frustrated by the lack of ergonomic and operational essentials in the public-cloud options. 1. Horizontal scaling. PG can do sharding or replication, but that's not the same thing. Manual resharding is something that, once you've…

Two things stick out here that I don't fully understand. Declarative schemas, and a well-defined update process (that isn't human-written DDL), are essential at any sort of organizational scale. Isn't this impossible because some schema changes require data migration? A data migration cannot be declaratively automated as far as I know. Queue support Why not use a dedicated and feature rich queue such as Rabbit MQ or,…

> Why not use a dedicated and feature rich queue such > as Rabbit MQ or, if you want to get really fancy, Kafka?

If your queue is in the same database as your data, you can do "ack message/mark job as complete" and "commit result of job to database" in the same transaction. That simplifies a lot of the harder parts of things like materialization pipelines or BI systems.

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

#263

Post by lazy_kangaroo »

yarcob wrote: post #261
GeneralMayhem wrote: post #244

Lots of people are citing cutting-edge bells and whistles for advanced query plans and data types, but after working at $BIGCO and seeing what exists there (and has existed for years), I get frustrated by the lack of ergonomic and operational essentials in the public-cloud options. 1. Horizontal scaling. PG can do sharding or replication, but that's not the same thing. Manual resharding is something that, once you've…

> Horizontal scaling I think the importance of horizontal scaling is overhyped. 99% of PostgreSQL applications are at a size where a single machine can easily handle the workload. To enable horizontal scaling, you need to make so many tradeoffs that I don't think it's worth it for most applications.

where did you get the 99% metric ? Most of the companies even with a single saas product have insane amount of data these days.

Not just application data, There is also a whole lot of analytical data collected at every step of the product usage cycle.

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

#264

Post by GeneralMayhem »

KronisLV wrote: post #258
GeneralMayhem wrote: post #244

Lots of people are citing cutting-edge bells and whistles for advanced query plans and data types, but after working at $BIGCO and seeing what exists there (and has existed for years), I get frustrated by the lack of ergonomic and operational essentials in the public-cloud options. 1. Horizontal scaling. PG can do sharding or replication, but that's not the same thing. Manual resharding is something that, once you've…

> 1. Horizontal scaling. PG can do sharding or replication, but that's not the same thing. Manual resharding is something that, once you've experienced it, you don't want to do it ever again, especially when things like Spanner/Cockroach exist. Database-level liquid sharding is such a dramatically superior solution that it makes any system that depends on an enumerated set of master instances seem completely obsolete…

It depends on what you're testing. Performance properties and version updates, sure, it's hard to find a substitute for containers. But that's not 99% of testing. If the database has a stable interface (for instance, its dialect of SQL+DDL), it can ship a fake implementation of that interface to use in unit tests or toy examples.

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

#265

Post by jpgvm »

lazy_kangaroo wrote: post #263
yarcob wrote: post #261

Earlier quoted context omitted.

> Horizontal scaling I think the importance of horizontal scaling is overhyped. 99% of PostgreSQL applications are at a size where a single machine can easily handle the workload. To enable horizontal scaling, you need to make so many tradeoffs that I don't think it's worth it for most applications.

where did you get the 99% metric ? Most of the companies even with a single saas product have insane amount of data these days. Not just application data, There is also a whole lot of analytical data collected at every step of the product usage cycle.

Generally you don't have your main application database also serve as your data warehouse for lots of reasons.

Generally you offload that to purpose built systems that favor those aforementioned tradeoffs or onto services that run them for you, i.e BigQuery, Snowflake, etc.

Your main application database is unlikely to need sharding unless you really do have a phenomenal amount of customers or you need regional sharding to meet legal requirements about data sovereignty for example.

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

#266

Post by KingOfCoders »

lazy_kangaroo wrote: post #263
yarcob wrote: post #261

Earlier quoted context omitted.

> Horizontal scaling I think the importance of horizontal scaling is overhyped. 99% of PostgreSQL applications are at a size where a single machine can easily handle the workload. To enable horizontal scaling, you need to make so many tradeoffs that I don't think it's worth it for most applications.

where did you get the 99% metric ? Most of the companies even with a single saas product have insane amount of data these days. Not just application data, There is also a whole lot of analytical data collected at every step of the product usage cycle.

From managing databases over the last two decades (starting with Ingres/mSQL), hardware has growing much faster (RAM/CPU/NVMe) than the data needs. I remember the first time we put 2Tb RAM into a machine to handle most analytics in memory.

From my experience TimescaleDB is fast and takes a lot of data in. If you're multi tenant, it's usually easy to shard.

And we do dataware housing on BigQuery, no need to have your own machine and manage the database.

Of course there are people who need unlimited horizontal scaling.

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

#268

Post by GeneralMayhem »

mirekrusin wrote: post #36
riku_iki wrote: post #34

Earlier quoted context omitted.

data is also sharded in those databases

true, thanks for pointing out, but this is a bit cheating isn't it? ie. atomic transactions cross shards flip over, right? it's basically ergonomic equivalent of just using multiple databases?

Cross-shard transactions will use some sort of two-phase commit protocol internally. They still work just fine (the extra complexity is transparent to the client), but with somewhat worse performance. Most of the difficulty/expertise involved in using such systems optimally is in designing your schema and access patterns such that you minimize the number of cross-shard transactions.

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

#269

Post by KronisLV »

GeneralMayhem wrote: post #264
KronisLV wrote: post #258

Earlier quoted context omitted.

> 1. Horizontal scaling. PG can do sharding or replication, but that's not the same thing. Manual resharding is something that, once you've experienced it, you don't want to do it ever again, especially when things like Spanner/Cockroach exist. Database-level liquid sharding is such a dramatically superior solution that it makes any system that depends on an enumerated set of master instances seem completely obsolete…

It depends on what you're testing. Performance properties and version updates, sure, it's hard to find a substitute for containers. But that's not 99% of testing. If the database has a stable interface (for instance, its dialect of SQL+DDL), it can ship a fake implementation of that interface to use in unit tests or toy examples.

> If the database has a stable interface (for instance, its dialect of SQL+DDL), it can ship a fake implementation of that interface to use in unit tests or toy examples.

Sometimes the interface just has too large of a surface area for it to be simulated properly.

For example, suppose that you have an Oracle stored procedure, which, when given about 20 parameters, goes through a decision tree in a PL/SQL block that spans a few thousands of lines and along the way uses both DBMS_OUTPUT.PUT_LINE, as well as DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS, any number of methods from DBMS_PREDICTIVE_ANALYTICS as well as any number of other packages that are included in the full release: https://docs.oracle.com/en/database/oracle/oracle-database/1... which may also include logic that makes network requests, sends e-mails or even triggers other pieces of code to be executed.

All of that would be almost impossible to fake well enough because of the scope of functionality that's used, especially if methods are not only called, but their output is also checked and the execution depends on this. Should systems be simpler and do less? Probably, but that's not the objective reality in many projects out there.

That said, i agree that it's a good thing to have if you treat your DB as a data store and not much more, which in my opinion is a pretty good and stress free way to go about things overall. But somehow that's also like saying "Just use an ORM to have your system be DB agnostic!" which never actually works out in practice because of implementation details that leak through.

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

#270

Post by rishav_sharan »

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.

Have you tried Postgres Notify? Its the native event subscription.

https://www.postgresql.org/docs/current/sql-notify.html

Post reply on HN