Postgres Feed

High availability of PostgreSQL databases is incredibly important to me. You might even say it’s a special interest of mine. It’s one reason I’m both excited and saddened by a feature introduced in 9.4. I’m Excited because it’s a feature I plan to make extensive use of, and saddened because it has flown under the radar thus far. It’s not even listed in the What’s new in PostgreSQL 9.4 Wiki page. If they’ll let me, I may have to rectify that.

PostgreSQL has provided table partitions for a long time. In fact, one might say it has always had partitioning. The functionality and performance of table inheritance has increased over the years, and there are innumerable arguments for using it, especially for larger tables consisting of hundreds of millions of rows. So I want to discuss a quirk that often catches developers off guard. In fact, it can render partitioning almost useless or counter-productive.

With the release of PostgreSQL 9.4, comes the ability to use binary JSON objects. This internal representation is faster and more capable than the original JSON included in 9.3. But how do we actually interact with JSON and JSONB in a database connection context? The answer is actually a little complicated and somewhat surprising.

For part of today, I’ve been experimenting with the new-ish pg_shard extension contributed by CitusData. I had pretty high hopes for this module and was extremely excited to try it out. After screwing around with it for a while, I can say it has a lot of potential. Yet I can’t reasonably recommend it in its current form. The README file suggests quite a few understandable caveats, but it’s the ones they don’t mention that hurt a lot more.

Materialized views are a great improvement to performance in many cases. Introduced in PostgreSQL 9.3, they finally added an easy method for turning a view into a transient table that could be indexed, mined for statistics for better planner performance, and easily rebuilt. Unfortunately, refreshing a materialized view in PostgreSQL 9.3 caused a full exclusive lock, blocking any use until the process was complete. In 9.4, this can finally be done concurrently, though there are still a couple caveats.