Bookmarks (172)

Postgres Index Scans Explained — Egor is back with a characteristically deep dive on index scans which should help clear things up if you’ve seen “Bitmap Index Scan” or “Parallel Index Only Scan” in your query plans and wondered what they really mean.

CloudNativePG: A New Kubernetes Operator for Postgres — EDB has released an Apache 2.0 licensed Kubernetes operator that can handle the full lifecycle of a Postgres cluster. GitHub repo.

The Postgres 15 Draft Release Notes — Postgres 15 isn’t due for several months yet (though the first beta will be out within weeks) but Bruce Momjian has completed the first draft of PG 15’s release notes and lays out the main tweaks and improvements.

Google Cloud Introduces AlloyDB for PostgreSQL — It’s not a perfect comparison but think about Amazon Aurora except from Google who boast this is “PostgreSQL in a way that only Google can deliver it.” AlloyDB is a managed Postgres-compatible service (though it does seem like elements of Postgres live under the hood) which promises seamless scaling, for a price. There’s a more technical explanation here.

Adventures in Collating Text in Linux, Docker and Postgres — A text sorting error popped up in an app the author was working on and it required digging around much more in the collation settings than you’d expect.

An Experience with Exporting Postgres RDS Partitioned Tables to S3 — We all love writing scripts to move data around, right?.. right? Well, it’s not always fun or easy, as we see here, and they ultimate had to put this approach on ice.

Observability Powered by SQL: Take OpenTelemetry Traces into Postgres — OpenTelemetry is a standard for instrumentation (traces, metrics, logs, etc.) and the Timescale folks have announced Promscale, a backend for Promethius and OpenTelemetry built on Postgres and TimescaleDB for analyzing such data with SQL.

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

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.

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.

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.

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.

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.

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

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.

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.

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.

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!

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

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

Amazon Aurora Serverless v2 Now Generally Available — Aurora is AWS’s Postgres (and MySQL) compatible managed database system and has long had a serverless ‘pay as you go’ variant. Version 2 of Aurora Serverless takes things up a notch in terms of scalability and granularity providing ‘instant’ and non-disruptive scaling when loads suddenly change.

▶  Discussing Postgres.js with Rasmus Porsager — Postgres.js is a fast, fully featured Postgres client library for both Node.js and Deno. In this 50 minute interview, he talks about its creation, as well as Postgres in general.

pg_party 1.4: PostgreSQL Partitioning for Ruby on Rails' Active Record — AR migrations and helpers for creating and managing table partitions. Now has full Postgres 11+ feature support and supports sub-partioning.

Three Tips For Startups Who Chose CockroachDB Over Postgres — We don’t want you to jump ship, but it’s often interesting to contrast and compare (and pretty much essential if you’re a CTO or influencing technology choices at your company).

Formatting SQL Code with pgFormatter in Vim — Last week, we learnt how to format SQL code with pgFormatter in Emacs, so it’s no surprise to see Vim turn up as an option too :-)

Postgres 14 B-Tree Index: Reduced Bloat with Bottom-Up Deletion — A look at how Postgres 14 is more efficient in deleting obsolete versions of index tuples in B-Tree indexes, resulting in smaller total index sizes.

Mapping Fracking Wells in Colorado with Postgres — A practical look at using Postgres, pgAdmin, and PostGIS to create maps from data.

▶  PostgreSQL – More Relevant Than Ever — One of the aforementioned Citus Con talks. Magnus, of the Postgres core team, spoke about how despite Postgres’s age, it’s more relevant in the database sphere than ever. This is a great talk to catch up on some history and the broad position and mindset of the Postgres ecosystem.

▶  All of the Citus Con Talks and Replays — Two weeks ago we linked to a playlist of videos from the recent Citus Con online Postgres conference, but now the official Citus Con site has the videos linked from its own schedule in an easier to discover way, plus you can now see all 38 sessions versus just 20 before.

Eight Things You Probably Didn't Know PostgreSQL Can Do — This isn’t new, but somehow we didn’t see it at the time, and it’s an interesting list. If you’ve spent much time digging around in Postgres you probably do know these things, but casual day to day users may not.

Free eBook: Effective Indexing in Postgres — Learn how to create the best Postgres index for your queries. We provide a deep dive into index types, operators, data types and more. Creating the right indexes can often improve your query performance by 10x or even 100x.

Fuzzy Name Matching in Postgres — Paul loads 50,000 fake names into a Postgres table then demonstrates some ways to find names that match certain requirements including those that sound similar when spoken aloud.

▶  Advanced INT to BIGINT Conversions — Integer overflows have caused all manner of bugs, problems, and outages over the years, so should we just move to BIGINT for everything? Not so fast, says Robert, who digs deeper into the dilemma in this 20-minute talk.

Orafce: Oracle Compatibility Functions for PostgreSQL — Orafce implements some of the functions from Oracle Database that might be useful for users migrating to Postgres. v3.21.0 backports regexp_replace and regexp_instr from the future Postgres 15.

'Our Experience with Postgres on ZFS' — ZFS is a file system with lots of useful advanced features (but with higher hardware requirements, too) including snapshots and transparent compression – both quite useful for database use cases.

Formatting SQL Code with pgFormatter within Emacs — If Emacs is your operating system editor of choice, this might be of use, though pgFormatter is a Perl-based SQL beautifier that can be used at the command line too (or even on the web).

Two Sizes Fit Most: Postgres and ClickHouse? — An opinion piece from GitLab’s CEO arguing that Postgres and Clickhouse cover most of the OLAP and OLTP bases businesses require (although arguably Postgres is entering the OLAP world more itself, in the shape of HTAP).

Find and Fix a Missing Postgres Index — “Missing indexes are a key ingredient if you are looking for a perfect recipe to ruin performance in the most efficient way possible,” says Hans-Jürgen, but luckily the order of the day here is how to avoid that problem, fix missing indexes, and improve performance.