Skip to main content

Posts

Showing posts from 2019

Postgres: Optimization & Beyond

Postgres, one of the widely used Relational Database Management System; has been widely adopted due to it's ability to handle different workloads such as web services, warehouses, etc.
Fun Fact: The name Postgres comes from it's predecessor originated from UC Berkley's Ingres Database (INteractive GRaphics iterchangE System; meaning it's Post-INGRES). There are times when the performance is straight forward and in other cases when the expected performance is not met; the Database requires some tweaking in the form of structural modifications to the table, Query Tuning, Configuration improvements, etc.

This article will provide some useful pointers and action plans to become a power-user in optimizing postgres.

What to do when a query is slow?


In most of the cases, the occurrence of a slow query is due to the absence of indexes, for those field that are being used in the where clause of the query.

That should have solved the problem, right? RIGHT?

You:



I hear you; Life…

Flyway - Database Migrations made easy & How not to accidentally Roleback all of your migrations

Flyway - by boxfuse: Is a schema migration tool and it acts more of like a version control for your relational databases.

If you are manually executing your sql scripts or if your administrator is manually executing the sql scripts, on your production or UAT environment, you definitely need this tool to be setup in all of your environments.

Before we proceed:

Statutory Warning: 

Never ever execute the following command, be it your production or UAT environment:

$ flyway clean   # Do not execute this, ever!!!!

Wondering what it does? It roles back whatever table migrations/changes you have done through flyway, along with their data. 

In short, Don't ever execute this command.

Now that we are done with all the warnings:


Installation:It is fairly straight forward:
Run the above command in a shell prompt.
Running the above creates a directory called as flyway-x.x.x/
Inside this directory are many other directories of which, the two most import directories are:
 conf/ - Configuration for eac…