#​459 — June 15, 2022

Web Version

Postgres Weekly

Fuzzy Text Search: From Not So Fuzzy to Fuzziest — If you’ve got free form text data supplied by other humans, it’s not going to be perfect (typos, slang, odd formatting, and the like) so supporting a little ‘fuzziness’ when querying data can go a long way. This is a nice tour through some options Postgres offers to you from least ‘fuzzy’ to full-on Fozzie Bear.

Brendan Scullion

⚠️  Imminent Postgres 14 Update to Avoid Silent Corruption of Indexes — Over on the Postgres mailing list, an important ‘out of cycle’ release has been preannounced for June 16 (tomorrow) that will fix a Postgres 14 issue that can cause silent corruption of indexes if you use CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY. There’s a full discussion on the mailing list about the problem too.

Gilles Darold

State of PostgreSQL 2022 Survey — The 2022 State of PostgreSQL survey is now open! Share your views on the PostgreSQL database, your favorite extensions, preferred frameworks, community experiences, and more.

Timescale sponsor

Migrating Scheduled Jobs to pg_timetable From pg_cronpg_timetable is an advanced job scheduling extension for Postgres and offers a bit more out of the box than the less frequently updated (but still very usable) pg_cron. If you want to migrate at all, here’s how.

Pavlo Golub

Building IoT Apps on Postgres, Citus, and Azure — If you’re creating an app that handles time-series data coming from devices non-stop, scaling can quickly become an issue and leaning on managed services, such as those provided by Azure, can help keep scaling easy.

Suryanarayana and Srirampur

Using Postgres Schemas — I always struggle to describe Postgres schemas/namespaces succinctly but “with minimal overhead, they enable segmentation and isolation of tables within a database” is a nice attempt. Aaron sells us on some of the benefits here.

Aaron O. Ellis

What to Research When Upgrading Postgres Version — Some quick fire pointers on how to research the changes that might impact you when switching Postgres version. Why Upgrade PostgreSQL? is also a handy resource.

Bruce Momjian

Case-Insensitive Pattern Matching — You may sense some echoes with the fuzzy text searching item (featured above) but Laurenz covers slightly different ground particularly around the complexity of handling case sensitivity across collations and why German’s ß keeps things interesting.

Laurenz Albe

Handling Empty Strings When Migrating from Oracle to Postgres — I’ve never had to migrate a database from Oracle to Postgres, but AWS is really keen for you to do this and so is attempting to educate us on the problems that can arise.

Pattanayak, Mahto, and Paladi (AWS)

Handling Trailing Zeros with NUMERIC — Another quirk you might run in to when doing an Oracle to Postgres migration.

Jagadeesh Panuganti

Webinar on June 16th: How to Reason About Indexing Your Postgres Database

pganalyze sponsor

Transaction Control Statements in Oracle vs Postgres
Akhil Reddy Banappagari

Using UUIDs to Prevent Enumeration Attacks
Tobias Petry

🔧 Code and Tools:

pg_graphql: Bring GraphQL Support to Postgres — A top feature for us several months ago but not a project to be slept on.


pguint: Unsigned Integer Types Extension — Brings signed 8 bit integer and unsigned 8, 16, 32, and 64 bit integer types to Postgres 9.1+.

Peter Eisentraut