From kbottner@comcast.net Sat Dec 27 00:12:07 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E9133D1B479 for ; Sat, 27 Dec 2003 00:12:07 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 59501-07 for ; Fri, 26 Dec 2003 20:11:20 -0400 (AST) Received: from sccrmhc13.comcast.net (sccrmhc13.comcast.net [204.127.202.64]) by svr1.postgresql.org (Postfix) with ESMTP id 856E1D1B43C for ; Fri, 26 Dec 2003 20:11:17 -0400 (AST) Received: from juxtapose (c-24-1-81-113.client.comcast.net[24.1.81.113]) by comcast.net (sccrmhc13) with SMTP id <2003122700111901600ro8oie>; Sat, 27 Dec 2003 00:11:19 +0000 From: "Keith Bottner" To: Subject: What's faster? Date: Fri, 26 Dec 2003 18:11:19 -0600 Message-ID: <008301c3cc0d$f3f337b0$7d00a8c0@juxtapose> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0084_01C3CBDB.A958C7B0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.4510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.6 tagged_above=0.0 required=5.0 tests=HTML_30_40 X-Spam-Level: X-Archive-Number: 200312/298 X-Sequence-Number: 5158 This is a multi-part message in MIME format. ------=_NextPart_000_0084_01C3CBDB.A958C7B0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable I have a database where the vast majority of information that is related to a customer never changes. However, there is a single field (i.e. balance) that changes potentially tens to hundreds of times per day per customer (customers ranging in the 1000s to 10000s). This information is not indexed. Because Postgres requires VACUUM ANALYZE more frequently on updated tables, should I break this single field out into its own table, and if so what kind of a speed up can I expect to achieve. I would be appreciative of any guidance offered. =20 BTW, currently using Postgres 7.3.4 =20 =20 Keith =20 =20 ------=_NextPart_000_0084_01C3CBDB.A958C7B0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Message
I have a = database=20 where the vast majority of information that is related to a customer never= =20 changes. However, there is a single field (i.e. balance) that changes= =20 potentially tens to hundreds of times per day per customer (customers rangi= ng in=20 the 1000s to 10000s). This information is not indexed. Because Postgres req= uires=20 VACUUM ANALYZE more frequently on updated tables, should I break this singl= e=20 field out into its own table, and if so what kind of a speed up can I expec= t to=20 achieve. I would be appreciative of any guidance offered.
 
