Materialized Views: Caching database query


Bookmark and Share

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

Materialzied View

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 …

Read More

Introduction to generating JSON using PostgreSQL


Bookmark and Share

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 …

Read More

Different types of Index in PostgreSQL


Bookmark and Share

This is part two of our PostgreSQL optimization series. You can read the first article where we discuss when to index here.

PostgreSQL uses a different set of algorithm while indexing tables, each type of algorithm is good for a certain set of data. Here we will be discussing the various algorithms available and when we should be using them. (Note these are the algorithms found in PostgreSQL 9.5)

Algorithms

B-Tree (Balance Tree), is the default algorithm used when we build indexes in Rails. It keeps a sorted copy of our column, which would be our index. So if we want to find the row of the word starting with a then as soon as the words starting with a are over. It will stop searching and return null, as the index has kept everything sorted. It is good in most cases, hence it is the default algorithm used.

Hash is one of the most popular indexing algorithms. But only the equate operator works on it, thus the query planner will only use an index with a hash algorithm if we do an equal operation searching for it. Another point to …

Read More

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

after_create vs after_save vs after_commit

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.

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