Early during the development of the PostgreSQL backend for FlashMQ.com—that is our amazing managed MQTT hosting service!—, I decided on using UUIDs rather than auto-incrementing integers wherever I needed or wanted surrogate keys. I am one of those people who prefers the use of natural keys where their use is … natural, but I certainly have nothing _against_ surrogate keys, only to their overuse, which usually results from an over-reliance on ORMs (which I do have something against). There are a couple of advantages to using UUIDs over auto-incrementing integers (available in PostgreSQL via sequences):
My latest split-off release from our super-stable FlashMQ.com PostgreSQL backend is version 1.0.0 the pg_utility_trigger_functions. It’s a very simple bundle of a bunch of trigger functions that I find useful to carry around with me, and it’s sufficiently-tested for me to bring it out immediately under a 1.0.0 stamp.
I just need to add a quick summary of what postgres back-end tool our Ruby on Rails application uses, and how we’ve configured it, because it’s quite confusing…
There are four postgresql backends:
- ruby-postgres. This version is no longer maintained. This is the version we used when the project began.
- postgres-pr. This is a pure ruby implementation, which is not interesting for us.
- postgres. This is the continuation of the unmaintained ruby-postgres. This version includes such fixes as that it can be compiled against libpg-8.3.
- ruby-pg. It is said that this one is now the official postgres back-end, but when I install it, the application still can’t find “postgres”.
Because the aforementioned article states that the pg extension is unstable, “postgres” seems to be what we should use. The article states that it is included in the ruby-pg package, but it doesn’t work when I install it, so I had to install “postgres”. I uninstalled ruby-pg, because it doesn’t seem necessary.
To continue, we once used a patched postgresql adapter, because we needed more accurate timestamps (the standard connection adapter rounded everything off to whole seconds), but if I recall correctly, this patch was only necessary on the connection adapter in Rails, not the back-end. We never commissioned the functionality that required this, so this existed only in the workdir of one of the devs.
As a final note; on our production server, we have a version of ruby-postgres installed in /usr/local. I can’t remember why…