[Nottingham] TALK: Relational databases, SQL and PostgreSQL.

[Graeme Fowler]

On 15/01/2007 09:49, Ovid wrote:
> A cardinal rule of software development:  optimize for *correctness*. 
> Then, and only then, do you optimize for performance.
<snip>
> Denormalization may be an option, but you have to have hard-numbers to
> back it up.  Otherwise, you're just guessing and this leads to more
> problems down the road.

Hear hear.

Anyone who's been using Request Tracker - 
http://www.bestpractical.com/rt/ - may have been badly burned by this 
exact problem in the past.

Without too much detail, the 3.5 development branch had *awful* - and I 
mean truly, madly, deeply awful - query optimisation. We were running it 
here for some months, breathlessly awaiting the first point release to 
the 3.6 branch to fix a query which effectively locked the entire 
database (yes, MySQL) whenever someone loaded a case - a frequent 
occurrence.

Even with 'explain', we couldn't get to the bottom of it; adding 
sort/key buffers, increasing available RAM, query caching, adding extra 
indices... nothing worked. Thankfully, 3.6 came along with some serious 
changes to the queries (though not the table structures) and made it all 
go away.

Not being a database specialist by any stretch, the various comments 
regarding normalisation are starting to make a lot more sense given that 
context! It appears that the data has been "normalised" by getting the 
inserts to be much more efficient.

Graeme