What Is SQL Used For? Kubernetes and Secure Infrastructures

Michael Aboagye, Vettabase

A PostgreSQL consultant who works as freelance Kubernetes systems and security engineer, Michael Aboagye has been using SQL for five years and is a trusted member of the PostgreSQL community. He began using the programming language as a means to validate data on the server side via PL/pgSQL but now uses it primarily for data analysis. Without SQL, Michael could not achieve what he says is his major accomplishment: solving clients’ technical problems.

In this blog post, the engineer shares an essential SQL tip for beginners, the best tools to solve daily problems when handling Kubernetes clusters, and the real reason he got into computing in the first place: learning You Got Served dance moves.

What is your current role, and what does it entail?

I work as a freelance Kubernetes systems/security engineer and PostgreSQL consultant at Vettabase Ltd. At Vettabase, I help clients optimize queries and automate database cluster deployment and secure infrastructure. At other times, I help random clients work on data engineering and analysis projects.

Do you remember your first interaction with a computer or how you got into tech?

Yes, 18 years ago at Ghana Secondary School, Koforidua. Honestly, my friends and I used to learn hip-hop dancing "moves" from Omarion of B2K fame, Marques Houston, Usher, and the entire You Got Served dancing crew via the school computing lab. Then YouTube emerged on the scene in its early days. So after school, I had another chance to learn basic computing with a Pentium PC.

I started with Windows. Less than a year later, I opted for the Unix operating system. I chose Unix because it requires minimal resources. Pentium PC could not manage the Windows operating system. My journey with Unix started with a Ubuntu server on a different computer. Then I switched to a Debian server to learn Linux systems management—working with Vim, creating files and folders via the terminal, and so on.

How long have you been using SQL?

I have been using SQL for five years.

Why did you start using it, and what was the problem you were solving?

Let’s start with a little intro: validating data on the client side does not prevent data quality issues. Also, I have learned not to rely on client-side validation because users can evade data validation. However, on the server side, we (database developers) have total control over incoming data from the client side. So back in the day, I used SQL to validate data on the server side via PL/pgSQL.

However, in recent times, I have relied on other tools too. There was a time I had to validate data in transit from another data source to the PostgreSQL database. I decided to use Great Expectations instead of PL/pgSQL because I had to monitor data quality in real time.

What problems do you solve with it now?

I often use SQL to perform data analysis via dbt apart from validating transactions on the server side.

“A SQL tip? Making complex queries as simple as A, B, and C. If SQL queries are readable, it's easier for other database developers to debug and improve a SQL query”

Is there a SQL query you're particularly proud of?

I think generating another table out of two or more tables via PostgreSQL views. Then, updating the newly generated table via materialized views. I think Timescale recently introduced materialized views for real-time data. Quite similar to materialized views in PostgreSQL.

What is the one SQL tip you wish someone had given you when you started learning or using it?

Making complex queries as simple as A, B, and C. If SQL queries are readable, it's easier for other database developers to debug and improve a SQL query.

SQL has been around for so long. Do you still need to update your knowledge from time to time? If so, which resources do you use?

Yes. I always need to update my SQL skills to discover new ways of optimizing queries to enhance performance. I get random tips from Federico Razzzoli of Vettabase Ltd. and learn new SQL stuff from pganalyze blog, and Stack Overflow. But on a daily basis, I refer to PostgreSQL and TimescaleDB docs for updates on SQL for transactions and time-series queries.

What are your favorite PostgreSQL tools? Any tips you'd like to share?

I do not have a set of favorite tools. I look out for suitable tools designed to resolve specific problems. For instance, if I want to find out which tables consist of many slow queries, I use pg_stat. In a non-Kubernetes environment, I prefer to use pg_basebackup to backup the database, while I use Velero and Restic to backup PostgreSQL objects in a Kubernetes cluster. For database migration, I choose between pg_loader and Longhorn. And, of course, I use TimescaleDB for time-series data.