~postgresweekly | Bookmarks (128)

Keynote Speakers Announced for PASS Data Community Summit 2024 — PASS Summit have just unveiled their keynote speakers for this year’s event (November, 4-8, 2024). Featuring keynotes from Microsoft, Redgate and a community expert, buy your 3-day ticket now before the next price bump.

PostGIS 3.5.0 Released — The latest release of the popular geospatial data indexing and querying extension landed hours before Postgres 17 final, so it still recommends RC1, but it should be good. Breaking changes are listed here.

pgMustard v5: Review Postgres Query Plans Quickly — A tool for helping review Postgres query plans, now with enhanced Postgres 17 support and new tip types for things like suboptimal JIT compilation and excessive trigger times. You only get five uses for free, though, as it’s ultimately a paid service.

Playing with BOLT to Get a Faster Postgres — BOLT is a tool from the LLVM project that optimizes binaries after they’re compiled by way of analyzing profiler data. Could it be used to speed up Postgres? Yes! But it’s complicated..

Optimizing Your Database for Analytics — Your database could be great for day to day operations, but when you need to run some complex analytics queries, some big decisions arise. You could replicate the data to do the analytics elsewhere in a variety of ways, or tweak Postgres to handle things better. A solid high-level look at the concepts involved. There’s ▶️ a video...

Why to Stop Using SERIAL in Postgres — SERIAL is a commonly used convenience for generating an automatically incrementing ID column, but it has a variety of quirks and is Postgres specific. The alternative? Identity columns, which are both a part of the SQL:2003 standard and supported by Postgres.

Hands on Postgres 17: What’s New & Impacting Performance — Join us for an exclusive webinar on the Postgres 17 release. Explore new enhancements—faster B-tree scans, adaptive vacuum strategies, and streaming I/O updates—and their impact on database performance & monitoring. Register today!

Postgres 17 RC1 vs sysbench on Small and Large Servers — If, like us, you’re anxiously awaiting next week’s Postgres 17 release, you might be curious at how it’ll stack up performance wise. Mark ran some benchmarks on 15.8, 16.4, 17 beta 3 and 17 RC1, and while there’s some detail to peck through, the summary is “17rc1 looks great - there are no...

Implementing Hybrid Search with Postgres and pgvector — Jonathan sets out to show us how to use pgvector in implementing hybrid search systems, but notes that he doesn’t yet want to answer whether you should – that’s a topic for a future post.

Window Functions for Data Analysis with Postgres — Window functions come in handy when you want to group or compare between rows you’ve already retrieved in a query – for instance, to calculate running totals, perform rankings, or work out rolling averages. They essentially provide higher order querying.

🐸  Rainfrog: A New Database Management TUI for Postgres — We love psql as much as anyone, but are always keen to see new takes, and this Rust-powered terminal UI is an elegant one. You get a query editor with highlighting, Vim-esque shortcuts, and can quickly switch between schemas.

📱  Selectable: An In-Development Android Postgres Client — A new freemium Android app for interacting with Postgres that’s admittedly still in its ‘early stages’ but available now on the Google Play Store to experiment with.

PL/Haskell 4.0: Use Haskell in Your SQL Functions — Haskell is a functional language but you can, nonetheless, use it to define procedures in such a way that Postgres can work with.

Postgres 17 Release Candidate 1 Released — If you’ve been uncertain about running any of the betas, now is a good time to kick the tires, as the release candidates tend to be largely identical to the final release. The final release is expected on September 26 (we may delay Postgres Weekly that week, due to this).

Tetris-SQL: Using SQL's Turing Completeness to Build Tetris — An implementation of Tetris in just a few SQL queries on Postgres! As well as being an impressive bit of work, it’s also educational, as Nuno takes time to walk through the implementation details in the README.

PG Back Web 0.3: A Postgres Backup System with Web Interface — A Go-powered app for bringing a bit more of a friendly user interface to Postgres backups, as well as scheduled backups, backup monitoring, and webhooks. It’s available as a Docker image too.

pgcopydb 0.17: Copy a Postgres Database to a Target Postgres Server — Automates and provides a little structure around the running of pg_dump | pg_restore between two running Postgres servers.

Run GitHub Actions Up to 2x Faster at Half the Cost — Blacksmith runs your GitHub Actions substantially faster by running them on modern gaming CPUs. Integrating Blacksmith is a one-line code change. 100+ companies like Ashby, Superblocks, and Slope use Blacksmith to help developers merge code faster.

Shrinking Big Postgres Tables: Copy-Swap-Drop — If you’ve got a big unwieldy table with only a subset of its data in active use but you don’t want to partition the table or perform lots of DELETE operations, you could copy the rows you want to keep into a new table, rename the new table, and drop the original one.

How Neon Dynamically Estimates Postgres’ Working Set Size — ‘Working set’ refers to the data that’s used/read/written by a system over a specific time period. Figuring out what the current ‘working set’ of a Postgres server is is actually quite difficult, but essential to do if you want to do autoscaling, as serverless platform Neon now can.

Understanding the Postgres Hackers Mailing List — If you want to get involved with Postgres development or otherwise have a deep interest in following along with developments, the pgsql-hackers list is the place to be. As a busy, highly technical list, there’s a lot of in-house jargon, which Greg explains here.

Why SELECT * FROM Table is Not Merely a 'Read' — Surely selecting all of the content from a table is a read operation that can be safely load balanced? Not so fast, says Hans, who comes armed with a counter-example.

The 2024 State of PostgreSQL Survey is Now Open — Timescale’s popular annual community survey is open once again. You can take it here and it closes on Monday, September 30. If you’re hungry for some stats now, you can check out the 2023 survey’s results here.

Master PostgreSQL Extensions: Pgvector, Citus, TimescaleDB — Unlock the full potential of PostgreSQL with our expert-led webinar series on Pgvector, Citus, and TimescaleDB. Learn integration, scaling, and best practices to optimize your database performance. Watch now to elevate your PostgreSQL game!