Re: Spec discussion: Generalized Data Queue / Modification Trigger

On Wed, 2010-03-03 at 13:43 -0800, Josh Berkus wrote:
> > I't may seem easy to replace a database table with "something else" for
> > collecting the changes which have happened during the transaction, but
> > you have to answer the following questions:
> >
> > 1) do I need persistence, what about 2PC ?
> >
> > 2) does the "something else" work well for all situations an event table
> > would work (say, for example, a load of 500GB of data in one
> > transaction)
>
> Those are good questions, and a generic system would need to work for
> all three of those requirements.
>
> > 3) what would I gain in return for all the work needed to implement the
> > "something else" ?
>
> Speed. In my test case, which was replicating view snapshots between
> PostgreSQL and Redis, the difference between using an event table and
> perverting the constrainttriggers to do an after-insert trigger directly
> to redis was a speed difference of around 400%, not counting vacuum
> overhead.

What do you mean by "speed difference" ?

Lag ?

Or the DMS speed the system could keep up with ?

Or something else ?

> >>>> (3) A method of marking DDL changes in the data modification stream.
> >
> > Yes, DDL triggers or somesuch would be highly desirable.
> >
> >>> Hmm..can you expand on what you have in mind here? Something more than
> >>> just treating the DDL as another item in the (txn ordered) queue?
> >> Yeah, that would be one way to handle it. Alternately, you could have
> >> the ability to mark rows with a DDL "version".
> >
> > But the actual DDL would still need to be transferred, no ?
>
> Yes. It may be that having a ddl change simply inserted into the
> replication stream is the way to go. Alternatively, DDL versioning
> makes a certain amount of sense except that it's pretty hard to make
> generic, and would require additional catalog tables.

But what would DDL versioning _gain_ ? I assume that you just have to
stop and wait for the new version of DDL to arrive, once your DML stream
switches to new DDL version ?

--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training