Rails: Conserve your database by “audit”ing its space in right way !!!

In most of the Rails applications, we track the important data for auditing. Most of the time, database table in which these audit records are stored, is under same database of our application.

Keeping this table in same database is helpful until it doesn’t grows tremendously. We use this table most of the time for analysis & sometimes for recovering the data. As this size increases, it will start showing impacts in space consumption, like, increase in database size and backup size and also time taken for database backup.

There are multiple reasons for size of table to increase like

• tracking lots of columns from different tables
• tracking more actions happening on data

So to optimize our database & backup storage usage and to increase speed in backup process, we just thought what if we store this history/audit records in another database and we found its very easy to do so.

First lets see advantages of this.

• Avoid rapidly growing database size.
• Reduced database backup size.
• Speed up in backup process.
• Data isolation.

Now lets see how to store audit records to second database.

1. Update your Gemfile

We used gem audited to keep track of our data. So you can add below entry to your Gemfile.

gem “audited”, “~> 4.7”

2. Create configuration for second database.

We can configure our application to connect to second database using YAML file similar to our database.yml code.

# config/audited.yml
development:
  encoding: utf8
  adapter: postgresql
  database: audit_development
  port: 5432
production:
  encoding: utf8
  adapter: postgresql
  database: audit_production
  port: 5432

The purpose of this configuration file is to have a nice clean place to store our database connection configuration options.

Note: Assuming database has already been created, and running on default postgres port i.e 5432.

3. Connect to the second database.

Using ActiveRecord::Base.establish_connection method, we can connect to second database. Using our YAML configurations let’s create connection to our second database.

Also let Audited::Audit table (i.e table which stores audit records) to read & write data to second database.

# config/initializers/audited.rb
AUDIT_DB = YAML.load_file(
  File.join(Rails.root, "config", "audited.yml")
)[Rails.env.to_s]
# Configure Audited to read/write to second database
Audited::Audit.class_eval do
  establish_connection AUDIT_DB
end

4. Create “audits” table in second database

Audited gem uses audits table to store model related changes. You can generate migration for audits table using below command,

rails generate audited:install

For more information refer to gem documentation.

# db/migrate/20180629113852_install_audited.rb
class InstallAudited < ActiveRecord::Migration[5.2]
  def self.up
    create_table :audits, :force => true do |t|
      t.column :auditable_id, :integer
      t.column :auditable_type, :string
      t.column :associated_id, :integer
      t.column :associated_type, :string
      t.column :user_id, :integer
      t.column :user_type, :string
      t.column :username, :string
      t.column :action, :string
      t.column :audited_changes, :text
      t.column :version, :integer, :default => 0
      t.column :comment, :string
      t.column :remote_address, :string
      t.column :request_uuid, :string
      t.column :created_at, :datetime
    end
    add_index :audits, [:auditable_type, :auditable_id], 
              :name => 'auditable_index'
    add_index :audits, [:associated_type, :associated_id], 
              :name => 'associated_index'
    add_index :audits, [:user_id, :user_type], :name => 'user_index'
    add_index :audits, :request_uuid
    add_index :audits, :created_at
  end
  def self.down
    drop_table :audits
  end
end

Wait, we don’t simply want to run this migration, because this will update schema in our Rails application, instead we want this migration to be executed on our second database.

Hence we need to update generated migration so it should connect with second database as below. (Note changes highlighted.)

# db/migrate/20180629113852_install_audited.rb
class InstallAudited < ActiveRecord::Migration[5.2]
  def self.up
    Audited::Audit.connection.create_table :audits, 
    :force => true do |t|
      t.column :auditable_id, :integer
      t.column :auditable_type, :string
      t.column :associated_id, :integer
      t.column :associated_type, :string
      t.column :user_id, :integer
      t.column :user_type, :string
      t.column :username, :string
      t.column :action, :string
      t.column :audited_changes, :text
      t.column :version, :integer, :default => 0
      t.column :comment, :string
      t.column :remote_address, :string
      t.column :request_uuid, :string
      t.column :created_at, :datetime
    end
    Audited::Audit.connection.add_index :audits,
      [:auditable_type, :auditable_id], :name => 'auditable_index'
    Audited::Audit.connection.add_index :audits,
      [:associated_type, :associated_id],:name => 'associated_index'
    Audited::Audit.connection.add_index :audits,
      [:user_id, :user_type], :name => 'user_index'
    Audited::Audit.connection.add_index :audits, :request_uuid
    Audited::Audit.connection.add_index :audits, :created_at
  end
  def self.down
    Audited::Audit.connection.drop_table :audits
  end
end

And then execute migration in order to create our table in second database.

rake db:migrate

That’s it, now all your audit records will be stored in second database.

Happy auditing !! 😃