Re: records chaining

You may want to check out the following article from PostgreSQL General Bits
that seems to relate directly to your question:

Distributed Indexing with Table Inheritance -
http://www.varlena.com/varlena/GeneralBits/98.php:

"The best example of this is a table which grows in time. Suppose you have a
log table of customer support calls that you keep over a very long period of
time. The application requires 1) most queries are on the current time set
and 2) occassional queries are required for the entire data set. You can
create a parent table and one child table of current information and other
child tables populated with archived data. The child tables look exactly
like the parent table in this case. "

Regards,
George

----- Original Message -----
From: "tövis" <tovises(at)freemail(dot)hu>
To: "pgsql novice" <pgsql-novice(at)postgresql(dot)org>
Sent: Wednesday, April 20, 2005 3:36 AM
Subject: Re: [NOVICE] records chaining

> I'am migrating from "single-user" database (Clarion - TopSpeed) to
> PostgreSQL. I'am newbie not only in PostgreSQL but in SQL also.
> With previouse database handling it was important to keep database files
> relativelly short "chunks":
> - single-user is always draw hole database file to the client;
> - archiving is very comfortable - 500 Mbyte pen drive was enough to draw
> all news;
> - now speed penalty for growing database files (I met SQL system where
> every year
> datas should be moved to an "archive", because of the painfull speed -
> not PostgreSQL).
> The system, what I planning is receiving short binary messages from some
> thousend of different sources, after converted to hex ASCII it's about 30
> Mbyte of ASCII string, in old system, in a quarter received about
> 8,000,000 messages - records.
> I would like to automate every year moving the database, keep it that way
> to get "archive" from the same application, to give the user easy way to
> get "messages" from 5 or more years old.
> The hardware is not choosen. I will do that, but of course I need to keep
> a realistic budget, and the accent should be on reliability, based not on
> clustering but on flexibility. That means I need keep large database in
> easy to handle size(s) - mirrored...
> The record "chaining" is need to keep track changing of sources database,
> where always are large fluctuation and modification - some hundred of
> records in a month. I've should track what and who and when changed the
> record!
>
> ----- Original Message -----
> From: "Jeff Eckermann" <jeff_eckermann(at)yahoo(dot)com>
> To: "tövis" <tovises(at)freemail(dot)hu>; <pgsql-novice(at)postgresql(dot)org>
> Sent: Tuesday, April 19, 2005 3:59 PM
> Subject: Re: [NOVICE] records chaining
>
>
>> This message, along with your previous one, suggests
>> that you are trying to limit the size of your tables.
>> Is there any good reason for this? How many records
>> do you expect to have? What hardware will you be
>> running on?
>>
>> --- tövis <tovises(at)freemail(dot)hu> wrote:
>>> I need record changing history. Of course I need to
>>> keep old records, and some how point to it for show
>>> changes. First of my idea is to make a single column
>>> in every record pointing to his "parent" but after I
>>> started thinking how SQL can handle it. Whats your
>>> opinion, is it possible to write a recursive SELECT
>>> to get all records back to that whats parent
>>> reference has no reference (NULL)? At this moment I
>>> would be happy to know that is it possible - I'm
>>> only planning and migrating an old database, the
>>> coding seem to be "far" away.
>>> Thanks in advance
>>> Gábor Rózsa
>>>
>>>
>>
>>
>>
>> __________________________________
>> Do you Yahoo!?
>> Plan great trips with Yahoo! Travel: Now over 17,000 guides!
>> http://travel.yahoo.com/p-travelguide
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>