On Monday 09 April 2007 11:50, Erik Jones wrote:
> On Apr 9, 2007, at 9:46 AM, Vivek Khera wrote:
> > On Apr 9, 2007, at 10:09 AM, btober@xxxxxxxxxxxxxxxx wrote:
> >> It would be a really great service to this community if you would
> >> capture those issues and publish documentation (but feel free to
> >> change or omit the names to protect the incompetent^w innocent!).
> >
> > There's no incompetence involved... the guy is clearly not a
> > Postgres person, but overall an excellent DB designer. He works on
> > extremely large databases at a large public university.
> >
> > The main one that comes to mind is that he suggested adding multi-
> > part primary indexes to keep the data ordered. Apparently Oracle
> > and/or DB2 keep the data sorted by primary key index. Since the
> > only reason was to keep the data sorted, the index would be useless
> > under Pg.
>
> You do have CLUSTER available for ordering a table on a single
> index. However, after you do a CLUSTER new rows and updates don't
> respect that and you have to CLUSTER again periodically, but isn't
> difficult to add to a regular maintenance schedule/script.
>
There are a lot of scenarios where you really cant afford the penelty running
a cluster entails (24/7 operation, 100gb tables, etc...)
> > Also, he recommended the use of 'index-only' tables -- eg, when the
> > table is just two or three integers, and the PK is a multi-part key
> > of all fields, it makes sense not to store the data twice.
> > However, in Pg you can't do that since visibility is only stored in
> > the data, not the index.
>
You can achieve the same effect with another version of clustered tables
available in other databases, where the order is preserved when data is
added/updated. Of course we don't have that either.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
