#​454 — May 11, 2022

Web Version

Postgres Weekly

Parquet and Postgres in the Data Lake — Partitioning provides one approach to breaking up huge static datasets into more manageable pieces (and discarding them when no longer needed) but what if you want to keep such things around without clogging up your ‘main’ database? Throw it into a ‘data lake’ and use an FDW to query it when you need to! Paul shows us one approach here.

Paul Ramsey

🔎 Database and Query Monitoring With pg_stat_statements — Learn how to store snapshots of metrics regularly and move from static, cumulative information to time-series data for more efficient database monitoring - complete with code examples.

Timescale sponsor

Counting Faster with Postgres — Counting is a fundamental data operation but if you do it the wrong way, it can scale poorly. Zach shows us some basics around how Postgres handles counting, how to speed it up, and some alternative approaches.

Zach Naimon

Understand Your Workloads Better with pg_stat_monitorpg_stat_monitor (which hit v1.0 and went GA last week) is an extension that collects query metrics, like pg_stat_statements say, but that digs down deeper with extra specs for short term analysis.

Matt Yonkovit

▶  Discussing Supabase with Ant WilsonSupabase is an open source Firebase alternative built atop Postgres, as well as an eponymous company offering it as a cloud-based Postgres-as-a-service (they just raised $80m). In this podcast, Supabase’s co-founder and CTO goes deep not just into Supabase but Postgres more generally too.

Software Engineering Radio podcast

Time Zone Management in Postgres — Laurenz isn’t wrong when he suggests developers and time zones aren’t huge friends. Databases are a common place for time zones to crop up and Postgres offers some types and conveniences to make working with them a little less painful.

Laurenz Albe

Changing Tires at 100mph: A Guide to Zero Downtime Migrations — If you’ve never watched people changing a car’s tires while driving along, head to YouTube and search for it – it’s neat – then come back to this quick article about similarly choreographing a schema change. Like with the tires, it's one way to do it, but certainly not the only one :-D

Kiran Rao

SSH Best Practices Using Certificates, 2FA and Bastions

Teleport sponsor

It's 'Beer O'Clock' for PostgreSQL — I can’t help but love an educational moment wrapped up in some silliness.

Andreas Scherbaum

How to Join MySQL and Postgres in a Live Materialized View
Bobby Iliev

🔧 Code and Tools:

Pigsty: A 'Batteries Included' Postgres Distribution — A curiously named attempt to bring Postgres together with a variety of useful extensions, observability and management tools into a single, more easily managed distribution you can run locally in a sandbox or deploy to the cloud with Terraform. GitHub repo.

Feng Ruohang

FerretDB 0.2: An In-Progress, Go-Powered MongoDB Alternative — MongoDB, you say? Yes. This is an attempt to recreate the essentials of MongoDB in Go so it could work as a drop-in replacement and it uses Postgres as the underlying database engine.