Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Postgres scaling advice

cybertec-postgresql.com

Post reply on HN
Print view View original

71–80 of 207 posts

Re: Postgres scaling advice

#71

Post by samf »

isoprophlex wrote: post #63

Pretty solid advice, nice article. One thing: > For example, on my (pretty average) workstation, I can do ca. 25k simple read transactions per 1 CPU core on an “in memory” pgbench dataset…with the default config for Postgres v13! Forget about reaching those numbers on managed DB-as-a-service instances, specifically Azure managed postgres. In my experience these have comparatively poor peak performance, with high vari…

Good info; I came here looking for experience with managed databases. Does anyone have experience with managed instances on other platforms?

Re: Postgres scaling advice

#72

Post by berns »

cuu508 wrote: post #16
kevincox wrote: post #8

I wonder when using a distributed database (like CockroachDB) will be the default for new applications. Right now it seems that they are less feature and harder to set up than traditional RDBMSes but I can only assume that this gap will narrow and at some point in the future things will be "scalable by default". (Of course no DB is going to prevent all ways to shoot yourself in the foot)

To me it looks like there are not many affordable options right now. CockroachDB understandably wants you to use their Cloud or Enterprise products: the OSS version is quite limited. For example it doesn't support row-level partitioning ( https://www.cockroachlabs.com/docs/stable/configure-replicat... ). Which means, if I understand correctly, it is not of much help for scaling writes to a single big table.

No, you didn't understand correctly. The feature that isn't supported is row level replication zones. Replication zones allows to define the location of replicas.

Re: Postgres scaling advice

#73

Post by mumblemumble »

spaetzleesser wrote: post #59
mumblemumble wrote: post #31

Earlier quoted context omitted.

It's resumé-driven development, and it's also entertainment-driven development. Bringing in new technologies gives you a chance to play with a new toy. That's an effective way to make your job more interesting when the thing you're supposed to be working on is boring. Which, in business applications, is more often than not the case.

In today’s job market resume driven development is a very rational choice. I work in medical devices so we are pretty conservative and generally way behind the cutting edge. This makes it really hard to find jobs at non medical companies. I would recommend anybody who has the chance to use the latest and shiniest stuff to do so because it’s good for your career .

Very good point. Seems like yet another example of how carefully optimizing all the individual parts of a system can paradoxically de-optimize the overall system.

Re: Postgres scaling advice

#74

Post by evanelias »

bmcahren wrote: post #15

Avoiding sharding and complex replication is very smart to postpone as late as possible with any database (mysql, postgres, mongodb). It can be very fragile or fail in unexpected or unusual ways and most importantly it can take much longer to fix. E.g. 18 hours instead of 2 hours of downtime.

Once your data grows very large, a successfully-implemented sharding solution actually improves availability, rather than reducing it.

With a huge monolithic database, a failure causes downtime for your entire product/company. Replica cloning and backups are slow. Major version upgrades are stressful because it's all-or-nothing.

With a sharded environment, a single shard failure only impacts a portion of your userbase, and smaller databases are faster to perform operational actions on.

There are definitely major downsides to sharding, but they tend to be more on the application side in my experience.

Re: Postgres scaling advice

#75

Post by nvanbenschoten »

cuu508 wrote: post #16
kevincox wrote: post #8

I wonder when using a distributed database (like CockroachDB) will be the default for new applications. Right now it seems that they are less feature and harder to set up than traditional RDBMSes but I can only assume that this gap will narrow and at some point in the future things will be "scalable by default". (Of course no DB is going to prevent all ways to shoot yourself in the foot)

To me it looks like there are not many affordable options right now. CockroachDB understandably wants you to use their Cloud or Enterprise products: the OSS version is quite limited. For example it doesn't support row-level partitioning ( https://www.cockroachlabs.com/docs/stable/configure-replicat... ). Which means, if I understand correctly, it is not of much help for scaling writes to a single big table.

