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

51–60 of 207 posts

Re: Postgres scaling advice

#51

Post by silon42 »

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.

Re: Postgres scaling advice

#52

Post by eatonphil »

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…

Neither of you have given a reproducible example one way or the other! I could share that I know of TB of core analytics data running postgresql and it does alright. But it would be nicest if there were a more tangible study to talk about.

Re: Postgres scaling advice

#53

Post by laurenz_albe »

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…

This seems to be unfounded criticism. When statistics are gathered, PostgreSQL samples a certain percentage of the table, so that obviously scales. The number of "most common values" and histogram buckets scales up to 10000, which should be good even for large tables. While I'll readily admit that not all aspects of cross-column dependencies are dealt with, and cross-table distributions are not considered, that has nothing to do with size. I guess you hit a problem somewhere, couldn't solve it and jumped to unwarranted conclusions.

Re: Postgres scaling advice

#54

Post by fabian2k »

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.

Re: Postgres scaling advice

#55

Post by lrem »

yuribro wrote: post #30
kevincox wrote: post #19

Earlier quoted context omitted.

I don't think it is quite the same. - Switching from a car to a van to a lorry is fairly low cost. You don't need to recreate your product (probably). - You don't need to run distributed databases in a cluster to start. But I think most importantly the decrease in dev speed and performance is an investment in future scalability. And I only imagine that this different will shrink over time to where for example a 1 nod…

> But I think most importantly the decrease in dev speed and performance is an investment in future scalability. It makes sense only if you'll ever need this scalability. And you take a hit on other fronts too: Infra cost, Deployment complexity. And both deployment complexity and code complexity also increase QA cost, instability, product and company reputation. >> much like people don't often start with SQLite today…

For personal projects, I start with SQLite by default. Never came close to its scalability limit too ;)

Re: Postgres scaling advice

#56

Post by boomer918 »

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…

Theoretically your argument would make sense, but practically your data distribution should be uniform enough for the statistics collector to work for terabytes and terabytes.

I've seen query plans get messed up with partial indexes, but if you have a regular index, you needn't worry.

Re: Postgres scaling advice

#57

Post by natmaka »

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…

Even when using as much stat samples as possible (3000000) for each of those tables: ALTER TABLE table_name ALTER column_name SET STATISTICS 10000; See https://www.postgresql.org/docs/current/sql-altertable.html

AFAIK the PostgreSQL's approach is based upon http://citeseerx.ist.psu.edu/viewdoc/download?doi=10.1.1.53....

Re: Postgres scaling advice

#58

Post by wiredfool »

ahachete wrote: post #27
bmcahren wrote: post #11

Earlier quoted context omitted.

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.

EC2's u-24tb1.metal is 224/448 cores/hyperthreads and 24TB RAM ;) https://aws.amazon.com/sap/instance-types/

And nearly 2 million for a 3 year reserved instance.

Re: Postgres scaling advice

#59

Post by spaetzleesser »

mumblemumble wrote: post #31
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…

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 .

Re: Postgres scaling advice

#60

Post by corty »

zdragnar wrote: post #43
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…

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 spend some money to keep it running or throw it away, which, given that hardware is expensive, usually results in throwing it away.

Post reply on HN