Bookmarks (113)

Life Altering Postgres Patterns — The author promises the title isn’t just clickbait and delivers twelve different bite-size, hard-learned tips and insights in areas from using UUIDs as primary keys and table naming to the use of schemas and views.

Rainfrog 0.3: A Database Management TUI for Postgres — Think a little more structured than psql with vim-style navigation of queries, history, schemas, etc.

⭐ Electric 1.0: A Postgres Sync Engine — “Electric solves sync.” The idea is that if you have data in Postgres you need to sync elsewhere, Electric solves the hard problems of sync for you, including partial replication, fan-out, and data delivery, no matter your data model or Web framework. More info here.

Teable: Open Source Airtable Alternative atop Postgres — Airtable is a popular data table database SaaS, but here’s an open-source alternative along similar lines that sits atop Postgres and allows direct data querying with SQL. GitHub repo.

Reducing Cloud Spend: Migrating Logs from CloudWatch to Iceberg with Postgres — Postgres provider Crunchy Data has a lot of logs to store and storing those in AWS CloudWatch costs a lot too. Craig explains how Crunchy Data implemented a new workflow to store the logs in Iceberg tables on S3 instead, while still allowing SQL querying via Crunchy Data Warehouse.

Creating Postgres Roles with Passwords Stored in Gopass — Gopass is a Go-powered CLI-based password manager for teams.

