7 ms·

These numbers make more sense on hardware from 5 years ago (to be fair, what they were dealing with), but postgres, on modern consumer hardware (Yes, consumer!

by geenat 4y ago

These numbers make more sense on hardware from 5 years ago (to be fair, what they were dealing with), but postgres, on modern consumer hardware (Yes, consumer! Ex: KC3000 + Ryzen 7950x), already does over 50k QPS without being overburdened. You could probably squeeze out 100k QPS with faster NVME's. That's like 10 tall servers for their peak QPS? (500k QPS on slide 19) Lots of natural sharding points here too: company, game, usage of data, etc. This is assuming you avoid or go light on expensive features like foreign keys (as Vitess already does). The "scale magic" in Spanner (Big Table) inspired DB's are just hidden automation of traditional sharding- CockroachDB: Sharded indexes, then runs map reduce for you. Scylla/Cassanda: Sharded indexes again, but more limitations for speedups: Eventually consistent. You don't have fast delete- only update (discord uses tombstones). JOIN's are "in app" only. Vitess: Proxy that dismantles/routes your query to the correct server. This scales, but is eventually consistent. JOIN's on co-located data, or "in app".... in simple terms, Vitess is like an externally managed "in app" query parser/router.

winrid 4y ago
Mongo's sharding also uses routers like Vitess but can be strongly consistent depending on connection and write settings. Data is divided into chunks and the routers route queries based on where your query lands on chunk ranges.
aeyes 4y ago
I see 500k qps with 30TB data doubling every 2 years on slide 19. In my experience me this isn't close to being possible with a single Postgres box. There is no way around sharding this workload. Vitess has different consistency models, it's not as eventually consistent as you describe.
- geenat 4y ago
  You'd certainly want to shard from the start in any case, but it's an order of magnitude less postgres boxes for the QPS.
  - leetrout 4y ago
    Wonder what the tradeoff on connection overhead would be? I've never been around a connection pooler that would be anywhere near this load.
    - aeyes 4y ago
      Not too much if connections are persistent. A problem is that pgbouncer is 1 CPU only so you need a couple of them which makes the pooling less efficient.
      - Mavvie 4y ago
        Does pgbouncer tend to run out of CPU? Or just at the large scale this article is about?
        aeyes 4y ago
        I have a 100k qps workload and we run 10 pgBouncer instances. I ran into the CPU limit with 6. Connections to pgBouncer are persistent so it's not caused by connection churn. The amount of incoming connections is in the low hundreds per instance. I tried without SSL as well and it didn't make a measurable difference. I'm not quite sure if this is a common problem or if it's our workload. It always made me wonder if moving pooling into Postgres is really a good idea.
    - geenat 4y ago
      Speaking only for postgres 15+, but in my testing, app drivers have been great about facilitating "connection pooling" without a separate server. Eliminates connection overhead at the app level for many projects. Golang and ASGI Python (FastAPI, Starlette, Sanic) can do a small connection pool per worker, each worker can handle thousands of users at once. PHP in 2023 has database connection persistence using FPM + PDO (effectively, one big connection pool shared between all workers on a server). Again, eliminates connection overhead- works well. Just remember to raise your max_connections in postgres, like in this: https://gist.github.com/gnat/cfe3754c3dc817c7fb8b2225ef4db628 https://gist.github.com/gnat/cfe3754c3dc817c7fb8b2225ef4db62...
- Nathanba 4y ago
  I really wonder about the justification for using MySQL/Postgres sometimes. How is it not simply superior in almost every way to start with ScyllaDB which is faster in every way and the only downside I can see is that you have a very limited way to query things. But so what, at least it's fast. I mean isn't that the nr.1 thing you want from a database, it's not about comfort.. it's about storing data. It is genuinely annoying though for example that you can't even alter a column type after the column was created in ScyllaDB.
  - christophilus 4y ago
    > the only downside I can see is that you have a very limited way to query things That’s a pretty big one. If you know you need the scale, the trade off might be worth it. But I’m 99% of projects, Postgres is more than adequate and has great flexibility.
    - Nathanba 4y ago
      yeah but I don't think that we can just trust that when people say this. They are invested into these single node databases for 10+ years probably. They are not going to tell you: Hey yeah, if you architect your app differently you could have x10 the performance.. but don't because it's a bit annoying when you query data. I mean is that being objective? Lots of people are not okay with the performance of mysql/postgres, that's the whole point of solutions like crdb/tidb/singlestore/vitess/yugabyte/citus and so many others. To tell someone that they should start with mysql or postgres is the equivalent of telling them that they'll have to migrate in the future and relearn a whole bunch of stuff about the operations, like backup etc. I don't really like the feeling of choosing a technology with the mindset that I'll have a lot of problems when there is a performance problem and at some point the pain will be so great that I'll have to switch to vitess.
      - richieartoul 4y ago
        It’s really not just about annoyance it’s about correctness and speed of iteration. Many apps that can be built trivially in a couple SQL queries cannot be implemented correctly in Scylla without reinventing half of tidb/vitess/etc. On top of that, it’s very hard to hire and pay the engineers who are capable of doing that in the first place. Finally, even if you manage to do it, your competitors will have shipped 100x more functionality than you in that time. That’s why people use these systems and why so many very smart people spend so much time trying to make them better
foota 4y ago
I think describing newsql databases as "just" hidden sharding is underselling them substantially. The hard part is in the details of distributed transactions and consistency, among other things.
- kevincox 4y ago
  Yup. Consistency is by far the biggest advantage. Sharding isn't incredibly hard but it makes every query think about it for consistency purposes. Often you have a natural sharding key and it isn't much of a concern at all (example isolated user accounts) but at some point you need to go cross-shard and it gets very complicated (or you give up consistency and you get complicated bugs). In theory a distributed SQL database can be a huge developer win because of this. Of course there are usually other tradeoffs to consider as well.

by geenat 4y ago

winrid 4y ago

Mongo's sharding also uses routers like Vitess but can be strongly consistent depending on connection and write settings. Data is divided into chunks and the routers route queries based on where your query lands on chunk ranges.

aeyes 4y ago

I see 500k qps with 30TB data doubling every 2 years on slide 19. In my experience me this isn't close to being possible with a single Postgres box. There is no way around sharding this workload. Vitess has different consistency models, it's not as eventually consistent as you describe.

geenat 4y ago
You'd certainly want to shard from the start in any case, but it's an order of magnitude less postgres boxes for the QPS.
- leetrout 4y ago
  Wonder what the tradeoff on connection overhead would be? I've never been around a connection pooler that would be anywhere near this load.
  - aeyes 4y ago
    Not too much if connections are persistent. A problem is that pgbouncer is 1 CPU only so you need a couple of them which makes the pooling less efficient.
    - Mavvie 4y ago
      Does pgbouncer tend to run out of CPU? Or just at the large scale this article is about?
      - aeyes 4y ago
        I have a 100k qps workload and we run 10 pgBouncer instances. I ran into the CPU limit with 6. Connections to pgBouncer are persistent so it's not caused by connection churn. The amount of incoming connections is in the low hundreds per instance. I tried without SSL as well and it didn't make a measurable difference. I'm not quite sure if this is a common problem or if it's our workload. It always made me wonder if moving pooling into Postgres is really a good idea.
  - geenat 4y ago
    Speaking only for postgres 15+, but in my testing, app drivers have been great about facilitating "connection pooling" without a separate server. Eliminates connection overhead at the app level for many projects. Golang and ASGI Python (FastAPI, Starlette, Sanic) can do a small connection pool per worker, each worker can handle thousands of users at once. PHP in 2023 has database connection persistence using FPM + PDO (effectively, one big connection pool shared between all workers on a server). Again, eliminates connection overhead- works well. Just remember to raise your max_connections in postgres, like in this: https://gist.github.com/gnat/cfe3754c3dc817c7fb8b2225ef4db628 https://gist.github.com/gnat/cfe3754c3dc817c7fb8b2225ef4db62...
Nathanba 4y ago
I really wonder about the justification for using MySQL/Postgres sometimes. How is it not simply superior in almost every way to start with ScyllaDB which is faster in every way and the only downside I can see is that you have a very limited way to query things. But so what, at least it's fast. I mean isn't that the nr.1 thing you want from a database, it's not about comfort.. it's about storing data. It is genuinely annoying though for example that you can't even alter a column type after the column was created in ScyllaDB.
- christophilus 4y ago
  > the only downside I can see is that you have a very limited way to query things That’s a pretty big one. If you know you need the scale, the trade off might be worth it. But I’m 99% of projects, Postgres is more than adequate and has great flexibility.
  - Nathanba 4y ago
    yeah but I don't think that we can just trust that when people say this. They are invested into these single node databases for 10+ years probably. They are not going to tell you: Hey yeah, if you architect your app differently you could have x10 the performance.. but don't because it's a bit annoying when you query data. I mean is that being objective? Lots of people are not okay with the performance of mysql/postgres, that's the whole point of solutions like crdb/tidb/singlestore/vitess/yugabyte/citus and so many others. To tell someone that they should start with mysql or postgres is the equivalent of telling them that they'll have to migrate in the future and relearn a whole bunch of stuff about the operations, like backup etc. I don't really like the feeling of choosing a technology with the mindset that I'll have a lot of problems when there is a performance problem and at some point the pain will be so great that I'll have to switch to vitess.
    - richieartoul 4y ago
      It’s really not just about annoyance it’s about correctness and speed of iteration. Many apps that can be built trivially in a couple SQL queries cannot be implemented correctly in Scylla without reinventing half of tidb/vitess/etc. On top of that, it’s very hard to hire and pay the engineers who are capable of doing that in the first place. Finally, even if you manage to do it, your competitors will have shipped 100x more functionality than you in that time. That’s why people use these systems and why so many very smart people spend so much time trying to make them better

foota 4y ago

I think describing newsql databases as "just" hidden sharding is underselling them substantially. The hard part is in the details of distributed transactions and consistency, among other things.

kevincox 4y ago
Yup. Consistency is by far the biggest advantage. Sharding isn't incredibly hard but it makes every query think about it for consistency purposes. Often you have a natural sharding key and it isn't much of a concern at all (example isolated user accounts) but at some point you need to go cross-shard and it gets very complicated (or you give up consistency and you get complicated bugs). In theory a distributed SQL database can be a huge developer win because of this. Of course there are usually other tradeoffs to consider as well.