28 January 2014
26 January 2014
14 January 2014
django-pglocks 1.0.2 has been pushed out. This fixes a bug that Aymeric Augustin found that keep a lock from being properly released. He also contributed a test framework to it… many thanks to him for that!
7 January 2014
I’ll be speaking at FOSDEM 2014 in Brussels! I’ll be giving two talks:
1 December 2013
There’s a very unpleasant replication issue in version 9.0.14, 9.1.10, 9.2.5, 9.3.0 and 9.3.1 of PostgreSQL. Be sure to read the linked wiki entry carefully; it can result in silent data corruption on secondary servers in replication sets.
3 July 2013
After all, isn’t that the most important issue?
Craig Kerstiens has a good overview of what the two types are. I wanted to give a quick and dirty flowchart as to which one I would use in a green-field development situation.
First, hstore performs better than JSON in nearly every situation, but performance is (as I wrote on Twitter) just one color in a rainbow of annoying decision points you have to make when building a system.
So, what would I do?
- If I am accepting outside data as JSON and want to store it in its hierarchic form, it would require a very clear performance case for me to want to take the time to convert it to hstore.
- If I am generating the data internally (rather than accepting it in its near-complete form), and the hierarchy is not deep, I’d use hstore for its faster performance.
- If retaining the types of the values is essential, JSON is the choice, since hstore stores everything as a string.
- If fully-arbitrary querying on key/value pairs is essential, hstore’s current indexing situation is far superior to JSON’s.
- If you only query on a very small number (1-3) of keys at the top level, it’s probably better to build b-tree indexes over that key’s value than use GiST or GIN indexes; hstore does not have a significant advantage over JSON then.
As JSON support in PostgreSQL matures, this calculus will change, perhaps significantly. But that’s the 9.2 situation, in my view.
2 July 2013
Advisory locks are a very useful feature in PostgreSQL, and they just aren’t used enough.
Here’s a scenario: You have a bulk import job. While that job is running, there’s an analysis job you want to prevent from starting, and you don’t want a bulk import to start while the analysis job is running. But any number of bulk importers can run at the same time. How do you communicate this?
With an advisory lock!
Each of the bulk importers can take a shared advisory lock. Those locks don’t block each other, so they can run freely. But the analysis job takes an exclusive advisory lock of the same type. It will wait until all the importers are done, and the importers will not be issued their shared locks until it completes.
Even better, PostgreSQL cleans them up for you when a session terminates; you don’t have to worry about a lock lingering when you didn’t mean it to.
To make using advisory locks easier in Django, I have a small context manager than can be used to wrap code that should run with an advisory lock held; it makes it much easier to use them in your application. You can find it on GitHub, and it installs using
My presentation from FOSDEM 2013, PostgreSQL as a Schemaless Database, is now posted (sorry for the delay!).
23 June 2013
I often find that I’m in the middle of a loop or something and discover an error. I want to exit the loop in a way that causes the database work I’ve done within it to be rolled back, but I don’t want that exception to propagate further.
This usually looks like:
try: with xact(): for thing in things: (etc. etc.) except Rollback: pass
Having noticed this pattern a lot, I’ve added it as a feature to
Xact. Xact defines a
Rollback exception. It processes it like any other exception, but then swallows it and normally exits the function or with clause. If you feel motivated, you can subclass
Rollback, although the utility of that escapes me at the moment.
When Django 1.6 is released,
Xact will be deprecated in favor of new functionality there… but for now, have fun with it!