Обсуждение: History Tables Vs History Field

I have a table that will recieve about 2000 inserts per day,  Although
it will technically never be dead data, about 99.999% of it will be
uninteresing after 30-40 days,  My problem is that I dont know if I
should create a new table that is a history table or add a indexed field
and ignore the data in queries unless someone asks for it. The latter is
my prefered way of dealing with it, Is there something Im missing? Is
there another way to do this?
Any Suggestions are appreciated.

Bart McFarling
p.s. Sorry about sending the Blank email earlier

Bart McFarling wrote:

> I have a table that will recieve about 2000 inserts per day,
> Although it will technically never be dead data, about 99.999% of
> it will be uninteresing after 30-40 days,  My problem is that I
> dont know if I should create a new table that is a history table
> or add a indexed field and ignore the data in queries unless
> someone asks for it. The latter is my prefered way of dealing with
> it, Is there something Im missing? Is there another way to do
> this? Any Suggestions are appreciated.

I guess if 99% of your queries would only ever use the "interesting"
data, you should go for the "history" version, which would speed your
those queries a lot. As you have distinct id's on both tables (having
fetched from the same sequence initially), u can always do a union
select on both tables in the rare cases this is needed.

HTH, just my 2c
Erwin Brandstetter

> I have a table that will recieve about 2000 inserts per day,  Although
> it will technically never be dead data, about 99.999% of it will be
> uninteresing after 30-40 days,  My problem is that I dont know if I
> should create a new table that is a history table or add a indexed field
> and ignore the data in queries unless someone asks for it. The latter is
> my prefered way of dealing with it, Is there something Im missing? Is
> there another way to do this?
> Any Suggestions are appreciated.

Hello Bart,

We normally do this sort of thing by using a history table.  There's no
strong reason why we do it this way as such, except that it just seems a
logical separation.

Advantages are that the data (in the history table) is easier to
manipulate (e.g., archive) without disturbing users who are looking at the
current 'live' data.  Also, it's simple to query both live data & old
history data by using a union should we need to.

Normally, we setup a db rule that on deletion of a record from the live
table, copies it into the history table.  In this way, we don't have to
manage it at the application level.  In the app., we simply delete the
record when we're done with it & the db takes care of copying it into the
history table for us.  We use a rule like this:

-- This is an SQL script to define a rule for deletions on buffer_pallets.
-- It automatically copies the records in the buffer_pallets table into the
-- pallet_history table

\connect - postgres

drop rule del_buffer_pallets;

create rule del_buffer_pallets
as on delete to buffer_pallets
do insert into pallet_history
select * from buffer_pallets
where pallet_urn = OLD.pallet_urn;

It's simple & clean.  You can setup a cron job to clear out data older
than n days etc.

Hope this helps.

Deep Joy - Paul

--
Paul M. Breen, Software Engineer - Computer Park Ltd.

Tel:   (01536) 417155
Email: pbreen@computerpark.co.uk

---------------------------------------------------------

This private and confidential e-mail has been sent to you
by Computer Park Ltd.

If you are not the intended recipient of this e-mail and
have received it in error, please notify us via the email
address or telephone number below, and then delete it from
your mailbox.

Email: mailbox@computerpark.co.uk

Tel: +44 (0) 1536 417155
Fax: +44 (0) 1536 417566

Head Office:
Computer Park Ltd, Broughton Grange, Headlands, Kettering
Northamptonshire NN15 6XA

Registered in England: 3022961.

Registered Office:
6 North Street, Oundle, Peterborough PE8 4AL

=========================================================