What Is SQL Used For? Kubernetes and Secure Infrastructures

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.

At Timescale, we love SQL. We built TimescaleDB, our relational database for time-series, on a foundation of PostgreSQL. We think a database should be boring, meaning it’s easy to use, scales without much effort, and you never have to think about its reliability or performance. When it comes to databases, boring is awesome. By packaging TimescaleDB as an extension to PostgreSQL, we not only get rock-solid dependability but also enable developers to use SQL, one of the world’s most popular programming languages, to build great time-series applications. To celebrate our love for SQL, we interviewed several of our favorite PostgreSQL community members for this series on how they use SQL for their daily projects, tool recommendations, and beginner tips.Today, we’re speaking with Michael Aboagye. We hope this series helps you learn about new tools, community leaders, and applications for PostgreSQL. As a complement to this series, be sure to check out our interviews with David Kohn and Miranda Auhl on how they learned SQL, and stay tuned for more throughout the month.

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.

Editor’s Note: Check out our blog posts on identifying performance bottlenecks and point-in-time querying with pg_stat_statements to uncover performance issues and improve your database monitoring.

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.

We just launched the third annual State of PostgreSQL survey to learn and share how PostgreSQL is used across the globe—and we'd love to hear about YOUR experiences.Take the State of PostgreSQL 2022 survey! It is open until June 30 at 11:59 p.m. PT.
Ingest and query in milliseconds, even at terabyte scale.
This post was written by
4 min read

Related posts