Theres a lot of back and forth on NoSQL databases. The unfortunate part with all the back and forth and unclear definitions of NoSQL is that many of the valuable learnings are lost. This post isn’t about the differences in NoSQL definitions, but rather some of the huge benefits that do exist in whats often grouped into the schema-less world that could easily be applied to the relational world.

Forget migrations

Perhaps the best thing about the idea of a schemaless database is that you can just push code and it works. Almost exactly five years ago Heroku shipped git push heroku master letting you simply push code from git and it just work. CouchDB and MongoDB have done similar for databases… you don’t have to run CREATE TABLE or ALTER TABLE migrations before working with your database. There’s something wonderful about just building and shipping your application without worrying about migrations.

SQL by default isn’t typically friendly to dive into, and especially so if you’re reading someone else’s already created queries. For some reason most people throw out principles we follow in other languages such as commenting and composability just for SQL. I was recently reminded of a key feature in Postgres that most don’t use by @timonk highlighting it in his AWS Re:Invent Redshift talk. The simple feature actually makes SQL both readable and composable, and even for my own queries capable of coming back to them months later and understanding them, where previously they would not be.

The feature itself is known as CTEs or common table expressions, you may also here it referred to as WITH clauses. The general idea is that it allows you to create something somewhat equivilant to a view that only exists during that transaction. You can create multiple of these which then allow for clear building blocks and make it simple to follow what you’re doing.

Just as PostgreSQL 9.3 was coming out I had a need to take advantage of the JSON datatype and some of the operators and functions within it. The use case was pretty simple, run a query across a variety of databases, then take the results and store them. We explored doing something more elaborate with the columns/values, but in the end just opted to save the entire result set as JSON then I could use the operators to explore it as desired.

Here’s the general idea in code (using sequel):

result = r.connection { |c| c.fetch(self.query).all }
mymodel.results = result.to_json

For a couple of years I’ve complained about the Postgres documentation and at the same time paraded it as one of the best sets of documentation I’ve encountered. In many ways the reason I veer towards Postgres as well as Python and Django is the quality of their documentation. If you need to find details about something its documented, and more importantly well and thoroughly documented.

In large part I came to Python by happenstance through Django, and Postgres through happenstance of an employer. Yet, Django was very little of an accident. The Django Tutorial got me a large part of what I needed to know and more excited about development than I had been in some time. Python has done some work at adding docs to make this even better, sadly its still very much needed for PostgreSQL.

There are two particular sets of features that continue to keep me very excited about the momentum of Postgres. And while PostgreSQL has had some great momentum in the past few years these features may give it an entirely new pace all together. One is extensions, which is really its own category. Dimitri Fontaine was talking about doing a full series just on extensions, so here’s hoping he does so I dont have to :)

One subset of extensions which I consider entirely separate is the other thing, which is foreign data wrappers or FDWs. FDWs allow you to connect to other data sources from within Postgres. From there you can query them with SQL, join across disparate data sets, or join across different systems. Recently I had a good excuse to give the postgres_fdw a try. And while I’ve blogged about the Redis FDW previously, the Postgres one is particularly exciting because with PostgreSQL 9.3 it will ship as a contrib module, which means all Postgres installers should have it… you just have to turn it on.

After my most recent post on documenting your database I had a colleague and friend chime in:

{% blockquote @danfarina https://twitter.com/danfarina/status/362007008079126528 %} @craigkerstiens You may want to mention for another post the generality of dollar quoting: it’s not just for CREATE FUNCTION. {% endblockquote %}

Luckily I was able to convince him to create the post. You can read a bit more on him below, but without further adieu here’s a bit on dollar quoting within Postgres:

Just a few days ago I was surprised by what someone was doing with their database, and not in the typical horrifying travesty against mankind. Rather, it was a feature that while familiar with I’d never seen anyone fully take proper advantage of - COMMENT or describing tables. Postgres has a nice facility for you to provide a description for just about anything:

• Table
• Column
• Function
• Schema
• View
• Index
• Etc.

If you’re deciding what to put in Postgres and what not to, consider that Postgres can be a perfectly good schema-less database. Of course as soon as people realized this then the common comes a question, is hstore or JSON better. Which do I use and in what cases. Well first, if you’re not familiar check out some previous material on them:

• hstore on PostgresGuide
• hstore in Postgres docs
• hstore with Django
• JSON datatype
• JavaScript support in Postgres

If you’re already up to date with both of them, but still wondering which to use lets dig in.

Earlier today on an internal Heroku group alias there was a dataclip shared. The dataclip listed off some data grouped by a category, there was a reply a few minutes later with a modification to the query that used the crosstab function to pivot directly in SQL. There were immediately several reactions on the list that went something like this:

While a mostly simple function in Postgres (there are a few rough edges), it really is all too handy. So here it is in action. Taking some data that looks like

• row identifier, in this case date
• category grouping, in this case OS
• value

Javascript in Postgres has gotten a good bit of love lately, part of that is from Heroku Postgres recently adding support for Javascript and part from a variety of people championing the power of it such as @leinweber (Embracing the web with JSON and PLV8) and @selenamarie (schema liberation with JSON and PLV8). In a recent conversation it was pointed out that it seems a bit of headache to have to create your own functions, or at least having an initial collection would make it that much more powerful. While many can look forward to PostgreSQL 9.3 which will have a bit more built in support for JSON a few functions can really help make it more useful today.