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

101–110 of 207 posts

Re: Postgres scaling advice

#101

Post by thraxil »

mvanbaak wrote: post #66
kitd wrote: post #33

Earlier quoted context omitted.

I see people talking about using Kubernetes for internal applications and I just can't figure out why. There is benefit in having established platforms for running your code, and this is especially true for large orgs where the people who run the systems are an entirely different group from those that developed or assembled it. And people (+ their skills) are what cost the most money in any business. It's true that m…

> There is benefit in having established platforms for running your code You do know k8s is very new, there's a constant stream of changes and updates to it, etc etc? it's not established. It's known, but that's it.

I think what they may mean is more that there is an established platform within the organization. One that you have expertise and experience with, monitoring/backup/security tools that work with it, etc. K8s might not have as long a pedigree as VMs, but if you already have a setup to run K8s, people who know how to use it, documentation and tooling that allow devs to run their apps on it securely and efficiently, etc. it's pretty reasonable to want to encourage devs to "just" use k8s it if they want to stand up a new service rather than spinning up whatever random collection of technologies that dev happens to know better.

Re: Postgres scaling advice

#102

Post by mcherm »

ccleve wrote: post #81

The difficulty with this advice is that it assumes that you have many small transactions. Yes, of course, you should try to build your application so queries and transactions are very short. That solves a great many problems. But sometimes you can't. Sometimes you just have to do joins across large tables. There just isn't any other way. Your query is going to run for 5, 10, maybe 30 seconds. That's a huge burden on…

> Sometimes you just have to do joins across large tables. There just isn't any other way. Your query is going to run for 5, 10, maybe 30 seconds.

While that is true, I would speculate (based on my own experience, not any actual research) that it is far more common that users have an unoptimized query or database schema (perhaps it just needs an index to be created) which is taking tens of seconds to run but doesn't NEED to, than it is that users have a need for a complex query that cannot run faster than that.

So for MOST users, the best advice is to learn how to use analyze query and other database tools to optimize execution. Only if you ALREADY know that should you be considering moving to a read replica or some sort of distributed database.

Re: Postgres scaling advice

#103

Post by jandrewrogers »

fabian2k wrote: post #54
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…

You can change the statistics target, see https://www.postgresql.org/docs/current/runtime-config-query... You can also create more advanced statistics over multiple columns: https://www.postgresql.org/docs/current/planner-stats.html But if your statistics are bad, it will certainly mess up some of your query plans.

Yes, everyone knows these things, it does not address the issue. Due to its architecture, there are difficult to change internal limits on how PostgreSQL samples the data.

Under some conditions, it is not possible for the sampling mechanics to build a representative model -- the actual statistics used for query planning will be quasi-random. One of the ways this manifests is that every time you rebuild the statistics you get a completely different statistical model even if the data has barely changed.

Re: Postgres scaling advice

#104

Post by btilly »

rc_hackernews wrote: post #22
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 haven’t worked at a FAANG or any other company even close to that level of scale, so you can take me with half a grain of salt too. But what you said is absolutely true. It’s also something you will very much experience once you start working professionally. I’m in no position to give you advice, and I think I might be giving advice to myself...just don’t let it get to you.

I have worked at FAANGs before.

I am in firm agreement. I think that far too many people are trying to solve the problems that they wish that they had, rather than making it easy to solve the ones that they do have. Going to something like Kubernetes when there is no particular reason for it is a good example of that trend.

When you really need distributed, there is no substitute. But far more think that they need it than do.

Re: Postgres scaling advice

#105

Post by ozkatz »

mmacedo_en wrote: post #88
ccleve wrote: post #81

The difficulty with this advice is that it assumes that you have many small transactions. Yes, of course, you should try to build your application so queries and transactions are very short. That solves a great many problems. But sometimes you can't. Sometimes you just have to do joins across large tables. There just isn't any other way. Your query is going to run for 5, 10, maybe 30 seconds. That's a huge burden on…

I've seen queries running for 1 minute 2 minutes raising user complaints. Then we looked at it, and with a few changes in indexes and query hints brought it down to sub-second execution. Before thinking about distributed systems, there is an entire database optimization toolkit to make use of: primary key review, secondary index creation, profiling, view or stored procedure creation, temporary tables, memory tables a…

This. Using any database requires building up an expertise and understanding how to use its capabilities properly. If you hit a wall with a non-distributed database and your solution is to replace it with a distributed one - you will have a bad time. The surface area of what you need to know to use it properly still includes your basic data modeling, indices, troubleshooting that was required before, with a whole lot of networking, consensus protocols and consistency models to worry about (just to name a few).

Re: Postgres scaling advice

#106

Post by btilly »

CharlesW wrote: post #90
mmacedo_en wrote: post #88

Earlier quoted context omitted.

I've seen queries running for 1 minute 2 minutes raising user complaints. Then we looked at it, and with a few changes in indexes and query hints brought it down to sub-second execution. Before thinking about distributed systems, there is an entire database optimization toolkit to make use of: primary key review, secondary index creation, profiling, view or stored procedure creation, temporary tables, memory tables a…

