#286 — December 19, 2018

Read on the Web

Postgres Weekly

What a year it's been for Postgres! The release of PostgreSQL 11 was the obvious highlight but there's also been a lot of great tutorials and tools we've got to enjoy.

This week we're taking a break from the usual weekly news to cover the top items from 2018, mostly based upon clicks from readers. We'll be back to full speed early in the new year and so wish you a Merry Christmas and Happy New Year.

🐘 Peter Cooper and Craig Kerstiens, editors

P.S. We're launching a 'tip of the week' section in the newsletter next year and are looking for (paid) submissions. See the very end of this issue for more info.

PDF:  The Major Features in Postgres 11, 2018's Big Release — Bruce maintains a popular slidedeck covering the features available in the latest version of Postgres and his roundup for Postgres 11 was our most popular link this year. Great work from Bruce as always.

Bruce Momjian

Postgres's Hidden Gems — A collection of responses to the question: “Whats your favorite hidden gem of Postgres that you wish more people knew about?”

Craig Kerstiens

A Look at High Availability on Google Cloud SQL for PostgreSQL — Download this guide to explore how Cloud SQL can optimize and boost a typical PostgreSQL HA technology stack through continuous health checks, synchronous data replication across zones, and automatic failover and scalability.

Google Cloud sponsor

My Favorite Postgres Queries and Why They Matter — An interesting combination of queries the author has found to be interesting/engaging to explore, study, and learn from.

Joshua Otwell

PostgreSQL Meltdown: What Was The Effect on Performance? — Remember the drama over the Spectre and Meltdown CPU vulnerabilities at the start of 2018? Postgres wasn't ultimately *too* badly affected. It'd be nice to see a proper look back at the long term impact soon.

2ndQuadrant

Top tools of 2018

PostgreSQL Anonymizer: A Tool to Mask Personal Information — The most popular tool we linked this year was, perhaps unsurprisingly, a new Postgres extension for masking or replacing personally identifiable information (PII) which helps navigate things like this year's newly-enforced GDPR regulations.

Damien Clochard

Why Upgrade PostgreSQL? Here's Why — A helpful tool to shed light on the real benefits of upgrading from any given version of Postgres. For example: Upgrading from 9.3.2 to 11.1? This site lays out all the benefits.

Hubert depesz Lubaczewski

On-Demand EDB Postgres Databases on the AWS Cloud in Minutes

EDB Postgres Cloud Database Service sponsor

Graphile: Instant GraphQL APIs for Postgres Databases — Auto-discovers tables, columns, relations, procedures and more, then runs a high-performance secure GraphQL API server that adheres to best practices.

Benjie Gillam

Reducing Disk Bloat with pg_repack — An extension for removing ‘bloat’ from tables & indexes without holding an exclusive lock. GitHub repo.

Knut Ingvald Dietzel

pgbedrock: A Declarative Role and Privilege Management Tool — A tool from Squarespace for managing a Postgres cluster’s roles, role memberships, schema ownership, and privileges in a declarative way from a specification file written in YAML.

Squarespace Inc.

Top tutorials of 2018

Materialized Views vs. Rollup Tables in Postgres — The second most popular link of the year comes from our own Craig Kerstiens! Materialized views are often used to make reporting faster, however another approach (rollup tables) can have some advantages while delivering the same benefits.

Craig Kerstiens

PostgreSQL Exercises — If you’re looking for a way to practice your SQL skills over the holiday season, PG Exercises offers some good example data and exercises to hone your skills.

Alisdair Owens

Three Database Modelling Anti-Patterns — Looking into some classic anti-patterns: entity attribute values, multiple values in a single column, and how using UUIDs can be an anti-pattern too.

Dimitri Fontaine

Postgres Features You May Not Have Tried But Should — Spoiler.. includes pub/sub, table inheritance, triggers, arrays, and more.

RapidLoop

Selecting All Columns Except One in Postgres — Google’s BigQuery has EXCEPT and REPLACE keywords for removing or replacing column results mid-query. Here’s a look at emulating such functionality on Postgres.

Lukas Eder

💡 Coming in 2019 — Tip of the Week

Next year we're launching a new section in the newsletter dedicated to a tip of the week. Just something that will help you in your day to day use of Postgres whether a tool, SQL technique, or a command line option you didn't realize existed..

The fun part is we're looking for submissions and we'll give you $50 (via PayPal) for any that get properly written up and used in an issue.

Interested? Reach out via this submission form. It's OK if you can't write up the tip in full just now, pitch us on your idea and we'll let you know if we're interested.