From gbajaj@tietronix.com Thu Jul  17 17:20:33 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.192])
	by svr1.postgresql.org (Postfix) with ESMTP id 3B710CCA8B
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jul 2003 17:20:33 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new,
	port 10024) with ESMTP id 98733-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jul 2003 17:21:00 +0000 (GMT)
Received: from hermes.tietronix.com (hermes.tietronix.com [216.138.125.222])
	by svr1.postgresql.org (Postfix) with ESMTP id 84142CCA83
	for <pgsql-sql@postgresql.org>; Thu, 17 Jul 2003 14:20:30 -0300 (ADT)
Received: from jabba [66.139.15.154] by hermes.tietronix.com with ESMTP
	(SMTPD32-8.00) id A88F374008A; Thu, 17 Jul 2003 12:10:39 -0500
From: "Girish Bajaj" <gbajaj@tietronix.com>
To: <pgsql-sql@postgresql.org>
Subject: Table Partitioning and Rules
Date: Thu, 17 Jul 2003 12:20:00 -0500
Message-ID: <002f01c34c87$a72f59a0$7764a8c0@tietronix.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0030_01C34C5D.BE5951A0"
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=3.8 tagged_above=0.0 required=5.0 tests=HTML_30_40,
	RCVD_IN_NJABL, SMTPD_IN_RCVD, UPPERCASE_25_50, X_NJABL_OPEN_PROXY
X-Spam-Level: ***
X-Archive-Number: 200307/231
X-Sequence-Number: 14345

This is a multi-part message in MIME format.

------=_NextPart_000_0030_01C34C5D.BE5951A0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

Hello All,

=20

I have a set of tables partitioned horizontally. DML below.=20

=20

Essentially Im trying to store a persons information in a table in the
database. Since we could have millions of people, with duplicates! Ive
decided we need to partition the table into segments where all people with
the LastName starting from A to G will be in one table. H-N will be in
another table and O-Z in the third. Ive created a VIEW that does a UNION on
all the tables.


Now, Im trying to create an INSERT and UPDATE RULE on the VIEW that would
direct an insert or update into the appropriate partition table depending on
the person LastName.=20

I cant seem to figure this out. Does anyone know how to do this?

=20

Thanks,
Girish


-- Table: public."contact_A_G"
CREATE TABLE public."contact_A_G" (
  "CONTACTID" int8 NOT NULL,
  "LastName" varchar(50),
  "FirstName" varchar(50),
  CONSTRAINT "contact_A_G_pkey" PRIMARY KEY ("CONTACTID")
) WITH OIDS;

-- Table: public."contact_H_N"
CREATE TABLE public."contact_H_N" (
  "CONTACTID" int8 NOT NULL,
  "LastName" varchar(50),
  "FirstName" varchar(50),
  CONSTRAINT "contact_H_N_pkey" PRIMARY KEY ("CONTACTID")
) WITH OIDS;

-- Table: public."contact_O_Z"
CREATE TABLE public."contact_O_Z" (
  "CONTACTID" int8 NOT NULL,
  "LastName" varchar(50),
  "FirstName" varchar(50),
  CONSTRAINT "contact_O_Z_pkey" PRIMARY KEY ("CONTACTID")
) WITH OIDS;


CREATE VIEW Contact AS
SELECT * FROM "Contact_A_G"
UNION
SELECT * FROM "Contact_H_M"
UNION
SELECT * FROM "Contact_N_Z";




------=_NextPart_000_0030_01C34C5D.BE5951A0
Content-Type: text/html;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

<html>

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; charset=3Dus-ascii">


<meta name=3DGenerator content=3D"Microsoft Word 10 (filtered)">

