When working with a database, sometimes performance problems are both far more subtle, and much worse than a query itself might suggest. The topic of this week’s Postgres (PostgreSQL) performance killers article concerns the use of the IN clause, and how misusing it can catastrophically obliterate the database in mysterious ways.
To that end, we’ll use a slightly revised single-table test case since it’s served us pretty well so far:
For the second of my ten part series on hidden Postgres (PostgreSQL) performance killers, I’m going to talk about something called an anti-join. It’s not a well-known approach outside of the database world, but due to how it works, it can impart almost magical plan revisions that drastically improve query performance in the right scenario. Developers can add it to a growing bag of tricks when working on database-driven content, since it comes in handy more often than you might expect.
Yesterday I gave the developers at my company what I call a DBA Chat. It’s something I try to do every month to keep them apprised on various features, caveats, performance considerations, and so on. I find that educating the folks who regularly work with the database does wonders for application performance and my sanity. The benefit of this long format is that I can go over more information than a time constrained set of slides.
Users both old and new are likely aware that Postgres has functions. Some lucky readers may have even written one or two. For those without much experience in this area, or are thinking about contributing a few functions to your database for the first time, there are a couple things you should know. This week, I want to cover a few of the function declaration decorators. If you have no idea what I’m talking about, then this is probably something you’ll want to read regardless of your experience level.
Last week, I covered how MVCC, Postgres’s storage system, works on a very high level. Near the end, I also mentioned that it doesn’t quite lend itself well to certain use cases, such as rapidly mutating session storage. Well, there is one caveat to that statement that I’d forgotten about because of its relatively limited utility: unlogged tables.
Here’s what the Postgres documentation has to say about unlogged tables:
Data written to unlogged tables is not written to the write-ahead log, which makes them considerably faster than ordinary tables.
