r/postgres • u/Betelgeuse650 • 3h ago
r/postgres • u/OrbeoMe • 12h ago
Performance A Postgres RLS fix made opening a chat 40× faster (117 ms → 3 ms)
Small dev note from building Orbeo, my messenger. Chats were slow to open, and I expected the network or the phone to be the problem. It was the database access rules (Row Level Security in Postgres).
The rule "only members can see a conversation's members" called a check function for every row. In a conversation with hundreds of people, that's hundreds of function calls per query: 117 ms.
The fix: the function now returns the whole set of the user's conversations, and the rule just checks membership in it:
using (conversation_id in (select my_conversation_ids()))
Postgres evaluates that subquery once and matches rows against a hash table. Same query: 3 ms. We used the same trick for message search.
Lesson: if an RLS policy calls a function per row, check how often it runs before blaming the network. 🙂
r/postgres • u/HistoricalRuinny • 18h ago
Question Where do you develop most of your Postgres databases?
Trying to get a sense of how people actually work with Postgres day to day. If you use more than one, pick the one you spend most time in and mention the rest in comments.
r/postgres • u/lucianboboc • 1d ago
Tools I built PGZen, a native PostgreSQL client for macOS — launched today on Product Hunt
Hey everyone!
I'm an independent developer, and I've been working on PGZen, a native macOS application focused entirely on PostgreSQL.
It includes a SQL editor with autocomplete, database exploration, ER diagrams, execution plan visualization, and safe database editing.
I wanted to build something that feels at home on macOS while providing useful tools for PostgreSQL development.
PGZen is now available on the Mac App Store, and I launched it on Product Hunt today:
https://www.producthunt.com/products/pgzen
I'd love to hear what PostgreSQL developers think and which features they'd find most useful.
Thanks for checking it out!
r/postgres • u/Downtown-Warning6818 • 1d ago
Question I'm Planning to Build Something Around PostgreSQL Maintenance – Where Should I Start?
Hey everyone!
I'm exploring the idea of building a tool or service focused on PostgreSQL maintenance and database management.
I'm particularly interested in solving real-world operational challenges such as:
Vacuum and autovacuum management
WAL growth and disk space management
Index bloat detection and cleanup
Query performance monitoring
Connection management and troubleshooting
Backup, recovery, and database health monitoring
I'd love to hear from people who work with PostgreSQL in production.
A few questions:
What are the biggest PostgreSQL maintenance challenges you face?
Which repetitive DBA tasks do you wish could be automated?
Are there existing tools that solve these problems well, or are there gaps worth exploring?
If you could have one tool to make PostgreSQL operations easier, what would it do?
I'm looking to understand actual pain points before jumping into development.
Would love to hear your experiences and suggestions! 🙌
r/postgres • u/briandiaz6 • 1d ago
Discussion PostgreSQL 19 is almost here. Are you planning to upgrade?
PostgreSQL 19 is scheduled for release on November 19, just a week after PostgreSQL 14 reached its end-of-life date. I've been looking through the upcoming changes, and a few things caught my attention.
What's interesting in PostgreSQL 19:
REPACK (CONCURRENTLY) should make it easier to rebuild bloated tables without blocking regular reads and writes.
Autovacuum improvements include parallel index cleanup and better prioritization.
Foreign key checks have been optimized, which could help with write-heavy workloads.
The query planner gets some improvements, including optimizations for certain NOT IN queries.
There are also a few compatibility changes worth checking before upgrading:
JIT is now disabled by default.
MD5 authentication produces warnings.
Some configuration defaults have changed.
Certain older database configurations may cause issues with pg_upgrade.
Personally, I think the more interesting question isn't whether PostgreSQL 19 has enough new features, but whether there's a good reason to upgrade immediately.
If you're still running PostgreSQL 14, moving to a supported version is obviously more urgent. But for teams already on 17 or 18, waiting until PostgreSQL 19 has had a few updates might make more sense.
What's your upgrade strategy? Are you planning to test PostgreSQL 19 as soon as it's released, or are you sticking with your current version for now?
r/postgres • u/Remarkable_Young9971 • 3d ago
Tools I spent months building a lightweight Postgres client for Mac because I was tired of heavy ones
I got tired of heavy, slow database clients, so I spent the last few months building LithePG, a lightweight Mac-native PostgreSQL client. It's pure Swift, the app is around 15 MiB and it's open source (MIT).
Some highlights:
Schema graph: an Obsidian-style view of your database, with tables as nodes and foreign keys as links. Click a table to see its columns and types.
On-device AI: "Ask in English" turns a plain request into SQL using Apple's on-device model. It only drafts read-only queries, drops them in the editor for you to review and never runs them. Your prompts and data never leave your Mac. (Needs macOS 26 with Apple Intelligence.)
Safe edits: edit cells in place and review the exact UPDATE before it runs. It won't overwrite a row someone else changed.
EXPLAIN as a tree: the plan shows as a tree, with the costliest node flagged.
Connections: passwords live in the Keychain, Production connections get a warning banner and pasted connection strings (Neon, psql) just work.
Install with brew install --cask omarpr/tap/lithepg or grab it at https://www.lithepg.app
Would love feedback, especially on what's missing from your daily workflow.
r/postgres • u/Equivalent_Cut_7938 • 4d ago
Tools LunoDB 2.3.2: one connection opens the whole server
Looking for feedback! I’m looking to make the best database client app for macOS, Windows and Linux 💪🏻
r/postgres • u/Foreign_Career_454 • 4d ago
Question how do you safely query your users table before tenant context in pg rls
running a multi-tenant B2B setup with pg rls & .net10 & for checking our db set up tenant-scoped is locked with SET LOCAL app.tenant_id = .... but during initial login handshake we query users and tenant_memberships to know tenant ID, so those tables cant have tenant-scoped rls applied; now, DB role has raw SELECT access across whole users table wanted to check how others lock down pre-tenant auth queries at the db layer? Do we use dedicated login? Or any lookup functions? or Just splitting schemas.
r/postgres • u/ZookeepergameIcy5686 • 5d ago
Performance ZeBridge syncs Postgres to offline SQLite clients: Postgres still decides which writes commit.
I’m building ZeBridge, which reads Postgres changes through pgoutput, publishes them to NATS JetStream, and delivers them to local SQLite replicas.
Say a field technician downloads a work order, loses connectivity, and adds a line item. While they’re offline, someone at the office deletes the order. What should the technician see when their phone reconnects?
That’s the kind of problem I built ZeBridge for. You enable sync on selected Postgres tables and identify the tenant column. The app queries a local SQLite replica and can queue edits offline. When the phone reconnects, the bridge submits each write to Postgres under the user’s identity. In the work-order example, the foreign key rejects the line item, and the app gets a rejection for that specific edit, including the SQLSTATE. If a write does commit but the phone loses the reply, its retry gets the receipt recorded in the same transaction.
Changes can also start outside the app. An existing service, a SQL script, or someone using psql can update the source tables. The bridge reads the changes through pgoutput and publishes them to NATS JetStream. Each device catches up from the stream, so a phone can go offline without needing its own Postgres connection or replication slot.
I measured one part of that path on a production hub: one SQL statement inserted 100,000 rows, and a client had all 100,000 locally 2.95 seconds after commit. The bridge published about 46,700 change events/s during that run. The 2.95 seconds is the figure I care about as an app developer: it ends when the rows are in the client database.
I’d be interested in how others have approached this. If you’ve added offline writes to an existing Postgres application, which database rule was hardest to explain to a user who made their edit hours earlier?
Runnable shared record example
For others running logical replication consumers: what test do you use to prove that your reported flush position can’t get ahead of what your downstream system can recover?
r/postgres • u/wtfse • 5d ago
Debugging exploit a 22 years old PostgreSQL composite-type vulnerability
r/postgres • u/adnanbiz • 8d ago
Performance Index on project_id still read 200,000 rows for "latest 20 issues" (measured on PG16)
I measured a query pattern that shows up in almost every app, and the result surprised me a bit, so sharing the numbers.
**Query:**
SELECT id, title, status, created_at
FROM issues
WHERE project_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 20;
**Setup:** PostgreSQL 16, 2M rows in `issues`, 2,000 projects. One big project has 200,000 issues, a typical one has ~900. Median of 5 runs of `EXPLAIN (ANALYZE, BUFFERS)`, data cached.
| Index | Project | Time | Buffers |
|---|---|---|---|
| `(project_id)` | big (200k rows) | 20.9 ms | 1,941 |
| `(project_id)` | typical (901 rows) | 2.42 ms | 911 |
| `(project_id, created_at DESC, id DESC)` | big | 0.091 ms | 23 |
| `(project_id, created_at DESC, id DESC)` | typical | 0.106 ms | 23 |
With only `(project_id)`, the plan for the big project was a bitmap index scan returning all 200,000 rows, a parallel bitmap heap scan, then a top-N heapsort to keep 20. So the cost scales with the size of the project, not the page. Your biggest tenant pays the most, on every page load.
With the composite index there's no sort node: it walks the index from the newest entry and stops after 20. Same 23 buffers for big and small projects.
Things that mattered:
- Equality column first, then the ORDER BY columns, in the same direction.
- Include the tiebreaker (`id`) in both the ORDER BY and the index. The same index then serves keyset pagination (`WHERE (created_at, id) < ($2, $3)`).
- Build it with `CREATE INDEX CONCURRENTLY` on a live table.
- Once it exists, the plain `(project_id)` index is redundant.
What gave it away in the plan: a Sort (top-N heapsort) right under the Limit, and `rows=` on the scan far above the LIMIT. Worth checking against your biggest tenant rather than average test data.
Curious if anyone has hit cases where the planner still preferred the single-column index here, e.g. with very skewed stats.
r/postgres • u/Sea_Employer_4000 • 8d ago
Tools I built an open-source tool for migrating data between PostgreSQL and Neo4j (RtoGtoR)
galleryr/postgres • u/Master-Bass-1905 • 9d ago
Discussion Readyset - Introducing Automatic Failover and Multi Server Support
Readyset is a query cache for MySQL and Postgres. It now has support for automatic failover and multi-server support. Basically it means that if the server that it’s shadowing fails over, the cache will automatically adjust and work post-failover. It’s a pretty interesting technical solution, and one I thought this audience might appreciate. You can read more here.
https://readyset.io/blog/introducing-automatic-failover-and-multi-server-support
r/postgres • u/dbforge_dev • 9d ago
Tools PostgreSQL GUI clients: lightweight tools vs full IDEs
PostgreSQL GUI tools fall roughly into three groups: lightweight clients (TablePlus, Beekeeper Studio, Postico) and full IDEs (pgAdmin, DBeaver, dbForge Studio for PostgreSQL). Asking which one is best doesn't get you far. It's more useful to look at what each group handles well, task by task.
| Task | Lightweight clients | Full IDEs |
|---|---|---|
| Browse and edit table data | Fast to open, clean UI. Usually the best experience here | Works, with filtering, grouping and export on top |
| Writing SQL every day | Editor with basic completion | Context-aware completion, formatting, snippets, refactoring, query history |
| Query performance | You run EXPLAIN yourself | EXPLAIN Plan view plus profiling tools. In dbForge you can compare profiling runs before and after a change |
| Server admin (roles, backups, maintenance) | Limited | Varies by tool, usually not the focus |
| Comparing schemas between environments | Usually not available | Schema and data compare with reviewable sync scripts and CLI runs |
| Test data, reports, automation | Rarely | This is where IDEs pay off |
| Cost | Free tiers or low-cost licenses, check the limits | Mostly paid, but part of them offers free editions |
When a lightweight client is enough
You mostly read and edit data, your schema is managed by an ORM or a migration tool, and you rarely write long SQL by hand. On a Mac, a native client like Postico may simply feel better than anything cross-platform.
When a full IDE starts to make sense
You own the schema, you work across dev, staging and prod, and the same tasks keep coming back: checking that environments match, preparing test data, producing the same report every week. That's where completion quality, compare tools and CLI automation save real time.
A note on dbForge Studio for PostgreSQL so it's clear where it fits. It also works with PostgreSQL-compatible services like Amazon Redshift, Aurora, Azure PostgreSQL and Supabase.
Which group are you in? And if you switched from one to another, what made you switch?
r/postgres • u/huseyinbabal • 12d ago
Performance Postgres vs MySQL: I Broke Both
r/postgres • u/Lucifer77-7 • 13d ago
Discussion I built an in-browser PostgreSQL playground running on WebAssembly (PGlite) with real business datasets
r/postgres • u/Mr_StyleNo • 14d ago
Question PostgreSQL 19 got delayed after a wave of bug reports. Is AI code review becoming too good?
PostgreSQL 19 was originally expected earlier this fall, but the release has been pushed to November 19. One interesting part of the story is how AI-assisted code reviews have changed the process. Developers are finding issues faster, including edge cases that might previously have gone unnoticed until after release. Several major features have been pulled from PostgreSQL 19, although not all of those decisions were directly caused by AI-discovered bugs. And that raises an interesting question. What happens when finding bugs becomes faster than reviewing and fixing them? On one hand, catching problems before release is obviously a good thing, especially for database software. On the other, maintainers still need to verify findings, prioritize fixes, and decide which issues are actually worth delaying a release over. Could AI-assisted code review eventually become a bottleneck rather than a productivity boost?
Would you rather wait longer for a more thoroughly reviewed release, or ship on schedule and address lower-priority bugs in subsequent updates?
r/postgres • u/BloodySouly • 14d ago
Question Database performance optimization: a visual cheat sheet
r/postgres • u/Ok_pettech • 15d ago
Performance Postgres vs MariaDB for AI Performance
interconnectd.comr/postgres • u/Hxndr1k • 15d ago
Tools I built a free Postgres GUI with an EXPLAIN ANALYZE tree view, RLS policy editor and advisors
r/postgres • u/huseyinbabal • 16d ago
Performance I Overloaded Postgres and SQLite
Article version: https://huseyinbabal.dev/posts/postgres-vs-sqlite-load-test/
r/postgres • u/Upper_Fee_4580 • 16d ago
Performance PostgreSQL makes you add an index for every filter combination. I built an extension that merges small indexes at query time instead
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 tooLeapfrog 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:
| Query | Exact index | Leapfrog | Stock | BitmapAnd |
|---|---|---|---|---|
customer with 5M rows AND deleted_at IS NULL | 0.14 ms | 160 ms (559 ms) | Index Scan 117 s (118 s) | 2.7 s (3.0 s) |
customer with 1M rows AND deleted_at IS NULL | 0.14 ms | 122 ms (152 ms) | Index Scan 1.8 s (24.5 s) | 1.5 s (2.0 s) |
customer with 1M rows AND product_id = ? (2M rows) AND deleted_at IS NULL: 11 match | 0.03 ms | 179 ms (244 ms) | BitmapAnd 610 ms (1.3 s) | 1.3 s (1.8 s) |
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:
| Query | Exact index | Leapfrog | Stock | BitmapAnd |
|---|---|---|---|---|
1M × 2M AND deleted_at IS NULL: 11 match | 0.1 ms | 259 ms cached, 3.9 s from disk | Index Scan 168 s | 1.1 s |
| 1M × 200k, 5,000 match, 1 row in 20,000 | 1.1 ms | 2.5 ms | BitmapAnd 384 ms | 373 ms |
| 10k × 1M, 100 match | 0.4 ms | 104 ms | BitmapAnd 338 ms | 295 ms |
| 1M × 200k, 500 match, all in the middle of the table | 0.6 ms | 66 ms | BitmapAnd 353 ms | 371 ms |
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 msLeapfrog, 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 msExact 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 msWhen 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.
| Writes and storage | 15 specialized | 14 general |
|---|---|---|
| Index size | 11.2 GB | 8.0 GB |
| WAL, 100k INSERTs | 3,595 MB | 166 MB |
| INSERT rows/s | 945 | 12,592 |
| WAL, UPDATE of a filter column (25k rows) | 1,004 MB | 70 MB |
| WAL, soft delete (25k rows) | 46 MB | 77 MB |
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:
| Query | Exact index | Leapfrog | Stock | BitmapAnd |
|---|---|---|---|---|
| 2 filters | 0.16 ms | 1.0 ms | BitmapAnd 71 ms | 72 ms |
| 3 filters, 21 rows found | 0.06 ms | 33 ms | BitmapAnd 71 ms | 74 ms |
| 4 filters, 0 rows found | 0.03 ms | 31 ms | BitmapAnd 59 ms | 65 ms |
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. OneORDER BYkey, 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_librariesorLOAD) that only adds a path;leapfrog.enabled = offturns 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 bya, not inidorder, so aLIMITstill 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.