SQL Isn’t That Hard (When You Don’t Approach It From an Object-Oriented Point of View)
John Pruitt, Sr. Software Engineer (Database/SQL) at Timescale
John Pruitt first learned SQL at university, but his first interaction with computers dates back to 1986 when he was only four. Having worked as a developer while completing his bachelor’s in Software Engineering at Auburn University in Alabama, he spent most of his career building time-series applications for the power industry. For a few years, he was also a database administrator (DBA), worked as a consultant, and owned his own business. His work ethic earned him a master’s degree in Engineering Management while working full-time.
In his spare time, you can find John outdoors, going to concerts, fermenting foods, or enjoying his favorite hobby—being a daddy. During the workday, he hones database designs and all the SQL-related elements that power Promscale, Timescale’s observability backend powered by SQL. His one tip for learning this programming language? Do not approach it from an object-oriented programming point of view.
Today, we’re speaking with John Pruitt. We hope this series helps you learn about new tools, training, and applications for PostgreSQL. Be sure to check out our interviews with David Kohn, Miranda Auhl, and Michael Aboagye, and stay tuned for more throughout the month.
Do you remember your first interaction with computers, and what drew you to them?
I was four years old when I was first introduced to computers. My father was a power systems engineer and wrote software as a part of his job. He bought a second-hand Commodore 64 with a stack of games on 5.25-inch floppy disks for me. This was 1986! I remember inserting floppy disks into the drive and waiting for what seemed like forever for the game to load. I couldn’t read yet, but I don’t think I had any instructions for the games anyway. So, I just had to try things until I figured out the controls. It was magical. It was like an interactive storytelling experience.
“Writing SQL is like solving a puzzle”
What do you use SQL for in your current job?
I work on the Promscale product. It is a unified observability backend for Prometheus metrics and OpenTelemetry traces built on PostgreSQL and TimescaleDB. While I do write Go and Rust, my work focuses on the database design and the SQL functions, procedures, and views (among other things) that power Promscale.
What is your everyday routine like?
Currently, I am the furthest westward of anyone on my team, which means that meetings are in the morning for me. In the afternoons, I’m usually heads-down and neck-deep in some tricky SQL. For me, getting into the “flow” is highly satisfying and meditative in a way. Writing SQL is like solving a puzzle.
When did you get into SQL? Can you give a brief overview of your history with SQL?
I was first introduced to SQL in college, but I didn’t really start using it heavily until working professionally. Much of my early experience with SQL was in custom time-series applications for the electric power industry. This work was with Sybase, SQL Server, and Oracle. I spent about three years working as a data architect at a bank. It was this role that took my SQL skills to another level as it was my primary focus, and I was dealing with large enough workloads that I needed to learn some advanced tricks to get it done. My boss at the time fed me some tricks, but I also read books and documentation. Modern SQL and Use the Index, Luke, are both examples of great resources. When I came across a feature I wasn’t familiar with, I tried it out. Experimentation is a great way to learn.
Later, I was lucky enough to work with PostgreSQL both as a developer and DBA. My time as a DBA taught me a great deal about databases that I expect many developers don’t even think about. I am happy to be back in a developer role, but my time as a data architect and DBA made me better at what I’m doing now.
"I’ll probably be ostracized for saying this, but many object-oriented programming practices felt like pointless busywork. Relational data modeling and SQL felt natural to me and mapped more closely to how I think about problems”
How did you learn SQL? Did you teach yourself or take a course?
I took an elective on databases in college, and I was lucky enough to have a couple of on-the-job mentors early in my career who were very knowledgeable in SQL and database tuning. This course was very introductory, however, but helpful nonetheless. After amassing a certain amount of knowledge and experience, it became easy to teach myself using documentation and experimentation. One of the things I appreciate most about PostgreSQL is how stellar the documentation is. If printed, it would be just shy of 3,000 pages. If you want to understand something about the database, you’ll likely find it in the documentation. I do also appreciate online articles that can go into more detail at times than the documentation or explain it more clearly, but you have to be careful about old articles that may no longer hold true.
What were some of your favorite things getting into it? What did you find hard?
I’ll probably be ostracized for saying this, but many object-oriented programming practices felt like pointless busywork. Relational data modeling and SQL felt natural to me and mapped more closely to how I think about problems. You can accomplish so much with a few lines of SQL compared to general-purpose languages.
What problems were you solving when you first started using SQL?
The first time I put SQL to practical use was during my senior design project. We were required to track our work, and I threw together a simple PHP web app backed by a SQL database to do it. The first time I used SQL professionally was intense. I was assigned a project to do alone. I needed to build a system for backing up encrypted medical records to DVDs (this was quite a while ago). I needed to build a web application that could auto-run from the DVD to provide a searchable index of the medical records. I had to figure out how to use an embedded database that could run from read-only media. It was a pretty unique challenge for a first-time database project.
What tips would you give to someone who is just starting to learn SQL now?
Do not approach SQL from an object-oriented programming point of view. Set that way of thinking aside, and approach SQL on its own terms. It’s a different animal. Don’t put it in an object-oriented cage.
“I find SQL to be a joy compared to ‘normal’ programming languages. It’s so easy to just connect to a database and start hacking away on a query”
What tools do you use to work with SQL/ Postgres in particular?
I like the command line psql client for PostgreSQL. It took some getting used to at first, but it is extremely powerful.
What do you find frustrating about SQL or PostgreSQL? What tools do you wish there were but can’t seem to find?
I find SQL to be a joy compared to “normal” programming languages. It’s so easy to just connect to a database and start hacking away on a query. It’s so immediate and interactive that I feel like it doesn’t slow me down or get in my way.
I prefer to write data definition language by hand, and I have never found a tool that would automatically generate graphical database diagrams from an existing database without requiring me to spend hours dragging boxes around to “detangle the mess.” I’ve had to get good at drawing mental pictures. That’s pretty frustrating.
Take the State of PostgreSQL 2022 survey! It is open until June 30 at 11:59 p.m. PT.