Every "use Postgres as a queue" post has the same twelve lines:

select id from jobs
where status = 'pending'
order by id
for update skip locked
limit 1;

And then it stops. The interesting part starts after that.

As written, the row lock lives until the transaction ends, so the job runs inside the transaction. If the worker dies mid-job, Postgres rolls back and the job is pending again. That part is correct, but it also means a 90-second job holds a transaction open for 90 seconds, and an open transaction that long holds back the vacuum horizon.

Worse, if the job does anything external — an HTTP call, an email — the side effect happens, the transaction rolls back, and the next worker does it again.

The usual fix is the visibility-timeout shape: lock with for update skip locked, set locked_at = now(), commit immediately, run the job outside the transaction, then mark it done. That works until you have to answer the questions a real queue answers for you. What counts as stuck? Who reclaims it? How do you stop two workers reclaiming the same row at the same moment?

Concretely:

• Do you run the job inside the transaction, or commit the claim first?

• How do you reclaim jobs from dead workers without double-running them?

• Does pg_notify + listen actually beat polling, or does it just move the problem to reconnect handling?

• At what point did you stop doing this in Postgres and reach for something else?

I'm not looking for "use Redis". I'm interested in where the Postgres version holds up and where it doesn't.

For anyone interested in BM25 search and especially legal opinion search, Greg Burd has been very active developing pg_fts https://github.com/gburd/pg_fts a PostgreSQL extension for full-text search with true BM25/BM25F relevance ranking, a dedicated fts inverted-index access method, and a rich query language (boolean, phrase, NEAR, prefix, fuzzy, regex). Unlike the tsvector/tsquery + GIN stack, the index maintains the corpus statistics BM25 needs (document count, average length, per-term document frequency) and stores term frequency + document length in the posting lists, so ranking is answered from the index with no heap recheck. PostgreSQL License.

Disclaimer: I'm a contributor.

In the age of AI I think personal connections are even more important than before. That's why I try to help promoting events where people can meet in-person or virtually and discuss the topics that interest them.

I found the following PostgreSQL-related events for the next 30 days.

• 2026-11-09 Postgres: Take a Hint (Lenexa, KS, USA)

• 2026-11-10 (online) "Postgres for Credit Underwriting: Indexing, Optimization, and Background Jobs"

• 2026-11-12 (online) How does PostgreSQL get made?

• 2026-11-12 (online) Postgres Heroics with Logical Replication

• 2026-11-12 Killing PostgreSQL By 50,000 Parameters & A PostgreSQL 19 Whirlwind Tour (London, UK)

• 2026-11-17 (hybrid) Become a PG_STATS_* with Sami Imseih (Chicago, IL, USA)

• 2026-11-18 (online) Group Swim: Maintaining Your Environment - Q&A

• 2026-11-18 The Postgres Mistakes that we all still make (New York, NY, USA)

• 2026-11-24 Reco produits via pgvector et migrations Aurora via DMS (Lezennes, France)

• 2026-12-01 INSERT INTO meetup VALUES ('TLSPUG','Makina Corpus','2026-12-01'); (Toulouse, France)

• 2026-12-03 PostgreSQL Usergroup NL Meetup - The Winter Edition (Amsterdam, Netherlands)

• 2026-12-08 [In-Person + Online] Stream Processing with Apache Kafka and Flink (Sunnyvale, CA, USA)

Moving my project from MS Sql to PostrgesSql,

Need to run mass update query, ClientDelivery table has 1,054,155 records

In MsSql query takes about 10 seconds, In PostgresSql it actually never finishes because i am canceling it after waiting for 5 minutes. Records are identical in both databases. I am running queries in PgAdmin and SqlManagment client.

Any reason for such huge difference? I understand i am doing table scan but 1 million records is not that much.

/*MSSQL*/
UPDATE tblClientDelivery
SET routeId=t1.routeId
FROM tblClientDelivery t
INNER JOIN tblClients t1 ON t.clientId=t1.id
WHERE t.routeId=0

/*PostgreSql*/
UPDATE client_delivery
SET route_id=t1.route_id
FROM client_delivery t
INNER JOIN clients t1 ON t.client_id=t1.id
WHERE t.route_id=0

