TekPub - Hello PostgreSQL (2012)

In this production, a SQL Server DBA (Rob Sullivan) and a developer (Rob Conery) take a deep dive into PostGreSQL (v9.1). This series was originally about Mono and ASP.NET MVC - but "the Robs" quickly found themselves amazed and surprised by just how capable, intelligent and *fast* PostGreSQL is. The goal of this production is to introduce people to PostGres who aren't familiar with the system.

1 - Why Should You Care? 00:29:00
You've got SQL Server, or maybe you use MySQL and you find it in yourself to care at all about PostGres. It lacks tooling, it's too strict, and it can't be as good as what you use right? This episode is for you…

2 - The Perils of MySQL 00:11:02
In this short episode, Rob answers the question "why not MySQL". We leave out the socio - political opinions (in other words… Oracle) and go straight into some of the strange decisions the MySQL team has made - favoring specs and standards over the integrity of your data.

3 - Data Types 00:28:32
PostGreSQL has no shortage of data types - and a first look at the list will make anyone cringe and think the Engineers have taken over the Asylum. But, as it turns out, each data type has a specific purpose and as you're about to see - are very applicable to the work we do every day.

4 - Table Inheritance 00:08:32
PostGreSQL has a very strange concept, built right into the engine: Table Inheritance. At first it might seem that the team lost its mind and is trying to introduce OOP concepts to a relational system… but (as with most things PostGres) - a deeper look reveals a very, very interesting way to partition your system for performance, while at the same time making your table structure a bit more semantic. We'll look deeper at this idea in Episode 7.

5 - Concurrency and the MVCC 00:13:41
Ever had a deadlock happen in your database? Too many writes happening - locking all the reads or shutting dow your application? Every developer faces this problem at some point in their career. The PostGres team has addressed this with the "MVCC" - a snapshotting system that intelligently avoids deadlocking - and it's automatic.

6 - Performance Tuning: Part 1 00:14:20
In this episode Rob Sullivan tweaks the performance of a 5-million row table (from the Stack Overflow dump) and shows how you do it with PostGreSQL's tooling. It's pretty much the same process with any database - but PostGres allows to you do just a bit more, as you'll see.

7 - Performance Tuning: Part 2 - Partitioning 00:15:20
As your database grows, even indexes can't help and become very, very costly to maintain. In this episode The Robs use PostGreSQL's built-in inheritance scheme to break the Stack Overflow Posts table apart into three tables: a Post Root table, a Questions table, and an Answers table. Along the way, they increase performance by 90%…

8 - Querying Text 00:17:58
In this episode Rob Sullivan shows you various ways to query text - including a little gem in Postgres where you can index a function call! We then dive into Full Text Indexing and how it works - slowly shaving milliseconds off of our tags query.

9 - PSQL 00:27:46
Many developers won't use a thing unless it has "good tooling" - which usually means a slick GUI tool with colorful buttons and a compelling experience. In this video, Rob C. dives into psql - the Postgres command line app and shows you just how clean, terse, and simple it is to do basic (and not so basic) tasks without a GUI.