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 then in this migration add the code below
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 for 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)
Top comments (0)