Bookmarks (173)

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...

🔎 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.

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.

Understand Your Workloads Better with pg_stat_monitor — pg_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.

▶  Discussing Supabase with Ant Wilson — Supabase 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.

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.

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 without downtime.

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

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.

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.

Custom Dashboards, Admin Panels, CRUD Apps — Build an Internal Tool on Any SQL Database 10x Faster

A Look at (Future) Parallel Server-Side Backup Compression — Postgres 15 will, fingers crossed, support server-side backup compression and Robert did a little digging around to see how well it might work (in terms of both speed and disk space saved) using different compression algorithms. Very promising!

pg_ivm 1.0: IVM (Incremental View Maintenance) Extension — Incremental View Maintenance (IVM) keeps materialized views up to date in a way where incremental changes are computed and applied rather than recomputing everything. pg_ivm provides a kind of ‘immediate maintenance’ where materialized views are updated immediately after a base table is modified.

▶  IO in Postgres: Past, Present, and Future — A technical talk given just two days ago digging deep into Postgres’s IO story including the current limitations, how those limitations are being worked around, and progress being made on improvements. (1 hour 6 minutes.)

Is Your Distributed Database Really PostgreSQL Compatible? — How do you evaluate the PostgreSQL compatibility of cloud native databases? This video provides a framework for comparing PostgreSQL compatibility of modern databases, including Google Spanner, Amazon Aurora, CockroachDB, and YugabyteDB.

Working with Large Postgres Databases — What is a small, medium, or large database when Postgres can handle up to an unlimited size (though tables are limited to 32TB)? Addressing potential issues for a ‘large’ database by anticipating future conditions is the goal for every DBA, says Robert.

Apache AGE 1.0: A Graph Extension for Postgres — An extension that provides graph database functionality (via openCypher) to Postgres and was inspired by AgensGraph. More info on the release.

Analyzing Historical Flight Data with MobilityDB — MobilityDB sits atop Postgres and PostGIS for providing geospatial trajectory analysis.. so it’s perfect for analyzing historical air traffic data, as demonstrated here.

Using SQL's MERGE in Postgres 15 — Last month, we mentioned that MERGE support is coming to Postgres 15 and this post presents a basic example of its use.

▶  Advanced Postgres Schema Design — A talk from PGConf NYC 2021 on going “beyond CREATE TABLE and CREATE VIEW with functional indexes, exclusion constraints, array types, transactional DDL, and more.”

Automating Interval Partitioning Maintenance on Amazon RDS or Aurora — Very AWS specific but there’s a lot of tying together ideas here for creating robust partitioned databases.

pgwatch2 v1.9 Final Released — pgwatch2 is a Postgres monitoring system using Grafana dashboards for visualization of a wide array of Postgres metrics.