<style>
<!--
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{font-family:Arial;
	color:windowtext;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{page:Section1;}
-->
</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span style=3D=
'font-size:
12.0pt'>Hello All,</span></font></p>

<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span style=3D=
'font-size:
12.0pt'>&nbsp;</span></font></p>

<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span style=3D=
'font-size:
12.0pt'>I have a set of tables partitioned horizontally. DML below. </span>=
</font></p>

<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span style=3D=
'font-size:
12.0pt'>&nbsp;</span></font></p>

<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span style=3D=
'font-size:
12.0pt'>Essentially Im trying to store a persons information in a table in =
the
database. Since we could have millions of people, with duplicates! Ive deci=
ded
we need to partition the table into segments where all people with the Last=
Name
starting from A to G will be in one table. H-N will be in another table and=
 O-Z
in the third. Ive created a VIEW that does a </span></font>UNION on all the=
 tables.</p>

<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span style=3D=
'font-size:
12.0pt'><br>
Now, Im trying to create an INSERT and UPDATE RULE on the VIEW that would
direct an insert or update into the appropriate partition table depending on
the person LastName. <br>
<br>
I cant seem to figure this out. Does anyone know how to do this?</span></fo=
nt></p>

<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span style=3D=
'font-size:
12.0pt'>&nbsp;</span></font></p>

<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span style=3D=
'font-size:
12.0pt'>Thanks,<br>
Girish<br>
<br>
<br>
-- Table: public.&quot;contact_A_G&quot;<br>
CREATE TABLE public.&quot;contact_A_G&quot; (<br>
&nbsp; &quot;CONTACTID&quot; int8 NOT NULL,<br>
&nbsp; &quot;LastName&quot; varchar(50),<br>
&nbsp; &quot;FirstName&quot; varchar(50),<br>
&nbsp; CONSTRAINT &quot;contact_A_G_pkey&quot; PRIMARY KEY
(&quot;CONTACTID&quot;)<br>
) WITH OIDS;<br>
<br>
-- Table: public.&quot;contact_H_N&quot;<br>
CREATE TABLE public.&quot;contact_H_N&quot; (<br>
&nbsp; &quot;CONTACTID&quot; int8 NOT NULL,<br>
&nbsp; &quot;LastName&quot; varchar(50),<br>
&nbsp; &quot;FirstName&quot; varchar(50),<br>
&nbsp; CONSTRAINT &quot;contact_H_N_pkey&quot; PRIMARY KEY
(&quot;CONTACTID&quot;)<br>
) WITH OIDS;<br>
<br>
-- Table: public.&quot;contact_O_Z&quot;<br>
CREATE TABLE public.&quot;contact_O_Z&quot; (<br>
&nbsp; &quot;CONTACTID&quot; int8 NOT NULL,<br>
&nbsp; &quot;LastName&quot; varchar(50),<br>
&nbsp; &quot;FirstName&quot; varchar(50),<br>
&nbsp; CONSTRAINT &quot;contact_O_Z_pkey&quot; PRIMARY KEY
(&quot;CONTACTID&quot;)<br>
) WITH OIDS;<br>
<br>
<br>
CREATE VIEW </span></font>Contact AS<br>
SELECT * FROM &quot;Contact_A_G&quot;<br>
 UNION<br>
SELECT * FROM &quot;Contact_H_M&quot;<br>
 UNION<br>
SELECT * FROM &quot;Contact_N_Z&quot;;<br>
<br>
</p>

</div>

</body>

</html>

------=_NextPart_000_0030_01C34C5D.BE5951A0--




From dev@archonet.com Thu Jul  17 18:51:43 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.192])
	by svr1.postgresql.org (Postfix) with ESMTP id 67516CC9B4
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jul 2003 18:51:43 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new,
	port 10024) with ESMTP id 48433-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jul 2003 18:52:11 +0000 (GMT)
