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.
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.
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).