Norman Dunbar wrote:

> Morning Lester,
>
>> My own data from sites is slowly growing and has 10+ years of history much of
>> which will never be read again, but needs to be available, so I've started
>> moving the historic stuff to a second database which is read only except for an
>> 'archive' cycle perhaps once every 6 months. This can then take advantage of the
>> read only mode and in my case a USB stick works as the off-line storage. That
>> feels like the right way to develop things, but which might benefit from better
>> 'cross database' facilities? With bulk data static on an SSD array, while the
>> active stuff is in memory?
>
> Sounds a bit like you are thinking of something like Oracle Tablespaces.
> These are used to separate distinct sets of tables (and/or indices)
> according to the needs of the application(s) running on that particular
> database.
>
> Your setup sounds like it would benefit from a couple - active and
> archive. You'd create the files for the active tables on read-write
> media while the archive stuff would be created as read-write, but run
> off the SSDs in read-only mode.
>
> When creating a table, you simply add "tablespace<whatever>" to the
> create table statement, and it will be created in the correct place.
>
> I have no idea how easy/hard/sane it would be to add this sort of thing
> to Firebird of course! ;-)

AND - Yes Norm that sounds about right! In my case only todays activity is added
and updated, so there is no need ever to edit the 'history', but it would be
nice to be able to simply display a visitors past activity quickly from a read
only area. I'm sure the same applies for many people? Actually one of the
customer requirements is that history should NOT be alterable so no one can
'adjust' the figures to meet performance targets :)

A thought just popped up ... with some fairly simple replication, the 'active'
database might be able to 'insert' into the read only? If we are only adding to
the end of a list controlled by a generator sourced ID then the indexes should
just 'insert' at the end? But of cause I'm forgetting that we need to update
more than just the primary index :(
We need fast access to the various 'person' id fields in the history ...
So an 'archive' cycle - which is not a problem in my case since this is not a
24/7 operation - does need to be run ...

It is getting to the point where I need two databases, since even compressed the
site data is growing fast, but I only need to download the active stuff and then
run an archive cycle on site and here at the off site archive ...

--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php