#367 — August 5, 2020

Web Version

Postgres Weekly

Postgres 13 Incompatibilities To Be Be Aware Of — Postgres 13 will be in general release soon (it’s currently in beta) and while Postgres tends to be reasonably backward compatible over time, there are some changes worth being aware of before you make a migration, as seen here.

Ibrar Ahmed

How to Securely Authenticate with SCRAM in Postgres 13 — A tutorial on setting up SCRAM-based password authentication. Interesting not just as a tutorial but also a primer on what SCRAM is and why channel binding improves security overall.

Jeff Davis

How to Tune PostgreSQL GUC Parameters — This article discusses GUC parameters that can be used for PostgreSQL tuning and how to configure them for improved performance. Take a look.

EDB sponsor

Measuring the Difference Between Dates — “What is the difference between two dates? You would think there was one answer, but there isn’t.” An interesting look at how there are multiple ways to get the difference between two dates or timestamps in Postgres and how they can return slightly different answers.

Bruce Momjian

effective_cache_size: A Practical Example — Some insights and a practical example of what the effective_cache_size setting does.

Hans-Jürgen Schönig

Announcing pgBackRest for Azure: Fast, Reliable Postgres Backups — Backups are a key staple of running any database. pgBackRest aims to be a fast, reliable, easy-to-use backup and restore solution with the ability to seamlessly scale to the largest databases, and now there’s official support for Azure.

Craig Kerstiens

Connection Pooling in Pgpool-II — Pgpool-II is a connection pooling tool for Postgres – this post looks at the basics of its operation.

B Peng

Best-Practices on How to Speed Up Your Postgres Queries. Free eBook — Companies like Robinhood and Atlassian are able to speed up their queries by orders of magnitude. This eBook shares our best practices for optimizing Postgres performance.

pganalyze sponsor

▶  How to use SSL in Postgres The Right Way: Encrypt Your Data in Transit

Kirill Shirinkin

Continuous Backups using WAL-G — WAL-G lets you manage the archiving of Postgres data and backups and can also let you restore a database to its state at a particular moment in time.

Angelico de los Reyes

Computing INTERVAL Values — Bruce seems to be on a date and time streak this week, and here he looks at some quirks of date/time intervals.

Bruce Momjian

▶  High Performance HTAP with Postgres & Hyperscale (Citus)

Marco Slot and Claire Giordano

supported by PopSQL

💡 Tip of the Week

The FORMAT function

When a fresh Postgres tip doesn't immediately come to mind, one of my favorite techniques is to look at the (amazing) Postgres documentation and skim through until I find something I didn't know about before but that I think might be useful in future. So it goes today with the FORMAT function!

If you're familiar with format, printf or sprintf in various languages like Python, C, or Ruby, you'll know what a format string is – a string using a simple template language made up of special delimiters which can be replaced by supplied values. Postgres offers the same idea in the FORMAT function:

SELECT FORMAT('%s %s', 'hello', 'world');
/* => 'Hello world' */

SELECT FORMAT('|%10s|', 'test');
/* => '|      test|' */

SELECT FORMAT('INSERT INTO %I VALUES(%L, %L)',
  'people', 'pat', null);
/* => 'INSERT INTO people VALUES('pat', NULL)' */

Sadly, FORMAT isn't anywhere near as powerful as string formatting languages in non-SQL languages (there's no %d or %f, for example) but you could find it useful for bringing together multiple columns into a preferred form of output (such as a column of cents into a formatted price):

SELECT FORMAT('$%s', ROUND(cents / 100, 2));

Be sure to check out the official documentation for more if you plan to use FORMAT.

Try out this Tip in PopSQL. PopSQL is a SQL editor built for teams. Write queries, visualize data, and share your results. A free premium trial awaits Postgres Weekly readers (no credit card required).