Received: from anchor-post-35.mail.demon.net (anchor-post-35.mail.demon.net
	[194.217.242.85])
	by svr1.postgresql.org (Postfix) with ESMTP id E0A06CC9B3
	for <pgsql-sql@postgresql.org>; Thu, 17 Jul 2003 15:51:41 -0300 (ADT)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-35.mail.demon.net with esmtp (Exim 3.36 #2)
	id 19dDrP-0006aF-0Z; Thu, 17 Jul 2003 19:52:11 +0100
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mainbox.archonet.com (Postfix) with ESMTP
	id 4E9F116A59; Thu, 17 Jul 2003 19:52:10 +0100 (BST)
Received: from client.archonet.com (client.archonet.com [192.168.1.16])
	by mainbox.archonet.com (Postfix) with ESMTP
	id A0D4616988; Thu, 17 Jul 2003 19:52:09 +0100 (BST)
Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: "Girish Bajaj" <gbajaj@tietronix.com>, <pgsql-sql@postgresql.org>
Subject: Re: Table Partitioning and Rules
Date: Thu, 17 Jul 2003 19:52:07 +0100
User-Agent: KMail/1.4.3
References: <002f01c34c87$a72f59a0$7764a8c0@tietronix.com>
In-Reply-To: <002f01c34c87$a72f59a0$7764a8c0@tietronix.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200307171952.07485.dev@archonet.com>
X-Virus-Scanned: by AMaViS snapshot-20020531
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/234
X-Sequence-Number: 14348

On Thursday 17 Jul 2003 6:20 pm, Girish Bajaj wrote:
> Hello All,
>
> I have a set of tables partitioned horizontally. DML below.
>
> Essentially Im trying to store a persons information in a table in the
> database. Since we could have millions of people, with duplicates! Ive
> decided we need to partition the table into segments where all people with
> the LastName starting from A to G will be in one table. H-N will be in
> another table and O-Z in the third. Ive created a VIEW that does a UNION =
on
> all the tables.

I'd not bother - databases are good at managing large amounts of informatio=
n.=20
If you really need to you can set up multiple partial indexes:

CREATE INDEX my_index_a ON contact (LastName) WHERE LastName>=3D'A' AND=20
LastName<'B'
CREATE INDEX my_index_b ON contact (LastName) WHERE LastName>=3D'B' AND=20
LastName<'C'
etc.

> Now, Im trying to create an INSERT and UPDATE RULE on the VIEW that would
> direct an insert or update into the appropriate partition table depending
> on the person LastName.
>
> I cant seem to figure this out. Does anyone know how to do this?

Something like:
CREATE RULE ... ON INSERT TO Contact WHERE NEW.LastName>=3D'A' AND=20
NEW.LastName<'H'
DO INSTEAD ...insert into correct table here...

You'll want to read the chapter on the rule system in the "Server Programmi=
ng"=20
section of the manual.

--=20
  Richard Huxton



From josh@agliodbs.com Thu Jul  17 19:03:41 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.192])
	by svr1.postgresql.org (Postfix) with ESMTP id 07F96CC4E3
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jul 2003 19:03:41 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new,
	port 10024) with ESMTP id 27564-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jul 2003 19:04:08 +0000 (GMT)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by svr1.postgresql.org (Postfix) with ESMTP id B59F1CBB94
	for <pgsql-sql@postgresql.org>; Thu, 17 Jul 2003 16:03:38 -0300 (ADT)
Received: from [66.219.92.2] (HELO temoku)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 3225756; Thu, 17 Jul 2003 12:04:25 -0700
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Richard Huxton <dev@archonet.com>,
	"Girish Bajaj" <gbajaj@tietronix.com>, <pgsql-sql@postgresql.org>
Subject: Re: Table Partitioning and Rules
Date: Thu, 17 Jul 2003 12:03:13 -0700
User-Agent: KMail/1.4.3
References: <002f01c34c87$a72f59a0$7764a8c0@tietronix.com>
	<200307171952.07485.dev@archonet.com>
In-Reply-To: <200307171952.07485.dev@archonet.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200307171203.13134.josh@agliodbs.com>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/236
X-Sequence-Number: 14350

Girish,

> > Essentially Im trying to store a persons information in a table in the
> > database. Since we could have millions of people, with duplicates! Ive
> > decided we need to partition the table into segments where all people w=
ith
> > the LastName starting from A to G will be in one table. H-N will be in
> > another table and O-Z in the third. Ive created a VIEW that does a UNIO=
N=20
on
> > all the tables.

This sounds hideously inefficient and a management headache besides.  I thi=
nk=20
PostgreSQL will accept up to 2 billion rows in any one table, and splitting=
=20
stuff into 3 tables will not improve your performance ... quite the opposit=
e.

Change your database design.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco




From rbt@rbt.ca Thu Jul  17 19:20:58 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.192])
	by svr1.postgresql.org (Postfix) with ESMTP id 9DA2CCC839
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jul 2003 19:20:58 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new,
	port 10024) with ESMTP id 48433-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jul 2003 19:21:25 +0000 (GMT)
Received: from mailav.tor1.inquent.com (mail.inquent.com [216.208.117.106])
	by svr1.postgresql.org (Postfix) with ESMTP id 60286CCAB1
	for <pgsql-sql@postgresql.org>; Thu, 17 Jul 2003 16:20:56 -0300 (ADT)
