PostgreSQL Tips and Tricks

#1

Post by gtuhl » Sat, Aug 08, 2009, 11:47 AM UTC

PostgreSQL Tips and Tricks
blog.gtuhl.com

Re: PostgreSQL Tips and Tricks

#2

Post by psnj » Sat, Aug 08, 2009, 12:28 PM UTC

Some reasonable advice in there, but #3? Ruh roh.

Re: PostgreSQL Tips and Tricks

#3

Post by berntb » Sat, Aug 08, 2009, 12:39 PM UTC

Interesting.

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

Re: PostgreSQL Tips and Tricks

#4

Post by figital » Sat, Aug 08, 2009, 12:46 PM UTC

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

Re: PostgreSQL Tips and Tricks

#5

Post by rudd » Sat, Aug 08, 2009, 1:27 PM UTC

psnj wrote: post #2

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

Re: PostgreSQL Tips and Tricks

#6

Post by rudd » Sat, Aug 08, 2009, 1:30 PM UTC

berntb wrote: post #3

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

Re: PostgreSQL Tips and Tricks

#7

Post by jwilliams » Sat, Aug 08, 2009, 1:55 PM UTC

rudd wrote: post #5

psnj wrote: post #2

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

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.

Re: PostgreSQL Tips and Tricks

#8

Post by chowmeined » Sat, Aug 08, 2009, 2:12 PM UTC

figital wrote: post #4

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

Re: PostgreSQL Tips and Tricks

#9

Post by psnj » Sat, Aug 08, 2009, 2:42 PM UTC

rudd wrote: post #5

psnj wrote: post #2

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

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 author thought that NF had little value, which probably doesn't belong in a list of database tips and tricks.

[edit: format]

Re: PostgreSQL Tips and Tricks

#10

Post by figital » Sat, Aug 08, 2009, 3:10 PM UTC

chowmeined wrote: post #8

figital wrote: post #4

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

Thanks for the link. Looking forward to trying the array approach as well.