

PostgreSQL Tips and Tricks - gtuhl
http://blog.gtuhl.com/2009/08/07/postgresql-tips-and-tricks/

======
abalashov
#4 seems like a zealous overgeneralisation. JOINs get a lot of crap.

Yes, it's no secret that large joins on huge amounts of data can often be very
intensive. From that does not follow, however, the exhortation to simply avoid
them categorically. That's a little too much blanket statement for me.

For instance, joins are often used in situations where there is a numeric type
column that refers to a very small table of enumerated values that have a
textual or other translation, and there is a need to produce the latter in a
single query. There's nothing wrong with that join from a performance
standpoint, even for very large values of n.

~~~
gtuhl
JOINs rightly get a lot of crap - they are massive performance bottlenecks if
you are working with large tables.

Having a single awkward example does not detract from that.

I'll add to this by noting the post does say "For smaller tables it doesn’t
matter but as tables get bigger avoid joining when you can" which is sound
advice.

I'd say joining scales up to perhaps a million or two rows unless you have a
lot of RAM (you can see join spills to disk in the EXPLAIN ANALYZE output). I
often am working with tables in the 10-30 million row range so my perspective
is probably a little slanted towards the negative.

------
nimbix
You might also be interested in this large collection of PostgreSQL SQL
tricks: <http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks>

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

~~~
rudd
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.

~~~
psnj
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]

~~~
gtuhl
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.

------
dylanz
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!

~~~
gtuhl
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.

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

~~~
chowmeined
Postgres lets you define your own aggregate functions and has an array type.
[http://mssql-to-postgresql.blogspot.com/2007/12/cool-
groupco...](http://mssql-to-postgresql.blogspot.com/2007/12/cool-
groupconcat.html)

~~~
neilc
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).

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

~~~
rudd
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...](http://www.postgresql.org/docs/8.4/interactive/multibyte.html)

~~~
berntb
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. :-)

~~~
olefoo
If you are looking for a book on Postgresql the Douglas book
[http://www.amazon.com/PostgreSQL-Developers-Library-Korry-
Do...](http://www.amazon.com/PostgreSQL-Developers-Library-Korry-
Douglas/dp/0672327562) is pretty good introduction to most of the topics
specific to postgres, it includes a sample implementation of a postgres
accessing script in most of the languages commonly used for the task, which
can be a bit repetitive (perl, python, php, java, C, same tune different
instrument) but that does make it a fairly good reference.

