Tips for Database Design (Part 2)

May 08, 2024

The database is often the bottleneck in our application, both in terms of performance and developer velocity. Changing the database is a lot harder than changing the code. Because of this, I prefer to defer database structure decisions as late as possible.

I have mostly worked with PostgreSQL and Ruby on Rails ActiveRecord in the last 10-15 years.

I'm splitting this post on tips into two parts:

General Tips
Ruby on Rails Tips

This part 2, Ruby on Rails Tips, today I’m going to cover tips about

So, here are my Ruby on Rails tips 👉

Annotate gem

The "annotate" gem is a real gem (pun intended) 🤣

It is the first gem I have installed in every new project. I would love this to be built into Rails at some point.

It adds schema information to all Active Record models as a comment. In this way, you don't need to go to `schema.rb` / `structure.sql` or depend on your editor to get this information.

Having this information at hand has saved me a lot of time. Especially useful part of the annotations is the list of indexes.

Here is an example:

Database monitoring tooling

The database will constantly be the application bottleneck. Good monitoring is essential because the local database has different performance characteristics and data than production.

A good and free tool is the PGHero gem. It is mounted as a gem and gives the information about:

Duplicated indexes
Unused indexes
Slow queries
Database integrity
Database and table sizes
How much has the database grown week by week
The general health of the database

For bigger projects, I reach for pganalyze. I find its Index Advisor and Query Performance tools particularly useful.

The Query Performance helped me realize I have to move the analytics queries out of the main database to a follower replica.

Polymorphic associations

Polymorphic associations are only a very popular concept in Ruby on Rails. For the JS world, I blame their poor database tooling.

As with everything they everything, there are trade-offs. The biggest is that you can’t have foreign keys.

I find polymorphic association useful for generic and supporting domain concepts like "comments," "reactions," "notifications," and "logs," which can be attached to or connected to other things in the system.

ActiveStorage is a great example of how polymorphic associations are useful.

One issue with default polymorphic behavior in ActiveRecord has 2 drawbacks, in my opinion:

I can't see at one glance what are all possible records for a polymorphic association
I can't restrict which records can be the parent of the polymorphic model

Example:

class Comment < ApplicationRecord
  belongs_to :record, polymorphic: true
end

What records can have comments? I don't know 🤷‍♂️

For this, I have a "belongs_to_polymorphic" helper in my ApplicationRecord:

class Comment < ApplicationRecord
  belongs_to_polymorphic :record, allowed_classes: [Post, Message, Category, Discussion::Thread]
end

Now, I can see which records can have comments. Plus, there are some other goodies to this. You can check the full implementation 👉 here.

I often add a polymorphic association to a record via concern.

module HasComments
  extend ActiveSupport::Concern

  included do
    has_many :comments, as: :record, dependent: :destroy
    has_many :visible_comments, -> { visible }, class_name: 'Comment', inverse_of: :record, as: :record
    has_many :pinned_comments, -> { visible.pinned }, class_name: 'Comment', inverse_of: :record, as: :record

    has_many :commenters, -> { distinct }, through: :visible_comments, source: :user
  end
end

I use two naming schemes for concerns "-able" (Commentable) and "has" (HasComments).

Namespacing

I namespace my code very aggressively, including my database models.

I use a little know Ruby on Rails feature to namespace models in a module - table_name_prefix:

module Catalog
  def self.table_name_prefix
    'catalog_'
  end
end

# table name becomes "catalog_products"
class Catalog::Product < ApplicationRecord
end


# table name becomes "catalog_categories"
class Catalog::Category < ApplicationRecord
end

In some situations, it is easy to have a namespace and put all related ActiveRecord models there. For example, in AngryBuilding, I have namespaces like "Taxation", "BulletinBoard", "Calender" and "Voting". This is the easy case.

However, sometimes, the whole namespace should be around a single record. Examples from AngryBuilding are "Building", "Apartment" and "Issue". Those records are a domain aggregate, the root of all other records in the namespace.

