Ruby on Rails and Postgres, Love at first sight

Posted by ryan
at 10:58 AM on Tuesday, November 15, 2005

With my recent selection of Planet Argon as my hosting provider for the rails app I’ve been working on I had two choices: use Postgresql or subject myself to ridicule and public humilition from the PA guys by sticking with MySQL. Fortunately I decided to take the opportunity to start learning about postgres, and I’m glad I have.

One of the unforeseen benefits I stumbled upon was using the inherits functionality of postgres to factor out some of the common columns that rails uses. For instance, everybody knows that adding the “lock_version”, “created_at” and “updated_at” columns gives you free optimistic locking and auto-timestamping, respectively. This is great, but adding these columns to every table definition in your SQL scripts is somewhat tedious. Enter “inherits”.

Instead of specifying these columns for every table like one would normally do: 

create table users (
    id integer primary key default nextval('users_id_seq'),
    username varchar(255) not null unique,
    password varchar(255) not null,
    <b>created_at timestamp not null default now(),
    updated_at timestamp not null default now(),
    lock_version integer not null default 0</b>
);

create table documents (
    id integer primary key default nextval('documents_id_seq'),
    title varchar(255) not null unique,
    body text not null,
    <b>created_at timestamp not null default now(),
    updated_at timestamp not null default now(),
    lock_version integer not null default 0</b>
);
we can factor out the three common columns into their own table that the other tables can inherit from. For the purposes of this demo I’ve called this table “traceable”: 
<b>create table traceable (
    created_at timestamp not null default now(),
    updated_at timestamp not null default now(),
    lock_version integer not null default 0
);</b>

create table users (
    id integer primary key default nextval('users_id_seq'),
    username varchar(255) not null unique,
    password varchar(255) not null
) <b>inherits(traceable);</b>

create table documents (
    id integer primary key default nextval('documents_id_seq'),
    title varchar(255) not null unique,
    body text not null
) <b>inherits(traceable);</b>
Just as we can factor out common attributes of an object model into an abstract or super class, postgres allows us to do the same with our table model.

I like it when architectural layers synch up like this, and postgres makes it easy to do so. The only question I have is if this structure will lead to a performance hit that negates its convenience. I would imagine it is similar to using a view, although that is a largely uneducated guess.

Comments

Leave a response

  1. mattc – May 10, 2006 @ 05:04 PM
    Very interesting idea. In response to Larry's concerns, which seem very valid to me. Inheriting these 3 fields still seems safe and manageable in the long term. Rails manages them for you for free, so you will never want to modify them, and would not have any reason to drop them. I think this concept is perfect for Rails tables. I'm going to play around with it myself.
  2. Will Reese – May 10, 2006 @ 05:04 PM
    I've been a PostgreSQL DBA for almost 4 years now, and I just recently spent a considerable amount of time removing inheritance from a very large production database. I would not use PostgreSQL's inheritance feature for this purpose. While it does cut down on the amount of DDL you have to write initially, it makes maintenance more difficult down the road. For example, you can not rename or drop inherited columns on the child tables (but you can on the parent). To get rid of the inheritance, you have to drop and recreate the table. Which is difficult once triggers, sequences, foreign keys and other dependencies come into play. PostgreSQL's inheritance feature is not pure evil, you just need to be aware of the constraints it will put on your schema. One interesting use for inheritance appears in PostgreSQL version 8.1. They use inheritance and a new feature called "constraint exclusion" to implement a form of table partitioning. This helps increase query performance for very large tables. Anyway, I just thought I'd pass on a little advice I learned the hard way. By the way, if you have not heard of Rails Migrations, you might want to check them out. They really help manage your schema modifications.
  3. Anonymous – May 10, 2006 @ 05:04 PM
    Conversely, inheritance is perfect for the case where you have a number of tables that should have a subset of fields that are identical across all the tables. In that situation, you can use inheritance to guarantee that all the tables will be updated simultaneously, and that none of the updates can be forgotten.
  4. gbsmith – May 10, 2006 @ 05:04 PM
    Hmmm... given: # Larry's caveats above # The fact that inheritence really is not what you want here and # That fact that there IS something that does what you want in PG already You should reconsider. Inheritence really isn't correct here. Granted, You can say that "user ISA traceable" and be right to a certain extent, but if you were to browse the traceable table, would anything really be gained? In fact it would be quite meaningless since it would just be a bunch of dates and ints that tell you nothing. Compare that to having an employees table which is in turn inherited by a managers table. Now browsing the employees table (which includes managers) would actually be useful. Postgres has a LIKE option on table creation (not to be confused with LIKE used in a WHERE clause) that allows you to use another table as a template for a new table, copying over all the columns in the process (see "http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html":http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html ). Each new table would have its very own copy of the traceable (I might go with "_traceable", as a way to distinguish it) columns, and there would be no inheritence overhead. Note that the flipside would be that any subsequent changes made to traceable table would NOT propagate to the new tables since they are only connected during `CREATE TABLE`. But that seems to be all you really want here - a table template. Your above example would become: -- make traceable as before -- notice where the LIKE goes create table users ( id integer primary key default nextval('users_id_seq'), username varchar(255) not null unique, password varchar(255) not null, LIKE traceable INCLUDING DEFAULTS ); create table documents ( id integer primary key default nextval('documents_id_seq'), title varchar(255) not null unique, body text not null, LIKE traceable INCLUDING DEFAULTS ); What's more, once all the tables were made you could lock down the traceable table or drop it altogether since it is not actually used for storing rows.
  5. asdf – May 24, 2006 @ 10:16 AM
    you could also use Rails Migrations to manage these tables and factor the adding of these columns into a private method that you call after each create_table.
  6. Ryan Daigle – May 25, 2006 @ 04:27 AM
    You're right, asdf - migrations could help me here as well. At the time of the post I hadn't quite grasped all that migrations could do, but this is how I would attack the problem now. Great point.
  7. iain – July 07, 2006 @ 03:15 AM
    another solution is the Row Version Migrations plugin, from http://www.redhillconsulting.com.au/rails_plugins.html#row_version_migrations which adds created_at, updated_at and lock_version columns to all tables created with migrations.