From: James Cloos <cloos@jhcloos.com>
To: pgsql-sql@postgresql.org
Subject: Two tables or three?
Date: Sat, 29 Oct 2005 09:38:08 -0400
Message-ID: <m33bmkzbnj.fsf@lugabout.cloos.reno.nv.us> (raw)
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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cloos@jhcloos.com
Subject: Re: Two tables or three?
In-Reply-To: <m33bmkzbnj.fsf@lugabout.cloos.reno.nv.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox