r/postgres • • 3h ago

Performance Postgres on ZFS

Thumbnail reddit.com
1 Upvotes

r/postgres • • 12h ago

Performance A Postgres RLS fix made opening a chat 40× faster (117 ms → 3 ms)

1 Upvotes

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 • • 18h ago

Question Where do you develop most of your Postgres databases?

2 Upvotes

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.

27 votes, 6d left
psql
pgAdmin
DBeaver
DataGrip
VS Code + extension
Other (comment)

r/postgres • • 1d ago

Tools I built PGZen, a native PostgreSQL client for macOS — launched today on Product Hunt

4 Upvotes

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 • • 1d ago

Question I'm Planning to Build Something Around PostgreSQL Maintenance – Where Should I Start?

3 Upvotes

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:

  1. What are the biggest PostgreSQL maintenance challenges you face?

  2. Which repetitive DBA tasks do you wish could be automated?

  3. Are there existing tools that solve these problems well, or are there gaps worth exploring?

  4. 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 • • 1d ago

Discussion PostgreSQL 19 is almost here. Are you planning to upgrade?

1 Upvotes

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 • • 2d ago

Question Supabase

Thumbnail reddit.com
0 Upvotes

r/postgres • • 3d ago

Tools I spent months building a lightweight Postgres client for Mac because I was tired of heavy ones

0 Upvotes

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 • • 4d ago

Tools LunoDB 2.3.2: one connection opens the whole server

Thumbnail
reddit.com
1 Upvotes

Looking for feedback! I’m looking to make the best database client app for macOS, Windows and Linux 💪🏻


r/postgres • • 4d ago

Question how do you safely query your users table before tenant context in pg rls

1 Upvotes

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 • • 5d ago

Performance ZeBridge syncs Postgres to offline SQLite clients: Postgres still decides which writes commit.

Thumbnail
github.com
1 Upvotes

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?

ZeBrdige project

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 • • 5d ago

Debugging exploit a 22 years old PostgreSQL composite-type vulnerability

Thumbnail
mehmetince.net
5 Upvotes

r/postgres • • 8d ago

Tools I built an open-source tool for migrating data between PostgreSQL and Neo4j (RtoGtoR)

Thumbnail gallery
3 Upvotes

r/postgres • • 8d ago

Performance Index on project_id still read 200,000 rows for "latest 20 issues" (measured on PG16)

1 Upvotes

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 • • 9d ago

Discussion Readyset - Introducing Automatic Failover and Multi Server Support

2 Upvotes

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 • • 9d ago

Tools PostgreSQL GUI clients: lightweight tools vs full IDEs

4 Upvotes

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.

TaskLightweight clientsFull IDEs
Browse and edit table dataFast to open, clean UI. Usually the best experience hereWorks, with filtering, grouping and export on top
Writing SQL every dayEditor with basic completionContext-aware completion, formatting, snippets, refactoring, query history
Query performanceYou run EXPLAIN yourselfEXPLAIN Plan view plus profiling tools. In dbForge you can compare profiling runs before and after a change
Server admin (roles, backups, maintenance)LimitedVaries by tool, usually not the focus
Comparing schemas between environmentsUsually not availableSchema and data compare with reviewable sync scripts and CLI runs
Test data, reports, automationRarelyThis is where IDEs pay off
CostFree tiers or low-cost licenses, check the limitsMostly 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 • • 12d ago

Performance Postgres vs MySQL: I Broke Both

Thumbnail
youtube.com
1 Upvotes

r/postgres • • 13d ago

Discussion I built an in-browser PostgreSQL playground running on WebAssembly (PGlite) with real business datasets

Thumbnail
reddit.com
1 Upvotes

r/postgres • • 14d ago

Question PostgreSQL 19 got delayed after a wave of bug reports. Is AI code review becoming too good?

2 Upvotes

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 • • 14d ago

Question Database performance optimization: a visual cheat sheet

4 Upvotes

r/postgres • • 15d ago

Performance Postgres vs MariaDB for AI Performance

Thumbnail interconnectd.com
1 Upvotes

r/postgres • • 15d ago

Tools I built a free Postgres GUI with an EXPLAIN ANALYZE tree view, RLS policy editor and advisors

Thumbnail
pgquery.dev
1 Upvotes

r/postgres • • 16d ago

Performance I Overloaded Postgres and SQLite

Thumbnail
youtu.be
2 Upvotes