Modeling a table with arbitrary columns - Mailing list pgsql-performance

Hi everyone,

I want to model the following scenario for an online marketing application:

Users can create mailings. The list of recipients can be uploaded as
spreadsheets with arbitrary columns (each row is a recipient). I expect
the following maximum quantities the DB will contain:

* up to 5000 mailings
* 0-10'000 recipients per mailing, average maybe 2000
* approx. 20 columns per spreadsheet

I see basically two approaches to store the recipients:

A) A single table with a fixed number of generic columns. If the
spreadsheet has less columns than the table, the values will be null.

CREATE TABLE recipient (
   mailing integer,
   row integer,
   col_1 text,
   …
   col_50 text,
   PRIMARY KEY (mailing, row),
   FOREIGN KEY mailing REFERENCES mailing(id)
);


B) Two tables, one for the recipients and one for the values:

CREATE TABLE recipient (
   mailing integer,
   row integer,
   PRIMARY KEY (mailing, row),
   FOREIGN KEY mailing REFERENCES mailing(id)
);

CREATE TABLE recipient_value (
   mailing integer,
   row integer,
   column integer,
   value text,
   PRIMARY KEY (mailing, row, column),
   FOREIGN KEY mailing REFERENCES mailing(id),
   FOREIGN KEY row REFERENCES recipient(row)
);


I have the feeling that the second approach is cleaner. But since the
recipient_value table will contain approx. 20 times more rows than the
recipient table in approach A, I'd expect a performance degradation.

Is there a limit to the number of rows that should be stored in a table?
With approach B the maximum number of rows could be about 200'000'000,
which sounds quite a lot …

Thanks a lot in advance for any suggestions!

Best regards,
Andreas



--
Andreas Hartmann, CTO
BeCompany GmbH
http://www.becompany.ch
Tel.: +41 (0) 43 818 57 01