Received: from localhost (localhost [127.0.0.1])
	by mailav.tor1.inquent.com (Postfix) with ESMTP
	id 0638F10258; Thu, 17 Jul 2003 15:14:31 -0400 (EDT)
Received: from [10.0.2.7] (unknown [10.0.2.7])
	by mailav.tor1.inquent.com (Postfix) with ESMTP
	id 2CB4410248; Thu, 17 Jul 2003 15:14:29 -0400 (EDT)
Subject: Re: Table Partitioning and Rules
From: Rod Taylor <rbt@rbt.ca>
To: Josh Berkus <josh@agliodbs.com>
Cc: Richard Huxton <dev@archonet.com>,
	Girish Bajaj <gbajaj@tietronix.com>, pgsql-sql@postgresql.org
In-Reply-To: <200307171203.13134.josh@agliodbs.com>
References: <002f01c34c87$a72f59a0$7764a8c0@tietronix.com>
	<200307171952.07485.dev@archonet.com>
	<200307171203.13134.josh@agliodbs.com>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-f9WFjJSPPKllRmCdnXft"
Message-Id: <1058469725.97321.86.camel@jester>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.4.3 
Date: 17 Jul 2003 19:22:06 +0000
X-Virus-Scanned: by AMaViS perl-11
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/238
X-Sequence-Number: 14352

--=-f9WFjJSPPKllRmCdnXft
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Thu, 2003-07-17 at 19:03, Josh Berkus wrote:
> Girish,
>=20
> > > Essentially Im trying to store a persons information in a table in the
> > > database. Since we could have millions of people, with duplicates! Ive
> > > decided we need to partition the table into segments where all people=
 with
> > > the LastName starting from A to G will be in one table. H-N will be in
> > > another table and O-Z in the third. Ive created a VIEW that does a UN=
ION=20
> on
> > > all the tables.
>=20
> This sounds hideously inefficient and a management headache besides.  I t=
hink=20
> PostgreSQL will accept up to 2 billion rows in any one table, and splitti=
ng=20
> stuff into 3 tables will not improve your performance ... quite the oppos=
ite.

PostgreSQL will go well beyond 2 billion rows in a table.  It just
becomes difficult to use OIDs.

--=-f9WFjJSPPKllRmCdnXft
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (FreeBSD)

iD8DBQA/Fvdd6DETLow6vwwRAp6lAJ94T/+JVoy4PRj6FoXPKtVsltxO1wCdE+Al
mTRNGiAwHXzj3bO1YAzTZD0=
=hgHJ
-----END PGP SIGNATURE-----

--=-f9WFjJSPPKllRmCdnXft--




From gbajaj@tietronix.com Thu Jul  17 19:22:22 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.192])
	by svr1.postgresql.org (Postfix) with ESMTP id B68ACCCAB1
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jul 2003 19:22:22 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new,
	port 10024) with ESMTP id 64589-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jul 2003 19:22:50 +0000 (GMT)
Received: from hermes.tietronix.com (hermes.tietronix.com [216.138.125.222])
	by svr1.postgresql.org (Postfix) with ESMTP id 4B578CCAA8
	for <pgsql-sql@postgresql.org>; Thu, 17 Jul 2003 16:22:20 -0300 (ADT)
Received: from jabba [66.139.15.154] by hermes.tietronix.com with ESMTP
	(SMTPD32-8.00) id A5501950078; Thu, 17 Jul 2003 14:13:20 -0500
From: "Girish Bajaj" <gbajaj@tietronix.com>
To: <pgsql-sql@postgresql.org>
Subject: Re: Table Partitioning and Rules
Date: Thu, 17 Jul 2003 14:22:41 -0500
Message-ID: <003b01c34c98$ca9861f0$7764a8c0@tietronix.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable
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
In-Reply-To: <200307171203.13134.josh@agliodbs.com>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/239
X-Sequence-Number: 14353

The problem is that Im worried about sequential scans. This particular table
can have upto 150 cols and 250 million records. Now we have a reporting
requirement that someone could select on ANY col and filter on any col as
well. Meaning someone could so a SELECT on col number 1,2,310,1000 from
contact where col num 75=3D'X' and col num 139 =3D 'Y'.