BTW, curr= ently using=20 Postgres 7.3.4
 
 
Keith
 
 
------=_NextPart_000_0084_01C3CBDB.A958C7B0-- From tgl@sss.pgh.pa.us Sat Dec 27 00:49:54 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 58E82D1B432 for ; Sat, 27 Dec 2003 00:49:54 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 60019-09 for ; Fri, 26 Dec 2003 20:49:08 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id E5A76D1B449 for ; Fri, 26 Dec 2003 20:49:04 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hBR0n719008603; Fri, 26 Dec 2003 19:49:07 -0500 (EST) To: "Keith Bottner" Cc: pgsql-performance@postgresql.org Subject: Re: What's faster? In-reply-to: <008301c3cc0d$f3f337b0$7d00a8c0@juxtapose> References: <008301c3cc0d$f3f337b0$7d00a8c0@juxtapose> Comments: In-reply-to "Keith Bottner" message dated "Fri, 26 Dec 2003 18:11:19 -0600" Date: Fri, 26 Dec 2003 19:49:07 -0500 Message-ID: <8602.1072486147@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/299 X-Sequence-Number: 5159 "Keith Bottner" writes: > I have a database where the vast majority of information that is related to > a customer never changes. However, there is a single field (i.e. balance) > that changes potentially tens to hundreds of times per day per customer > (customers ranging in the 1000s to 10000s). This information is not indexed. > Because Postgres requires VACUUM ANALYZE more frequently on updated tables, > should I break this single field out into its own table, Very likely a good idea, if the primary key that you'd need to add to identify the balance is narrow. Hard to say exactly how large the benefit would be, but I'd think the update costs would be reduced considerably. regards, tom lane From nolan@gw.tssi.com Sat Dec 27 01:07:11 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 3C8ECD1B450 for ; Sat, 27 Dec 2003 01:07:11 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 65119-08 for ; Fri, 26 Dec 2003 21:06:25 -0400 (AST) Received: from gw.tssi.com (gw.tssi.com [198.147.197.1]) by svr1.postgresql.org (Postfix) with ESMTP id B77C3D1B432 for ; Fri, 26 Dec 2003 21:06:21 -0400 (AST) Received: from gw.tssi.com (nolan@gw.tssi.com [127.0.0.1] (may be forged)) by gw.tssi.com (8.12.6/8.12.6) with ESMTP id hBR16MjC002312; Fri, 26 Dec 2003 19:06:23 -0600 Received: (from nolan@localhost) by gw.tssi.com (8.12.6/8.12.6/Submit) id hBR16MLg002310; Fri, 26 Dec 2003 19:06:22 -0600 From: Mike Nolan Message-Id: <200312270106.hBR16MLg002310@gw.tssi.com> Subject: Re: What's faster? To: kbottner@comcast.net (Keith Bottner) Date: Fri, 26 Dec 2003 19:06:21 -0600 (CST) Cc: pgsql-performance@postgresql.org In-Reply-To: <008301c3cc0d$f3f337b0$7d00a8c0@juxtapose> from "Keith Bottner" at Dec 26, 2003 06:11:19 PM X-Mailer: ELM [version 2.5 PL3] MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/300 X-Sequence-Number: 5160 > Because Postgres requires VACUUM ANALYZE more frequently on updated tables, > should I break this single field out into its own table, and if so what kind > of a speed up can I expect to achieve. I would be appreciative of any > guidance offered. Unless that field is part of the key, I wouldn't think that a vacuum analyze would be needed, as the key distribution isn't changing. I don't know if that is still true if that field is indexed. Tom? Even then, as I understand things vacuum analyze doesn't rebuild indexes, so I could see a need to drop and rebuild indexes on a regular basis, even if you move that field into a separate table. -- Mike Nolan From tgl@sss.pgh.pa.us Sat Dec 27 04:00:58 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id D7AEDD1B436 for ; Sat, 27 Dec 2003 04:00:58 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 80619-03 for ; Sat, 27 Dec 2003 00:00:08 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 67D6CD1B44B for ; Sat, 27 Dec 2003 00:00:08 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hBR40419009345; Fri, 26 Dec 2003 23:00:04 -0500 (EST) To: Mike Nolan Cc: kbottner@comcast.net (Keith Bottner), pgsql-performance@postgresql.org Subject: Re: What's faster? In-reply-to: <200312270106.hBR16MLg002310@gw.tssi.com> References: <200312270106.hBR16MLg002310@gw.tssi.com> Comments: In-reply-to Mike Nolan message dated "Fri, 26 Dec 2003 19:06:21 -0600" Date: Fri, 26 Dec 2003 23:00:03 -0500 Message-ID: <9344.1072497603@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/301 X-Sequence-Number: 5161 Mike Nolan writes: >> Because Postgres requires VACUUM ANALYZE more frequently on updated tables, >> should I break this single field out into its own table, and if so what kind >> of a speed up can I expect to achieve. I would be appreciative of any >> guidance offered. > Unless that field is part of the key, I wouldn't think that a vacuum > analyze would be needed, as the key distribution isn't changing. The "analyze" wouldn't matter ... but the "vacuum" would. He needs to get rid of the dead rows in a timely fashion. The wider the rows, the more disk space is at stake. Also, if he has more than just a primary index on the main table, the cost of updating the secondary indexes must be considered. A balance-only table would presumably have just one index to update. Against all this you have to weigh the cost of doing a join to get the balance, so it's certainly not a no-brainer choice. But I think it's surely worth considering such a design. regards, tom lane From darcy@druid.net Sat Dec 27 10:52:54 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 0A41FD1B4AC for ; Sat, 27 Dec 2003 10:52:54 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 31612-02 for ; Sat, 27 Dec 2003 06:52:07 -0400 (AST) Received: from druid.net (druid.net [216.126.72.98]) by svr1.postgresql.org (Postfix) with ESMTP id 6A77ED1B47A for ; Sat, 27 Dec 2003 06:52:04 -0400 (AST) Received: by druid.net (Postfix, from userid 1000) id 8784F1B52; Sat, 27 Dec 2003 05:52:08 -0500 (EST) From: "D'Arcy J.M. Cain" To: "Keith Bottner" , Subject: Re: What's faster? Date: Sat, 27 Dec 2003 05:52:07 -0500 User-Agent: KMail/1.5.2 References: <008301c3cc0d$f3f337b0$7d00a8c0@juxtapose> In-Reply-To: <008301c3cc0d$f3f337b0$7d00a8c0@juxtapose> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200312270552.07823.darcy@druid.net> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/302 X-Sequence-Number: 5162 On December 26, 2003 07:11 pm, Keith Bottner wrote: > I have a database where the vast majority of information that is related to > a customer never changes. However, there is a single field (i.e. balance) > that changes potentially tens to hundreds of times per day per customer > (customers ranging in the 1000s to 10000s). This information is not > indexed. Because Postgres requires VACUUM ANALYZE more frequently on > updated tables, should I break this single field out into its own table, > and if so what kind of a speed up can I expect to achieve. I would be > appreciative of any guidance offered. We went through this recently. One thing we found that may apply to you is how many fields in the client record have a foreign key constraint. We find that tables with lots of FKeys are a lot more intensive on updates. In our case it was another table, think of it as an order or header table with a balance, that has over 10 million records. Sometimes we have 200,000 transactions a day where we have to check the balance. We eventually moved every field that could possibly be updated on a regular basis out to separate tables. The improvement was dramatic. -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner. From tgl@sss.pgh.pa.us Sat Dec 27 18:31:31 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E3E4ED1B4B7 for ; Sat, 27 Dec 2003 18:31:31 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 71990-02 for ; Sat, 27 Dec 2003 14:30:42 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 3AF4BD1B4E1 for ; Sat, 27 Dec 2003 14:30:41 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hBRIUd19012772; Sat, 27 Dec 2003 13:30:39 -0500 (EST) To: "D'Arcy J.M. Cain" Cc: "Keith Bottner" , pgsql-performance@postgresql.org Subject: Re: What's faster? In-reply-to: <200312270552.07823.darcy@druid.net> References: <008301c3cc0d$f3f337b0$7d00a8c0@juxtapose> <200312270552.07823.darcy@druid.net> Comments: In-reply-to "D'Arcy J.M. Cain" message dated "Sat, 27 Dec 2003 05:52:07 -0500" Date: Sat, 27 Dec 2003 13:30:38 -0500 Message-ID: <12771.1072549838@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/303 X-Sequence-Number: 5163 "D'Arcy J.M. Cain" writes: > We went through this recently. One thing we found that may apply to you is > how many fields in the client record have a foreign key constraint. We find > that tables with lots of FKeys are a lot more intensive on updates. BTW, this should have gotten better in 7.3.4 and later --- there is logic to skip checking an FKey reference if the referencing columns didn't change during the update. regards, tom lane