~postgresweekly | Bookmarks (131)

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

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.

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

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.

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

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.

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

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.

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.

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.

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!

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.

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

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

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.

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.

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.

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

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.

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