Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 70F6E476742 for ; Thu, 16 Jan 2003 09:35:28 -0500 (EST) Received: from lakemtao03.cox.net (lakemtao03.cox.net [68.1.17.242]) by postgresql.org (Postfix) with ESMTP id 2F5A1476809 for ; Thu, 16 Jan 2003 09:34:35 -0500 (EST) Received: from [192.168.1.1] ([68.11.66.83]) by lakemtao03.cox.net (InterMail vM.5.01.04.05 201-253-122-122-105-20011231) with ESMTP id <20030116143439.KAGI8666.lakemtao03.cox.net@[192.168.1.1]> for ; Thu, 16 Jan 2003 09:34:39 -0500 Subject: Re: schema/db design wrt performance From: Ron Johnson To: PgSQL Performance ML In-Reply-To: <20030116092005.C22344@mail.libertyrms.com> References: <20030116135140.32097.qmail@web21106.mail.yahoo.com> <20030116092005.C22344@mail.libertyrms.com> Content-Type: text/plain Organization: Message-Id: <1042727678.892.31.camel@haggis> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.1 Date: 16 Jan 2003 08:34:38 -0600 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/146 X-Sequence-Number: 793 On Thu, 2003-01-16 at 08:20, Andrew Sullivan wrote: > On Thu, Jan 16, 2003 at 05:51:40AM -0800, CaptainX0r wrote: > > All, > > > > I just noted in another thread that use of foreign keys in postgres > > significantly hinders performance. I'm wondering what other > > Since I think I'm the one responsible for this, I'd better say > something clearer for the record. > > The foreign keys implementation in PostgreSQL essentially uses SELECT > . . . FOR UPDATE to ensure that referenced data doesn't go away while a > referencing datum is being inserted or altered. > > The problem with this is that frequently-referenced data are > therefore effectively locked during the operation. Other writers > will block on the locked data until the first writer finishes. > > So, for instance, consider two artificial-example tables: > > create table account (acct_id serial primary key); > > create table acct_activity (acct_id int references > account(acct_id), trans_on timestamp, val numeric(12,2)); > > If a user has multiple connections and charges things to the same > account in more than one connection at the same time, the > transactions will have to be processed, effectively, in series: each > one will have to wait for another to commit in order to complete. This is true even though the default transaction mode is READ COMMITTED? -- +------------------------------------------------------------+ | Ron Johnson, Jr. mailto:ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | "Basically, I got on the plane with a bomb. Basically, I | | tried to ignite it. Basically, yeah, I intended to damage | | the plane." | | RICHARD REID, who tried to blow up American Airlines | | Flight 63 | +------------------------------------------------------------+