If you are going to allocate sharded databases per client with identical schemas, might as well give each of them an sqlite database? Since you're massively cutting down the writes if it's one database per customer.
Postgres scaling advice
91–100 of 207 posts
Re: Postgres scaling advice
#92I like how the article starts with the metrics of what a single node can already do. The trend appears to be to scale right at the start, even before you have your first real customer ... the implications and results are obvious.
Re: Postgres scaling advice
#93In 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 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.
Re: Postgres scaling advice
#94One point I found very interesting was the following paragraph: > For example, on my (pretty average) workstation, I can do ca. 25k simple read transactions per 1 CPU core on an “in memory” pgbench dataset…with the default config for Postgres v13! In my own very unscientific experiments I never got values as high as that, but in the area of around 4k transactions per second total on multiple cores. Of course I'm comp…
As this is a public reference: GitLab's Postgres cluster handles peaks of 300K tps, where the master node alone supports around 60K-80K. And this is not in-memory (datasize in the order of 8TB, RAM 600GB). https://about.gitlab.com/blog/2020/09/11/gitlab-pg-upgrade/ And there's still room for vertical scaling. Disclaimer: we provide Postgres support for GitLab.
HN is missing an user follow and user tagging button :-)
Re: Postgres scaling advice
#95Earlier quoted context omitted.
To me it looks like there are not many affordable options right now. CockroachDB understandably wants you to use their Cloud or Enterprise products: the OSS version is quite limited. For example it doesn't support row-level partitioning ( https://www.cockroachlabs.com/docs/stable/configure-replicat... ). Which means, if I understand correctly, it is not of much help for scaling writes to a single big table.
Hi cuu508, CockroachDB engineer here. You are correct that row-level partitioning is not supported in the OSS version of CRDB. However, it sounds like there's a bit of confusion about where manual table partitioning is and is not needed. The primary use-case for row-level partitioning is to control the geographic location of various data in a multi-region cluster. Imagine a "users" table where EU users are stored on…
Thanks for explaining, and sorry -- looks like I jumped to conclusions too quickly!
Re: Postgres scaling advice
#96Earlier 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.
1.0 was released in 2015. There are stable LTS vendors for it.
It's pretty established. And much saner than cobbling together Ansible/Puppet/Chef playbooks for everything.
Re: Postgres scaling advice
#97For anyone interested in general advice regarding scaling traditional SQL databases, check out my article [0] where I collected various techniques when I investigated this topic in my last workplace.
[0] https://stribny.name/blog/2020/07/scaling-relational-sql-dat...
Re: Postgres scaling advice
#98In 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 think it's easier to run a small k8s cluster than it is to attempt to recreate a lot of the functionality provided manually, especially if you're running in a cloud where the control plane is handled for you. It provides unified secrets management, automatic service discovery and traffic routing, controllable deployments, resource quotas, incredibly easy monitoring (with something like a prometheus operator). Being…
Agreed. What is it that people are doing (or not doing) where a simple managed k8s cluster is more work than the minimum way to do this?
Are teams not even setting up automated builds and just sshing into a box?
Re: Postgres scaling advice
#99The 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 n…
It is ironic that you accuse me of "unwarranted conclusions". I've been customizing and modifying PostgreSQL internals for almost two decades, I know how to read the source. You aren't as familiar with PostgreSQL as you think you are.
This wasn't my problem, I was asked by a well-known company with many large PG installations and enterprise support contracts to look at the issue because no one else could figure it out. The limitations of the statistics collector are not only evident in the source but they are documented there. There are also deep architectural reasons why you can't trivially modify the statistics collector -- I looked into this option -- to work for larger tables without introducing other serious issues.
If you have a uniform distribution of values, the statistics collector will work fine. In the above case, the values followed a power law distribution which created extreme biases in the statistical model due to necessary restrictions on sampling. Other distributions can have similar effects once you exceed the ability of the statistics collector to acquire a representative sample.
Re: Postgres scaling advice
#100The 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…
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 improvements left when it first looked like it was topping out. Add a caching layer, have better indexes, more targeted queries, appropriate use of window functions, etc. Give those a try before biting the bullet on distributed data.
Point #3, here is a good rule of thumb for distributed systems. Whatever scale you hit on a single machine, you can probably gain 1-2 orders of magnitude of performance by switching to a faster language and carefully optimizing. If you switch to distributed, you'll LOSE at least an order of magnitude performance due to the overhead of RPCs, but are able to scale indefinitely afterwards.
If you're distributing for reliability, great. But if you're distributing for performance and you have less than 20 machines in your system, either your problem is embarrassingly parallel or you likely aren't achieving a net win.
I've seen a lot of people prematurely distribute, run into performance challenges, solve them, then pat themselves on the back for being smart enough to have distributed their code. While failing to recognize that they were addressing a self-inflicted injury.