I cant possibly index all the cols in the table. So I thought Id best manage
the data by splitting up the table into multiple partitions and eventually
depending on application logic, only scan those tables that are necessary to
scan sequentially instead of the whole big table.

Im getting a little confused here cause eventually I would want to join in
this 250 million gigantic table as well.. and that would be a real big
problem causing loads of sequential scans wouldn't it?

Thanks,
Girish


-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]=20
Sent: Thursday, July 17, 2003 2:03 PM
To: Richard Huxton; Girish Bajaj; pgsql-sql@postgresql.org
Subject: Re: [SQL] Table Partitioning and Rules

Girish,

> > Essentially Im trying to store a persons information in a table in the
> > database. Since we could have millions of people, with duplicates! Ive
> > decided we need to partition the table into segments where all people
with
> > the LastName starting from A to G will be in one table. H-N will be in
> > another table and O-Z in the third. Ive created a VIEW that does a UNION

on
> > all the tables.

This sounds hideously inefficient and a management headache besides.  I
think=20
PostgreSQL will accept up to 2 billion rows in any one table, and splitting=
=20
stuff into 3 tables will not improve your performance ... quite the
opposite.

Change your database design.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco





From josh@agliodbs.com Thu Jul  17 19:39:11 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.192])
	by svr1.postgresql.org (Postfix) with ESMTP id BC852CCA89
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jul 2003 19:39:11 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new,
	port 10024) with ESMTP id 35485-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jul 2003 19:39:38 +0000 (GMT)
Received: from davinci.ethosmedia.com (unknown [209.10.40.251])
	by svr1.postgresql.org (Postfix) with ESMTP id 2FACECCABE
	for <pgsql-sql@postgresql.org>; Thu, 17 Jul 2003 16:39:09 -0300 (ADT)
Received: from [66.219.92.2] (HELO temoku)
	by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2)
	with ESMTP id 3225887; Thu, 17 Jul 2003 12:39:55 -0700
Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: "Girish Bajaj" <gbajaj@tietronix.com>, <pgsql-sql@postgresql.org>
Subject: Re: Table Partitioning and Rules
Date: Thu, 17 Jul 2003 12:38:42 -0700
User-Agent: KMail/1.4.3
References: <003b01c34c98$ca9861f0$7764a8c0@tietronix.com>
In-Reply-To: <003b01c34c98$ca9861f0$7764a8c0@tietronix.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200307171238.42550.josh@agliodbs.com>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/241
X-Sequence-Number: 14355

Girish,

> I cant possibly index all the cols in the table.

Why not?=20=20

Seriously, what you can do is use query logging to log the columns and=20
combinations of columns that users most frequently ask for.  Then you can=
=20
index for those queries.

Your partitioning scheme assumes that users will be searching on the last n=
ame=20
most of the time, so I'd start with that.

> Im getting a little confused here cause eventually I would want to join in
> this 250 million gigantic table as well.. and that would be a real big
> problem causing loads of sequential scans wouldn't it?

Yes, it would.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco




From scrawford@pinpointresearch.com Thu Jul  17 19:39:23 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.192])
	by svr1.postgresql.org (Postfix) with ESMTP id EA8BACC839
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jul 2003 19:39:23 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new,
	port 10024) with ESMTP id 30186-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jul 2003 19:39:51 +0000 (GMT)
Received: from polaris.pinpointresearch.com (66-7-238-176.cust.telepacific.net
	[66.7.238.176])
	by svr1.postgresql.org (Postfix) with ESMTP id BDD7DCCAE2
	for <pgsql-sql@postgresql.org>; Thu, 17 Jul 2003 16:39:21 -0300 (ADT)
Received: from betelgeuse.pinpointresearch.com
	(betelgeuse.pinpointresearch.com [66.7.238.179])
	by polaris.pinpointresearch.com (Postfix) with ESMTP
	id D71D0103C7; Thu, 17 Jul 2003 12:39:51 -0700 (PDT)
