Postgres scaling advice • Hacker News Board

Re: Postgres scaling advice

#131

Post by andreimatei1 » Tue, Jan 26, 2021, 6:38 PM UTC

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)

I think the "default" will evolve with whatever offers the best "serverless" experience in the public clouds. In particular, the cheapest and most granularly-billed option.

Re: Postgres scaling advice

#132

Post by bcrosby95 » Tue, Jan 26, 2021, 6:43 PM UTC

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.

How long does it take to get the data off disk and into memory after coming online? A decade ago filling just 64GB of memory with our hot dataset was a painful process. I can't imagine it's any nicer with 768GB.

Re: Postgres scaling advice

#133

Post by striking » Tue, Jan 26, 2021, 6:53 PM UTC

corty wrote: post #118

vlovich123 wrote: post #109

Earlier quoted context omitted.

Set up your docker file to be part of your CI so that your binary blobs are built from source with regularity? That’s typically the solution I’ve seen work well. Manually maintained stuff (especially for stuff that may not be the thing everyone is primarily doing) generally doesn’t scale well without automation (speaking as someone who’s seen organizations grow). This is also true of “getting started” guides. Can’t t…

Yes, of course. That would be ideal. That's what we do for everything we can control. But as someone in the IT dept., far too often you get some container that either was built by someone who long left the company or an external consultant who got paid to never return. Sourcecode is usually unavailable, and if it is available, will only build on that one laptop that the consultant used. The IT department gets left wi…

I hate to raise a seemingly obvious point, but this doesn't seem like a problem with Docker.

Re: Postgres scaling advice

#134

Post by mixmastamyk » Tue, Jan 26, 2021, 6:55 PM UTC

majewsky wrote: post #115

mmacedo_en wrote: post #68

I'm building an app using Postgres for the first time. Naturally I was a bit worried about performance and scaling if the not launched yet app becomes a major success. I began simulating a heavy use scenario. 100k users creating 10 records daily for three years straight. 100000 x 10 x 365 x 3 ~= 1 billion rows or about 200 GB with a record's length of 200 bytes. This is peanuts for modern databases and hardware. Seem…

You classifying 11 writes per second as "heavy use" reminds me of how people on average completely underestimate how fast computers actually are (when they're not bogged down by crappy programs).

I don't believe the grandparent's simulation actually took three years, it was likely operations with a particular data size that was tested.

Still, your main point stands. Around 2001 I wrote a C-program to record every file and size on a large hard disk. We were all amazed that it finished (seemingly) before the enter key had come back up. Must be a bug somewhere, right? Nope.

Much earlier I wrote a Pascal program on a 486 in school that did some calculations over and over again, writing the output to the screen. It blew my mind then how fast the computer could do it.

Re: Postgres scaling advice

#135

Post by laurencerowe » Tue, Jan 26, 2021, 6:56 PM UTC

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…

Micro-service architectures are an absurd overcomplexity for smaller internal apps. They only really make sense when a monolithic system becomes too large for a single team to manage, at which point the micro-service boundaries reflect team boundaries.

Re: Postgres scaling advice

#136

Post by orlovs » Tue, Jan 26, 2021, 6:57 PM UTC

I am totally pro-hosted solutions. However, hosted postgres is always none of it. I had experience on same size machine some queries took 30x time longer on hosted vs selfhosted

Re: Postgres scaling advice

#137

Post by kodah » Tue, Jan 26, 2021, 6:59 PM UTC

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…

> but in the case of making a real application that people will use, it seems like a lot of us can get away with a single DB, a few workers of our app, and maybe a cache.

A couple of points:

1. Kubernetes can run monoliths. It's certainly not exclusive to microservices or SOA. It's just a compute scheduler, quite similar to AWS's EC2 reservations and auto-scaling groups (ASG's).

2. I can't speak for every corporation, but if you already have patterns for one platform (note: "platform" in this context means compute scheduling. eg: AWS, GCP, Kubernetes, Serverless) then you will inevitably try to copy patterns you already implement internally. A lot of times, for better or for worse, it's not what fits best unless what fits best and what you have available are highly conflicting.

3. A lot of times "scaling" is actually code for multi-tenancy. As an industry, we should probably be explicit when we're scaling for throughput, redundancy, and/or isolation. They are not the same thing and at times at odds with each other.

4. I don't really like your use of "real application" here as it implies some level of architectural hierarchy. My main takeaway after 10+ years of professional development is that architectures are often highly contextual to resource availability, platform access, and personal preferences. Sometimes there's a variable of languages too, because some languages make microservice architecture quite easy while others make it a royal PITA.

Re: Postgres scaling advice

#138

Post by sudhirj » Tue, Jan 26, 2021, 7:06 PM UTC

Deadron wrote: post #77

smoyer wrote: post #70

Earlier quoted context omitted.

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

The 12 factors are mostly common sense that apply in pretty much any situation - they help with fancy deployments but also with single servers on DO or even on-Prem servers.

Re: Postgres scaling advice

#139

Post by AdrianB1 » Tue, Jan 26, 2021, 7:09 PM UTC

I have experience with other relational DB products, but the principle should be similar; I have a few databases over 1 TB, but scaling to tens of TB would require more RAM than what a typical 1S or 2S can support. CPU's are not that problematic with the huge number of cores in AMD Epyc, but RAM is a serious limitation, in my world I need between 15% and 60% RAM to database size ration, depending if it is transactional, reporting or somewhere in between. Taking a 50 TB database as an example, it is too much for a 4TB RAM 2 socket Epyc system.

Re: Postgres scaling advice

#140

Post by tommyzli » Tue, Jan 26, 2021, 7:28 PM UTC

jajool wrote: post #64

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…

I have seen many PostgreSQL benchmarks having solid performance with TB data but my real world experience is the complete opposite. Here are some of the main issues that I have encountered so far: 1. Queries on large tables (around 10 GB) are slow even when "index only scan" is used because of MVCC and the way postgreSQL manages concurrency. 2. Hot-standby instances can't be used for anything serious since all querie…

1. You'll have to define "slow" - I have a 3TB table where an index only scan takes under 1ms

2. hot_standby_feedback is absolutely safe. I've got 5 hot standbys in prod with that flag enabled

3. Again, it depends on how "heavy" your update throughput is. It is definitely tough to find the right balance to configure autovacuum between "so slow that it can't keep up" and "so fast that it eats up all your I/O"