#314 — July 17, 2019

Read on the Web

Postgres Weekly

How to Upgrade Your Postgres Passwords to SCRAM — Support for SCRAM authentication was introduced in Postgres 10 and offers significant benefits over MD5. Here’s the how and why, plus how to upgrade your existing logins to using the SCRAM approach.

Jonathan S. Katz

▶  Cleaning Out Crocodiles' Teeth with Postgres Indexes — In a 40 minute talk at Postgres Open (2018), an engineer from Citus Data (now part of Microsoft) told a crocodile-themed story of all the index types in Postgres. This is a lot more educational than it sounds, you’ll learn a lot, and this is a good use of 40 minutes if you need to get your head around Postgres indexing.

Louise Grandjonc

Learn How Postgres Vacuum Tuning Enhances DB Performance — Join the Free PostgreSQL webinar: Postgres Vacuuming Through Pictures and learn key concepts of vacuum and autovacuum in PostgreSQL, Vacuum Operations, how to tune vacuum for Postgres deployment, and more.

2ndQuadrant PostgreSQL Webinars sponsor

Using ICU Collations to Avoid glibc 2.28 Data Corruption — This is a technical one! The newest version of the GNU C Library (2.28) made big changes to Unicode collations (collations are the rules governing how strings are compared or sorted) which could cause issues with your indexes, including corruption. Postgres uses OS-level collations by default but can also use ICU collations, essentially a separate, third party, cross platform set of collations, and this article digs into both why and how you’d make the switch.

Laurenz Albe

PostgreSQL Conference Europe 2019 Now Open for Registrations — It’s in Milan, Italy this October. Speakers won’t be selected until August (the CFP is now closed) but you’re able to get tickets now.

PGConf.EU

A Look at Partitioning Enhancements in Postgres 12 — Postgres 12 gets some “pretty dramatic” improvements in partition selection, referential integrity improvements, and introspection.

Kirk Roybal

Implementing 'Autonomous Transactions' in Postgres — More familiar in the Oracle world, autonomous transactions are independent transactions that are initiated by other transactions (i.e. they have a different context and don’t interfere with the ‘calling’ transaction). Postgres doesn’t have native support for this but there are two workarounds.

Kaarel Moppel

Reverse Proxying to pgAdmin — pgAdmin is a popular Web-based admin and database management tool for Postgres.

Dave Page

Indexing Documents for Full Text Search

Dave Page

CREATE STATISTICS: What Is It For? — These code-rich slides from a recent talk highlight the use case for CREATE STATISTICS, a command introduced in Postgres 10 that can create extended statistics objects to assist in query planning.

Tomas Vondra slidedeck

SQL, Python, and R. All in One Platform. Free Forever — Mode combines a SQL editor, native Python and R notebooks, and viz builder in one platform. Connect, analyze, & share.

Mode sponsor

▶  How to use MongoDB and PostgreSQL in GraphQL — A demonstration of bringing various databases together (the choice of MongoDB and Postgres are reasonably arbitrary here) using the GraphQL query language, Apollo Server, and Node.js.

Ben Awad

▶  The Vision for Graph Database from Postgres — A talk given a few weeks ago at Postgres Vision 2019 about AgensGraph, a multi-model graph database built on top of Postgres. The audio isn’t great but you get a good overview of what AgensGraph is and how it helps.

AgensGraph

supported by Percona

💡 Tip of the Week

Test how your app copes with Postgres failing or suffering high latency

If you run your own Postgres server, it's as solid as a rock, right? Except when it isn't. And if you're running on cloud infrastructure, you never really know how it's working operationally, so it pays to build your systems in a resilient way that can cope with Postgres failing.

You could test your app under various failure scenarios by giving your app incorrect database authentication details, by taking your database server down, or even by putting a firewall rule in place, but what about intermittent failures? You need something less severe for that.

Toxiproxy is an open source system that simulates different network conditions so you can see how your app performs under scenarios where backend services, such as your Postgres database, are intermittent or suffering from high latency. It's both a Go-powered TCP proxy (so you can use it for other services than Postgres) and a handful of client libraries for Go, Ruby, Python, .NET, PHP, Node.js, Java and Haskell, so you can integrate it directly into your test suites.

This week's tip is sponsored by Percona. This white paper discusses the importance of high availability database architectures and how PostgreSQL can be configured for high availability.

🗓 Upcoming Postgres Events