Re: webmail user mailbox schema design?

From: ddf <oratune_at_msn.com>
Date: Fri, 1 May 2009 09:37:16 -0700 (PDT)
Message-ID: <f44652b0-b7e6-4cc1-94c7-6a01a9e3f79e_at_o20g2000vbh.googlegroups.com>



On May 1, 11:06 am, meranayag..._at_yahoo.com wrote:
> Hi Folks,
>  Does anyone have any insight into what user mailbox stores look like
> for yahoo, gmail, hotmail, etc? I am curious if they have, e.g. a
> mailbox table for each user that contains all their email or whether
> they have a single large table with email for all users or whether
> they have a smaller subset of tables with the users distributed
> between them? Each user can have a large number of messages, so a
> single table would end up having billions of records (for one of these
> webmail systems with millions of users) and likewise you wouldn't want
> separate tables for each user because then you'd have millions of
> tables. Any thoughts? Thanks.
> Sean

Have you considered using a single, partitioned table for your messages? It's likely you'll have an ACCOUNTS table to identify unique email addresses, an ACCOUNT_DETAILS table to contain profile information and a MESSAGES table linked to ACCOUNTS via the user_id. Partitioning the MESSAGES table on user_id could provide speed and manageability by segregating each users messages to a single partition; partition pruning would eliminate visits to unrelated partitions effectively reducing the data set to a fraction of the total number of messages stored.

It's not a directive, just a thought possibly worth considering.

David Fitzjarrell Received on Fri May 01 2009 - 11:37:16 CDT

Original text of this message