Optimising PostgreSQL database query using indexes


Bookmark and Share

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 …

Read More

Database transaction in Rails


Bookmark and Share

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 …

Read More

Skip unnecessary files while running Rails Generate

Rails Generate is one of the features provided by Rails that would speed up the application development. However, we won’t be needing all the files generated by rails when running the generate command.

For example:

rails generate controller Home Index new create,  would create the following set of files:

before_altering_the_generator

As one knows from experience, the helper; javascript and stylesheet files are not always used. In fact, most of the time, we would have a single stye.scss, and main.coffee that would take care of 95% of the total behaviour of our application. So, if we do not delete those files after generation, we would just end up with a lot of files in our system which would have no code throughout the life cycle of the program.

Fortunately, rails gives us the ability to customize its own generators so that we can disable these files from being generated. Once we place the below code in the application.rb, it will stop the generation of these extra files.

Now, when you run rails generate controller Home Index new create, you will get less files.

after_altering_generators

Read More

Counter Cache: How to get started

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.

project_list

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 …
Read More