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 ways to run Shell Commands in Ruby


Bookmark and Share

During development of an application, there will come cases when we need to access system command from our program itself. In some cases we just need to know if the status was complete, in some cases, we also need to know the output the application returns.But before we start, let’s find out what Shell Commands are. Simply put, the shell is a program that takes your commands from the keyboard and gives them to the operating system to perform. There are several additional shell programs available on a typical Linux system. Described below are 6 different way to execute a shell script from ruby and their properties.

  • Exec
  • System
  • Backticks ()
  • IO#popen
  • open3#popen3
  • open4#popen4
  • Exec

    Kernel#exec (or simply exec) replaces the current process by running the given command, which can take one of the following forms:

    exec(commandline)
    command line string which is passed to the standard shell

    exec(cmdname, arg1, …)
    command name and one or more arguments (no shell)

    exec([cmdname, argv0], arg1, …)
    command name, argv and zero or more arguments (no shell)

    In the first form, the string is taken as a command line that is subject to shell expansion before being executed.

    In the second form (exec(“command1”, “arg1”, …)), the first is taken …

    Read More

    New binding.irb introduced in ruby 2.4


    Bookmark and Share

    Ruby 2.4 will have the feature to introduce a REPL session, using IRB, in between your code execution for better debugging. IRB, which stands for Interactive Ruby, is the standard REPL which is bundled along with ruby. Pry is a popular alternative for IRB, which has many developer-friendly features like tab compilation and syntax highlighting. One of the most heavily used features of pry is the ability to introduce a REPL session in between your code execution for better debugging. Instead of using p or puts to print the result and various variables, this helps us try out various codes and fixes in between the code to find the right solution.

    binding.pry being used.

     

    To use binding.irb in your code, you need to require the IRB library to your code and call binding.irb where you want to introduce the REPL.

    and you will see a REPL like below.

    screen-shot-2016-11-22-at-10-47-27-am

     

    Read More

    Deploying Sidekiq to Ubuntu 16.04


    Bookmark and Share

    Sidekiq is a popular background processing tool available in Ruby. It’s fast, robust and reliable compared to other solutions out there. Sidekiq run as a process outside of rails (but including the rails environment), which means it doesn’t start when you start your rails application. During development, we start sidekiq in another terminal (or tab) using the command

    to run it as a daemon we use the -d option

    To kill a sidekiq daemon, you need to do  the PID of the sidekiq process. When a sidekiq process starts it enters its pid to file which can be found at

    So the command to stop it would be

    But making it a daemon is not a good idea, as there is no code from sidekiq to restart the process when it fails or exits on its own. So in ubuntu, which is our favorite OS for the production server, we make sidekiq a systemd process.

    Before we make it into a service and if you are using rvm you need to create a wrapper for systemd so that ruby with all the gems are available for it.

    Once that is done you need to create a sidekiq.service file under your ‘/etc/systemd/system/‘. You can find …

    Read More