On Apr 9, 2007, at 9:46 AM, Vivek Khera wrote:
>
> On Apr 9, 2007, at 10:09 AM, btober@ct.metrocast.net 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.
>
> 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.
That would be cool.
>
> One thing that was really counter-intuitive to me from a guy who
> runs really large databases, was to get rid of some of the FK's and
> manage them in the application layer. This one scares me since
> I've had my behind saved at least a couple of times by having the
> extra layer in the DB to protect me... the data integrity would be
> managed by some external program that sweeps the DB every so often
> and purges out data that should no longer be there (ie stuff that
> would have been CASCADE DELETEd).
This is often debated and it does seem strange to here that stance
from a dba. It's normally the application developers who want to do
that.
erik jones <erik@myemma.com>
software developer
615-296-0838
emma(r)
