Mastering Postgres Video Course By Aaron Francis
Anytime you bring up databases in public, someone will inevitably suggest that Postgres (aka PostgreSQL) can address all of your data storage needs and then some. I love relational databases as much as the next person; but, I've never felt the kind of fervor and passion that seem to permeate the Postgres ecosystem. As an outsider, it's fascinating! So when I saw that Aaron Francis had a video course on Mastering Postgres, I jumped at a chance to get an insider's look at the database technology that seems to have a cult-like following.
I recently finished one of Aaron's other video courses, High Performance SQLite, and was fully impressed with both the production quality and the content of the course materials. His Mastering Postgres course follows in stride, delivering an in depth review of the PostgreSQL database features using fun and engaging bite-size video chapters. Though, at the time of this writing, several of the final modules are still pending: JSON, Operating Postgres, and Popular Extensions.
Aside: I believe that the vast number of available extensions is a big part of what makes Postgres such a popular database. It seems that for every problem that exists, someone will enthusiastically tell you that there's a Postgres extension that handles the solution space.
As someone who is a big fan of SQL, Postgres is simultaneously familiar and disorienting. It has a lot of the same basic stuff that other databases have; but, it has some oddly verbose and funky syntax and a host of features that seem dramatically different.
For example, it has its own notion of "schemas" for grouping related database tables. And it has compound ranges (both inclusive and exclusive) with range operators. And it has Boolean-based aggregate functions such as BOOL_AND() and BOOL_OR(). And it has many functions for generating sequences of data such as generate_sequence() or json_to_recordset(). And it allows for indexes to be applied to only a subset of table rows. And it allows for indexes to be defined on Function expressions. And it allows you to create totally custom data types complete with constraints. And it even allows you to define complex column definitions, such as an "Address" column that is a composite of several address-related values.
Seeing this new information is both exciting and befuddling. I get the sense that it's all very powerful; but, I've also spend the last 25-years thinking about data-access in relatively simple CRUD (Create, Read, Update, Delete) scenarios. As such, it's not immediately obvious to me how I might incorporate some of this outlier functionality into my web application architecture.
I know this goes beyond the scope of the video course, but it'd be cool for Aaron to include a small "cookbook" section where some concrete problem-solution thought experiments could be addressed. For those of us that are familiar with SQL but who are new to Postgres, such a section might help shed light on leaning into new ways of managing data.
If you're not coming to this course from a different SQL database technology, the Mastering Postgres course covers a lot of common database functionality as well (albeit with a Postgres slant). And, just as with his SQLite course, Aaron takes a generous amount of time to go over table index design, data access patterns, and performance evaluation and debugging. This covers techniques that are universally applicable and simultaneously underrepresented in a lot of other database teaching materials.
Overall, the Mastering Postgres video course is solid. I'm beginning to see why Postgres has gained such popularity. It seems to have a lot of nice developer ergonomics (despite the verbosity of so many of its directives). I'm looking forward to seeing what Aaron covers in the forthcoming "Popular Extensions" module—as I said above, I suspect this is where a lot of the Postgres magic is going to live.
Reader Comments
We moved from Oracle to Postgres at my previous job and I found it to be (mostly) painless. There were a couple things I found super frustrating though. Off the top of my mind...
You couldn't reliably use simple comparison operators (==, !=) when comparing one string to the other. In some cases, we had to use
IS NOT DISTINCT FROMorIS DISTINCT FROMrespectively. Why the simple operators would sometimes work and other times fail remains a mystery to me.Type coercion was necessary so much that it became burdensome and made SQL query statements extremely verbose (and in a few cases tricky to get right).
I'm sure all these other databases exist for a reason. But I think they're solving problems I don't typically have. So, I personally prefer MySQL because it generally stays out of your way and just works.
@Chris,
I remember him talking about the
DISTINCT FROMstuff in the course; I think it was in the context of comparing non-null values to null values.But, I tend to agree with you about MySQL. It hasn't really got in my way. And, it feels a lot more concise than the stuff I was seeing in the video. But, the truth is, so much of my data workflow is just simple CRUD (and then some more report-level queries). I haven't needed too much.
@Ben Nadel,
Simple problems only need simple solutions 🙃
@Chris,
Reminds me of the "web scale" comic, which will always be one of my favorite videos: https://www.youtube.com/watch?v=b2F-DItXtZs&t=142s
@Ben Nadel,
That's epic! I hadn't seen that one. In fact, it's been a while since I've seen those comics. Thanks for reminding me of them. I'm going to try very hard not to fall down that rabbit hole and stay focused on what I actually had planned for today...but I enjoyed the laugh!
Post A Comment — ❤️ I'd Love To Hear From You! ❤️
Post a Comment →