As a part of our database optimization series, this article is related to creating materializing views in the database.

Materialized View Purpose

Before starting with a materialized view, let’s talk about database views.

What is a database view?

A database view is a stored set of queries, which gets executed whenever a view is called or evoked. Unlike the regular tables, the view doesn’t occupy any physical space in your hard disk but its schema and everything is stored in the system memory. It helps abstract away the underlying tables and makes it easier to work with.

They can also be called as pseudo tables.

Quoted from the PostgerSQL documentation.

Making liberal use of views is a key aspect of good SQL database design. Views allow you to encapsulate the details of the structure of your tables, which might change as your application evolves, behind consistent interfaces.

 

Now to access all the managers

Making more use of views makes your DB design much cleaner, but here we are talking more about using Materializing views. As that would lead to the more direct performance boost.

So what is a Materialized view?

The materializing view was first introduced in oracle. But …

Introduction

One of the major requirements for any online business is to have a backend that either provides or can be extended to provide an API response. Building  websites with static HTML and simple jquery ajax is coming to an end. In this era, Javascript frameworks rules the market. Hence, it is a good decision for the database to support JSON, as JSON is becoming the glue that connects the frontend and backend.

Rails have an inbuilt support for generating JSON, as it’s our swiss army knife of web development, and encourages the REST URL structure . And its a good choice for building API. It is good enough to a particular point of growth. Very soon you will reach bottlenecks, where you have more requests than you can handle and you have to either spawn up more servers or use some concurrent languages like elixir, go, etc. Before we go to that scale and burn down the existing codebase, we can use database to generate JSON responses for us, which is 10 times faster in generating JSON than Rails (though more verbose).

Since PostgreSQL 9.2, the database has taken a major leap in supporting JSON. The …

Those who have been starting with Rails 5, must have noticed the new application_record.rb file present in your model folder. And all new models seems to be inheriting the application_record.rb instead of the ActiveRecord::Base. This is done so that we don’t pollute the ActiveRecord::Base namespace with our own extensions.  Before when we require something, say an extension to the ActiveRecord itself we used to have it included using the following code.

Now the problem with this approach is that when we use rails engines this NewFeature gets added in there as well, and it could end up doing things that we didn’t expect.

With the new application_record.rb, which would be inherited by all the models, we need to include the new module at the ApplicationRecord and it would be available as the new feature of ActiveRecord. Every new engine generated using rails plugin new would also be having their own application_reocord.rb

One more point to note is that we can place application wide hooks in this file. So if you were to do

it would be triggered when a new record is created in any of the models of the rails application.

At Red Panthers PostgreSQL is our go to database we use it everywhere. So thinking about how to optimize our database performance is one of the most talked about topic at our office. The best way to speed up report generation and data retrieval within a rails application is to leave it to the database, as they have algorithms and optimizations build just for that. We always felt that most Ruby on Rails projects out there, do not use the full potential of a database and they usually just limit it to a data store. PostgreSQL or any database for that matter is much more than that.

We would be blogging on how we use PostgreSQL in our projects to speed up our client’s applications. This particle is the first part of a series of article we would be writing on database optimization.

Database Indexes:

Indexes are a special lookup table that the database search engine can use to speed up data retrieval. An Index is similar to a pointer to a particular row of a table. As a real world example, consider a Britannica Encyclopedia with 22 volumes of books, and an extra book listing  the index,with which you can find out the …

Most of the rails applications build, works around databases. And the most commonly used relationship in database is the one to many relationship. In rails its represented as has_many on the parent side and belong_to on the child’s side. Now, as we write the relationship we also need to make sure what should be done to the children when the parent is destroyed. Else we will be stuck with a lot of orphan records once the parent is destroyed.

So to avoid that, we pass in the dependent attribute at the parents side, like below:

has_many :users, dependent: :destroy

The above code will run the destroy method on the children, when the parent is destroyed.

Like destroy, rails provides a total of five options. They are

• 1: destroy – run the destroy method on all the associated objects, there by also triggering the callbacks
• 2: delete_all – causes the associated methods to be deleted directly from DB, no callbacks triggered. This would be a faster, compared to :destroy, to delete the associated models.
• 3: nullify – sets the foreign key to be set to NULL. no callbacks triggered. We use it when we don’t want the children to be …
  

