A solid pre-flight checklist by PG aficionado Craig Kerstiens. It covers all the essentials: backups, high-availability, logging, statement timeouts & connection pooling.

This week we’re talking about by Postgres with Craig Kerstiens, Chief Product Officer at Crunchy Data, and a well known ambassador for Postgres. Just Postgres. That’s what this week’s show is about.

Craig Kerstiens:

Today I’m excited to introduce a new place for devs to polish their Postgres skills, a Postgres playground from Crunchy Data. What is the playground? Put simply it is:

• Postgres running in your local web browser
• With canned datasets you can load
• Guided tutorials to follow along to learn about the power of Postgres

Since it’s running in the browser you can only connect to it using the embedded psql interface provided. So, it’s not actually useful for real workloads, but it is useful for learning and experimenting.

Craig Kerstiens on the little wins coming in Postgres 14, which is scheduled for the end of September:

Postgres is, and for some time will continue to be, the first database I turn to. As Postgres focuses on the little things, it just deepens my commitment to it. Why look elsewhere when the bond just grows over time? So today I wanted to call some extra attention to those little things, the ones that don’t get the spotlight, but simply make a developer’s life better.

He goes on to highlight the JSON syntax improvements, read-only roles, what’s new in psql, and more.

Craig Kerstiens:

Postgres has had “JSON” support for nearly 10 years now. I put JSON in quotes because well, 10 years ago when we announced JSON support we kinda cheated. We validated JSON was valid and then put it into a standard text field. Two years later in 2014 with Postgres 9.4 we got more proper JSON support with the JSONB datatype. My colleague @will likes to state that the B stands for better. In Postgres 14, the JSONB support is indeed getting way better.

A small but solid improvement to how you query JSONB, making it more JSON-y than ever.

Craig Kerstiens told me about this on our recent Postgres episode of The Changelog and my jaw about dropped out of my mouth.

… earlier today I was starting to wonder why couldn’t I do more machine learning directly inside [Postgres]. Yeah, there is madlib, but what if I wanted to write my own recommendation engine? So I set out on a total detour of a few hours and lo and behold, I can probably do a lot more of this in Postgres than I realized before. What follows is a quick walkthrough of getting a recommendation engine setup directly inside Postgres.

Craig doesn’t necessarily suggest you put this kind of solution in production, but he doesn’t come out and say don’t do it either. 😉

PostgreSQL aficionado Craig Kerstiens joins Jerod to talk about his (and our) favorite relational database. Craig details why Postgres is unique in the world of open source databases, which features are most exciting, the many things you can make Postgres do, and what the future might hold. Oh, and some awesome psql tips & tricks!

It feels like we’re starting to pass the peak of the hype cycle of microservices. It’s no longer multiple times a week we now see a blog post of “How I migrated my monolith to 150 services”. Now I often hear a bit more of the counter: “I don’t hate my monolith, I just care that things stay performant”

What follows is an excellent rundown of all the advantages that a monolith has over microservices. For a real-world case study, listen to the details of Segment’s transition back to a monorepo.

Craig Kerstiens:

I’ve learned a lot of skills over the course of my career, but no technical skill more useful than SQL. SQL stands out to me as the most valuable skill for a few reasons:

1. It is valuable across different roles and disciplines
2. Learning it once doesn’t really require re-learning
3. You seem like a superhero. You seem extra powerful when you know it because of the amount of people that aren’t fluent

I tend to agree. I still use (and sometimes love) ORMs and database libraries while building apps, but the more I’ve learned SQL over the years, the more I appreciate it for what it is.

Craig drills into each of his 3 points above in this excellent post.

Craig Kerstiens on why common table expressions (CTEs) are so cool:

For some reason most people throw out principles we follow in other languages such as commenting and composability just for SQL. … [CTEs] actually makes SQL both readable and composable, and even for my own queries capable of coming back to them months later and understanding them, where previously they would not be.

Click through for examples of these in use.