🔒 A Postgres Security Checklist — The Center for Internet Security (CIS) releases security benchmarks for a variety of popular tools and services used in Internet infrastructure, and now there’s a set of Postgres guidelines (though it's behind an email wall with commercial-use restrictions, alas).

Semantic Search in Postgres: An Overview — Full text search is great, but if you want to query data based on its meaning rather than the literal data, you’ll need to bring in extensions like pgvector. Hans-Jürgen explains how it all works here.

Why Postgres Needs a Better API for Alternative Table Engines? — Postgres has mechanisms for allowing extensions to provide their own index types and table access methods, but it’s still not as easy to roll out powerful alternative table engines as it could be. Alexander of OrioleDB explains the limitations and some potential ways forward.

A Practical Guide to Taming Postgres Isolation Anomalies — A practical, Ruby-based (though SQL heavy) walkthrough of Postgres’ concurrency control, detailing real-world issues like lost updates, write skew, and other isolation anomalies. Even if you rarely run into conflicts, you’ll come away with valuable insights on managing transactions.

Think About MERGE in Terms of a RIGHT JOIN — RIGHT JOIN isn’t encountered too often in SQL as you can just write a more universally understood LEFT JOIN alternative instead. However, right join semantics play an important role with the MERGE statement.

pgzx 0.3: Create Postgres Extensions using Zig — If you’re familiar with pgrx as a way to build Postgres extensions with Rust, this is a similar idea in the world of Zig, another modern systems language that’s a little more C-like than Rust.

pg_ivm 1.10: Incremental View Maintenance (IVM) Extension — A more efficient way to update materialized views incrementally, applying only changes instead of fully recomputing the view like REFRESH MATERIALIZED VIEW does.

pgFormatter 5.6: A Tool to Format SQL Code — You can test it out online, or you can grab the source from GitHub (it’s written in Perl) and run it for yourself. What’s new in v5.6.

Xata Agent: An 'AI Expert' in Postgres — An open source agent that monitors your database, finds root causes of issues, and suggests fixes and improvements. It’s early days in this space but it’s interesting to see different takes on the idea.

▶  🎤 Why Python Developers Just Use Postgres — Dawn Wages of the Python Software Foundation joins Claire Giordano to talk about why Postgres is particularly popular in the Python and Django communities.

Can Artificial Intelligence Create Better Tables Than You? — As someone guilty of leaning on LLMs for a little SQL grunt work lately, this was an interesting read. As ever, you need to stay on your toes and check anything produced before rolling it into production!

Troubleshooting Duplicate Primary Key Rows — If a table has unwanted ‘duplicate rows’ (which can happen due to a variety of quirks causing corrupt unique indexes) Greg has a multi-step technique for getting things back under control.

Sure, You Can Roll Your Own Auth - If You Hate Free Time — Don’t sacrifice your control. FusionAuth is downloadable, and can be developed and tested locally. A single-tenant architecture provides better scale and security than a purely multi-tenant SaaS model, even if you deploy via the cloud. Try it for free!

You Can Make Postgres Scale — Newsflash: Postgres scales! After last week’s experiments with a 1 trillion row table using Citus, we know Postgres scales, but once you go beyond a certain point, bringing multiple machines into play becomes a potentially complex necessity and the PgDog horizontal scaling/sharding approach offers just one solution.

1 Trillion Rows in Citus? — “Postgres scales,” but just how far does that go? Hans-Jürgen decided to find out, running a little (or is it big?) experiment to see if a 1 trillion row table is even achievable.

❌ Ways to Cancel Queries in Postgres — If you’ve got a query or two hogging Postgres’s resources, terminating them is an option, and there are several ways to go about it.

▶ Don’t Let Postgres Maintenance Slip Through the Cracks — Secure your spot to learn the proactive maintenance practices that uncover hidden database performance risks, like slow queries, checkpoint behavior, & connection issues, before they escalate.

Postgres Gets a Self-Join Elimination Optimization — Several years ago, someone suggested a join optimization where redundant inner self-joins (often added by ORMs) can be removed under certain conditions. This so-called Self-Join Elimination (SJE) optimization has now been committed to Postgres and is explained in this GitHub commit.

Introducing pgNow: A Point-in-Time Diagnostic Tool — A free (but not open – it’s in preview behind an email wall) cross-platform desktop tool created by Redgate to highlight and analyze live data about queries, indexes, configuration settings, and other important elements of a Postgres cluster’s operation. Even if you don’t grab it, Ryan gives a thorough overview of what it offers here.

FerretDB 2.0: An Open Source MongoDB Alternative — MongoDB!? What if I told you this is Postgres-powered MongoDB? FerretDB is an implementation of MongoDB built on top of Postgres and Microsoft’s DocumentDB extension and, unlike MongoDB, it’s Apache 2.0 licensed. GitHub repo.

pytest-postgresql 7.0: A Pytest Plugin for Testing Postgres-Using Python Apps — A plugin for the Pytest Python testing framework for managing fixtures when testing against a Postgres database.

sqldef 1.0: Idempotent Schema Management for Databases — A Go-powered CLI tool and WASM library for diffing SQL schemas (demo here). Supports MySQL, Postgres, SQLite and SQL Server. Built by one of Ruby’s core team members, too.

New Random Functions in Postgres 17 — Postgres 17 introduced some new random number generation functions so getting an integer between 1 and 10 is now as easy as random(1,10).

Validating Data Types from Semi-Structured Data Loads — Postgres 16 and up offer the pg_input_is_valid function, a quick way to determine if a supplied value can be parsed into a specified type. Elizabeth shows how this can be used validation checks in a few situations.

PGConf.dev 2025: May 13-16 in Montréal, Canada — The successor event to PGCon, the PostgreSQL Development Conference is one of the community’s biggest annual get-togethers with a particular focus on bringing together Postgres’ contributors. Registration is open, but the big news this week is the schedule is now live and it’s a packed one.

The Schedule is Out for POSETTE: An Event for Postgres! — POSETTE 2025, a free & virtual developer event, is happening June 10-12. The speakers and schedule were just announced! Join experts via 4 livestreams to learn about the world's most advanced open source relational database. Get the details.

A Roundup of Postgres Query Plan Visualization Tools — The EXPLAIN command, along with its many options, gives you a deep view into how Postgres plans to execute a query and is a great way to figure out why certain queries are slow. The raw output can be tricky to read, though, but there are several tools that can help break things down.

While Postgres Redlined, Robinhood Sharded to Scale — A short write up, from an engineer who used to work at Robinhood, about Postgres scaling problems the company encountered and how sharding was their savior.

BemiDB: Zero-ETL Data Warehouse for Postgres — Open source (GitHub repo) but with a commercial angle for more enterprise-y features, BemiDB, written in Go, acts as a Postgres read replica (so all your data is synced in automatically) optimized for analytics workloads.

pg_activity 3.6: top-Like Activity Monitoring Tool — Much as you might use top or htop to monitor processes and CPU usage, pg_activity gives you a similar look behind the scenes of Postgres with views over running queries, up to date performance stats, and more.

EdgeDB is Now Gel (and Postgres is the Future) — Gel (formerly EdgeDB) is an ‘integrated data platform on top of Postgres’ that provides its own query language in an attempt to work around SQL’s perceived flaws (better explanation here).

Don’t Let Postgres Maintenance Slip Through the Cracks & Let Performance Fall Off a Cliff — Secure your spot to learn the proactive maintenance & monitoring practices to uncover hidden performance risks in your database, such as slow queries, checkpoint behavior, and connection issues, before they escalate, & ensure your database reliability.

Postgres 17.4, 16.8, 15.12, 14.17, and 13.20 Released — This makes two bumps to all maintained release lines in as many weeks (this is an 'out of cycle' release – explained here). As before, the focus is bug fixes. Postgres 17 gets fixes for a memory leak in pg_createsubscriber and a tweak to quoting function behavior in libpq.

(Ab)using pgRouting to Use Postgres as a Graph Database — pgRouting is an extension for providing geospatial routing functionality, commonly along with PostGIS. But, surprisingly, its graph and path searching mechanisms can be used to navigate any data structured in a graph-like form.

Postgres in the Time of Monster Hardware — You might think your M4 Max is pretty fast, but imagine having an AMD EPYC with 192 cores per socket and 10 terabytes of RAM that benchmarks at 160x faster than a Xeon-powered server from 15 years ago! Modern CPU power (not to mention faster storage) invites us to ask questions about how we scale database...

Expanding pgai Vectorizer — pgai is Timescale’s suite of tools for making it easier to use AI capabilities from Postgres, including automatically creating and syncing vector embeddings for data. Now they’ve upped the ante with SQLAlchemy support and support for more embedding models via LiteLLM (more detail here).

Incremental Archival from Postgres to Parquet — Crunchy Data’s pg_parquet and pg_incremental extensions can be used to set up a reliable pipeline to export ranges of rows to the columnar Parquet format on S3.

Representing Graphs in Postgres — Postgres isn’t a graph database, but you can emulate the concepts involved. Alternatively, you could use an extension like Apache AGE if you need Cypher-like graph querying support.

A Look at Virtual Generated Columns in Postgres 18 — Postgres 18 is gaining the ability to have ‘virtual generated columns’ where the expression for a virtual column is computed at read-time so they’re not stored on disk as all generated columns currently are.

A PostgreSQL Compatibility Index to Compare Implementations — Postgres has found itself in an envious, though complex, situation of becoming a lingua franca among databases where databases that contain no Postgres code at all attempt to be “compatible” with it in various ways. But how compatible? The Postgres Compatibility Index is an attempt to test and monitor numerous aspects.

PostgreSQL 17.3, 16.7, 15.11, 14.16, and 13.19 Released — A raft of updates to all maintained lines of Postgres to tackle one security vulnerability and lots of smaller bugs. As minor updates, the upgrade process is simple and doesn’t require any dumping and reloading.

Scaling Postgres without Boiling the Ocean — Postgres scales pretty far out of the box with the default settings, but once a server starts getting some serious load ‘weird issues’ can start to rear their head. Shayon addresses some common issues of this type and the solutions for them.

Citus 13: Now with Postgres 17 Support — Citus is a long standing open source extension that makes it easier to horizontally scale Postgres. Bought by Microsoft some years ago, it continues to get releases, including this new one with Postgres 17 support, including for distributed queries using JSON_TABLE() and improved support for distributed partitioned tables.

How About Trailing Commas in SQL? — Could this be the simplest yet most requested SQL feature? It's implemented in some dialects, but could it be implemented in Postgres's dialect? And should it? It’s far trickier than it sounds.