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

81–90 of 207 posts

Re: Postgres scaling advice

#81

Post by ccleve »

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 your server, and it will slow down the other tenants. In that case, the only answer is to distribute the queries across other boxes.

I agree with the advice in general -- delay moving to a distributed system as long as possible -- but sometimes you just can't.

Re: Postgres scaling advice

#82

Post by dekimir »

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.

> In memory provides real-time transactions you can't guarantee when using disk-based storage.

This is changing as we discover better ways of coding IO operations.

https://www.globenewswire.com/news-release/2019/11/05/194114...

https://itnext.io/modern-storage-is-plenty-fast-it-is-the-ap...

Re: Postgres scaling advice

#83

Post by codyb »

evanelias wrote: post #74
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 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.

Re: Postgres scaling advice

#84

Post by bmcahren »

evanelias wrote: post #74
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 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 replaced enough instances in the cluster. Having worked in Oracle and MySQL for years then having switched to a company with a MongoDB framework I forgot how stressful upgrades would be with such archaic limitations.

Re: Postgres scaling advice

#85

Post by vp8989 »

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 thinking on efficiency vs scalability is largely influenced by the US tech company meta where founders give up equity so they don't have to worry about profitability for a very long time.

In that case, it is preferred to burn piles of cash on AWS instead of potentially needing to sacrifice revenue because you can't scale quickly enough.

An architecture that is not scalable is considered a failure whereas one that is complex and inefficient is much more tolerated (as long as it can scale out) ... at least until the funding dries up or Wall Street activists get involved.

Re: Postgres scaling advice

#86

Post by singron »

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 mostly applies to update heavy tables since index only scans use the visibility map, which would be frequently invalidated.

3 is definitely true, especially the larger the table. I've had success splitting frequently updated columns out into their own much smaller table, or any other trick to concentrate updates into a small table. Also MVCC bookkeeping requires updating pages, so an UPDATE that doesn't change any field and SELECT FOR UPDATE will cause the same problem.

Re: Postgres scaling advice

#87

Post by Tostino »

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.

Totally depends on how you shard. In my case (b2b), i'd be sharding by tenant. Having a single tenant go down would not have the same impact as every single tenant going down.

Re: Postgres scaling advice

#88

Post by mmacedo_en »

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 and so on.

Re: Postgres scaling advice

#89

Post by cliftonk »

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…

You could always start with postgresql and use the transaction log to materialize views for particular use cases (ie differential data flow). This post is clickbaity in that it doesn’t give any administration advice but right in the broad sense that postgres/mysql are still the best low-latency single sources of truth available

Re: Postgres scaling advice

#90

Post by CharlesW »

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…

> 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 a scale where you've learned that query optimization can't solve every database performance problem.

Post reply on HN