Postgres scaling advice • Hacker News Board

Re: Postgres scaling advice

#171

Post by mulmen » Wed, Jan 27, 2021, 1:38 AM UTC

barrkel wrote: post #164

btilly wrote: post #100

Earlier quoted context omitted.

Point #1, there is a world of difference between a reporting database and a transactional database. If you need a reporting database, ship logs and set up a reporting database separate from your transactional one. That solves most of the problem. Point #2, the fact that you've hit performance problems does not mean that you need to distribute. Every real system that I've seen has had order of magnitude performance im…

What if you ingest 10s of millions of rows a day, and need to conditional updates based on those 10s of millions of rows? Either you're going to do 10s of millions of fetches and a whole lot of code, or you can push some of the work to the database and perform updates that involve joins. Those queries may take minutes to complete; but the SQL will be pretty short. Big queries that fetch millions of rows isn't solely…

My relational experience is largely in the analytics world. Can you give me a (simplified/obfuscated) example of this type of workload? Why do the rows need to be updated together or so frequently? My imagination is failing me but this sounds like an interesting problem.

Re: Postgres scaling advice

#172

Post by mulmen » Wed, Jan 27, 2021, 1:43 AM UTC

btilly wrote: post #170

barrkel wrote: post #165

Earlier quoted context omitted.

The hot path may be a SET inside an UPDATE which uses a join which needs to touch millions of rows. You can break that query apart and run it in application logic: do some fetches, do application-side joins, do lots of little updates. Or you can write a single piece of SQL. The former is a whole lot more code and will run slower but individually each item will be fast. The latter is a lot less code and runs faster ov…

Every system has ways that it can fall down hard. Here is a fun one for Postgres. Modify your query to be using a stored procedure that creates/drops temporary tables. Watch your database fall over from needing to VACUUM system tables. (This was not a hypothetical disaster. It was the result of trying to use a third-party ETL tool that had been designed for Oracle and didn't understand how temporary tables differ on…

VACUUM system tables? Is that a thing? At $dayjob I use Redshift quite a bit and as far as I know there's never a VACUUM operation on any system tables but maybe I'm not looking closely. Are system tables even "real" tables?

Re: Postgres scaling advice

#173

Post by mulmen » Wed, Jan 27, 2021, 2:03 AM UTC

jandrewrogers wrote: post #39

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)

What we need is a database that can both scale-up and scale-out. Most distributed databases offer poor efficiency and performance on a per node basis, which has a high operational cost. This is why people avoid using distributed databases unless they need it. A scale-up database can serve as much workload as pretty large scale-out database in practice. This discontinuity creates the market for scale-up systems. There…

If nothing prevents scaling up a scale-out database does that not also mean nothing prevents scaling out a scale-up database? Aren't all scaling problems trivial at that point?

Re: Postgres scaling advice

#174

Post by 1996 » Wed, Jan 27, 2021, 2:20 AM UTC

bcrosby95 wrote: post #132

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.

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.

On a modern raid of NVME, you can do several gigs per second - with 20 PCIe Gen3 lanes on a 3400G about 10 GB/s is reasonable, for a theoretical max of 20GB/s.

Assuming your system is very badly configured and you can only do 3 gigs per second, about 400 s, so about 5 minutes, which is not very painful

Re: Postgres scaling advice

#175

Post by riku_iki » Wed, Jan 27, 2021, 2:39 AM UTC

pas wrote: post #93

wejick wrote: post #41

Earlier quoted context omitted.

I know one of the biggest Ecommerce shop in Asia were using 1 big DB with multiple read only slave in monolithic architecture for more than 5 years. However not only driven by DB performance, but also on organizing hundreds of engineers they adapted microservice architecture. Then they slowly migrating to per domain specific DB, it is just classic microservice migration story. While single DB may bring us pretty long…

Monzo (UK bank) has 1600+ microservices, but mandates a common framework/library and uses Cassandra. (Which is basically a shared nothing, bring your own schema "database".) So it makes sense to combine advantages of different approaches.

using non-ACID db for financial services probably requires lots of trickery.

Re: Postgres scaling advice

#176

Post by evanelias » Wed, Jan 27, 2021, 2:39 AM UTC

bastawhiz wrote: post #159

evanelias wrote: post #110

Earlier quoted context omitted.

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

> 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. That's because sharded tables restrict what features you can use (e.g., no JOINs). If you constrained the feat…

> no JOINs

Sharded environments still have some JOINs. Typically all data for a single user/customer/whatever should be placed on a single shard, and it's still very useful to join across tables for a single user.

> If you constrained the features on the non-sharded databases, you'd achieve the same net result.