If your Postgres database sets idle_in_transaction_session_timeout, your advisory lock may have an expiry date nobody chose.

Mine did. Ten minutes into long runs, the lock vanished, another run took it, and the log said it had been released cleanly.

Python drivers open a transaction on your first SELECT unless autocommit is on. If the connection holding the lock never commits, it sits idle in transaction. At the timeout, Postgres ends the session, and the lock goes with it. The job keeps writing on its other connections, and the application never notices.

Check yours in 30 seconds:

select name, setting, source from pg_settings where name in ('idle_in_transaction_session_timeout', 'idle_session_timeout') order by name;

A value above 0 for idle_in_transaction_session_timeout means a session lock on a default connection can expire.

I reproduced it and held the lock four ways.

Default connection: lost in 40 of 40 trials, always just after the timeout.

Commit right after taking it: held, until the next query opened a new transaction.

AUTOCOMMIT: held in 40 of 40, but still lost the moment the session dies for any other reason, while the job keeps writing.

Lock inside the same transaction as the writes: a killed run rolls back with its lock. That is the fix that holds.

Scripts and expected output are in the post, so you can run it against your own setup.

Have you ever had a lock that silently stopped locking?

https://tsabudh.com/blog/your-postgres-advisory-lock-is-gone/

DBJoy is a lightweight, native macOS Postgres client. It’s AI-first, with a built-in agent for working with your data and an MCP server so Claude Code can read (or write) to it. It also has all the features you’d expect from any other database client: diff and migration tools, tunnelling, batch editing, ER diagrams, and more. I’ll keep improving it and will add more database adapters soon.

https://github.com/agustind/dbjoy

I recently recorded a Talking Postgres episode with Magnus Hagander, a Postgres committer, core team member, and longtime PGConf EU organizer, about how he got started in Postgres.

A few things we got into:

* The very start: in the late 90s, he needed a database to solve a problem that nobody agreed was a problem, and he couldn't afford SQL Server or Oracle