> Then we looked at it, and with a few changes in indexes and query hints brought it down to sub-second execution. This is exactly what the parent comment said: "you should try to build your application so queries and transactions are very short". If you're claiming that the parent is incorrect about "sometimes, the only answer is to distribute the queries across other boxes", my guess is that probably don't work at…

Based on my past experiences, I'd be happy to take an even money bet that more than 95% of organizations that go distributed for performance reasons actually caused themselves more problems than they solved.

This does NOT mean that there are no use cases for distributed - I've seen Google's internals and it would be impossible to do that any other way. But it does mean that when someone is telling you with a straight face that they needed horizontal scalability for performance, you should assume that they were probably wrong. (Though probably saying that is not the wisest thing - particularly if the person you're talking to is the architect whose beautiful diagrams you'd be criticizing.)

So yes, there are problems that require a distributed architecture for performance problems. That doesn't contradict the point that every other option should be explored first.

Re: Postgres scaling advice

#107

Post by evanelias »

codyb wrote: post #83
evanelias wrote: post #74

Earlier quoted context omitted.

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 userbas…

Wouldn’t sharding generally result in overlap between the key ranges so that a database shard going down doesn’t have to result in any downtime? Then your issue is replication of writes I suppose. Probably depends on use case what configuration you choose.

With a typical sharded relational database setup, each sharding key value maps to exactly one shard.

There should be replicas of that shard, which can be promoted in case of a master failure. But in rare cases all replicas may also be degraded or inconsistent and therefore non-promotable. When this happens to a giant monolithic non-sharded database, the impact is far more catastrophic than when it happens to a single smaller shard.

In any case, replication is a separate concern from sharding. Each sharded database replica set (traditionally 1 master + N replicas) has a data set that is unique / independent of other sharded database replica sets.

That said, some of the "NewSQL" distributed databases may arrange their data in more complex ways. [Edit to clarify: I mean in respect to the combination of both sharding and replication. With a traditional sharded relational database, you have replica sets where each replica contains the exact same shard or shards; with a NewSQL distributed DB, data may be replicated in a more complex arrangement]

Re: Postgres scaling advice

#108

Post by nforest »

pg_bot wrote: post #26
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…

What you're describing is called resumé driven development. It happens every few years when people want to cash in on trends/buzzwords that people believe will be disruptive to all industries but are just tools to have in the toolbox for most. New tools pop up all the time that fit this mould. Over the past ten years I can think of Hadoop (Big data), MongoDB (NoSQL), Kubernetes, "Serverless" computing, and TensorFlow…

For artificial intelligence, I think it's more often marketing driven development. It's easier to seem disruptive if you claim to have AI in your product. Easier to get funding and have people talk about your company. I feel like it comes more often from business executives than technical people.

Re: Postgres scaling advice

#109

Post by vlovich123 »

corty wrote: post #60
zdragnar wrote: post #43

Earlier quoted context omitted.

In big enough organizations, it is very easy to lose track of who owns what, especially when it is those little ad-hoc internal tools. Manually managing the infrastructure for them is a recipe for them to become permanently enshrined in the wasteland of "services we think we use, but do not maintain because we don't remember who needed it or put it up or how to configure it". K8s isn't the only answer, but if you are…

Having a Dockerfile that copies a few binary blobs into an age-old distro image isn't an improvement, it's a huge liability. And most of that stuff that no one knows anything about anymore is like that. Same as with an old VM or PM. I'd rather have that old crap as a physical machine. Why? Because the hardware lifetime "naturally" limits the lifetime of such applications. If the hardware dies, it forces a decision to…

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 tell you how much maintenance and run time I’ve saved converting those wikis to Python scripts.

Re: Postgres scaling advice

#110

Post by evanelias »

bmcahren wrote: post #84
evanelias wrote: post #74

Earlier quoted context omitted.

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 userbas…

Shards increase the number of failure modes and increase the complexity of those failure modes. For most businesses, the recommendation holds true... keep it simple, don't shard until you need . I find it somewhat concerning that MongoDB has a better architecture for upgrades than Postgres. You add a replica to the cluster running the new major version and then switch that replica over as your primary once you've rep…

> Shards increase the number of failure modes and increase the complexity of those failure modes.

I would only agree with this during the initial implementation of sharding. Once deployed and stable, I have not found this to be the case, at all.

I say this as someone who has directly architected a sharded database layer that scaled to over a trillion rows, and later worked on core automation and operations for sharded databases that scaled to an incalculable number of rows (easily over 1 quadrillion).

In both cases, each company's non-sharded databases were FAR more operationally problematic than the sharded ones. The sharded database tiers behave in common ways with relatively uniform workloads, and the non-sharded databases were each special snowflakes using different obscure features of the database.

> keep it simple, don't shard until you need

I would have wholeheartedly agreed with this until a few years ago. Cloud storage now permits many companies to run monster 10+ TB monolithic relational databases. Technically these companies no longer "need" to shard, possibly ever. But at these data sizes, many operations become extremely painful, problematic, and slow.

Post reply on HN