In just under a month, I’ll be in Seattle preparing for the PASS Data Community SUMMIT 2022. This is the first in-person Summit since 2019, and the first ever SUMMIT to offer both in-person and virtual attendance. In many ways, this is a homecoming for the #sqlfamily just as much as it is the premier data event for the growing Microsoft data platform. 🎉
I’m honestly thrilled to be a part of the event, especially because I get to offer a full-day precon session about PostgreSQL, the most advanced open-source relational database in the world… and one that Microsoft, Amazon, Google, and countless others continue to invest significant resources into.
Why is there a PostgreSQL precon at an event focused primarily on the Microsoft Data Platform… and why should you consider attending the training and PASS SUMMIT?
I’m glad you asked. 😉
A SQL Server Developer Walks Into a New Job
I’ve been working with data and software development for nearly 20 years. The first two years I built applications with open-source databases, primarily MySQL and PostgreSQL. For the next 14 years I worked for one software company that exclusively used SQL Server and .NET for all development. It was here that I got to know about PASS, SQL Saturday, and the larger #sqlfamily.
But then, a little more than four years ago, I joined a new company to help grow the data analysis team. Between the time I accepted the job and my first day, someone forgot to tell me that they were migrating away from SQL Server to Amazon RDS PostgreSQL.
That was a rude awakening on my first day.
I had to quickly figure out how to do even the most basic things with PostgreSQL that were so easy with SQL Server.
- What IDE could I use without SSMS?
- Why can’t I just select the TOP(1) record from a table?
- How do I set (and use) a variable in a script?
- It’s 2018 and the best thing I can get to tune my query is this text dump from EXPLAIN and are you kidding me this is torture!!!!!!!! (only slightly paraphrased quote)
I started to quickly find success as I asked for help, applied principles from my years with SQL Server and all that I learned from #sqlfamily, and eventually… by not constantly wishing everything was like SQL Server.
The more I learned, the more I grew to love PostgreSQL and the community that surrounds it.
Ever since, I’ve been on a bit of a mission to pass on as much of that knowledge as I can… even though I know there’s still so much more I have to learn.
From PostgreSQL Newbie to Knowledgeable Hacker
The training course is intended to be a start to finish overview of the major parts of PostgreSQL and how they relate to the experience you already have as a SQL Server developer or DBA. While you’ll have the best experience following along inside of PostgreSQL yourself, you will still learn a lot (and walk away with all the training scripts and slides) by attending.
The expected outline of the course will look like this. Because it’s difficult to determine ahead of time the level of experience that everyone has coming into the course, this may be adjusted this slightly as we go, but again, any of the prepared material we don’t cover fully will still be provided and I’m happy to discuss it with attendees afterwards.
- Connecting to PostgreSQL and running your first SQL – I want you to experience PostgreSQL as quickly as possible. We’ll discuss a few good options for connecting to PostgreSQL quickly, run a few scripts, and talk about what we learned.
- Installation options – While it is assumed that attendees will have ready access to a PostgreSQL server to start the session, we will discuss the various options for installing a cluster. Remember, PostgreSQL is an open-source project which doesn’t have a single company managing it like a commercial product providing finely tuned GUIs for all of your settings.
- You’re Not in Kansas Anymore – Before we dive in too deeply, we’ll take a few minutes to talk about the major differences between SQL Server and PostgreSQL that are likely to surprise you. Knowing is half the battle. 😉
- Configuration – Like any complex database solution, there are many configuration settings. Knowing which ones to look at first, where to find help, and how to test them can mean the difference between fast queries or a dead server.
- Intro to psql – Many of us have grown to love a well-designed GUI for interacting with our database. It took me too long to realize that understanding `psql` and learning the basics made me a better PostgreSQL developer and administrator. This could be a half-day course unto itself, but you owe it to yourself to become familiar with this 🔥 tool.
- Hands-on SQL comparison – Now that you’ve gotten connected and run some basic SQL, we’ll take time to look at many of the first “gotcha” moments writing queries in PostgreSQL. By the end of this section, you’ll be ready to read and write many of your common SQL pattens in a new way that works with PostgreSQL.
- Enhancing PostgreSQL with Extensions – Extensions are the magic power of PostgreSQL and one of the primary reasons so many companies are investing heavily in the platform. We’ll look at a few of the must-have extensions that are available with most PostgreSQL offerings.
- Indexing – Indexing is indexing… until it’s not and you have access to multiple datatype specific index types. 🤯
- Query Tuning – We can’t hide from it any longer. It’s time to dive into the world of EXPLAIN and query tuning in PostgreSQL. There are multi-hour classes available on this topic alone, so we can’t dive too deep, but you’ll know what to do with the wall of text and how to take next steps to improve your queries.
- Functions and Stored Procedures – Once again, they’re the same… but different. And functions aren’t always told they’re bad and to be avoided at all costs. They actually get invited to parties. 😊
- Triggers – Like functions, triggers aren’t shunned at all costs. How they work is similar (but again… different… and maybe better) and PostgreSQL developers believe they have a valuable purpose that doesn’t have to be a hidden menace.
🎉 BONUS section 🎉
No training would be complete without a review of how to get help and connect with other developers. Although we’ll certainly talk about community throughout the day, I want to take a few minutes at the end of the session to point you in the right direction to continue learning about PostgreSQL and continue being successful when the time comes.
What We Won’t Cover Directly
I know a full day of training feels like we should be able to cover everything. I must keep reminding myself that some subjects are just too deep and specific for the first into most users will have to this new (to them) database server.
After receiving some questions about the course recently, I changed the title to more clearly align with the description of the course (and the content outline above). That is, the course will more closely align with users that are database developers, architects, and “accidental DBAs”.
Therefore, this time around, we will not specifically cover some deeper-level topics that would be labeled more classic “DBA” tasks including:
- More complex backup and recovery for enterprise installations
- HA and replication setups
- Upgrades from one version of Postgres to another
- Security and access deep dive
- Deeper database maintenance scenarios
I have no doubt that some questions and side discussions will hit on aspects of these topics, but we won’t do any hands-on work here specifically. Maybe that will be part two for next year. 😀
Please Join Us!!
I hope that this precon is helpful to many folks attending SUMMIT. Coupled with the other fantastic precon and 300+ regular sessions, there is so much to learn at the PASS Data Community SUMMIT this year as we have the opportunity to come together for the first time in three years.
Please join us, either virtually or in-person if you’re able! I have no doubt it will be the highlight of your year.
See you November 15-18!
