#​479 — November 2, 2022

Web Version

Together with  Timescale  logo

Postgres Weekly

B-tree vs. BRIN Indexes in Postgres Data Warehouses — Hans-Jürgen describes it as an ‘epic battle’ between the two popular index types and puts on a mini re-enactment that essentially demonstrates why doing your own benchmarking makes sense.

Hans-Jürgen Schönig

Safely Renaming a Table with No Downtime Using Updatable Views — Just use ALTER TABLE x RENAME TO y? In production, you might not want to do that as it can confuse clients currently using the database, but there is a way to pull it off cleanly..

Brandur Leach

Tips for Better Data Aggregation in PostgreSQL and TimescaleDB — Get familiar with TimescaleDB continuous aggregates, how they enable you to retrieve data aggregations with speed and convenience, and extra tips to make the most of them.

Timescale sponsor

Data Loading in Postgres for Newbies — Quickly covers various routes to ingest data from CSV, JSON, backup files, via foreign data wrappers, and even generating data with queries. Using jq along with \copy is a neat approach I hadn’t considered before.

Elizabeth Christensen

IN BRIEF:

Postgres Databases and Schemas — Craig has worked on Postgres databases of every scale, but they all have layers in common. This is aimed at beginners but quickly explains how clusters, databases, schemas and instances differ from each other.

Craig Kerstiens

Cross-Partition Uniqueness Guarantees with Global Unique Indexes — More deep diving from the folks at Highgo.

Cary Huang (Highgo)

Push-Based Outbox Pattern with Postgres Logical Replication
Oskar Dudycz

How to Upgrade PostGIS-Related Libraries on Ubuntu in Three Steps — Such as GEOS or GDAL.
Florian Nadler

Easier Upgrades and Image Management for Postgres in Kubernetes — Using Crunchy PostgreSQL for Kubernetes in particular.
Andrew L'Ecuyer

How to Migrate Oracle Hierarchical Queries to Amazon Aurora
Raghav and Chintha (AWS)

🛠 Code and Tools

pg_stat_kcache: Gather Statistics About Disk Access and CPU Consumption — Sits atop pg_stat_statements and creates a couple of views you can use to measure CPU time, page faults, swaps, bytes read and written, and more at the database and query level.

Dalibo and PoWA Team

Making Postgres More Affordable. Starting at $10/Month

Crunchy Bridge sponsor

PostgreSQL 15 64-Bit FDWs for Windows — Specifically, 64 bit binaries for file_textarray_fdw (process arbitrarily delimited text) and odbc_fdw (handy to connect to ODBC data sources).

Leo Hsu and Regina Obe

pgdump-aws-lambda: Stream pg_dump Output to S3 via AWS Lambda — There are other ways to do this sort of thing on AWS, but this is a pretty direct and flexible way to take database backups.

James H

PGroonga: Extend Full Text Search Features to All Languages — Brings the Groonga fulltext search engine to Postgres which can suit certain use cases better than the Postgres default, such as with Japanese or Chinese.

PGroona Project