From: Steve Crawford <scrawford@pinpointresearch.com>
Organization: Pinpoint Research
To: "Girish Bajaj" <gbajaj@tietronix.com>, <pgsql-sql@postgresql.org>
Subject: Re: Table Partitioning and Rules
Date: Thu, 17 Jul 2003 12:39:51 -0700
User-Agent: KMail/1.5.1
References: <003b01c34c98$ca9861f0$7764a8c0@tietronix.com>
In-Reply-To: <003b01c34c98$ca9861f0$7764a8c0@tietronix.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200307171239.51837.scrawford@pinpointresearch.com>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/242
X-Sequence-Number: 14356

OK, so basically you are trying to keep a hundered some odd attributes on 
everyone in the US. It's possible that a 150 column table is properly 
normalized (I have a similar situation) but it is rare.

Suppose it is really properly normalized. You can still benefit from indexes 
on just some of the columns by choosing those most commonly used in queries. 
You may also want to research partial indexes (create index foo ... where 
bar=baz) which can under certain circumstances be far smaller and faster than 
full indexes.

Breaking the table up won't improve the full table scan - in fact as each 
sub-table's file grows it will probably fragment on the disk much worse than 
a single growing file would which will, along with all the overhead of 
joining all the tables, make things worse.

Review your structure carefully. Plan on $$$ for the hardware.

Cheers,
Steve


On Thursday 17 July 2003 12:22 pm, Girish Bajaj wrote:
> The problem is that Im worried about sequential scans. This particular
> table can have upto 150 cols and 250 million records. Now we have a
> reporting requirement that someone could select on ANY col and filter on
> any col as well. Meaning someone could so a SELECT on col number
> 1,2,310,1000 from contact where col num 75='X' and col num 139 = 'Y'.
>
> I cant possibly index all the cols in the table. So I thought Id best
> manage the data by splitting up the table into multiple partitions and
> eventually depending on application logic, only scan those tables that are
> necessary to scan sequentially instead of the whole big table.
>
> Im getting a little confused here cause eventually I would want to join in
> this 250 million gigantic table as well.. and that would be a real big
> problem causing loads of sequential scans wouldn't it?
>
> Thanks,
> Girish
>
>
> -----Original Message-----
> From: Josh Berkus [mailto:josh@agliodbs.com]
> Sent: Thursday, July 17, 2003 2:03 PM
> To: Richard Huxton; Girish Bajaj; pgsql-sql@postgresql.org
> Subject: Re: [SQL] Table Partitioning and Rules
>
> Girish,
>
> > > Essentially Im trying to store a persons information in a table in the
> > > database. Since we could have millions of people, with duplicates! Ive
> > > decided we need to partition the table into segments where all people
>
> with
>
> > > the LastName starting from A to G will be in one table. H-N will be in
> > > another table and O-Z in the third. Ive created a VIEW that does a
> > > UNION
>
> on
>
> > > all the tables.
>
> This sounds hideously inefficient and a management headache besides.  I
> think
> PostgreSQL will accept up to 2 billion rows in any one table, and splitting
> stuff into 3 tables will not improve your performance ... quite the
> opposite.
>
> Change your database design.




From dmitry@openratings.com Thu Jul  17 19:41:15 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.192])
	by svr1.postgresql.org (Postfix) with ESMTP id B0DA7CCABE
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jul 2003 19:41:15 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new,
	port 10024) with ESMTP id 36264-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 17 Jul 2003 19:41:43 +0000 (GMT)
Received: from exchange.hq.openratings.com (unknown [141.149.165.226])
	by svr1.postgresql.org (Postfix) with ESMTP id 36D34CCA89
	for <pgsql-sql@postgresql.org>; Thu, 17 Jul 2003 16:41:14 -0300 (ADT)
Received: from openratings.com (cancun.hq.openratings.com [10.0.0.225]) by
	exchange.hq.openratings.com with SMTP (Microsoft Exchange
	Internet Mail Service Version 5.5.2653.13)
	id 38QM4WCW; Thu, 17 Jul 2003 15:41:44 -0400
Message-ID: <3F16FC16.5070105@openratings.com>
Date: Thu, 17 Jul 2003 15:42:14 -0400
From: Dmitry Tkach <dmitry@openratings.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20021130
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Girish Bajaj <gbajaj@tietronix.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Table Partitioning and Rules
References: <003b01c34c98$ca9861f0$7764a8c0@tietronix.com>
In-Reply-To: <003b01c34c98$ca9861f0$7764a8c0@tietronix.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/243
X-Sequence-Number: 14357

