Skip site navigation (1) Skip section navigation (2)

Modeling a table with arbitrary columns

From: Andreas Hartmann <andreas(at)apache(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Modeling a table with arbitrary columns
Date: 2009-10-29 20:52:33
Message-ID: hccvai$v2v$1@ger.gmane.org (view raw or flat)
Thread:
Lists: 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


Responses

pgsql-performance by date

Next:From: Thomas KellererDate: 2009-10-29 21:24:26
Subject: Re: Modeling a table with arbitrary columns
Previous:From: Scott MarloweDate: 2009-10-29 16:59:48
Subject: Re: database size growing continously

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group