Re: Transaction Speed and real time database - Mailing list pgsql-hackers

Hi,

On Monday 24 July 2006 10:33, Csaba Nagy wrote:
> [please use "reply to all", otherwise you'll have what you just had: the
> guy who you write goes home for the weekend and all the rest of the
> people on the list who would answer you won't know there is soemthing to
> answer...]
>
> On Fri, 2006-07-21 at 13:39, moises wrote:
> > Sorry if I can't explain me clear.
> > I want to use an extension of postgres, (because I'm very clear that
> > postgres is not a real time database.)
> > I want to modify some modules of postgres for this convenience, for
> > example, add some time constrains to transactions, I know that is a very
> > hard work, Our Applications are 100% real time works, controlling
> > industrial process, and not OLAP or OLTP applications.
> >
> > My question is how many fast are postgres in transaction actually?
> > Because maybe we don't need to work, in looking for more speed, just
> > constrains and planners.
>
> I have to admit RT is for me just a vague idea I still remember from
> some courses, but I'm not sure if RT and transactional is compatible at
> all... do you really need a transactional data base for RT applications
> ? Cause postgres is just that, an excellent transactional DB, which BTW
> is fast enough for transactional work (where you expect that sometimes
> operations fail due to others working in parallel). I'm not sure in
> industrial RT applications you could afford failures due to
> concurrency... and in that case you would be better off using something
> non-transactional - but this is just my limited understanding of the
> problem. Others on this list might know better...
>
> Cheers,
> Csaba.
>

As someone 'from the industry' I have to chime in here. IMHO there is no way 
to make PostgreSQL itself RT capable - not for a long time. 
The following items (at least) are roadblocks:

- index tree growth (undeterministic in time and duration)
- need to vacuum (for FIFO applications)
- database file growth (also undeterministic, but can probably be worked 
around by initializing the database with the max. no. of records, then 
deleting and vacuuming without -f. But this requires FSM to be large enough, 
and still there is the need for frequent vacuum).

OTOH, one has to be very careful to not mix terms here. In industrial 
(production floor) applications, the term 'real time database' refers to 
soemthing completely different than a relational, transactional DB.

A 'real time database' in this scenario is something which keeps aggregates of 
data points and events over time, and this is usually the only type of data 
retrieval they allow for: 'give me channels x,y,z from start to end time'. 
The prime property of such an RTDB is constant-time inserts, and priorization 
of access paths. Normally they work in embedded controllers or on real-time 
OSes.

There are RTDBs which can also be seen as relational databases, but the design 
of these is usually the RT part with a relational 'addon'.  

Because of the features of a full-fledged relational database engine, 
engineers often wish they had one of those instead ;-). Usually, we solve 
this with some sort of streaming 'frontend', which buffers the data flow 
(usually to disk) until it's inserted into the database. This lowers the 
real-time requirement to 'has to be fast enough on average'. We have several 
of these types of applications in production at various customers, some for 
6+ years continuously (using PostgreSQL 7.0!).

Hope this clears up the issues somewhat for 'normal' database people.

Greetings,Jörg
-- 
Leiter Softwareentwicklung - S.E.A GmbH
Mail: joerg.hessdoerfer@sea-gmbh.com
WWW:  http://www.sea-gmbh.com