Scaling with PostgreSQL without boiling the ocean | Hacker News Reader

Scaling with PostgreSQL without boiling the ocean

shayon.dev

40 pointsplaur78214 comments

How it started:

> Scaling PostgreSQL successfully doesn’t always require a full team of DBAs and experts.

How it's going:

> Monitor TOAST table sizes and access patterns via pg_stat_sys_tables

Don't get me wrong, this is a good article I wish I read three years ago, but this is 100% expert level stuff that glazes the eyes of the average CRUD dev.

You do make a good point - I think there is something to be said about making things more easier for application/product engineers such that these things are sort of "auto pilot"

hey hey! thanks for reading and posting to HN - (post author)

FYI, The "Stop Relying on IF NOT EXISTS for Concurrent Index Creation in PostgreSQL" link points to localhost.

Also, consider using a tool like this: https://pub.dev/documentation/linkcheck/latest/

oops! (:p) fixed, thanks!

I mean, these are all useful techniques for scaling Postgres, but, like... they're also a list of why scaling Postgres is hard.

FWIW some of these also apply to MySQL, like the use of FKs and major version upgrades. I think scaling any centralized and business-critical resource is hard.

Sure, any relational, ACID database is going to be hard to scale. But of the ones that people actually use a lot, Postgres is the hardest.

IDK, does MySQL have built in table partitioning yet? Pg is just different hard IME, not harder.

I assume you mean built-in sharding across multiple nodes/instances/servers? No, there's no built-in support for that in MySQL, at least when using a general-purpose storage engine like InnoDB. (There are alternative engines like MySQL Cluster / NDB, as well as Spider in MariaDB, but these are not widely used and have some major shortcomings.)

Or if you just mean partitioning across the same node ("partitioned tables"), then yes, MySQL has had that feature for over 16 years.

That all said, I agree 100% with your overall point: scaling any relational database is challenging, and I don't see any evidence that Postgres is harder than others. In my direct experience, nearly every item in the original post has some analog in MySQL that can become problematic at scale. So I'm not sure what Postgres-specific concerns GP is referring to.

Your experience seems limited.

https://dev.mysql.com/doc/refman/8.4/en/partitioning-types.h...

Why do you believe Postgres is harder than others, specifically?

As I mentioned down-thread, nearly every item in the original post has some analog in MySQL that can become problematic at scale.

Scaling any database is hard. With databases specifically built for scaling horizontally, you pay the price up front as the infrastructure is complex. Don't get fooled by "run this docker image on N servers" instructions. Even with heavily automated deployments, if you don't know the architecture and what is doing what, you will hit issues.

When I worked with nosql I always thought switching to postgres was the way to go since whatever nosql devs do essentially gets a competitive feature integrated into postgres. But when you look beyond the core, postgres seems like it lets any terrible hack fill a role..

I mean why can't I setup a few interconnected databases and send my write commands to any one of them and have them succeed or fail with normal transaction semantics obscuring unnecessary details? I think every modern attempt at a non trivial database has a better solution than Patroni.

Reply on news.ycombinator.com