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.
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).
Attachment:
smime.p7s
Description: S/MIME cryptographic signature
