From: James Cloos <cloos@jhcloos.com>
To: pgsql-sql@postgresql.org
Subject: Two tables or three?
Copyright: Copyright 2005 James Cloos
Date: Sat, 29 Oct 2005 09:38:08 -0400
Message-ID: <m33bmkzbnj.fsf@lugabout.cloos.reno.nv.us>
Lines: 38
User-Agent: Gnus/5.110004 (No Gnus v0.4) Emacs/23.0.0 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

I'm designing a schema for an app that requires two large blobs per
row.  Given that querying does not hit those, I presume it is
beneficial to move the blobs out of the main table.

But should each blob be in its own table, or should they go into a
single, three column table?

The main table does need a sequential primary key column, so I expect
to use that value to reference the blobs by using it as a primary key
in the blobs' table(s).

Each row will have an associated first_blob, virtually all of them
with also have an associated second_blob.

The usage scenario has the user doing some queries on the data in
the main table and manually selecting one or more rows at a time;
upon selection the app will need to grab the blobs and show those.
Most of the time both will be grabbed by interactive users, but
noninteractive clients may only care about the first blob.

The blob table(s) will need to CASCADE row deletions from the
first table.  I expect to have the ui flag deletable rows and
use a vacuum-like process to do the actual deletions.  (In part
this will give some opportunity to 'un-delete', but I also presume
a performance benefit.  Yes?)

Given that usage pattern, is there any benefit for one or two
blob-specific table(s)?  Or for that matter any benefit for
splitting them out at all?

This will be on 8.1.  (Devel work is being done on the current
beta.)

Thanks,

-JimC
-- 
James H. Cloos, Jr. <cloos@jhcloos.com>