PostgreSQL 9.6 new features


Bookmark and Share

POSTGRESQL is an open-source object-relational database system. It is not controlled by any corporation or other private entity. The source code is available free of charge. PostgreSQL supports transactions, subselects, triggers, views, foreign key referential integrity, and sophisticated locking.

New features in Postgres are:

  • Parallel execution of sequential scans, joins and aggregates.
  • Avoid scanning pages unnecessarily during vacuum freeze operations.
  • Synchronous replication now allows multiple standby servers for increased reliability.
  • Full-text search can now search for phrases (multiple adjacent words).
  • postgres_fdw now supports remote joins, sorts, UPDATEs, and DELETEs.
  • Substantial performance improvements, especially in the area of scalability on multi-CPU-socket servers.

Parallel execution of sequential scans, joins and aggregates

PostgreSQL can devise query plans which can leverage multiple CPUs in order to answer queries faster. This feature is known as the parallel query. Mostly, queries that touch a large amount of data but return only a few rows to the user will get benefit by using Parallel Query. It can now execute a full table scan in multiple parallel processes, up to the limits set by the user.

Avoid scanning pages unnecessarily during vacuum freeze operations

Freezing …

Read More

Difference between Date, Time and DateTime


Bookmark and Share

Date and time are one of the most important aspects which every coder has to deal with in Ruby. Well, let’s get to know how we keep it up alive and functional.
There are 3 different classes in Ruby that handles date and time. They are Date, Time and DateTime. Date and DateTime classes are both from date library. And Time class from its own time library.

In this article we’ll see how Date and Time works. Let’s have a look at each one of them.

Date

When you need a string format of year, month and day, you have to go through Date class.

  • Has date attributes only (year, month, day)
  • Based on integer whole-day intervals from an arbitrary “day zero”
  • Can handle date arithmetic in units of whole days
  • Date object is created with ::new, ::jd, ::ordinal, ::commercial, ::parse, ::strptime, ::today, Time#to_date etc.
  • Takes 4 bytes to store.

Eg:

Time

If you need both date and time values, we can make use of Time class.

  • Has date and time attributes (year, month, day, hour, min, sec, subsec)
  • Can handle negative times before unix time
  • Can handle time arithmetic in units of seconds

Eg:

Also rails provide a really good time class called ActiveSupport::TimeWithZone. It contains all the features the Time

Read More

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

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 you can find out the …

Read More