27 February 2014
1 February 2014
FOSDEM PgDay 2014 was not, in fact, the worst day of my life; it was a great deal of fun. However, the slides from my presentation, The Worst Day of Your Life, dealing with PostgreSQL disasters, are now available.
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