Materialized View on Rails (Postgres)

Definition:

By proactively computing the outcomes and saving them in a “virtual” table, a materialized view takes the standard view mentioned above and materializes it.

Example:

Create a new rails application with the PostgreSQL database.

# here we create the directory which name denote the rails_application
mkdir rails_applications 

# go to the directory
cd rails_applications

# here we create a new rails application with postgresql database
rails new demo_api --api --database=postgresql

cd demo_api

# create a rails migration

rails g migration add-customer-address-table

class AddCustomerAddressTable < ActiveRecord::Migration[7.0]

  def change
    # here we create the table for customers
    create_table :customers do |t|
      t.string :first_name, null: false
      t.string :last_name, null: false
    end
    # here we create the table for addresses
    create_table :addresses do |t|
      t.references :customer, null: false
      t.string :street
      t.string :city
    end

  end

end

Why did we use a materialized view?

If you want to use the data from 5 different tables with the help of join.5 different tables having a huge amount of data then we used the materialized view.

create the cache amount of data in our disk.

So rails are managed through the disk, in rails, we do not store the data in the database the data is available in the disk when the new row is created or modified the disk will be managed.

when you hit the query and search for particular data, it is searched from the disk and not from the database

Drawbacks of materialized view:

Disk space

I have 5 tables and each table contains 1 lakh data total amount of data is 5 lakh in the future it will be 10 lahk and 15 lahk. More data inserts than disk space errors occur disk overloaded

# create the materialized view

# create a one migration than in this migration add below code

class AddCustomerDetailsMaterializedView < ActiveRecord::Migration[7.0]
  # this method is responsible for create the customer details view
  def up
    # this query is responsible for creating the materialized view
    execute %{
      CREATE MATERIALIZED VIEW customer_details AS
        SELECT
          customers.id as customer_id,
          customers.first_name as first_name,
          customers.last_name as last_name,
          addresses.street as street,
          addresses.city as city
        FROM
          customers
        JOIN addresses ON
          customers.id = addresses.customer_id
    }
    # this query is responsible for the create the unique index
    execute %{
      CREATE UNIQUE INDEX
        customer_details_customer_id
      ON
        customer_details(customer_id)
    }
  end

  # this method is responsible fot the drop the customer details view
  def down
    execute "DROP MATERIALIZED VIEW customer_details"
  end
end

rails db: create && rails db:migrate

insert into customers (first_name, last_name) values ('bhartee', 'sahare');
insert into addresses (customer_id, street, city) values (1, 'dabha', 'nagpur');

# this command is refresh the data of materialized view

REFRESH MATERIALIZED VIEW customer_details;

select * from customer_details

demo_development-# ;
 customer_id | first_name | last_name | street |  city  
-------------+------------+-----------+--------+--------
           1 | bhartee    | sahare    | Dabha  | Nagpur
(1 row)