But what makes you think, that it is quicker to scan 10 tables with 25 
million rows each than it would be to scan one table with 250 million rows?
It won't... In fact, it will be *longer*.

If you have a way to narrow the number of tables to scan down based on 
the condition, you can have that logic implemented with partial indices, 
as it was suggested earlier in this thread...

Dima

Girish Bajaj wrote:

>The problem is that Im worried about sequential scans. This particular table
>can have upto 150 cols and 250 million records. Now we have a reporting
>requirement that someone could select on ANY col and filter on any col as
>well. Meaning someone could so a SELECT on col number 1,2,310,1000 from
>contact where col num 75='X' and col num 139 = 'Y'.
>
>I cant possibly index all the cols in the table. So I thought Id best manage
>the data by splitting up the table into multiple partitions and eventually
>depending on application logic, only scan those tables that are necessary to
>scan sequentially instead of the whole big table.
>
>Im getting a little confused here cause eventually I would want to join in
>this 250 million gigantic table as well.. and that would be a real big
>problem causing loads of sequential scans wouldn't it?
>
>Thanks,
>Girish
>
>
>-----Original Message-----
>From: Josh Berkus [mailto:josh@agliodbs.com] 
>Sent: Thursday, July 17, 2003 2:03 PM
>To: Richard Huxton; Girish Bajaj; pgsql-sql@postgresql.org
>Subject: Re: [SQL] Table Partitioning and Rules
>
>Girish,
>
>  
>
>>>Essentially Im trying to store a persons information in a table in the
>>>database. Since we could have millions of people, with duplicates! Ive
>>>decided we need to partition the table into segments where all people
>>>      
>>>
>with
>  
>
>>>the LastName starting from A to G will be in one table. H-N will be in
>>>another table and O-Z in the third. Ive created a VIEW that does a UNION
>>>      
>>>
>
>on
>  
>
>>>all the tables.
>>>      
>>>
>
>This sounds hideously inefficient and a management headache besides.  I
>think 
>PostgreSQL will accept up to 2 billion rows in any one table, and splitting 
>stuff into 3 tables will not improve your performance ... quite the
>opposite.
>
>Change your database design.
>
>  
>





From gsstark@mit.edu Fri Jul  18 06:15:05 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.192])
	by svr1.postgresql.org (Postfix) with ESMTP id 75CA9CAE16
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 18 Jul 2003 06:15:05 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new,
	port 10024) with ESMTP id 77015-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 18 Jul 2003 06:15:33 +0000 (GMT)
Received: from sabre.velocet.net (sabre.velocet.net [216.138.209.205])
	by svr1.postgresql.org (Postfix) with ESMTP id AB9FCCADB4
	for <pgsql-sql@postgresql.org>; Fri, 18 Jul 2003 03:15:01 -0300 (ADT)
Received: from stark.dyndns.tv (H162.C233.tor.velocet.net [216.138.233.162])
	by sabre.velocet.net (Postfix) with ESMTP
	id 01C5C138D72; Fri, 18 Jul 2003 02:15:28 -0400 (EDT)
Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar)
	by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian))
	id 19dOWe-0001v2-00; Fri, 18 Jul 2003 02:15:28 -0400
To: "Girish Bajaj" <gbajaj@tietronix.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Table Partitioning and Rules
References: <003b01c34c98$ca9861f0$7764a8c0@tietronix.com>
In-Reply-To: <003b01c34c98$ca9861f0$7764a8c0@tietronix.com>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 18 Jul 2003 02:15:28 -0400
Message-ID: <87he5kjr7j.fsf@stark.dyndns.tv>
Lines: 44
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/247
X-Sequence-Number: 14361


"Girish Bajaj" <gbajaj@tietronix.com> writes:

> I cant possibly index all the cols in the table. So I thought Id best manage
> the data by splitting up the table into multiple partitions and eventually
> depending on application logic, only scan those tables that are necessary to
> scan sequentially instead of the whole big table.

But that's only going to help if one of the columns they're searching on is
the last name column isn't it?

I'm a fan of partitioned tables but you have to consider what advantage you're
trying to achieve to understand if it will actually be helpful for you:


