Good post, but most of those are SQL specific, and not really unique to Postgres, right? But yeah, very crucial points you covered that you see a lot of people not implementing!
PostgreSQL Tips and Tricks
11–20 of 29 posts
Re: PostgreSQL Tips and Tricks
#12You might also be interested in this large collection of PostgreSQL SQL tricks: http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks
Re: PostgreSQL Tips and Tricks
#13Earlier quoted context omitted.
It's not particularly bad advice. Perhaps for someone who hasn't worked with databases long enough, it's dangerous to tell them "don't worry about normality." But denormalizing certain groups of tables can certainly cause a significant performance gain.
Yes, you're right. I agree that there are times that denormalizing makes sense. My point was that the item said: * Throw away the normal forms you learned in school * Denormalize [...] whenever it makes a query faster. I think that's bad advice as stated. As you imply, you have to know why the rules are there to know when to break them intelligently. I didn't get that message from the item as posted, rather that the…
Its bad advice because people denormalize the wrong tables. They'll actually slow queries down by denormalizing a table with a few rows into a table with many rows - which makes things slower.
Re: PostgreSQL Tips and Tricks
#14Earlier quoted context omitted.
It's not particularly bad advice. Perhaps for someone who hasn't worked with databases long enough, it's dangerous to tell them "don't worry about normality." But denormalizing certain groups of tables can certainly cause a significant performance gain.
Agreed. Most of the serious commercial databases I've worked with/near/on have been de-normalised to some extent. e.g. SalesForce run massive amounts of data. Their implementation really boils down to one de-normalised table; with a dozen or so supporting tables. When I hear about some (not all) NoSQL implementations I occasionally wonder why they just didn't adopt a de-normalised SQL solution.
Because if you're going to do that - why not drop the SQL and the ORM altogether? Most shops at that level aren't doing analysis on their production SQL anyway - they dump that into a data warehouse. So why not do your CRUD on NoSQL and dump that some place ppl can run SQL on it?
Re: PostgreSQL Tips and Tricks
#15So glad to see PostgreSQL bubbling up these day's as it's been my DB of choice for many years. I'd love to see a more portable version and of course something like mySQL's group_concat(). If you are game for trying it out be sure to check out the tools at sqlmanager.net (unfortunately they don't offer Linux versions anymore).
Postgres lets you define your own aggregate functions and has an array type. http://mssql-to-postgresql.blogspot.com/2007/12/cool-groupco...
Note that the array_accum user-defined agg described by that blog post is built into PostgreSQL 8.4 (it's called array_agg(), per the SQL standard).
Re: PostgreSQL Tips and Tricks
#16Some reasonable advice in there, but #3? Ruh roh.
It's not particularly bad advice. Perhaps for someone who hasn't worked with databases long enough, it's dangerous to tell them "don't worry about normality." But denormalizing certain groups of tables can certainly cause a significant performance gain.
I think "don't worry about normality" is a ridiculously bad statement. But perhaps "selectively denormalize when you run out of other optimzations" would be a little more reasonable.
One approach is to make all your denormalizations populated by triggers -- that way to you can still think about the DB in "clean" terms in your code when modifying data, but get your denormalized tables for queries, too.
Re: PostgreSQL Tips and Tricks
#17Interesting. Any nice reference about pgsql and query optimization? Any book recommendation? As an old mysql guy, as far as I do db:s, the advice re subqueries (#4) was unusual... :-) (I looked at trying pgsql for a hobby a few years back and found lacking support for different char sets for different tables, etc. Is that [still] so?)
Yes, I'm pretty sure charset support in Postgres is at the database level, not the table level. See here: http://www.postgresql.org/docs/8.4/interactive/multibyte.htm...
Ah, thanks. Sigh.
(No book recommendations for something like e.g. "High Performance MySQL"? Well, nice with no fanatical fanboys in this area, at least. :-)
Re: PostgreSQL Tips and Tricks
#18Earlier quoted context omitted.
Agreed. Most of the serious commercial databases I've worked with/near/on have been de-normalised to some extent. e.g. SalesForce run massive amounts of data. Their implementation really boils down to one de-normalised table; with a dozen or so supporting tables. When I hear about some (not all) NoSQL implementations I occasionally wonder why they just didn't adopt a de-normalised SQL solution.
Because if you're going to do that - why not drop the SQL and the ORM altogether? Most shops at that level aren't doing analysis on their production SQL anyway - they dump that into a data warehouse. So why not do your CRUD on NoSQL and dump that some place ppl can run SQL on it?
We do it the other way round to your suggestion.
SQL Server + NHibernate does handle the CRUD stuff, custom fields and DDD-based domain logic very well.
NoSQL (CouchDB in our case) doesn't handle transactions, locking and business rules effectively. However it excels at providing extremely fast views on schemaless data such as our core domain model plus custom fields.
Re: PostgreSQL Tips and Tricks
#19Earlier quoted context omitted.
It's not particularly bad advice. Perhaps for someone who hasn't worked with databases long enough, it's dangerous to tell them "don't worry about normality." But denormalizing certain groups of tables can certainly cause a significant performance gain.
Yes, you're right. I agree that there are times that denormalizing makes sense. My point was that the item said: * Throw away the normal forms you learned in school * Denormalize [...] whenever it makes a query faster. I think that's bad advice as stated. As you imply, you have to know why the rules are there to know when to break them intelligently. I didn't get that message from the item as posted, rather that the…
Been unplugged all day so haven't been around to respond. This is a completely fair criticism - the wording in my post is indeed too plain. Without understanding the normal forms making smart decisions about denormalization is going to be very difficult.
Re: PostgreSQL Tips and Tricks
#20Good post, but most of those are SQL specific, and not really unique to Postgres, right? But yeah, very crucial points you covered that you see a lot of people not implementing!
A lot of generic SQL stuff in there for sure. But, last I worked with MySQL (it has admittedly been a few years) the indexing options were severely limited when compared with PostgreSQL. Last I used it you could not index an expression or qualify an index with a where clause for example. Also at that time MySQL could only make use of one index per query.
All of those deficiencies may have since been eliminated.