I don't nest ActiveRecord classes inside other ActiveRecord classes. I created a namespace, which is the plural version of this record.

Here is an example:

class Building < ApplicationRecord
  # NOTICE: those are not 'building_reports` or `building_manager`
  has_many :reports, class_name: 'Buildings::Report'
  has_many :managers, class_name: 'Buildings::Manager'
end

# separate namespace from root record
module Buildings
  def self.table_name_prefix
    'building_'
  end
end

class Buildings::Report < ApplicationRecord
  belongs_to :building
end

class Buildings::Manager < ApplicationRecord
  belongs_to :building
end

Handling race conditions and errors

Let's say you have an application like ProductHunt where users can vote on posts. One user can vote only once on a post.

The way to ensure this is with a UNIQUE index in votes tables on the user_id and post_id columns. Then, in your application, you have code like:

Vote.find_or_create_by!(user: user, post: post)

Then you start noticing in your exception tracker that many requests fail with `PG::UniqueViolation`.

The reason for this is that 2 requests hit the code at the same time. Both find the return nil and try to create a vote. The first one succeeds, and the second one fails.

The solution for this is this utility, I copy from project to project:

AngrySupport::Handle::RaceCondition.call do
  Vote.find_or_create_by!(user: user, post: post)
end

It catches the errors and reply. You can see the code in this 👉 gist.

Another group of race-condition errors I have seen is the "PG::TRDeadlockDetected" in ActiveJob workers.

I handle this with a helper in my base "ApplicationJob" class:

class ApplicationJob < ActiveJob::Base
  include AngrySupport::Handle::Job::DatabaseErrors
end

You can see the code in this 👉 gist.

Handling JSONB columns

JSONB columns are very useful. They have similar issues as polymorphic associations. You often have to guess what the structure of the JSONB column content is.

For this, the "ActiveRecord::Attributes" API comes to the rescue. It allows us to map JSONB columns to Ruby classes; this way, we don't have to deal with hashes.

Here is an example where we use “CustomValue” class to represent a JSONB column.

First we have to define an new type

class CustomValueType < ActiveRecord::Type::Value
  def type
    :jsonb
  end

  def cast(value)
    case value
    in CustomValue then value
    in Hash then CustomValue.from_json(value)
    end
  end

  def deserialize(value)
    CustomValue.from_json(ActiveSupport::JSON.decode(value))
  end

  def serialize(value)
    value.to_json
  end
end

Then we can use this type in a record.

class MyRecord < ApplicationRecord
  attribute :custom, CustomValueType.new
end

This works not only for JSONB columns; you can use, for example, a string column to represent a custom month class - "2024-05" 😇

Useful gems

The Ruby on Rails ecosystem, which is related to databases, is rich. The Active Record itself includes a lot of batteries already 🔋

Here are some of the gems I use regularly.

annotate - Puts database structure as a comment in your models
pghero - A performance dashboard engine
counter_culture - Enhances counter caches. I have a full post about it 👉 here.
strong_migrations - Catch unsafe migrations in development
database_validations - Moves validations from Ruby to database
blazer - Business Intelligence tool as engine, allows you to write SQL and create dashboards inside your app

Conclusion

This concludes my tips for this week. 😅

ActiveRecord is one of the best and most magical parts of Rails. However, it is too easy to shoot yourself in the foot. Often, it is not even ActiveRecord's fault when we have bad database design, for example.

It would help if you had everything in front of you so you don't have to go around gathering information.

My "belongs_to_polymorphic" helper, "ActiveRecord::Type::Value", and "annotate" gem solve for this.

If you haven't checked part 1, you can check it 👉 here.

If you have any tips, you strongly agree or disagree with them or have ideas for something I missed, уou can ping me on Threads, LinkedIn, Mastodon, Twitter, or just leave a comment below 📭

Rado's Tips

Discussion about this post