Partitioned tables only really improve query performance if virtually all
queries use a common constraint. The canonical example is accounting tables
being partitioned based on fiscal year. Virtually all the queries--even ones
doing massive batch queries best served by sequential scans--will only scan
the current fiscal year.

In your case unless you can impose a constraint on the UI that users always
perform their queries on a single letter of the alphabet at a time and only
see results for people whose last names match that letter, it's not really a
great match as far as query performance.


The other advantage of partitioned tables is space management; it allows
placing each partition on a separate physical storage space. However without
native support in Postgres doing it via rules is going to be a headache. I
would think you would be better off striping the disks together and storing it
as a single large table. That's the only clean approach Postgres really allows
at this point anyways. 


Finally, if I WAS going to partition based on the first letter of a text
string, which I doubt I would, I would probably create 26 partitions right off
the bat. Not try to make up arbitrary break points. If those arbitrary
breakpoints turn out to be poorly chosen it'll be a complex manual job to move
them. Whereas if you just have 26 partitions some will be large and some small
and you can move partitions between physical storage freely to balance things.

-- 
greg




From dev@archonet.com Fri Jul  18 07:06:42 2003
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.225.181])
	by svr1.postgresql.org (Postfix) with ESMTP id C41E8CCC53
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 18 Jul 2003 07:06:42 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new,
	port 10024) with ESMTP id 39871-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 18 Jul 2003 07:07:01 +0000 (GMT)
Received: from anchor-post-35.mail.demon.net (anchor-post-35.mail.demon.net
	[194.217.242.85])
	by svr1.postgresql.org (Postfix) with ESMTP id E422CCADB4
	for <pgsql-sql@postgresql.org>; Fri, 18 Jul 2003 04:06:30 -0300 (ADT)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-35.mail.demon.net with esmtp (Exim 3.36 #2)
	id 19dPKX-0006ad-0Z; Fri, 18 Jul 2003 08:07:02 +0100
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mainbox.archonet.com (Postfix) with ESMTP
	id 9F75416A61; Fri, 18 Jul 2003 08:07:00 +0100 (BST)
Received: from client.archonet.com (client.archonet.com [192.168.1.16])
	by mainbox.archonet.com (Postfix) with ESMTP
	id F146E16A5F; Fri, 18 Jul 2003 08:06:59 +0100 (BST)
Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Steve Crawford <scrawford@pinpointresearch.com>,
	"Girish Bajaj" <gbajaj@tietronix.com>, <pgsql-sql@postgresql.org>
Subject: Re: Table Partitioning and Rules
Date: Fri, 18 Jul 2003 08:06:58 +0100
User-Agent: KMail/1.4.3
References: <003b01c34c98$ca9861f0$7764a8c0@tietronix.com>
	<200307171239.51837.scrawford@pinpointresearch.com>
In-Reply-To: <200307171239.51837.scrawford@pinpointresearch.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200307180806.58921.dev@archonet.com>
X-Virus-Scanned: by AMaViS snapshot-20020531
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/248
X-Sequence-Number: 14362

On Thursday 17 Jul 2003 8:39 pm, Steve Crawford wrote:
> OK, so basically you are trying to keep a hundered some odd attributes on
> everyone in the US. It's possible that a 150 column table is properly
> normalized (I have a similar situation) but it is rare.
>
> Suppose it is really properly normalized. You can still benefit from
> indexes on just some of the columns by choosing those most commonly used =
in
> queries. You may also want to research partial indexes (create index foo
> ... where bar=3Dbaz) which can under certain circumstances be far smaller=
 and
> faster than full indexes.

The other issue is, that even if your table is normalised you may want to=
=20
split vertically. That's going to depend on usage patterns, and I don't kno=
w=20
what you've got, but say you used three tables:

contact_address
contact_personal
contact_bank_details

Now a search by sales would be interested in ...address & ...personal where=
as=20
accounts would look at ...address & ...bank_details.

This _might_ make sense, but probably only if you can group columns into=20
related groups and users are more interested in some groups than others. If=
=20
you are lucky the gains might be in cache usage, whereas the costs will be =
in=20
joining groups for results.

I'm not saying you should do this just to try and improve performance, but =
it=20
might make sense if users look at it that way.

> Review your structure carefully. Plan on $$$ for the hardware.

Or remind your users that patience is a virtue ;-)

--=20
  Richard Huxton