5 ms·

Sharding a database can make it faster

91 points by nsoonhui 5y ago

Vanit 5y ago
> Databases have been around for over 50 years. You might not think there’s anything left to innovate after all that time, but database fragmentation is one of the fastest-developing verticals in the tech industry. The complexity that characterizes existing data infrastructures seems to be only getting worse. Maybe I'm just getting salty, but why is there filler in this article. This isn't a recipe for garlic bread.
- manigandham 5y ago
  It's a basic intro article to sharding for people who are new to databases in general.
- cpach 5y ago
  At least they didn’t go on to describe in 300 words how their grandma’s homegrown DB engine was the best DB engine of them all ;-)
  - bryanrasmussen 5y ago
    Growing up on the lower east side I was always torn between playing stick ball with my boys, or running home to hear about my mom's latest exploits. My mom was really my hero, because even as she took her fight for data sanitation out into the world she never lost site of building pure and clean relational engines from the ground up. Really, my mom wasn't just my hero but a hero to all the kids in the district. Heck, almost every year we would spend days on the playground instead of the hated classroom thanks to her ingenuity! But the best part of any day was when I was down in the sandlot with Butchie, Achmed, and Patty and my mom's voice would call down from our open kitchen window where she was baking and optimizing throughput "Bobby Tables, please accept these cookies I made for you and your friends in order to have energy for playing!" That's why I am happy to share with you my mom's recipes for cookies and secure storage of cookie related acceptance data. on edit: should be lost sight not lost site but will let it stand because hah.
  - rzzzt 5y ago
    It is a dark and gloomy Wednesday night. Colin steps out of his beat-up Corolla, with the rain relentlessly (but unsuccessfully) trying to dissolve his leather jacket. He's getting close to 30 and is always telling his friends that he will have a weekend house with a koi pond in the backyard some day, but they laugh in response -- who cares about fish these days? Things are getting complicated at work. The app he's been working on for the past few months turned out to be a hit, new subscribers are appearing at every moment; in short, business is booming! They even got a column in the local newspaper, getting high praise for both form and functionality. A problem presents itself when too many people are trying to use the app at the same time - it looks like the database software can not keep up with the demands of this immense crowd. An impromptu meeting is held, and Colin is tasked with coming up with a solution. Getting closer to the front door, he thinks to himself, "How can we handle this incredible volume of traffic when it reaches the database cluster?", but his thoughts soon drift off again.
- pwdisswordfish9 5y ago
  The Overflow is a spam blog. That’s not even the worst of it: <https://meta.stackoverflow.com/q/412989/ https://meta.stackoverflow.com/q/412989/>
- hkt 5y ago
  Recipes should probably not have filler either (unless it is a recipe for pie)
i_like_waiting 5y ago
Does sharding work for splitting DWH tables? I guess it has to, but whats strategy there, if its mostly about "slicing and dicing" in every way possible. Maybe recent vs. historical data, or some known customer segments, extracted from previous analysis?
- snissn 5y ago
  Date partitions very good. Also other enums or even client ids can be great partitions (in addition simultaneously with date)
- manigandham 5y ago
  Sharding = partitioning and it's the only way data warehouses can scale to handle large datasets. Tables are partitioned on the primary key, usually with range-based sharding for flexibility. Some warehouses like BigQuery force this as a numeric key (dates by default) while others leave it up to you. Partitioning can also use multiple columns to help with more even distribution and/or better query performance.
  - tremon 5y ago
    The technical difference between sharding and partitioning is at which level the split lies. If you're splitting the data storage within a single database, it's called partitioning. If you're splitting the data across multiple databases instead, it's sharding. But you can't implement either if you haven't logically partitioned your data first.
  - i_like_waiting 5y ago
    yeah, partitioning is very good at almost any level, with very few exceptions. Sharding on other hand is pure tradeoff, I haven't looked into hard, but I guess 1 computer, where database sits, cannot have unlimited RAIDs of hard drives. So question is, should I push for 1 server only as long as possible, or when its better to shard between multiple?
- thejosh 5y ago
  How big your data is also dictates your strategy. And also your software. What works for say snowflake night not work for others
deleted 5y ago
[deleted]
donatj 5y ago
Before Aurora popped up and saved the day, we were looking at sharding our MySQL database. The levels of added complexity were keeping me up at night. I am sure we’ll reach a point where Aurora doesn’t solve it anymore but until then.
- manigandham 5y ago
  Using Aurora is analogous to vertical scaling (as in scaling up with bigger and bigger hardware). Distributed relational databases have made tremendous progress in recent years and you have many compelling options now if you need it. For MySQL there's SingleStore (memsql), Vitesse/Planetscale, and TiDB.
  - matwood 5y ago
    Aurora also horizontally scales with readers.
    - zinodaur 5y ago
      Replication lag can make that pretty tricky - you never know if the client doing the reading has seen a state further in the future than the replica
      - daneel_w 5y ago
        That's not a concern unless your application is broken. An application should stick to one DB instance for one and the same isolated task, not open multiple parallel connections to interact with.
        evanelias 5y ago
        You're trivializing read-after-write consistency. It's actually quite a challenging problem for most applications, especially for anything web-based. Let's say you add a comment to a piece of content and then refresh the page. With a naively-implemented CRUD app, your comment disappears if your request's read query was routed to a lagging replica. This gets even trickier when multiple datacenters in different geographic regions are involved.
almog 5y ago
This article describes modulo as a possible strategy for sharding/partitioning a DBMS. While modulo (along with random allocation) has an instructional value as an introduction to partitioning, it's best followed by a warning when it comes rebalancing: it violates a basic requirement for most partitioning schemes (with the exception of distributed partitioned logs), that is, rebalancing better move as few items as possible.
- zeckalpha 5y ago
  It depends on how you do it. If you assign values to a ring and have nodes cover adjacent items on the ring (node A is primary for n, secondary for n+1 and n-1), then the rebalancing is much more efficient and can happen in the background.
  - almog 5y ago
    What you're describing is Consistent Hashing. It is a mean to _assign partitions to nodes_ and it is independent of how you partition your _data_ (though it's typically used with hash based partitioning of th edata). At any rate, the way I understood it, the article described partitioning of the data by `key mod k`, k being the current number of nodes.
AtlasBarfed 5y ago
Why would this be news? B+ trees do not scale, it's why log-structured merge trees were invented. Unfortunately, cross-table joins don't shard or scale well unless the data is well suited to a large scale sharding (like "customer" or "region". You'll probably start doing "data lake" type frameworks for join calculations across shards. If you are sharding a database due to scale that modern big machines can't handle, you'll probably want to move your largest tables to cassandra or dynamo or other purely scalable approaches. "fastest-developing verticals" ... sigh.
- _3u10 5y ago
  Scaling isn’t some kind of universal property. For many problems Cassandra doesn’t scale. For many problems log structured merge trees don’t scale.
  - ewuhic 5y ago
    Like for which exactly?
    - zbentley 5y ago
      Off the top of my head and in no particular order: - Super DELETE-heavy workloads. Tombstone cleanup can hurt. - Complex multidimensional indexes. Things that you'd use multiple semi-overlapping compound indexes on a single table for in a traditional RDBMS. - Operational pain of operating large Cassandra clusters. Recovering from multi-node failures while under high write load can be nail-biting as you watch the replication progress and the hint expiration "death clock" race for completion. - Even semi-frequent scans over huge (GBs in size) tables can cause unexpected hiccups in other ongoing CRUD operations. If the scans hit enough nodes/partitions, the impact on other workloads can be pervasive. - Emulating transactions (with an optimistic lock, full-cluster reads, and a cleanup program that expires interrupted transaction records) at scale can get costly. If you need this, cassandra was probably the wrong choice in the first place, but you asked for limitations and I have seen this one a couple of times.
      - 5y ago
nerdyadventurer 5y ago
Does any one know any helpful tool that can be used to make Postgres sharding easier?
- studmuffin650 5y ago
  Citus is probably the best way to do it today as it will handle most of the work for you.
  - rad_gruchalski 5y ago
    Why Citus and not, for example, YugabyteDB?
- cpitman 5y ago
  The author is a contributor to Apache ShardingSphere. https://github.com/apache/shardingsphere https://github.com/apache/shardingsphere
- brightball 5y ago
  Citus. Built from scratch to make this as easy as possible.
- winslett 5y ago
  By taking on a vendor's tool, I would argue you trade off ease for understanding. If you truly need to shard, it's best to move deliberately and potentially roll your own at the application level. Your understanding of the infrastructure is far more important than sharding for the sake of sharding.
- zozbot234 5y ago
  Postgres uses table partitions for sharding, potentially combined with FDW to a remote database. The Postgres docs describe these features.
aaomidi 5y ago
Yeah. That's why people shard. There's effectively no other reason to bring in that complexity.
zen_1 5y ago
Obligatory link: https://www.youtube.com/watch?v=b2F-DItXtZs https://www.youtube.com/watch?v=b2F-DItXtZs
daneel_w 5y ago
In other news, having two cash registers can make the lines shorter.

91 points by nsoonhui 5y ago

> Databases have been around for over 50 years. You might not think there’s anything left to innovate after all that time, but database fragmentation is one of the fastest-developing verticals in the tech industry. The complexity that characterizes existing data infrastructures seems to be only getting worse. Maybe I'm just getting salty, but why is there filler in this article. This isn't a recipe for garlic bread.

manigandham 5y ago
It's a basic intro article to sharding for people who are new to databases in general.
cpach 5y ago
At least they didn’t go on to describe in 300 words how their grandma’s homegrown DB engine was the best DB engine of them all ;-)
- bryanrasmussen 5y ago
  Growing up on the lower east side I was always torn between playing stick ball with my boys, or running home to hear about my mom's latest exploits. My mom was really my hero, because even as she took her fight for data sanitation out into the world she never lost site of building pure and clean relational engines from the ground up. Really, my mom wasn't just my hero but a hero to all the kids in the district. Heck, almost every year we would spend days on the playground instead of the hated classroom thanks to her ingenuity! But the best part of any day was when I was down in the sandlot with Butchie, Achmed, and Patty and my mom's voice would call down from our open kitchen window where she was baking and optimizing throughput "Bobby Tables, please accept these cookies I made for you and your friends in order to have energy for playing!" That's why I am happy to share with you my mom's recipes for cookies and secure storage of cookie related acceptance data. on edit: should be lost sight not lost site but will let it stand because hah.
