Thread: Two tables or three?

Two tables or three?

From
James Cloos
Date:
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>


Re: Two tables or three?

From
Tom Lane
Date:
James Cloos <cloos@jhcloos.com> writes:
> 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.

You shouldn't contort your schema artificially in order to do that.

Postgres automatically stores wide fields out-of-line, so if "large"
means "more than a couple KB after compression" then the system will do
this behind the scenes and there's no need for you to do it.  See
http://developer.postgresql.org/docs/postgres/storage-toast.html
        regards, tom lane


Re: Two tables or three?

From
James Cloos
Date:
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

Tom> You shouldn't contort your schema artificially in order to do that.

Tom> Postgres automatically stores wide fields out-of-line ...

Cool.  Good to know it is even better than I thought. :)

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