Hi cuu508, CockroachDB engineer here. You are correct that row-level partitioning is not supported in the OSS version of CRDB. However, it sounds like there's a bit of confusion about where manual table partitioning is and is not needed. The primary use-case for row-level partitioning is to control the geographic location of various data in a multi-region cluster. Imagine a "users" table where EU users are stored on European servers and NA users are stored on North American servers.

If you are only looking to scale write throughput then manual partitioning is not be needed. This is because CRDB transparently performs range partitioning under-the-hood on all tables, so all tables scale in response to data size and load automatically. If you are interested in learning more, https://www.cockroachlabs.com/docs/stable/architecture/distr... discusses these concepts in depth.

Re: Postgres scaling advice

#76

Post by bcrosby95 »

__s wrote: post #18
WJW wrote: post #10

Earlier quoted context omitted.

This question is similar to asking on a car forum when using a 40 foot lorry will be the default starter car for everyone. The answer is "probably never" because while it does offer superior cargo transport scalability, the tradeoffs are not worth it for the vast majority of users. The question is posed like distributed databases have no disadvantages over non-distributed databases, but that is simply not the case. C…

To back up how far one server can go, Stack Overflow used a single database for a long time https://nickcraver.com/blog/2016/02/17/stack-overflow-the-ar... (2013 post had much less redundancy, but even their 2016 architecture is pretty undistributed in terms of being able to recreate everything from the single source of truth database)

Yeah, it's been a while since I've been in a high traffic situation, but back in the late 00s we had a couple sites with 3-5 million daily unique users. Each site had a single master, a couple read replicas, and some queries were cached in memcached.

The problem in MySQL we eventually ran into was the read replicas fell behind master during peak load (by 30-60 minutes depending upon the day). So we still had to hit the master for certain queries. I left before we took the next step to fix this issue.

Re: Postgres scaling advice

#77

Post by Deadron »

smoyer wrote: post #70
jjice wrote: post #13

In the opinion of a last semester CS student who has never written an application from scratch that needed more than a SQLite DB (so take me with a half grain of salt), it seems like premature optimization, while always talked about, is very common. I see people talking about using Kubernetes for internal applications and I just can't figure out why. If it's a hobby project and you want to learn Kubernetes, that's a…

> I see people talking about using Kubernetes for internal applications. I think the important issue when first starting a project is to create a "12 Factor App" so that if and when you create a Docker image and/or run the application in Kubernetes, you don't have to rewrite the entire application. Most of the tools I write run on the CLI but I am in fact a fan of Kubernetes for services, message processing and certa…

12 factor apps sacrifice performance and simplicity of your environment for scalability. Unless you are guaranteed to start with a worldwide audience its complete overkill. A better solution is to write your application with the rules in mind with the goal of making it easy to transition to a 12 factor style app when its needed. Scale up then scale out will result in the best performance for your users.

Re: Postgres scaling advice

#78

Post by Tostino »

silon42 wrote: post #51
jandrewrogers wrote: post #42

The assertion that PostgreSQL can handle dozens of TB of data needs to be qualified, as this is definitely not the case in some surprising and unexpected cases that are rarely talked about. PostgreSQL's statistics collection, which is used by the query planner, doesn't scale with storage size . For some ordinary data distributions at scale, the statistical model won't reflect any kind of reality and therefore can pro…

Is it easy to disable it? Personally I would not want any unpredictable behavior from query planner anyway, especially at scale.

You would get predictably bad behavior and performance without the statistics collected.

Re: Postgres scaling advice

#79

Post by [deleted] »

bmcahren wrote: post #11
wilsonrocks wrote: post #4

This was an interesting read for a database novice. It seems like a lot of the quoted stats are about in memory datasets - is that realistic?

Yes. Memory can reach 768GB on a single instance today and I imagine that to expand. From there you can scale by sharding. In memory provides real-time transactions you can't guarantee when using disk-based storage.

[deleted]

Re: Postgres scaling advice

#80

Post by f430 »

anybody know if this applies to Temporal Tables? I've thought of using Datomic but it seems like Temporal Tables does the job, not sure about immutability and how that might impact performance because you are now dealing with keeping a timestamp record of every transactions.
Post reply on HN