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
# Create unlogged tables in test environment to speed up build
config.to_prepare do
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.create_unlogged_tables = true
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.
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.
Top comments (0)