Re: Possibilities of PgSQL

From: Karel Břinda <konference(at)brinda(dot)info>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Possibilities of PgSQL
Date: 2007-08-24 08:12:29
Message-ID: 1187943149.3153.0.camel@karkulka
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin


> 100MB of text in each row?? is each row a copy of the english
dictionary?
>

No, that was only example.

It will be web application (Python + PgSQL). I thought about table with
records + table with changes but is it some easy way how to get
differences between 2 strings?

Shane Ambler píše v Čt 23. 08. 2007 v 22:21 +0930:
> Karel Břinda wrote:
> > Hi,
> >
> > I have a question about PgSQL. I am working at some project and I
want
> > to have few tables with special properties:
> >
> > There would be many rows and these would be changed every day for
many
> > times. I want to be able to get know how did the row looked in given
> > time (f.e. 2 day ago, 6 minutes ago, 2nd Sept. 2002,...).
Nevertheless
> > it should work with diffs (f.e. if I had a row with 100MB string and
I
> > changed only 2 chars it should not require on disk 200MB but only
100MB
> > + few (kilo)Bytes).
>
> 100MB of text in each row?? is each row a copy of the english
dictionary?
>
> My thoughts are to have a table that records the changes, something
> along the lines of a timestamp of the change with substring start and
> end positions of the change with the before and after text that has
> changed that can be used to 'replay' the changes.
>
> When you want to rewind to a certain time you find all entries after
the
> given time and undo the changes to get back to where it was.
>
> > Is there any possibility how to solve it? I have heard that I can do
it
> > with triggers (but the worst thing is how to implement diffs) but I
hope
> > that there is any other (easier) way.
>
> Triggers are the most reliable way to implement this, I would think
that
> pl/perl may be the fastest way to implement it as a trigger.
>
> Basically you would need to compare before and after as the record
was
> saved - doing that with 100MB of data will make saving changes
somewhat
> slower.
>
> What sort of client is updating this data?
> I would think that the client could keep track of changes as they are
> typed and save this info with the updated data thus removing the time
to
> compare the before and after data at the DB end.
>
>

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Peter Eisentraut 2007-08-24 11:59:43 Re: tar, but not gnu tar
Previous Message Mikko Partio 2007-08-24 04:18:12 Re: Warm-Backup configuration question