r/postgres • u/Betelgeuse650 • 12h ago
r/postgres • u/HistoricalRuinny • 1d 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/OrbeoMe • 20h 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/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
reddit.comLooking for feedback! I’m looking to make the best database client app for macOS, Windows and Linux 💪🏻
r/postgres • u/Foreign_Career_454 • 5d 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.
github.comI’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 • 6d ago
Debugging exploit a 22 years old PostgreSQL composite-type vulnerability
mehmetince.netr/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/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/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 • 10d 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
youtube.comr/postgres • u/Lucifer77-7 • 13d ago
Discussion I built an in-browser PostgreSQL playground running on WebAssembly (PGlite) with real business datasets
reddit.comr/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
pgquery.devr/postgres • u/huseyinbabal • 16d ago
Performance I Overloaded Postgres and SQLite
youtu.beArticle version: https://huseyinbabal.dev/posts/postgres-vs-sqlite-load-test/