Sharding, and all that

[Richard Huxton]

Andy Wardley wrote:
> Richard Huxton wrote:
>> Yep - that's what "sharding" is all about - separate disconnected silos
>> of data. 
> 
> I thought sharding specifically related to horizontal partitioning.  i.e.
> splitting one table across several databases,  e.g. records with even row
> ids in one DB, odd in another.  Apologies if my terminology is wrong.
> 
> I was thinking more specifically about vertical partitioning along the
> functional boundaries which wouldn't be sharding by my (possibly incorrect)
> definition.  Apologies for being off-topic, too  :-)

If "sharding" means anything at all, then it has to be something other
than partitioning or partial replication, otherwise we could say
"partitioning" or "partial replication". Of course it's entirely
possible it *doesn't* mean anything at all, and is just partitioning2.0

>> Then you're not maintaining referential integrity. There's no point in
>> having a user-id that doesn't *mean* anything. 
> 
> I'm not suggesting that the user id doesn't mean anything.  It means
> exactly the same thing as it does in any other database - a unique
> identifier to a user record.
> 
> I'm saying that the user record in the message DB doesn't need to
> store email address, username, password, favourite colour, challenge
> question/response, and all the other cruft that goes with user
> administration.
> All it needs is a unique id, an authentication realm (identifying the
> authentication service) and authentication token (identifying the user
> at that service).  And perhaps any message-board specific niceties such
> as your local nick and display preferences.
> 
> Similarly in the authentication DB there's no need to store information
> in the users table relating to message board preferences.

Having one 300-column user_details table is probably a bad idea anyway.
However, if you can't say uid=123456 on your message is the same as
uid=123456 in your user-login table then I'd say it doesn't mean
anything. You can't even say two messages posted by uid=123456 are the
same user without providing guarantees about the security of your uid
transfer code (which is back to providing your own relational integrity).

> I can see that trivially splitting one user table into two leads to all
> sorts of integrity problem.  But I'm thinking of them as two separate
> user databases from the outset and accepting that they're potentially
> disjoint.
> 
> The best (but poor) example I can think of right now is how my gravatar
> popped up automatically when I signed up at github.  Not because the github
> user database is referentially at one with the gravatar database, but
> because
> I used the same public identifier (my email address) on both systems.
> 
> So it could be argued that there is *a* point in having a user id (such
> as email address) that doesn't *mean* anything to the *current* database,
> because it might have meaning to *other* databases.  It's the closest
> thing we've got to referential integrity in a distributed world.

Yep, but that's option #1 - "don't care". If your gravatar didn't work,
or you got the wrong one, well whatever.

Option #2 would be something like OpenID where you can't (and dont' want
to) have a single large database accessed by everyone. So - you have to
build in trust mechanisms. Or GPG key-signing say.

>> Primary keys, foreign
>> keys and all the other bits and pieces of RI in a SQL database are there
>> to maintain the *meaning* of your data.
> 
> Sure, I recognise the fact that you lose referential integrity at the
> boundary between your db and the "outside world".  But internally,
> the DB remains referentially intact.  The message board still has its
> own user records for messages to reference.  The fact that the
> authentication
> realm/token may at some point in the future become invalid is really no
> different to the current situation where a user's email address changes
> and they can no longer login or get a password reminder/reset.

No, there's a vital difference. If I want to I can track every change a
user makes to their account, so long as I can trust the uid. Barring
errors in the RDBMS code it's a relatively simple task for me to
guarantee that. If the police come knocking because someones posted
threatening messages or whatever then once they've got their warrant
they can have their data. Of course maybe it doesn't matter, but that's
putting you back in #1 - "don't care".

>> Before that though, make sure you do have a problem. Pick the right tool
>> for the job - if high concurrency/complex queries/procedural code for
>> constraints is a requirement then it's probably not MySQL. Always
>> consider what an extra couple of grand on hardware will gain you.
> 
> For this particular project we don't really have a database problem that
> can't be solved with a bit of replication and a whack of the hardware
> hammer.
> The majority of the load will be searches that we're reading from a single
> read-only table.  So we can replicate that easily across as many web
> heads as
> required for performance and it gives us simple redundancy in case of
> machine
> failure, etc.
> 
> All the read/write functionality (mostly CMS-like stuff) will happen
> (initially) on a single server with master read/write database.  It'll
> be fairly low-load and it's not mission critical (in the sense that having
> the CMS offline for a few hours is an inconvenience not a hanging offence).

If it's a low load of writes, replicate the lot and manually failover if
required.

> The impetus was more about turning one large and tangled CMS-like system
> into
> several smaller, simpler systems.  90% of the tables fall neatly into one
> sub-system or the other (front-end search and content delivery, back-end
> content management, image management, accounting and billing, and so on).
> It's the last few  tables (mostly relating to users, unsurprisingly) that
> straddle spread-eagled across the database dangling their tools in the
> works.

Schemas.

http://www.postgresql.org/docs/8.3/static/ddl-schemas.html
http://www.postgresql.org/docs/8.3/static/manage-ag-overview.html

I tend to have a setup like:

lookups
  user_types
  topic_categories
pages
  page_headers
  page_bodies
  access_rights
images
  ...
users
  user_logins
  user_prefs
system
  settings
  activity_log

Separate creation / update scripts for each module, along with some
utility scripts to apply different rights to different schemas. Make
sure your different apps connect as different users to control what they
see.

Throw in the lovely new pgtap to integrate with your Perl testing framework.

http://pgtap.projects.postgresql.org/

> So I'm really coming at this from the perspective of someone thinking about
> building distributed apps and wondering how the database is going to work
> behind the scenes, rather than someone with a database performance problem
> considering partitioning.

-- 
  Richard Huxton
  Archonet Ltd