#324 — September 25, 2019

Read on the Web

Postgres Weekly

Advanced SQL: A Look at 'Window Frames' — A deep dive into advanced uses of window functions (an earlier article covers those, if you need it). Window frames, an SQL feature in which Postgres is leading in support, let you take window functions to a much deeper level to perform complex aggregations. Some great examples in here and a must read.

Michal Konarski

SQLPro for Postgres - macOS and iOS Database Client — A powerful, native iOS and macOS Postgres database client. Easily browse and edit records, either visually or using a truly great query editor. If you work with Postgres make sure to check out SQLPro. Get one year free today.

Hankinsoft Development, Inc sponsor

Yugabyte DB 2.0 GA: A 'Jepsen Tested', High-Performance Distributed SQL DBMS — Yugabyte DB is a Google Spanner-inspired, cloud-native distributed SQL database that’s 100% open source and aims to be Postgres compatible in most cases.

Kannan Muthukkaruppan

A Few Special-Case Performance Enhancements in Postgres 12 — With every new release of PostgreSQL, there are a range of performance enhancements, some general, some very niche. Here’s a (very) brief look at three very specific, niche improvements.

John Naylor

Azure Data Factory Can Now Use Azure Database for PostgreSQL As a Sink — One for Azure users only, specifically users (or potential users) of Azure Data Factory, Microsoft’s data integration service.

Parikshit Savjani

A Practical Example of Using Row Level Security — An example of giving different permissions to different types of user for working upon the same table.

Hans-Jürgen Schönig

▶  Getting PostgreSQL 12 Beta 4 Running in Less Than Six Minutes — A demonstration of how pgenv can make Postgres easier to install.

Luca Ferrari

Manage Linux Control Groups in Postgres with pg_cgroups — The pg_cgroups extension makes it easier for you to manage resource limits for Postgres clusters on Linux by using control groups (a.k.a. cgroups).

Laurenz Albe

📕 20 Patterns to Watch for in Engineering Teams — Actionable insights to help you debug your development process with data. Get your copy.

GitPrime sponsor

What’s Faster? COUNT(*) or COUNT(1)? — Spoiler: It doesn’t matter, unless you’re using Postgres!

Lukas Eder

Comparing Distributed SQL Performance: Yugabyte vs. Aurora PostgreSQL vs. CockroachDB — You should, by now, know to look at benchmarks with a skeptical and trained eye, of course 😄 though in this case, it’s nice to see a competing service actually being recommended for many use cases.

YugaByte

SQLTools: Database Tools for VS Code Users — Supports various database types (Postgres, of course, but Cassandra support has also just been added) and offers a connection explorer, query runner, IntelliSense, bookmarks, query history, etc.

Visual Studio Marketplace

supported by Datadog

💡 Tip of the Week

Using CASE in ORDER BY — the basics

CASE expression is essentially the 'if/then' of the SQL world. Given one value, CASE can return another of your choice – but did you know you're able to use it in ORDER BY clauses to define custom orders based upon other values?

Here's a simple use case. Let's say you have a table listing employees and their job titles:

id | name. | title
-------------------
 1 | Oscar | Cleaner
 2 | Carol | CEO
 3 | Jimbo | CFO
 4 | Bobby | Assistant

If you wanted to have some sort of order based upon job titles, you could use CASE like so:

SELECT * FROM employees ORDER BY
  CASE
  WHEN title = 'CEO' THEN 1
  WHEN title = 'CFO' THEN 2
  WHEN title = 'CTO' THEN 2
  ELSE 3
  END;

This is only a basic example to show off the idea. We'll dig into some more advanced examples in coming weeks.

This week's tip is sponsored by Datadog. Monitor PostgreSQL performance with Datadog.

🗓 Upcoming Events