X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3C1CC56607; Fri, 11 Mar 2005 20:53:00 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 69182-01; Fri, 11 Mar 2005 20:52:55 +0000 (GMT) Received: from dfw-gate4.raytheon.com (dfw-gate4.raytheon.com [199.46.199.233]) by svr1.postgresql.org (Postfix) with ESMTP id B8F4156609; Fri, 11 Mar 2005 20:52:53 +0000 (GMT) Received: from ds02c00.directory.ray.com (ds02c00.directory.ray.com [147.25.138.118]) by dfw-gate4.raytheon.com (8.12.10/8.12.10) with ESMTP id j2BKpKoH010713; Fri, 11 Mar 2005 14:51:25 -0600 (CST) Received: from ds02c00 (localhost [127.0.0.1]) by ds02c00.directory.ray.com (Switch-3.1.4/Switch-3.1.0) with ESMTP id j2BKpF1D014184; Fri, 11 Mar 2005 20:51:15 GMT Received: from ds02c00.directory.ray.com with LMTP by ds02c00 (2.0.6/sieved-2-0-build-559); Fri, 11 Mar 2005 20:51:14 +0000 Received: from notesserver5.ftw.us.ray.com (notesserver5.ftw.us.ray.com [151.168.145.35]) by ds02c00.directory.ray.com (Switch-3.1.4/Switch-3.1.0) with ESMTP id j2BKp8W9014134 sender Richard_D_Levine@raytheon.com; Fri, 11 Mar 2005 20:51:09 GMT Subject: Re: Questions about 2 databases. To: Tom Lane Cc: jellej@pacbell.net, pgsql-performance@postgresql.org, pgsql-performance-owner@postgresql.org X-Mailer: Lotus Notes Release 5.0.8 June 18, 2001 Message-ID: From: Richard_D_Levine@raytheon.com Date: Fri, 11 Mar 2005 15:51:07 -0500 X-MIMETrack: Serialize by Router on NotesServer5/HDC(Release 6.5.2|June 01, 2004) at 03/11/2005 03:51:09 PM MIME-Version: 1.0 Content-type: text/plain; charset=US-ASCII X-SPAM: 0.00 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.214 tagged_above=0 required=5 tests=AWL, NO_REAL_NAME X-Spam-Level: X-Archive-Number: 200503/198 X-Sequence-Number: 10903 > this seems > like a dead waste of effort :-(. The work to put the data into the main > database isn't lessened at all; you've just added extra work to manage > the buffer database. True from the view point of the server, but not from the throughput in the client session (client viewpoint). The client will have a blazingly fast session with the buffer database. I'm assuming the buffer database table size is zero or very small. Constraints will be a problem if there are PKs, FKs that need satisfied on the server that are not adequately testable in the buffer. Might not be a problem if the full table fits on the RAM disk, but you still have to worry about two clients inserting the same PK. Rick Tom Lane To: jellej@pacbell.net Sent by: cc: pgsql-performance@postgresql.org pgsql-performance-owner@pos Subject: Re: [PERFORM] Questions about 2 databases. tgresql.org 03/11/2005 03:33 PM jelle writes: > 1) on a single 7.4.6 postgres instance does each database have it own WAL > file or is that shared? Is it the same on 8.0.x? Shared. > 2) what's the high performance way of moving 200 rows between similar > tables on different databases? Does it matter if the databases are > on the same or seperate postgres instances? COPY would be my recommendation. For a no-programming-effort solution you could just pipe the output of pg_dump --data-only -t mytable into psql. Not sure if it's worth developing a custom application to replace that. > My web app does lots of inserts that aren't read until a session is > complete. The plan is to put the heavy insert session onto a ramdisk based > pg-db and transfer the relevant data to the master pg-db upon session > completion. Currently running 7.4.6. Unless you have a large proportion of sessions that are abandoned and hence never need be transferred to the main database at all, this seems like a dead waste of effort :-(. The work to put the data into the main database isn't lessened at all; you've just added extra work to manage the buffer database. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match