The slides from my presentation on PostgreSQL for Servoy Developers, presented at ServoyWorld 2011, are available here.

tl;dr: If you make a tradeoff, be honest about it. Don’t lie to yourself that you are making a positive architectural decision when you make a negative tradeoff.

In a flash of snarkiness, I posted this to my Twitter account, based on a colleague’s attempt to solve a YouTube password problem:

Google

tl;dr: If you are doing a .distinct() query and limiting the results using .values() or .values_list(), you may be in for a surprise if your model has a default ordering using the Meta value ordering. You probably want to clear the ordering using .order_by() with no parameters.

If a model is ordered, either

tl;dr: Don’t retrieve a whole row just to get the primary key you had anyway. Don’t iterate in the app; let the database server do the iteration for you.

There’s a couple of bad habits I see a lot in Django code (including, sadly, my own), which is abuse of a ForeignKey field. Let’s take

There’s a very nasty root exim exploit in the wild.

Updated: To be fair to the hard-working exim team, this bug was fixed some time ago.

tl;dr: You can’t compare NULLs. A nullable primary key is a contradiction in terms. You can’t join on NULL, so a NULL foreign key refers to nothing, by definition. NULL doesn’t do what you think it does, no matter what you think it does.

NULL in SQL is annoyingly complex.

There’s really no conceptual

This is a follow-up to the previous post, in which we talked about ways of handling huge result sets in Django.

Two commenters (thanks!) pointed out that psycopg2 has built-in support for server-side cursors, using the name option on the .cursor() function.

To use this in Django requires a couple of small gyrations.

First, Django

tl;dr: Don’t use Django to manage queries that have very large result sets. If you must, be sure you understand how to keep memory usage manageable.

One of the great things about modern interpreted, garbage-collected languages is that most of the memory management happens behind the scenes for you. Unfortunately, sometimes, the stage equipment comes crashing

Christmas just came early for me. psycopg2.3, now in beta, includes named tuples as return values from queries.

If you are tired of writing result[4], and would much prefer to write result.column_name, you now can.

Yay!

Yesterday, I commented on a post about how widespread uptake on 9.0 replication will be. I disagreed with the assessment that “users” (by which we mean small installations of PostgreSQL, defined however you care to) will not be interested in 9.0’s hot standby/streaming replication.

Ultimately, of course, we’ll find out. But I strongly feel that 9.0’s streaming replication will