#396 — March 10, 2021

Web Version

Postgres Weekly

Citus 10 Released: Distributed 'Superpowers' for Postgres 12 and 13? — Citus is a long standing Postgres extension (open source but now broadly developed by a team at Microsoft) that makes Postgres more distributed and horizontally scalable. v10 introduces, most significantly, columnar storage support for Postgres as a way to reduce storage and improve the performance of analytical queries.

Marco Slot (Microsoft)

Building a Recommendation Engine Inside Postgres with Python and Pandas — Learn how you can leverage Python and Pandas (a popular data analysis tool) from directly inside Postgres to build your own recommendation engine.

Craig Kerstiens

[Webinar] Everything Fails - How to Survive DB Disasters — All applications fail. It’s unavoidable. In this talk, we will walk through the concept of an active-active, always-on database that is rooted in the fundamental principles of distributed systems.

Cockroach Labs sponsor

Migrating to Aurora: Easy, Except The Bill?“Migrating our production database from Postgres to Aurora was easy, until we noticed that our daily database costs more than doubled.” Luckily they went on to mitigate this, though they seem mildly ambivalent about the move.

Kimberly Nicholls

How Citus 10 Brings Columnar Compression to Postgres — We’ve mentioned the release of Citus 10 above, but how does the new columnar storage mechanism work? This is a rather novel addition to Postgres but this post does a good job of explaining things including the pros and cons.

Jeff Davis (Microsoft)

Using pg_notify for Real Time Dashboards — Now this could be useful. pg_notify is a Postgres function you can use to send notifications between Postgres clients, and along with triggers and the NOTIFY and LISTEN commands you could use it to power real-time dashboard apps.

Daniel Lifflander

Best Practices for Postgres Major Version Upgrades and Replicas on AWS RDS — One benefit of using a managed service like that provided by AWS RDS is that the upgrades get managed to some extent too. But there are still things to consider, particularly with major level (e.g. 12->13) upgrades.

Divya Sharma and Anisha Cherodian (AWS)

Visualize Key Postgres Metrics Alongside the Rest of Your Stack — Collect OOTB and custom Postgres metrics and correlate them with related traces and logs. Start today with a free trial.

Datadog sponsor

Cleaning Up Your Postgres Database — Craig shares a few actionable steps on how to improve your Postgres database performance.

Craig Kerstiens

Replacing a Column Search with a Full Text Search Solution“Disk is cheap. Time is expensive.” So if you really need the flexibility of full text search, don’t be scared to set aside the storage to do it properly. Some interesting query examples here, too.

Dan Langille

Where Can Postgres Config Parameters Be Set? — Proving that it’s not just in postgresql.conf.

Hubert depesz Lubaczewski

🔧 Tools and Code

Speeding Up pgbench Using COPY FREEZEpgbench is a benchmarking tool that comes with Postgres and was written by the author of this post back in 1999. Here he looks at an optimization he’s making to speed up the benchmarking process thanks to a forthcoming Postgres 14 enhancement.

Tatsuo Ishii

pg_activity 2.1: top-Like Postgres Activity Monitoring Tool — Much as you might use top on a server to monitor processes and CPU usage, pg_activity provides a similar approach for checking up on PostgreSQL. Built on Python 3.6.


Postgres Server Exporter: A Postgres Metrics Exporter for Prometheus

Prometheus Monitoring Community