Select Page

joffi / Pixabay

Recently, I found myself in the situation where I was given the responsibility to decide which database technology to use for an upcoming internal OptimalBI project.

The project is a reporting data engine and, apart from the ability to scale, the initial set of requirements were more business oriented than technically defined. The low cost of running a Relational Database Service (RDS) instance in the Amazon Web Service’s Cloud, therefore narrowed my choice to MySQL and PostgreSQL.

I have been working with MySQL quite a lot over the past two years and I’m used to the way it works. However, from my experience as a developer and MySQL Data Base Administrator, it might not be the best option, as developing complex code requires a significant level of effort.

I was also keen to learn about a Relational Database Management System (RDBMS) technology which is gaining more and more popularity, and, despite having little experience of PostgreSQL I decided to give it a go!

What is PostgreSQL?

PostgreSQL has been around for the last 20 years and claims to be the most advanced open-source object-relational database management system with a focus on extensibility and standards compliance following ANSI-SQL:2008 and is fully ACID (Atomicity, Consistency, Isolation, Durability) compliant.

According to DB-Engines Ranking, it is placed number four in the list of most popular RDBMS used following Oracle, MySQL and MS SQL Server. Some of its well-known users are Yahoo!, Reddit, Skype, Instagram and TripAdvisor.

From a developer’s perspective, there is a range of useful features:

  • Support for function based and partial indexes.
  • Support for a wide variety of data types (including creating user defined types, support for GIS objects, JSON and arrays).
  • Complex, custom design and procedures capability: including common table expressions; such a relief after developing on MySQL! Window functions, full outer joins, a consistent interface when working with date/times, materialised views, set operations (such as INTERSECT, EXCEPT), proper temp table support and lateral subqueries.
  • Ability to plug in a wide variety of languages including C/C++, Java, .Net, Perl, Python, Ruby and others.

All of this provides a toolset to create powerful, flexible, feature rich designs with simple and readable code.

Also, having started my journey into PostgreSQL, I have found some comprehensive documentation to get answers to whatever questions I could think of, however, I have found community support has been a bit lacking.

If you would like to start developing in PostgreSQL, I would recommend having a look at:

In the next blog, I will share some of what I discovered and the features of PostgreSQL I find most interesting.

Anastasia

Citius, Altius, Fortius!

Anastasia blogs about Data Vault and Business Intelligence for both technical and non-technical people.

We run regular business intelligence courses in both Wellington and Auckland.

%d bloggers like this: