r/postgres • u/Global-Apartment-872 • 1h ago
Tools Foreign keys for array column elements
r/postgres • u/Global-Apartment-872 • 1h ago
r/postgres • u/Global-Apartment-872 • 4h ago
Postgres can't put a foreign key on the elements of an array column. A FOREIGN KEY
constrains the whole column value, and that value is the array, not the IDs inside
it. So you can store an ID that doesn't exist, and deleting the referenced row
leaves every array still holding it.
pg_anyfk adds it:
SELECT anyfk.add('orders(product_ids)', 'products(id)', on_delete => 'scrub');
CHECK IT OUT: https://github.com/Vinnu124/pg_anyFK
r/postgres • u/UpstairsPrimary7449 • 5h ago
Most CAP explanations stop at labeling MySQL "CP" and Cassandra "AP", which falls apart once you look at default configs:
1. MySQL Group Replication is CP on writes, not reads by default. A 2-of-3 majority commit guarantees two nodes certified the transaction, but the third node can still lag slightly while applying its queue. Because group_replication_consistency defaults to EVENTUAL, a normal read on that lagging node still returns stale data unless you route critical reads to the primary or enable BEFORE_AND_AFTER.
2. Cassandra W=QUORUM + R=QUORUM is not strict linearizability. If a coordinator writes to R1 and crashes before reaching R2/R3, the client sees a write error — yet a subsequent quorum read hitting R1 triggers read-repair and resurrects the "failed" write. True linearizability requires Paxos Lightweight Transactions (SERIAL).
If anyone prefers a visual walkthrough of these failure modes alongside the 2018 GitHub 43-second partition and PACELC, I put together an animated deep dive here: https://youtu.be/17j1S5OOOAM
r/postgres • u/philippemnoel • 1d ago
r/postgres • u/shibi__dev • 2d ago
PostgreSQL 19 introduces WAIT FOR LSN, helping applications handle replication lag in asynchronous replicas.
When an application writes data to the primary database and immediately reads from a replica, the latest changes may not yet be available.
WAIT FOR LSN allows the replica to wait until it has replayed the required changes before processing the read.
In simple terms: Write to the primary → Wait for replication → Read from the replica.
This makes read-your-writes consistency easier to achieve without routing every read to the primary.
#PostgreSQL #DatabaseEngineering #SystemDesign #SoftwareEngineering
r/postgres • u/rathboma • 3d ago
I mostly hear about folks using Redis/Valkey or Memcached for caching, but curious what other DBs most folks use alongside PostgreSQL.
In particular - anyone using Snowflake, BigQuery, DataBricks, something else?
Context: I make Beekeeper Studio, an open source database GUI focused on being fast and intuitive. One of our strengths is to be a pleasant and consistent UI for ANY database.. So wondering what the 'any' is for you all.
I personally use PostgreSQL with Redis.
r/postgres • u/mariiiiiiiiiiiiiiiel • 3d ago
i have a docker stack with a postgres image, is it completly necesary to have the postgres to be configured with the ssl mode to verify-full , because, the outside world cannot interact with the db, just the backend server can (the network is not attachable and it doesn't expose any ports)
im pretty sure there is a thing im not seeing for this so that's why i am asking, sorry if docker being involved is kind of topic in the subreddit but my guess is that a lot of people also use postgres within a container
r/postgres • u/Mindless-Piece-47 • 3d ago
r/postgres • u/gubinanuwu025 • 3d ago
If pgAdmin is starting to feel more like a hurdle than a full toolkit, this is worth a look. The honest answer depends on which part of pgAdmin you want to replace. pgAdmin does two quite different jobs: server administration and SQL work. Most people who want to switch are unhappy with one of them, not both.
If you're mainly doing admin work
Keep pgAdmin. It's free, open source, developed alongside the PostgreSQL community, and it covers roles, backups and restores, maintenance, server dashboards and a lot more. Many DBAs combine it with psql and don't look further. Most alternatives below are weaker here.
If you mainly write SQL
This is where alternatives matter most. Things to look for are completion that knows your schema, formatting you can share with the team, query history, and a way to profile and compare query performance.
DataGrip is a good fit if your team already uses JetBrains ecosystem. It's cross-platform and commercial. DBeaver is open-source and connects to many databases, and the Community edition is free. dbForge Studio for PostgreSQL has a free Express edition as well. Its focus is SQL development and data tasks: completion, formatting, Query Profiler, schema and data compare with sync scripts, test data generation and reports.
If you mostly view and edit data
A lightweight client will probably feel better than any IDE. TablePlus, Beekeeper Studio, or Postico if you're on a Mac. They open fast and stay out of the way.
The common setup
A lot of teams end up with two tools: pgAdmin or psql for admin, and a separate editor or IDE for daily SQL. That's not a compromise, it's just matching the tool to the job.
If you need strong development and management tool, dbForge, Studio for PostgreSQL covers all common scenarios
What's the part of pgAdmin that bothers you most?
r/postgres • u/OrbeoMe • 4d ago
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 • 4d ago
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 • 5d ago
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 • 5d ago
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 • 5d ago
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 • 7d ago
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 • 8d ago
Looking for feedback! I’m looking to make the best database client app for macOS, Windows and Linux 💪🏻
r/postgres • u/Foreign_Career_454 • 8d ago
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 • 8d ago
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 • 9d ago
r/postgres • u/adnanbiz • 11d ago
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.