#327 — October 16, 2019

Read on the Web

Postgres Weekly

Nondeterministic Collations — Postgres 12 supports a new deterministic property that can be set to false on CREATE COLLATION to request that strings be recognized as equal when the underlying locale says they are, even if they are different at the byte level. This opens up a lot of ways to match against text without using explicit functions to do so.. here’s a look at some of them.

Daniel Verité

Prewarming Postgres's I/O Caches — The more Postgres can cache data in memory and the less it has to hit the disk, the better for performance. But it takes time for the most commonly used parts of a dataset to naturally end up in memory.. so what if you could speed up the process?

Hans-Jürgen Schönig

eBook: The Most Important Events to Monitor in Your Postgres Logs — In this pganalyze eBook, we are looking at the Top 6 Postgres log events for monitoring query performance and preventing downtime.

pganalyze sponsor

How to Set Up Streaming Replication in Postgres 12 — Percona’s Avinash Vallarapu talks about how to set up streaming replication in Postgres 12, basically a way to keep other servers ‘warm’ with your live data.

Avinash Vallarapu

What Is Autovacuum Doing to My Temporary Tables? — Temporary tables are not cleaned up by Postgres’s autovacuum process, you’ve got to do it on your own. Here’s why and how, complete with an example.

Hans-Jürgen Schönig

supported by Digital Ocean

💡 Tip of the Week

Saving a copy of your psql history

You've been hacking away in psql and you've come up with a clever query, want to look back at your work, or otherwise want to show off what you were doing in your session (perhaps to demonstrate to the boss that this database management stuff isn't so easy, after all). You want to see your history.

The easiest way to check your query history is:


Easy enough, but did you know that you can also use this to save your history to a file?

# \s queries.log
Wrote history to file "queries.log".

This week’s Tip of the Week is sponsored by DigitalOcean. Now available: Worry-free PostgreSQL hosting on DigitalOcean. We’ll handle setting up, backing up, and updating — so you can focus on building great apps.

📂 Code and Projects

Checking Postgres Object Ownership — A rather epic query that can list tables, views, foreign tables and sequences not owned by the postgres role.

Euler Taveira

Oracle FDW for Postgres v2.2.0 — A foreign data wrapper for easy access to Oracle databases. v2.2.0 adds support for COPY for foreign tables and Oracle’s XMLTYPE.

Laurenz Albe

Creating a Multi-Layered Secured Postgres Database — Learn security best practices for managing your Postgres databases by watching this on-demand webinar.

EnterpriseDB sponsor

asyncpg 0.19.0: A fast Postgres Client Library for Python/asyncio — To go with your brand new Python 3.8 (that dropped this week), why not a newly updated high performance client library for asyncio. Now supports Postgres 12 and SCRAM-SHA-256 authentication.


MobilityDB: A 'Moving Objects' Database (MOD) Built on PostgreSQL and PostGIS — As PostGIS brings geospatial objects to Postgres, MobilityDB aims to go a step further in bringing time into play for representing the movement of items within geographic space. This is very much under development/prototype for now, but may interest some of you.


A SQL Implementation of An Ancient Handwriting Recognition Algorithm — Yep, you read that write.. ;-)

Noah Doersing

🗓 Upcoming Events

  • pgDay Santiago (October 29 in Santiago, Chile) — pgDay Santiago is intended for PostgreSQL users of all levels, with talks for everyone and the chance to meet with your peers.
  • PG Down Under (November 15 in Sydney, Australia) — The second outing for this annual, Australian Postgres conference.
  • PgConf.Russia (Febuary 3-5, 2020 in Moscow, Russia) — One day of tutorials and two days of talks in three parallel sessions.
  • PGConf India (Febuary 26-28, 2020 in Bengaluru, Maharashtra, India) — A dedicated training day and a multi-track two-day conference.