Bookmarks (131)

Looking for Hidden Hurdles When Postgres Faces Partitions — Andrei ran into a curious situation where partitioning a table introduced some tricky performance issues.

Postgres vs. Pinecone for Vector Storage and Queries — A response to a recent post from Pinecone that compared Pinecone and Postgres for vector search tasks.

Bringing Row Pattern Recognition to Postgres — The SQL standard includes an ability to search for a sequence of rows in a table based upon a defined pattern. Tatsuo is looking to bring it to Postgres too. He shows us how it works and the progress of his work.

You Make A Good Point! — Postgres Savepoints — Did you know that within a transaction it’s possible to define points where the remainder of the transaction can be rolled back to? They’re called savepoints.

Speeding Up Index Creation in Postgres — Indexes are fundamental to getting the most performance out of your queries, but it can take a long time to create new ones on large datasets. Hans-Jürgen looks at a way to significantly speed up the process.

▶  All the Talks from PGConf.dev 2024 — If you didn’t get to attend May’s PGConf.dev conference in Canada, you can now catch up with everything that happened on stage, at least, including talks on autovacuuming going awry, petabyte-scale deployments, how Amazon RDS scales, and how to make a bigger impact with your Postgres blog posts.

Magic Tricks for psql: Settings, Presets, Echo, and Saved Queries — “I wanted to share a couple of my favorite things I’ve found that make getting around Postgres better. If you’re just getting started with psql, or haven’t ventured too far out of the defaults, this is the post for you.”

PLV8ify: Turn JavaScript Files into PLV8 Functions — Bundles TS/JS input and writes SQL containing Postgres functions using the PLV8 extension (which enables using JavaScript as a procedure language in Postgres).

Which Cloud Providers Support auto_explain? — auto_explain is a Postgres module for logging execution plans of slow queries automatically, but not every cloud platform supports it out of the box..

Keyset Pagination with Descending Order — Some workarounds for an interesting sorting and pagination problem you can run into with SQL.

Who's in Charge of Postgres? — Postgres is different to many databases by not being directly under the control of a benevolent leader or corporation that drives its development. It has a core team, the PostgreSQL Community Association holds the trademark, but who’s ‘in charge’? All of us, suggests Bruce.

▶  Discussing the Postgres Startup Ecosystem — Nikolay and Michael of the Postgres FM podcast discuss the rapidly shifting and growing commercial ecosystem around Postgres, including companies like Neon, Tembo, and Aiven. (35 minutes.)

Using AI to Generate Data Structures for Postgres — It might be some time before we trust LLMs and AI models to diagnose diseases or run power plants, but when it comes to helping you define models and schemas, they can quickly point you in the right direction.

Autovacuum Tuning Basics — Running VACUUM manually reclaims wasted space/bloat in tables caused by deleted and updated rows. The autovacuum system, however, takes this job away from us, and can be tweaked to get the right space/performance tradeoffs for your situation.

Full-Stack Observability from Honeybadger — Honeybadger's new logging platform is a bit like Splunk, DataDog, or CloudWatch Logs, but with just the good parts and a reasonable price tag. Plus, it's easy to integrate with Crunchy Bridge, Journald, AWS, and more!

Can Postgres Replace Redis as a Cache? — Redis is a popular, high performance in-memory data storage server commonly used for caching, but if we can "just use Postgres for everything", could Postgres take on the job instead? Raphael investigates.

Crunchy Bridge Adds Iceberg to Postgres — Crunchy Data has extended its Crunchy Bridge for Analytics platform with support for querying Iceberg tables, complete with full query pushdown and powerful write-through caching. (Iceberg is a table format specifically for large analytic datasets.)

Parallel Queries in Postgres — Support for running parts of a query in parallel (a.k.a. parallel queries) didn’t land in Postgres with a single big thump, so the feature is easily overlooked. Elizabeth gives us a quick primer.

ETL Helper 1.0: Python Library for Database to Database Transfers — One for the Pythonistas. Works with Postgres, SQLite, SQL Server and Oracle, and we love that it comes from the British Geological Survey.

pgPedia: A Postgres Encyclopedia of Sorts — An interesting, cleanly formatted wiki-esque reference guide to Postgres’ features and settings that complements the official documentation well. Well worth poking around a bit.

🎧 Say Hello to the Talking Postgres Podcast — The Path to Citus Con podcast was created to promote Citus Con (now POSETTE) by way of interviews with speakers and other Postgres luminaries, but has turned into a great Postgres podcast generally. To reflect this growth, it’s had a rebrand.

Psycopg 3.2 Released — Psycopg is the most popular Postgres adapter for Python. (As an aside, we interviewed Psycopg’s creator in issue 390 – three years ago! – when he had begun work on Psycopg 3.)

PostgreSQL 17 Beta 2 Released — A month on from beta 1 comes the next step in the long journey to the final release of Postgres 17, expected this September or October. The draft release notes remain the best way to update yourself on what’s new.

Building and Scaling Notion’s Data Lake — Notion is a popular Web-based note taking and wiki app and, therefore, has a lot of data under its control. Starting from a purely Postgres-based approach (which they wrote about scaling here), they’ve grown significantly and had to integrate with numerous other technologies in order to adopt a data lake approach.

