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 first introduced in oracle. But …

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 leap in supporting JSON. The …

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 note is that Hash index …

Read More