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.
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.