- rzzzt 5y ago
  It is a dark and gloomy Wednesday night. Colin steps out of his beat-up Corolla, with the rain relentlessly (but unsuccessfully) trying to dissolve his leather jacket. He's getting close to 30 and is always telling his friends that he will have a weekend house with a koi pond in the backyard some day, but they laugh in response -- who cares about fish these days? Things are getting complicated at work. The app he's been working on for the past few months turned out to be a hit, new subscribers are appearing at every moment; in short, business is booming! They even got a column in the local newspaper, getting high praise for both form and functionality. A problem presents itself when too many people are trying to use the app at the same time - it looks like the database software can not keep up with the demands of this immense crowd. An impromptu meeting is held, and Colin is tasked with coming up with a solution. Getting closer to the front door, he thinks to himself, "How can we handle this incredible volume of traffic when it reaches the database cluster?", but his thoughts soon drift off again.
pwdisswordfish9 5y ago
The Overflow is a spam blog. That’s not even the worst of it: <https://meta.stackoverflow.com/q/412989/ https://meta.stackoverflow.com/q/412989/>
hkt 5y ago
Recipes should probably not have filler either (unless it is a recipe for pie)

i_like_waiting 5y ago

Does sharding work for splitting DWH tables? I guess it has to, but whats strategy there, if its mostly about "slicing and dicing" in every way possible. Maybe recent vs. historical data, or some known customer segments, extracted from previous analysis?

snissn 5y ago
Date partitions very good. Also other enums or even client ids can be great partitions (in addition simultaneously with date)
manigandham 5y ago
Sharding = partitioning and it's the only way data warehouses can scale to handle large datasets. Tables are partitioned on the primary key, usually with range-based sharding for flexibility. Some warehouses like BigQuery force this as a numeric key (dates by default) while others leave it up to you. Partitioning can also use multiple columns to help with more even distribution and/or better query performance.
- tremon 5y ago
  The technical difference between sharding and partitioning is at which level the split lies. If you're splitting the data storage within a single database, it's called partitioning. If you're splitting the data across multiple databases instead, it's sharding. But you can't implement either if you haven't logically partitioned your data first.
- i_like_waiting 5y ago
  yeah, partitioning is very good at almost any level, with very few exceptions. Sharding on other hand is pure tradeoff, I haven't looked into hard, but I guess 1 computer, where database sits, cannot have unlimited RAIDs of hard drives. So question is, should I push for 1 server only as long as possible, or when its better to shard between multiple?
thejosh 5y ago
How big your data is also dictates your strategy. And also your software. What works for say snowflake night not work for others

deleted 5y ago

[deleted]

donatj 5y ago

Before Aurora popped up and saved the day, we were looking at sharding our MySQL database. The levels of added complexity were keeping me up at night. I am sure we’ll reach a point where Aurora doesn’t solve it anymore but until then.

