Postgres – bridge between worlds

    NoSQL databases promise to solve:

  • scaling
  • easier database interaction

Scaling often means automatic sharding, replication and multiple write points.
Easier database interaction usually means schema-less data, simpler migrations and removal of object-relational impedance mismatch.

Postgres is an ORDBMS, not just plain relational database, which means that many of the points that NoSQL proponents argue about relational database don’t apply to it.
In practice this means that Postgres can have best of both worlds.

While origin of “joins are slow” argument is probably from people not too familiar with databases and relational theory, let’s look at real world scenario people usually have on mind.
If our model looks something like

post {
  string name
  string[] tags

in relational database we must implement tags in a separate table and do a join to reconstruct the post. In Postgres you can do it in more application developer friendly way using varchar[] data type, ie:


and if we wish to lookup posts by tags, Postgres supports indexes optimized for arrays.

If we don’t really know in advance what can be in our data and need something like xml, key-value or json, Postgres with its advanced type support doesn’t fall short.
XML, HSTORE and even json (while not really useful currently) are available.

What about some non-trivial schemas, for example:

actor {
  string name
  date born
  movie[] playedIn
  address[] knownAddresses

address {
  string country
  string town
  string street

movie {
  string name
  date released
  string[] categories

where both actor and movie are objects with identity.
Postgres has support for both types and tables, where each table is also a type.
When we don’t need identity we can use composite types

CREATE TYPE address AS (country VARCHAR, town VARCHAR, street VARCHAR)

When we need identity we will use table with a primary key (for this example let’s use name to keep it simple):

  born DATE,
  knownAddresses address[]
  released DATE,
  categories VARCHAR[]
CREATE TABLE actor_playedIn (
  actor_name VARCHAR REFERENCES actor,
  movie_name VARCHAR REFERENCES movie,
  PRIMARY KEY(actor_name, movie_name)

Finally we need to create this complex object (using joins of course)

CREATE VIEW actor_document AS
SELECT, a.born, played.playedIn, a.knownAddresses
FROM actor a
  SELECT p.actor_name, array_agg(m) AS playedIn
  FROM actor_playedIn p
  JOIN movie m ON = p.movie_name
  GROUP BY p.actor_name
) played ON played.actor_name =

So, this document (aggregate root) can be used as key -> value if actor primary key is used as key and whole object is used as value.
This makes it cache friendly so we can store it in Memcached and bypass database on requests for actor by its id.

In next post we will analyze easier migration argument and common misconceptions around it.

One thought on “Postgres – bridge between worlds

  1. Luca Francesca

    Interesting choice.
    I think postgres us the best you can use..sometimes even better than, say, Oracle.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>