Optimize PostgreSQL: Essential Tuning Strategies — Discover proven PostgreSQL performance tuning strategies from Percona’s expert Support Engineers. Free eBook includes real-world examples and advice on query optimization, indexing, partitioning, schema design, and more. Get your copy now.

Looking at the New Built-in Collation Provider in Postgres 17 — There’s a built-in UTF-8 locale and collation provider with binary string comparisons in the Postgres 17 beta. Here’s why it’s interesting, how to use it and how it performs.

PGExtensions: A Comparison of Extension Support Across Clouds — Getting extensions running on your own Postgres server is one thing, but when it comes to using hosted Postgres services or cloud providers, you’re often restricted to what the provider has made available.

pg_dirtyread: Read Dead But Unvacuumed Tuples from a Relation — So if a row has been deleted, say, you could potentially still read it.

pg_back 2.4: A Simple, Thorough Backup Tool for Postgres — A Go-powered tool for dumping your databases to files, including roles, server parameters, and more, in the format of your choice.

Making the Postgres Visibility Map Visible — If you’ve never gone far down the Postgres performance or transactions rabbit hole, you may have never encountered the visibility map. It’s a mechanism by which Postgres tracks which pages of a table contain only tuples that are visible to all transactions. This has some performance implications.

pg_easy_replicate: Switch Databases with Minimal Downtime — A Ruby-powered orchestrator to simplify the task of setting up logical replication between two Postgres databases then letting you switch over to the newer one with minimal downtime.

pspg: A Unix Pager Designed for Postgres Tables — If you use psql you probably use less as your pager, but it doesn’t directly support tabular data. This does. Nowadays, it also supports MySQL, CSV and TSV too.

pg_squeeze: Extension to Automate Table Bloat Cleanup — While it’s not a replacement for vacuuming, it’s a handy extension to go a step further and gain even more space improvements in-situ.

The Dalibo Postgres Execution Plan Visualizer — Give this the output from EXPLAIN ANALYZE and get a better view at what Postgres plans to do with your query. It’s also open source if you want to use it locally or within an app.

pgModeler: A Postgres Database Modelling Tool — An easy way to create and edit database models in a more visual way. It’s packaged up as a paid product but is also open source (GPLv3 – repo here) so you can build it yourself.

Load from Postgres to Postgres Faster via DuckDB — How to export and import data from Postgres to Postgres in a fast way with ConnectorX and DuckDB.

An Introduction to Generated Columns in Postgres — In Postgres, generated columns are columns that are automatically populated based upon the result of a user-defined expression when rows are inserted or updated.

Custom ENUM Type Columns and ORDER BY — Enumerated types make it easy to enforce constraints on what can go in certain columns, but you might need to take care when ordering them.

A Mentoring Program for Postgres Contributors — Prolific Postgres contributor Robert Haas is starting a mentoring program where people who want to contribute code to Postgres can be mentored by current committers. It’s only open to nine people so far, but this is a great opportunity for anyone interested.

▶  The State of the Postgres Extension Ecosystem — David Wheeler is overseeing a huge push in the way that Postgres extensions are cataloged, published and distributed, and he gave a talk at POSETTE covering the whys and hows, along with a little history. He also wrote about the Extension Ecosystem Summit that took place at PGConf․dev.

pg_bestmatch.rs: Elevate Your Text Queries with BM25 — BM25 is a popular ranking function that can improve full text searches across a large number of documents. pg_bestmatch.rs is an extension that brings BM25 related functions and statistics generation into Postgres.

How to Upgrade Postgres.app on macOS — Postgres.app is a popular way to get Postgres instances running quickly on macOS, but what about updating it without losing existing data?

Identity Crisis: Sequence v. UUID as Primary Key — “When used mindfully, both integers and UUIDs make quite workable solutions, with (in my opinion) appropriately levered UUIDs edging out a little ahead.”

Reduce Vacuuming Using ON CONFLICT — If you reduce the number of dead tuples caused by failed insert operations, you’ll also reduce the need for Postgres to vacuum the table. Win win.

Redis With Wings. Better Performance. Less Overhead — Dragonfly (24k GitHub stars) is a fully compatible drop-in Redis replacement. Dragonfly achieves 25X better performance than Redis and supports millions of QPS on a single instance. Download and deploy yourself or get a free trial of Dragonfly Cloud.

A Review of PGConf.dev 2024 — Postgres might have seen no commits during the recent Postgres developer conference, but plenty was happening. This is a really neat and thorough roundup for both those who did and didn’t attend.

Benchmarking Amazon RDS for PostgreSQL Deployment Options — A performance comparison between RDS for PostgreSQL Single-AZ DB instance, Multi-AZ DB instance, and Amazon RDS Multi-AZ DB Cluster deployments.

Register Now: Optimizing Slow Queries with EXPLAIN to Fix Bad Query Plans — The focus of this webinar is to teach the essential skills of optimizing slow queries with EXPLAIN ANALYZE, teach you about anti-patterns that you will encounter when debugging queries, and leave you with practical advice on how to address common problems.