X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 46F70B4398A for ; Wed, 9 Jul 2003 22:14:47 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 01375-06 for ; Wed, 9 Jul 2003 19:14:14 -0300 (ADT) Received: from charon.simplot.com (jrsmailhub.simplot.com [12.18.144.212]) by svr1.postgresql.org (Postfix) with ESMTP id D4AB2B430C1 for ; Wed, 9 Jul 2003 19:14:13 -0300 (ADT) Received: from npa01zz001 (10.62.26.12) by charon.simplot.com (MX V5.3 AnGp) with ESMTP; Wed, 9 Jul 2003 16:13:58 -0600 Received: from jearl by npa01zz001 with local (Exim 3.36 #1 (Debian)) id 19aNCG-0000Wp-00; Wed, 09 Jul 2003 16:13:56 -0600 To: Michael A Nachbaur Cc: Cliff Wells , pgsql-sql@postgresql.org Subject: Re: Home-brewed table syncronization References: <200307091414.19959.mike@nachbaur.com> <1057786137.15508.29.camel@software1.logiplex.internal> <200307091450.32440.mike@nachbaur.com> From: Jason Earl Date: Wed, 09 Jul 2003 16:13:56 -0600 In-Reply-To: <200307091450.32440.mike@nachbaur.com> (Michael A. Nachbaur's message of "Wed, 9 Jul 2003 14:50:32 -0700") Message-ID: <87y8z7jql7.fsf@npa01zz001.simplot.com> User-Agent: Gnus/5.1002 (Gnus v5.10.2) Emacs/21.3 (gnu/linux) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/111 X-Sequence-Number: 14225 Michael A Nachbaur writes: > On Wednesday 09 July 2003 02:28 pm, Cliff Wells wrote: >> On Wed, 2003-07-09 at 14:14, Michael A Nachbaur wrote: >> > So, I'm looking at syncronizing 4 tables from one master database to >> > several child databases. I'm thinking of doing the following with >> > DBD::Multiplex: >> > >> > DELETE FROM TableA; >> > INSERT INTO TableA (..) VALUES (...); >> > .... >> > >> > on all the child databases, but I'm not sure what kind of impact >> > this would have on my servers. My impression is that this would >> > hammer the indexes, and might blow any memory optimization out >> > the window. Only a few records in my dataset will change from >> > time-to-time, but just the process of determining what is >> > different may take more effort than simply rebuilding. >> >> Keep a timestamp associated with each record. Only update the records >> with timestamps later than your last sync. > > I'm dealing with an existing database structure that, though I can > change it, has a lot of impact on the rest of my infrastructure. If > I can find a way of doing this without resorting to timestamps, I'd > much rather do it that way. Would it be possible to add another table (changelog) that contained the primary key of the record that has changed and a timestamp? The changelog table could be maintained via triggers (on update, insert, or delete). Your synchronization software could then be relatively simple. It would simply need to check your changelog table for rows that have changed. Jason