A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties called the atomicity, consistency, isolation and durability (ACID) properties, to qualify as a transaction.

We use database transactions to make sure that all the instructions we send to database are successful, and would cause changes to the database only if they are successful. Let’s say that you are working on a banking application which would withdraw money from one account and deposit into another account. The code for it would look like below

But for some reason, the withdrawal was successful but the deposit was not, the amount was taken out but never deposited to the other user.To avoid these kind of issues, database has a functionality called transactions, in which you can  build up each sql query. But if for any reason, any of the sql statements fails or an exception rises in the block, all the transactions are rolled back to their original form.

In rails, the transaction method is available as class method and instance method, but the functionality for both is same. There is no difference when you will use.

and

The …

after_save, after_create and after_commit are called active record call backs in rails. They get executed when we work on the database, similarly we also have before_* callback and callbacks on destroy as well. In this article I will explain you about the difference between *_save, *_create and *_commit callbacks.

The purpose of each as per rails docs:

after_create
Is called after Base.save on new objects that haven‘t been saved yet (no record exists)

after_save
Is called after Base.save (regardless of whether it‘s a create or update save)

after_commit
Is called after the database transaction is completed.

Now to explain the real difference between the three, we must first explain about database transaction. They are a protective block around a group of sql statements, that are permanent only if all of them succeed in a single atomic statement.

When rails execute a create, the after_save and after_create would be called within the transaction block of the create statement. So they will be executed before executing the sql statement to make permanent changes in the DB. If the query fails, then no change will happen to the DB, but we would have executed the instructions of the after_create and after_save block.

Where as after_commit, is called after the execution of the final/outer transaction block. Thus the changes in the DB would be permanent.

          “God object: In object-oriented programming, is an object that knows too much or                  does too much.” 

– Source: Wikipedia

God class in OOP doesn’t stand for a good thing; here it’s called an anti-pattern. In Object Oriented Programming, we create a new class to define the properties and capabilities of an object. It is using these objects that we build our system, mimicking how it would work in the real world.

For example: Let’s assume that we are trying to build a software to manage the office of a school.

In a typical school office, there would be different departments to handle different parts of the administration. For example, the Purchase department for the purchases, the Accounts department for the accounts, the Records department for the records, the HR department to manage the staff. When one department wants something from another department, it would ask that department for the information. As per protocol, it shouldn’t take the files from the other department without the staff or the department lending it.

So, mimicking the real world would mean that we build one class for each department of the office. We should build a class called Accounting, and in that class we will write all the functions that the Accounts department does, and only what it does. We will not write the functions for the HR department in the account class. Keeping all the code for a particular function at a single place makes our code more efficient and cleaner. Now every time we want the Accounts department to do something, we will create an object of that class and pass the data we have to get the information we need.

Note: In an actual …

Displaying the number of tasks under a project or the number of comments in a post or the number of users in an organization or anything similar is a common requirement in most rails applications. The code for doing it is also simple- @project.tasks.count; but the problem with this code is that every time you run it, you are counting the number of tasks of that project one by one. So, the speed of execution decreases with more number of rows. This code will slow down your page load, if you are displaying the details of more than one project in your page as shown below.

To speed this up, rails gives you an in-build mechanism called “Counter Cache“. As the name suggests, it literally means to cache the number of referenced rows it has (number of tasks a project has).

Example code definition

To implement counter_cache, you need to pass in the counter_cache: true option along with the belongs_to relationship. You also need to add a migration to add an extra column called tasks_count to store the count. This needs to be added to the other model, which has the has_many reference.

Migration

If you are adding counter cache to an existing system, you need to update your tasks_count with the existing counts. To do that, one can use the code given below. Either place the code along with the migration or run it in console in both production/development environments.

Also note that the tasks_count is just the default column name; if you wish to change it with another name, just pass that name along with the :counter_cache option as below.

Now, to use the counter cache in your calculations, you should use the method “size” instead of “count”. The method “size” will use the counter_cache if its present, where as using “.count” itself would do the actual sql count.

Points to Remember

• :counter_cache is the optional attribute of a belongs_to relationship
• It …