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
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
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.
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.