Creating unlogged (PostgreSQL) tables in Rails

One of the most important aspects of a relational database is durability. The database has to make certain guarantees which add overhead to the database system. But what if you want to give up on the durability aspect and increase the speed instead?

This can be especially be done in test environment where one may not care about the durability aspect and want to run tests faster. PostgreSQL supports multiple settings for non durability which forgo data integrity and can increase the performance. One such thing is unlogged tables.

Data written to unlogged tables is not written to the write-ahead log which makes them considerably faster than ordinary tables. But it comes with a rider. These tables are not crash proof. Whenever there is a crash or unintended shutdown, such tables are truncated.

But they can be used in test environment where we don't really care about the durability aspect. They can also be used for temporary tables which are recreated even if they are wiped out. We can create unlogged tables as follows.

prathamesh@/tmp:prathamesh> create unlogged table users (name varchar, email varchar);
CREATE TABLE
Time: 0.031s

Unlogged tables and Rails

Rails allows creating unlogged tables with PostgreSQL adapter from Rails 6 onwards. We can either create unlogged tables in a migration or we can set a global setting that all tables are created as unlogged.

Creating unlogged table in a migration

Rails provides create_unlogged_table similar to create_table which creates an unlogged table.

class CreateUsers < ActiveRecord::Migration[6.0]
  def change
    create_unlogged_table :users, id: :uuid do |t|
      t.text :name
      t.text :email
      
      t.timestamps
    end
  end
end

Creating all tables as unlogged tables

We can set ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.create_unlogged_tables to true to create all tables as unlogged. This can be set for test environment as follows.

# config/environments/test.rb

config.to_prepare do
  ActiveSupport.on_load(:active_record) do
    ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.create_unlogged_tables = true
  end
end

That's it. All tables created in test environment are by default unlogged.

This setting is set to false by default in all environments.

Does this actually show any improvements?

Our test suite in a Rails API app has seen improvement in execution time after this change. The database setup step was earlier taking 15 seconds, now it takes 7 seconds. Total tests execution used to take 45 seconds, now it takes 41 seconds.

Caution!

Unlogged tables are not crash proof and should not be used in production environment unless durability is not a concern. Don't use them blindly.

Interested in knowing more about Rails and PostgreSQL, subscribe to my newsletter.