No, that's not the main reason for operational benefits. Rather, it's simply because the shards all have a uniform schema, uniform query workload, and relatively small data size (as compared to a large monolithic DB). You can perform operational maintenance at better granularity -- i.e. on a single shard rather than the entire logical data set. And if a complex operation is fine on one shard, it's very likely fine on the rest, due to the uniformity.

For example, performing a DBMS major version upgrade on a large monolithic DB is a nightmare. It's an all-or-nothing affair. If you encounter some unforeseen problem with the new DB version only in prod, you can expect some significant application-wide downtime. Meanwhile for a sharded environment it's both easier and safer from an operational perspective, assuming your team is comfortable automating the process once it has been proven safe. You can upgrade one shard and confirm no problems after a week in prod before proceeding to the rest of the fleet. If unforeseen problems do occur, worst-case it only impacts a tiny portion of users.

> it also restricts your ability to perform other operations (more complicated queries or reports are ~impossible). It is not without its tradeoffs.

Yes, this is why I said above "There are definitely major downsides to sharding, but they tend to be more on the application side in my experience." OP claimed the downsides were operational (e.g. more complex failures or larger downtime), which I do not agree with.

Re: Postgres scaling advice

#177

Post by Tostino » Wed, Jan 27, 2021, 2:48 AM UTC

mulmen wrote: post #172

btilly wrote: post #170

Earlier quoted context omitted.

Every system has ways that it can fall down hard. Here is a fun one for Postgres. Modify your query to be using a stored procedure that creates/drops temporary tables. Watch your database fall over from needing to VACUUM system tables. (This was not a hypothetical disaster. It was the result of trying to use a third-party ETL tool that had been designed for Oracle and didn't understand how temporary tables differ on…

VACUUM system tables? Is that a thing? At $dayjob I use Redshift quite a bit and as far as I know there's never a VACUUM operation on any system tables but maybe I'm not looking closely. Are system tables even "real" tables?

Yes, that is a thing in Postgres. They are real tables and use the same underlying storage management as the rest of the database.

Re: Postgres scaling advice

#178

Post by btilly » Wed, Jan 27, 2021, 2:54 AM UTC

mulmen wrote: post #172

btilly wrote: post #170

Earlier quoted context omitted.

Every system has ways that it can fall down hard. Here is a fun one for Postgres. Modify your query to be using a stored procedure that creates/drops temporary tables. Watch your database fall over from needing to VACUUM system tables. (This was not a hypothetical disaster. It was the result of trying to use a third-party ETL tool that had been designed for Oracle and didn't understand how temporary tables differ on…

VACUUM system tables? Is that a thing? At $dayjob I use Redshift quite a bit and as far as I know there's never a VACUUM operation on any system tables but maybe I'm not looking closely. Are system tables even "real" tables?

Yes, system tables are just tables. And every time to create/drop a temporary table you've inserted/deleted a bunch of data from https://www.postgresql.org/docs/12/catalog-pg-attribute.html. If you do so faster than it can vacuum, you're in for a world of hurt.

Re: Postgres scaling advice

#179

Post by mulmen » Wed, Jan 27, 2021, 3:03 AM UTC

btilly wrote: post #178

mulmen wrote: post #172

Earlier quoted context omitted.

VACUUM system tables? Is that a thing? At $dayjob I use Redshift quite a bit and as far as I know there's never a VACUUM operation on any system tables but maybe I'm not looking closely. Are system tables even "real" tables?

Yes, system tables are just tables. And every time to create/drop a temporary table you've inserted/deleted a bunch of data from https://www.postgresql.org/docs/12/catalog-pg-attribute.html . If you do so faster than it can vacuum, you're in for a world of hurt.

Huh. For a long time there was no autovacuum in Redshift and AFAIK this was never a problem for us. I wrote our VACUUM script and it would never pick up system tables. Maybe Redshift does something differently.

Re: Postgres scaling advice

#180

Post by evanelias » Wed, Jan 27, 2021, 3:26 AM UTC

throwdbaaway wrote: post #146

evanelias wrote: post #110

Earlier quoted context omitted.

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

One of those companies is facebook right? I think this blog post provides a much more balanced view: http://yoshinorimatsunobu.blogspot.com/2017/11/towards-bigge...

Yes. Yoshinori is among the best of the best, and I could not hope to capture that level of nuance in a quick HN comment :)

But in terms of being a more balanced view, my read of his post aligns pretty closely with my main point in this subthread: the disadvantages of sharding fall more on the application side (limitations on joins, secondary indexes, transactions) than on the operational side (availability, performance and resource management, logical backup, db cloning, replication).