I mentioned something about this the last time a discussion involving Django and Postgres came up, but it bears repeating.

If your environment is set up such that database connections are long-lived, please double-check that you're not using SQLAlchemy's default behavior to open an explicit transaction (e.g., "BEGIN TRANSACTION") upon connection. (It may no longer be the default, but it was last I worked with a Django shop, ~two years ago, now.) That behavior interferes with VACUUM's ability to do its job, your tables can bloat linearly with how "write-hot" they are, and performance of queries using those tables will reflect that.

Briefly, this is a consequence of Postgres' MVCC (multi-version concurrency control) architecture. Physically, an UPDATE statement is an atomic INSERT/DELETE operation; each version of every row is stored on disk, and Postgres keeps track of which versions of which rows are "visible" in the context of which transactions. Obviously, that's not a sustainable approach, both in terms of performance, and resource consumption. You'll fill up your storage with "dead" tuples, and you'll have to trawl through disk page after disk page of them to find a single "live" one.

Enter VACUUM and later, the autovacuum daemon. They know which transactions are open, and which versions of rows were modified by which transactions. "Dead" tuples older than any open transaction are truly dead, and their disk footprint can be re-used by subsequent INSERT — or UPDATE — operations.

SQLAlchemy's (former?) default behavior to open an explicit transaction upon connection isn't ideally suited for use with MVCC-based databases (so if you're using InnoDB, you'll probably have some flavor of this same problem) — particularly if your environment has connections open for days at a time, which was the case at that former gig.

When I found this problem in their stack, tables with hundreds, or perhaps a few thousand rows had disk footprints in the gibibytes. They were among the hottest tables in the system and performance, consequently, sucked. The only thing that kept them alive was that the people they had working on this stuff before me had a weekly maintenance window (site outage) where they ran a VACUUM FULL and REINDEX. How no-one twigged to the fact that app performance followed a perfect sawtooth pattern with a period of exactly one week, lock-step in phase with the maintenance cycle, I'll never understand...

You can check whether your environment is configured this way with:

  SELECT * FROM pg_stat_activity WHERE current_query = '<IDLE> in transaction' ORDER BY xact_start;

If any of those guys are older than ... not very old at all (seconds, perhaps minutes at the outside, but that's a function of how your stack works, and only you can really say what's abnormal) then, Houston, you have a problem.

A caveat: this test is fairly specific, but somewhat less sensitive, with false negatives being possible. What you're really looking for is Postgres backends with an xact_start — current transaction start time — that is (potentially significantly) out of line with how long your normal database operations should take. Those usually show up as "<IDLE> in transaction" but you could very well happen to run the above query exactly when one of these offending connections is busy servicing a query, itself...

EDIT: clarifications