manigandham 5y ago
Using Aurora is analogous to vertical scaling (as in scaling up with bigger and bigger hardware). Distributed relational databases have made tremendous progress in recent years and you have many compelling options now if you need it. For MySQL there's SingleStore (memsql), Vitesse/Planetscale, and TiDB.
- matwood 5y ago
  Aurora also horizontally scales with readers.
  - zinodaur 5y ago
    Replication lag can make that pretty tricky - you never know if the client doing the reading has seen a state further in the future than the replica
    - daneel_w 5y ago
      That's not a concern unless your application is broken. An application should stick to one DB instance for one and the same isolated task, not open multiple parallel connections to interact with.
      - evanelias 5y ago
        You're trivializing read-after-write consistency. It's actually quite a challenging problem for most applications, especially for anything web-based. Let's say you add a comment to a piece of content and then refresh the page. With a naively-implemented CRUD app, your comment disappears if your request's read query was routed to a lagging replica. This gets even trickier when multiple datacenters in different geographic regions are involved.

almog 5y ago

This article describes modulo as a possible strategy for sharding/partitioning a DBMS. While modulo (along with random allocation) has an instructional value as an introduction to partitioning, it's best followed by a warning when it comes rebalancing: it violates a basic requirement for most partitioning schemes (with the exception of distributed partitioned logs), that is, rebalancing better move as few items as possible.

zeckalpha 5y ago
It depends on how you do it. If you assign values to a ring and have nodes cover adjacent items on the ring (node A is primary for n, secondary for n+1 and n-1), then the rebalancing is much more efficient and can happen in the background.
- almog 5y ago
  What you're describing is Consistent Hashing. It is a mean to _assign partitions to nodes_ and it is independent of how you partition your _data_ (though it's typically used with hash based partitioning of th edata). At any rate, the way I understood it, the article described partitioning of the data by `key mod k`, k being the current number of nodes.

AtlasBarfed 5y ago

Why would this be news? B+ trees do not scale, it's why log-structured merge trees were invented. Unfortunately, cross-table joins don't shard or scale well unless the data is well suited to a large scale sharding (like "customer" or "region". You'll probably start doing "data lake" type frameworks for join calculations across shards. If you are sharding a database due to scale that modern big machines can't handle, you'll probably want to move your largest tables to cassandra or dynamo or other purely scalable approaches. "fastest-developing verticals" ... sigh.

_3u10 5y ago
Scaling isn’t some kind of universal property. For many problems Cassandra doesn’t scale. For many problems log structured merge trees don’t scale.
- ewuhic 5y ago
  Like for which exactly?
  - zbentley 5y ago
    Off the top of my head and in no particular order: - Super DELETE-heavy workloads. Tombstone cleanup can hurt. - Complex multidimensional indexes. Things that you'd use multiple semi-overlapping compound indexes on a single table for in a traditional RDBMS. - Operational pain of operating large Cassandra clusters. Recovering from multi-node failures while under high write load can be nail-biting as you watch the replication progress and the hint expiration "death clock" race for completion. - Even semi-frequent scans over huge (GBs in size) tables can cause unexpected hiccups in other ongoing CRUD operations. If the scans hit enough nodes/partitions, the impact on other workloads can be pervasive. - Emulating transactions (with an optimistic lock, full-cluster reads, and a cleanup program that expires interrupted transaction records) at scale can get costly. If you need this, cassandra was probably the wrong choice in the first place, but you asked for limitations and I have seen this one a couple of times.
    - 5y ago

nerdyadventurer 5y ago

Does any one know any helpful tool that can be used to make Postgres sharding easier?

studmuffin650 5y ago
Citus is probably the best way to do it today as it will handle most of the work for you.
- rad_gruchalski 5y ago
  Why Citus and not, for example, YugabyteDB?
cpitman 5y ago
The author is a contributor to Apache ShardingSphere. https://github.com/apache/shardingsphere https://github.com/apache/shardingsphere
brightball 5y ago
Citus. Built from scratch to make this as easy as possible.
winslett 5y ago
By taking on a vendor's tool, I would argue you trade off ease for understanding. If you truly need to shard, it's best to move deliberately and potentially roll your own at the application level. Your understanding of the infrastructure is far more important than sharding for the sake of sharding.
zozbot234 5y ago
Postgres uses table partitions for sharding, potentially combined with FDW to a remote database. The Postgres docs describe these features.

aaomidi 5y ago

Yeah. That's why people shard. There's effectively no other reason to bring in that complexity.

zen_1 5y ago

Obligatory link: https://www.youtube.com/watch?v=b2F-DItXtZs https://www.youtube.com/watch?v=b2F-DItXtZs

daneel_w 5y ago

In other news, having two cash registers can make the lines shorter.