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.
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@xxxxxxxxxx>
software developer
615-296-0838
emma(r)
