It's All About the Database

Open to Open Source?

Recently I took a detailed look at open source solutions PostgreSQL and GO (the programming language) running on the Linux operating system.

Here are my thoughts:

Linux

For years, we have heard the Linux vs Windows debate.

Windows has been the winner on the corporate desktop for a long time. Open source advantages could now change that.

When Windows was released in the mid-1990s it was a response to users who said the Apple-MacIntosh graphical user interface (GUI) was easier to use. It was. At the time, UNIX was the only alternative to either but did not have a GUI. I should note the MAC OS and LINUX both have ties to UNIX.

A lot has happened since then. Now, most, if not all, popular operating systems like Linux have GUIs. I use Linux Mint and often find its' simpler design easier to navigate than recent versions of Windows.

With servers, it gets more complicated. Especially when you factor on premises VMs or bare-metal servers versus cloud PAAS or IAAS approaches. Regardless, the open-source advantages still apply.

Newer versions of Windows has to run on newer hardware. That's good news for hardware vendors. Bad news for the environment. Linux easily runs on older hardware extending its’ life. This is an environmentally friendly reason to use it since "e-waste" is now a growing issue. In 2019 there was 53.6 million metric tons of e-waste. Experts say it is going to get worse.

Many people say open source software like Linux provides superior security and better quality. It is more flexible and easier to customize. It adheres better to open standards. It avoids vendor lock in, expensive licensing fees and complicated licensing schemes.

Above all, it is FREE and available to anyone.

PostgreSQL

According to the folks at postgresql.org :

“PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads. The origins of PostgreSQL date back to 1986 as part of the POSTGRES project at the University of California at Berkeley and has more than 30 years of active development on the core platform.

PostgreSQL has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of the open source community behind the software to consistently deliver performant and innovative solutions. PostgreSQL runs on all major operating systems, has been ACID-compliant since 2001..”

I took PostgreSQL for a test drive using the COVID19 data vault management solution I described in my June 2020 blog. In June 2020, I was publishing data to an Azure SQL Database. Now I wanted to publish data to a PostgreSQL database.

Please refer to the diagram below to see my approach.

Starting from my Windows server at step (1) I use Powershell to "pull" data to a staging area for some basic data cleansing. Next (2), I "push" CSV and JSON data to my GitHub site. Note: I am publishing the data to the GitHub site to satisfy a recent request to provide easy access to "live" daily and historical CSV and JSON data. This data is now live, available, and updated nightly. See my description on GitHub for details.

In step (3) I use a Linux Bash shell script to "pull" daily changes from GitHub and load them to my PostgreSQL database. I am using the most current version, PostgreSQL 13.

Once I was able to navigate through typical installation and configuration issues like port numbers and fire wall settings, PostgreSQL worked great and continues to. It is an easy database to work with. If you have a lot of SQL Server T-SQL experience like I do, psql the equivalent in PostgreSQL is easy to pick up.

If you want to see how it ranks with other databases check out db-engines.com, a popular site for commercial and open source database rankings.

GO

According to Wikipedia:

"GO [or GOLANG as it is often called] was designed at Google in 2007 to improve programming productivity in an era of multi-core, networked machines and large code bases. The designers wanted to address criticism of other languages in use at Google, but keep their useful characteristics:

1. Static typing and run-time efficiency (like C).
2. Readability and usability (like Python or JavaScript),
3. High-performance networking and multiprocessing"

If you have experience with C or C# like I do, GO is easy to learn. Its quick and now has a large following. It is rapidly becoming a popular language among blockchain developers. Here is an interesting interview that talks about that.

All of this piqued my interest in blockchain since a blockchain is really a distributed or more accurately a replicated database concept. A concept that I have worked with in the past. That said, I decided to take the plunge and learn GO.

Looking Forward

Hopefully you have seen the advantages of working with open source solutions like Linux, PostgreSQL and GO. I have. I am sold.

This came about when a colleague of mine and I were discussing the challenges of building a network of replicated databases. I started looking at the tools you could use to do that.

By the way, one thing I forgot to mention. The API. I would add gRPC to the mix. I have blogged about that in past.

That being said, PostgreSQL, GO, gRPC on Linux would provide a solid foundation for building a network of replicated databases.

It looks like that could be the future.

More about that soon.

Thanks for visiting.