Well, then please help me find a better design cause I can't see one...
what we have here is a big "membership" table of email lists. When
there's a sendout then the memberships of the affected group are heavily
read/updated, otherwise they are idle. None of the memberships is
archive data, they are all active data... the only problem is that they
are so many. Is it so hard to believe that >100 million rows is all
active data, but only used in bursts once per week (that's an example,
some groups are more active, others less) ?
I suppose you have a table memberships (user_id, group_id) or something
like it ; it should have as few columns as possible ; then try regularly
clustering on group_id (maybe once a week) so that all the records for a
particular group are close together. Getting the members of a group to
send them an email should be faster (less random seeks).
For tables with very few small fields (like a few integers) the
26-something bytes row overhead is significant ; MySQL can be faster
because MyISAM tables have no transaction support and thus have very
little things to store besides actual row data, and the table can then fit
in RAM...
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
