PostgreSQL is a hugely popular Open Source relational database, in this series we're looking at some advantages of PostgreSQL. In this article we'll take a quick look at using it for data warehousing use cases.

PostgreSQL For Data Warehouses

Using PostgreSQL as a starting point for creating a data warehouse with Amazon Redshift can be beneficial for a number of reasons:

Familiarity with PostgreSQL

If you are already familiar with PostgreSQL, then using it as a starting point for your data warehouse can be helpful because you will already be familiar with the SQL syntax and data types used by the database. This can make it easier to create and load tables, write queries, and perform other tasks within your data warehouse.

Integration with PostgreSQL Tools and Libraries

If you are using PostgreSQL as the backend for your data warehouse, you can take advantage of a wide range of tools and libraries that are designed to work with PostgreSQL. This can include tools for ETL (extract, transform, and load) operations, data modelling, and data visualisation, as well as libraries for a variety of programming languages that can help you to integrate your data warehouse with other applications and systems.

Compatibility with PostgreSQL Data Types and Features

PostgreSQL and Redshift share many common data types and features, which makes it easy to migrate data and queries between the two systems. For example, both systems support data types such as text, numbers, and dates, as well as advanced features such as window functions and JSON support. This can make it easier to create and maintain your data warehouse, as you will not need to worry about compatibility issues between the systems.

Ease of Migration

Migrating data and queries from PostgreSQL to Redshift is generally straightforward, as both systems use similar SQL syntax and data types. There are a variety of tools and techniques available for migrating data between the systems, including the COPY command in Redshift, which can be used to load data from files or other sources into your data warehouse. This can make it easier to set up and maintain your data warehouse, as you can leverage your existing knowledge and tools to migrate data and queries between the systems.

Summing Up

PostgreSQL is perfectly viable for a lot of real world Data Warehousing applications. It's especially suited for adding basic reporting capabilities directly on your OLTP databases, providing you with more timely business analytics.

Yet at the same time you are not designing your self into a corner, should the time come to migrate or integrate with other tools.