postgresql when it's not your job

18:00

The Djangocon US 2014 Call for Proposals is open!

As program chair this year, I am very pleased to announce that the Call for Proposals for Djangocon US 2014 is now open! It only runs through the end of June, so be sure to get them in promptly!

no comments

11:01

“The Worst Day of Your Life” at Nordic PostgreSQL Day 2014

I’ll be presenting “The Worst Day of Your Life” (dealing with major PostgreSQL disasters) at Nordic PostgreSQL Day 2014. My first trip to Scandinavia!

no comments

12:35

“Really, Really Fast Django” at Djangocon EU

I’ll be presenting “Really, Really Fast Django” at Djangocon EU. Do come! It’s on a private island!

The slides are available here.

1 comment

10:41

“The Worst Day of Your Life” at FOSDEM PgDay 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.

no comments

12:10

“JSON in PostgreSQL” at SCALE 12x

I’ll be presenting “JSON in PostgreSQL” at SCALE 12x in Los Angeles on Friday, February 21, 2014. Do come!

no comments

18:09

“PostgreSQL Proficiency for Python People” at PyCon 2014

I’ll be giving a tutorial at PyCon 2014 in Montréal. Register early and often!

3 comments

11:25

django-pglocks 1.0.2 released

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!

no comments

14:51

Speaking at FOSDEM 2014

I’ll be speaking at FOSDEM 2014 in Brussels! I’ll be giving two talks:

• “The Worst Day of Your Life” (on finding and recovering from PostgreSQL data corruption and bugs) on January 31st at the Raddison Blu hotel.
• “Real-Life PostgreSQL JSON” on February 1 in the PostgreSQL devroom at the main FOSDEM conference.

no comments

01:22

PostgreSQL Replication Bug

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.

no comments

08:18

JSON vs hstore: Which will get you into a cool bar in the Mission?

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.

8 comments