PostgreSQL when it is not your job

Part of the reason why PostgreSQL has so any knobs is that these things are not always thing that cookie cutter approaches work with. Although if you do need to worry about these, it probably is your job and you probably are at least going to learn it.

I am not convinced about his list of "stupid db tricks you should not do." For example:

1) Sessions in the db are sometimes a good thing and sometimes not. They do have a real performance cost (we have a query in LedgerSMB that for large db's takes 20x longer because of having to do this, but the performance cost is necessary and still worth it, though we will probably offer non-web-based alternatives instead later where this wont be necessary). Of course that query is actually using another table to provide "discretionary locks" of rows to a session.... and those locks MUST persist across transactions because of the fact we are mapping to a series of HTTP requests...

2) I dont know about celery queues, but with listen/notify, you can do some really cool message queuing in PostgreSQL.

3) Usually when my app has to store files attached to data, I usually find that it's simpler to put it in the db than the filesystem. That guarantees that the files are in the backups among other things. Also for larger files, PostgreSQL's lob interface (up to 2gb) provides seek operations and more. performance issues here end up occurring outside PostgreSQL.

I am with him on very long-running transactions.

COPY is good for some things, but if you are trying to create more portable code you probably want to do something like insert foo (....) values (...), (....), (....)....

I would also suggest it is important to know the difference between LIKE '%this%' and full text searching on PostgreSQL. These are not simple drop-in equivalents. However additionally LIKE '%this%' cannot use an index (though like 'this%' can, and you can do full text indexing on Pg).

PostgreSQL is able to convert correlated subqueries with IN () clauses into joins in most cases. My guess is that it could have been two queries refactored into one.

Actually since PostgreSQL 9.1 LIKE '%this%' is indexable with the pg_trgm contrib module. Since it is based on the trigrams in your search query it obviously has its caveats, generally the longer the query the more effective the index lookup is. I would imagine for example '%th%' requires a full table/index scan since it contains zero trigrams.

http://www.postgresql.org/docs/9.1/static/pgtrgm.html#AEN137...

One question on avoiding giant IN clauses with Django?

Say I have a class called Fridge, and a classes called Vegetables and Condiments.

Both of these have ManyToMany relationships between themselves and Fridge.

So something like:

    class Fridge(models.Model):
         condiments = models.ManyToManyField(Condiments)
         vegetables = models.ManyToManyField(Vegetables)

And here we have a QuerySet that represents our white fridges:

    qs = Fridges.objects.filter(color='white')

First query:

"Given a list of condiment IDs, get me all the fridges that have ANY of those condiments in them (modifying the original QuerySet).""

Second query:

"Given a list of vegetable IDs, get me all the fridges that have ALL of those vegetables in them (modifying the original QuerySet)."

How on earth would I do that without building a list of fridge IDs and adding an IN clause to my queryset?

Here are solutions that do it with IN clauses:

First query:

        condiment_ids = [...] # list of condiment IDs
        condiments = Condiment.objects.filter(
            id__in=condiment_ids).all()
        condiment_fridges = None
        for condiment in condiments:
            qs = condiment.fridge_set.all()
            if not condiment_fridges:
                condiment_fridges = qs
            else:
                condiment_fridges = condiment_fridges | qs
        qs = qs.filter(id__in=[l.id for l in condiment_fridges])

Second query:

        vegetable_ids = [...] # list of vegetable IDs
        vegetables = vegetable.objects.filter(id__in=vegetable_ids).all()
        vegetable_fridges = None
        for vegetable in vegetables:
            qs = vegetable.location_set.all()
            if not vegetable_fridges:
                vegetable_fridges = qs
            else:
                vegetable_fridges = vegetable_fridges & qs
        qs = qs.filter(id__in=[l.id for l in vegetable_fridges])

These solutions seem horrible and hackish and I was wondering if there was a better way to do them with Django. Something like object HAS these objects or object HAS ALL of these objects.

Should I just post this on StackOverflow instead?

"Anything above 8GB exceeds what it is designed to handle and can cause major performance problems, such as the database becoming unresponsive for 1-2 minutes."

Can you please expand on that? What version did you test, on what hardware, and what workload?