[kenjackson]

I'm sure I missed something, but this doesn't guarantee unique keys across the database does it?

What if you have two tables with the same autoincrement value being updated at the same millisecond by two users with the same UserID%NumShards?

Or is there some relationship between physical and logical shards that makes this impossible?

[mikeyk]

Hey Ken, (author here)

There's a 1:1 mapping between the user's hash modulo the number of shards, and the table they're writing to. So, if we had 1000 logical shards, we have 1000 schema/tablespaces, with the same tables in each. And the database's own 'nextval()' feature makes sure never have the same ID twice. Hope that clarified things.

[speleding]

I understand 1000 logical shards is probably a good number to use with PostgreSQL. I'm currently looking at doing something like this myself but I was thinking that it would be so much more elegant if each user could live in his own shard.

Does anybody know of a database product / solution that will handle hundred thousands of shards? I don't have a need for joins across shards, each user account only uses it's own data. And data and access within a shard would be very small, a SQLlite instance for every user would be a theoretical solution. The idea would be to have one (in memory) table to connect the app to the correct shard for the user and then get "perfect" horizontal scaling. Is anybody doing this?

[kenjackson]

Yep, that clarifies it. Thanks. And thanks for write-up. This is the type of stuff I love to see posted on HN.