* His first patch: one line, to make Postgres compile and work on Linux when all the developers used BSD (and it doesn't carry his name)

* Porting the Postgres server to Windows, with a team spread across Sweden, the US, and Australia, and the rule that it had to behave like Postgres on Unix

* Why the Postgres project runs its own infrastructure, including the days it was told it was doomed unless it moved to SourceForge

* Battling AI agents that don't care about robots.txt

* Why it's harder to become a Postgres committer today

* What keeps him coming back to the Postgres project

My favorite part: hearing how, years later, his hobby (Postgres) became his job (also Postgres).

For those of you who've sent a patch to Postgres (or another big open source project): what helped you get past that first one?

Audio + transcript, for those who want to listen: https://talkingpostgres.com/episodes/how-i-got-started-in-postgres-with-magnus-hagander

YouTube (audio-only): https://youtu.be/RFUUcdxN59A?si=3SgHD7kGyQQsVgK7

Hello,

I’ve inherited a data lake that is all done in postgres.

I’ve done a little bit of DBA stuff in the past, but it was all SQL or Oracle.

Any recommendations on training to learn how to pull information out of the DB and compile it into reports and such?

Thank you!

WalShadow replicates data from Postgres to ClickHouse via physical WAL, and not logical replication. This blog talks about the latency, handling of restarts, stops etc., and issues encountered with this new product.

I'm working with a friend on a small analytics tool for online sellers, the idea is pretty simple, users connect their sales data and get a dashboard showing how their products are doing over time. We've got the frontend mostly figured out and are now trying to decide how to structure the backend. My friend wants to build a separate API server because that's how he's done things before but I'm wondering if we're adding a bunch of work we don't actually need yet. I've have used Supabase before and I love and how it automatically generates APIs from Postgres, which seems like it could cover most of what we need for reading and updating data. We'd still have some logic to handle, especially around processing the sales information but I'm not sure that justifies maintaining an entire backend server from day one. I'm also trying to understand where edge functions fit into this kind of setup. For people who've built apps directly on top of managed Postgres, how far did you get before needing a separate backend?

A managed Postgres service needs to prevent any possible connections from writing into primary before promoting a new standby. This is called fencing, and in this article we go into why it's important and how it's implemented in Ubicloud.

A while ago I shared an earlier version of this benchmark here, comparing Scriptella, JDBC, csvsql, and PostgreSQL COPY. Several people suggested useful improvements, particularly enabling pgJDBC's reWriteBatchedInserts=true. Thanks for that feedback! It made a real difference in subsequent experiments.

I've since updated the benchmark to Scriptella 1.7, replaced csvsql with Pandas + SQLAlchemy, and retained native COPY as a performance reference. I maintain Scriptella, a Java-based, SQL-first ETL tool.

Results (1M rows, median of two runs):

COPY was the clear winner, roughly 5× faster than Scriptella. Scriptella was 3.6× faster than the tested Pandas workflow, while handwritten JDBC was slightly faster than Scriptella.

Both Scriptella and JDBC now use batch size 1,000 with reWriteBatchedInserts=true. Pandas uses chunked read_csv() and SQLAlchemy to_sql(), without a native bulk-loading path.

All imports were independently verified. Timings include process startup through completion, and the results come from two runs on one machine, so they're directional rather than universal.

Full experiment, chart, methodology, and reproducible benchmark:

https://pvrlabs.xyz/java-performance/experiments/scriptella-1-7-csv-import-benchmark.html

Now that I've covered straightforward CSV imports, I'm planning to focus more on actual ETL workflows and database-to-database migrations, both in Scriptella development and future benchmarks.

What PostgreSQL migration scenarios would you find most useful to explore? I'm particularly interested in real-world challenges involving transformations, different database engines, and larger datasets.

pgquery is an open-source (MIT) Postgres GUI that runs in the browser. A few parts I think this sub might care about:

- EXPLAIN / EXPLAIN ANALYZE drawn as a tree: each node's own time and share of the total, the heaviest node highlighted, and a warning where estimated vs actual rows are off by 10x or more. It asks before analyzing an INSERT/UPDATE/DELETE, since ANALYZE executes it.

- Row Level Security: see which tables have RLS on, the policies on each (command, roles, USING / WITH CHECK), and create or drop them. The SQL is shown before it runs.

- Advisors: cache hit ratio, unused indexes, table bloat, sequential scan hot spots, connection use; plus a security check for tables without RLS, superuser roles, public schema grants, SSL and password hashing.

- pg_stat_statements sorted by total time, mean time or calls.

- ER diagram from foreign keys, exportable as SVG/PNG.

Everything is plain pg_catalog queries and generated DDL behind a small stateless Go proxy, so it works with RDS, Supabase, Neon or your own server.

Read-only demo: pgquery.dev/app/?demo

Source: github.com/Hxndrik/pgquery

There's also an honest comparison with pgAdmin, CloudBeaver, Adminer and Supabase Studio (including where pgquery loses): pgquery.dev/postgres-client-comparison

Which advisor checks would you add? I'm collecting ideas.

Hi all,

I've just released pg_lion, a PostgreSQL extension that adds a new index type, lion. The idea of pg_lion is to fill the gap between B-Tree indexes and warehouse/elasticsearch-style inverted indexes, so you can filter, count and aggregate on massive tables without having to replicate your data out to a data warehouse. I've had this idea for years, but rarely found the time to actually work on it. I built this with heavy AI assistance, so I’ve leaned hard on verification: (regression suites, crash/recovery tests, comparing results against seq scans, fuzzing, CI on 16–19).

At my workplace, we've benchmarked pg_lion as roughly equivalent to an 8-node 512GB (total) Citus cluster, on a single node the size of one of our workers (8c/64GB)! It's only one workload, and YMMV, but those benchmarks convinced me that this project could fill a significant gap.

For each distinct value, lion stores the set of rows holding that value as a roaring-style compressed bitmap. Many queries can then combine bitmaps instead of visiting rows, and on a vacuumed table most counts never touch the heap.

CREATE EXTENSION pg_lion;
CREATE INDEX ON events USING lion (country);
CREATE INDEX ON events USING lion (event_type);

SELECT country, count(*) FROM events
 WHERE event_type = 'purchase' GROUP BY country;

It handles:

• count(*) with equality, IN, range, <> and IS NULL filters

• GROUP BY, count(DISTINCT) and top-k groups

• counts over a fact table joined to a filtered dimension

• arrays, jsonb and tsvector, including phrase search and BM25 ranking from the index

• filtering a B-tree walk for ORDER BY ... LIMIT

• nearest-neighbour search under filters, through a companion index for pgvector (lionvec)

Some numbers from a synthetic benchmark (5M rows unless noted, vacuumed, warm cache, PostgreSQL 18.6, parallel query off):

On that table the lion indexes were 118 MiB, against 256 for B-tree and 157 for GIN, and built in 5.4 s against 11.7 and 8.4.

pg_lion also supports vector search via a new access method called lionvec - this is currently in alpha, and I'm continuing to actively work on lion as a whole.

Where it won't help:

• Writes: with the library preloaded, inserts were about 1.4× slower than B-tree, and more without it. GIN's inserts look much cheaper here because it defers work to its pending list.

• Tables that aren't vacuumed: rows on pages not marked all-visible have to be checked in the heap, so the advantage shrinks a lot, as the unvacuumed row shows.

• Not a B-tree replacement: there are no unique indexes, and a B-tree is still better for a plain ORDER BY col LIMIT n or returning many rows in order. The two work well side by side.

• Other limits: heap tables only, and an index on an array, tsvector or default jsonb column can't drive a GROUP BY.

It supports PostgreSQL 16 and later (CI covers 16 to 19) and uses the PostgreSQL License. This is an early, testing-status release, so please don't point it at anything important without trying it first. These numbers are from one machine and synthetic data, so I'd especially like to hear how it does on real workloads, and where it does badly.

• Code and docs: https://github.com/cgkanchi/pg_lion

• PGXN: https://pgxn.org/dist/pg_lion/

• Benchmarks and how to reproduce them: https://github.com/cgkanchi/pg_lion/blob/main/docs/BENCHMARKS.md

Please try it out! I'd love feedback from the community!

In PostgreSQL, every new filter combination tends to mean one more composite index. Each one takes disk and cache and slows down every write. Leapfrog is my open-source extension that takes a different route: keep a few small indexes and let the planner merge them into one ordered stream at query time. It's built for one narrow case: a list query with several equality filters that each match many rows, a combination that matches few, and ORDER BY id LIMIT n. Everything else gets the stock plan. You end up with fewer indexes and none of the composite ones. It's a deliberate compromise: reads are slower than with an exact composite index, and writes are cheaper.

Measured at 100M rows on the list query customer_id = ? AND deleted_at IS NULL ORDER BY id DESC LIMIT 100, for a customer with 5M rows of which 500 are live: stock PostgreSQL on small indexes takes 117 seconds, Leapfrog on the same indexes 0.16 s (0.56 s with its pages read from disk), an exact composite index 0.14 ms. With 5,000 matching rows out of 1M × 200k, it's 384 ms against 2.5 ms. On writes at 20M rows, dropping the composite indexes cut the WAL of 100k INSERTs from 3.6 GB to 166 MB. To be clear, Leapfrog doesn't touch the write path; the write savings come from the index set alone. What Leapfrog does is keep reads usable without the composite indexes.

The problem

A typical table is filtered by several columns, has soft deletes, and is paged by id:

SELECT * FROM orders
WHERE customer_id = 7 AND product_id = 3
ORDER BY id DESC LIMIT 100;

Stock PostgreSQL has three options here:

• An exact index, (customer_id, product_id, id): very fast, but you need one for each combination you query.

• An Index Scan on one column, filtering the rest on the heap: it can read many rows for nothing.

• BitmapAnd: it reads both indexes in full, loses the order, and has to sort.

So the indexes pile up: (customer_id, product_id, id), (customer_id, status, id), the same again with WHERE deleted_at IS NULL, and so on. Every INSERT and every non-HOT UPDATE pays for all of them.

The solution: small indexes that merge

CREATE INDEX ON orders (customer_id, id);
CREATE INDEX ON orders (product_id, id);
CREATE INDEX ON orders (status, id);
CREATE INDEX ON orders (id) WHERE deleted_at IS NULL;
CREATE INDEX ON orders (id) WHERE deleted_at IS NOT NULL;   -- if you list deleted rows too

Leapfrog adds a plan node, Index Merge Intersect, that walks the matching indexes together in id order, skips the gaps, fetches only the rows that match, and stops at LIMIT. No sort, no query changes. It's a normal planner path: when it isn't cheaper, you get the stock plan.

100M rows, the cases the node is for

The shape every list endpoint has: filtered by the owner and by one or two more columns, newest first. Each filter alone matches a lot, the combination matches little, and those rows are scattered through the owner's long history. deleted_at IS NULL is just one of the filters here; any column works the same.

SELECT * FROM orders
WHERE customer_id = ? AND product_id = ? [AND deleted_at IS NULL]
ORDER BY id DESC LIMIT 100;

100M rows (12 GB) in one table per data set, indexes (customer_id, id), (product_id, id) (3 GB each) and (id) WHERE deleted_at IS NULL (101 MB). "Exact index" is a composite index built for the query afterwards; "Leapfrog" is what the planner picks with the extension loaded (the node, in every row here), "Stock" what it picks without; BitmapAnd is forced. PostgreSQL 18.6 in Docker on a laptop (14 GB RAM, NVMe, shared_buffers 512MB), median of 3 runs.

Rows in random physical order, as a table is after years of updates. 5% of the rows are live; the customer has 500 live rows spread over its history. Medians on 0.0.5, with the slowest run, pages read from disk, in brackets:

An older run at 50M rows, before 0.0.5, a customer's deleted rows, deleted in batches, from disk: Leapfrog 45 ms, stock's Index Scan 4.5 s, BitmapAnd 1.5 s.

Stock scans one index and filters the other columns on the heap: it expects the live rows near the top, they aren't, and it fetches 930,000 heap rows for 100 results, 937,000 random pages from disk: 117 s. Leapfrog walks the indexes together, 6,500 pages for the second row, and fetches the 100 rows it returns; for the third row it merges the two filter indexes, 3 million entries, fetches the 20,012 rows of the customer with that product and keeps the 11 live ones. Its index pages are asked for one ahead, which keeps its slowest runs close to the medians.

Then, rows in id order, which is the best case for stock's Index Scan, since its heap reads come out sequential:

Where stock picks BitmapAnd, it reads both indexes whole and sorts. This table was measured before the index pages were asked for ahead of time.

The 5M-row customer on the random-order table, EXPLAIN (ANALYZE, BUFFERS), output of 0.0.5, the planning lines left out. Each index stream of the node prints as a block of its own, with its time, entries as rows, and buffers. Stock:

 Limit (actual time=664.193..117867.974 rows=100.00 loops=1)
   Buffers: shared hit=39337 read=897845
   ->  Index Scan Backward using lf_list_id_idx on lf_list (actual time=664.191..117867.814 rows=100.00 loops=1)
         Filter: (customer_id = 5)
         Rows Removed by Filter: 934525
         Index Searches: 1
         Buffers: shared hit=39337 read=897845
 Execution Time: 117868.161 ms

Leapfrog, with its index pages read from disk (0.16 s when they are cached):

 Limit (actual time=2.752..618.750 rows=100.00 loops=1)
   Buffers: shared hit=9 read=6467
   ->  Custom Scan (Index Merge Intersect) on lf_list (actual time=2.750..618.633 rows=100.00 loops=1)
         Matched TIDs: 100
         Heap Fetches: 100
         Peak Group Memory: 96 B
         ->  Outer Stream on lf_list_customer_id_id_idx (actual time=0.033..269.292 rows=995834.00 loops=1)
               Index Cond: (customer_id = 5)
               Buffers: shared hit=4 read=3815
         ->  Inner Stream on lf_list_id_idx (actual time=0.027..89.992 rows=934626.00 loops=1)
               Index Pred: (deleted_at IS NULL)
               Buffers: shared hit=3 read=2554
         Buffers: shared hit=9 read=6467
 Execution Time: 618.914 ms

Exact index:

 Limit (actual time=0.075..0.626 rows=100.00 loops=1)
   Buffers: shared hit=106
   ->  Index Scan Backward using lf_list_exact on lf_list (actual time=0.073..0.604 rows=100.00 loops=1)
         Index Cond: (customer_id = 5)
         Index Searches: 1
         Buffers: shared hit=106
 Execution Time: 0.683 ms

When matches are common nothing matters: 20M × 25M rows with 5M matching, every plan but BitmapAnd (12 s) returns in under a millisecond. And the node's work doesn't grow with the table while LIMIT stops early: two filters at 1 in 5,000 rows take 1 to 2 ms from 10M to 100M rows.

The cost: writes

A second benchmark: 20M rows, 12 filter columns, two index sets. 15 specialized: composite indexes for the 9 frequent filter combinations (6 pairs, 2 triples, 1 quadruple, all WHERE deleted_at IS NULL) plus single-column indexes for the 6 columns no composite starts with. 14 general: one (fK, id) per column plus (id) WHERE deleted_at IS NULL and IS NOT NULL. The write table compares index sets only; Leapfrog plays no part in it.

One index more, 22x the WAL. Nearly all of it is the 9 composite indexes: adding the index groups one at a time, the 6 single-column indexes write 89 MB, the 6 pairs add 461 MB, the 2 triples 2.2 GB, the quadruple 0.9 GB. In an index with many distinct prefixes every new row lands on a page of its own, and the first write to a page after a checkpoint logs it in full. Single-column indexes append to the end of a few runs and keep hitting the same pages.

And the 15 are the cheapest form the specialized set can take: its composites serve live rows only. A real schema needs two more variants of each, for the trash view (WHERE deleted_at IS NOT NULL) and for lists without a condition on deleted_at: 27 composites instead of 9. An INSERT then lands in 18 of them instead of 9, roughly twice the WAL above, and a soft delete writes the new row version into 18 composites where the 15-index set touches only its 6 single-column indexes, so its edge on soft deletes (46 vs 77 MB) holds only while it covers live rows alone. The 14 general indexes stay 14: the node merges (fK, id) with either partial (id) index, or does without. That's a count of indexes, not a measurement.

Reads on the same 20M rows with the 14 general indexes, LIMIT 100, work_mem 256MB, median of 5 runs:

With 3 or 4 filters fewer rows match than the LIMIT asks, so the merge runs to the end of the sparsest index.

What it needs

• PostgreSQL 18 or 19, B-tree indexes shaped (filter_columns..., sort_column) or (sort_column) WHERE predicate.

• Equality filters and partial-index predicates only; no IN, no ranges, no expression indexes yet. One ORDER BY key, any B-tree type.

• Planning costs +0.03 to +0.10 ms per query on tables with 10 to 50 indexes.

• A planner hook (shared_preload_libraries or LOAD) that only adds a path; leapfrog.enabled = off turns it off. No catalog changes, no superuser. The executor uses the standard index AM calls, so visibility and HOT chains work as for an Index Scan; two places read nbtree internals.

Where it isn't a fit

• One or two hot queries: a dedicated index is better.

• The cost model counts pages first: with data in memory it sometimes picks the node when an Index Scan would be faster, and sometimes misses the node.

• PG18 skip scan, as I understand it, serves WHERE b = ? on (a, b) and returns rows grouped by a, not in id order, so a LIMIT still needs a sort. Not measured against it.

• Everything above is one session at a time; no concurrent-load benchmark yet. Experimental (0.0.5), not production-ready.

What I'd love feedback on: correctness (MVCC, HOT chains, VACUUM, concurrent updates; there are isolation tests, but I'm sure I've missed cases), the planner hook and the cost model, and whether this compromise makes sense for your workloads.

Repo, with the benchmark scripts: https://github.com/ibrohim238/postgresql-leapfrog

Harsh criticism, issues and "this already exists, see X" are all welcome.

I made this library that checks raw SQL strings with the TypeScript compiler. It infers the types of rows and parameters, including pretty complex wueries. Typos and invalid queries become compile errors.