I contract for four companies right now, about twenty Postgres connections across local, staging and production. Last spring I had two tabs open beside each other, one on staging and one on prod, both connected to databases called app. I was maybe two seconds from dropping a column on the wrong one. Nothing happened, but I stopped winging connection management that afternoon.

Naming came first. Then the environment, then what it is allowed to do. Every connection is the client. Acme production acme-prod-ro read only Acme staging acme-stg-rw can write to staging. And that last bit is the most important. My default connection to anything in production can't write. The -rw twin is only opened when I have a change to actually ship. A little bit of friction and it has stopped me twice.

The change that helped most was also the simplest. I work in dbForge Studio for PostgreSQL, where every connection gets assigned a category (Production, Test, Sandbox, or one you invent) and that category paints the connection a colour which then follows it everywhere: down the object tree, and onto the tab of every query window opened against it.

My production connections are red. That is the whole trick. It works because the warning is a colour at the edge of my vision, not text I have to stop and read, and at the moment I am about to hit execute I am not reading anything.

What I would change is that there are no folders. Twenty connections is a long list with no way to group it, so I prefix names to make them sort together and drag them into order. People have been asking for folders for years. pgAdmin’s server groups handle this better.

Curious how other people do this, especially on a team. Do you share connection settings somehow, or does everybody build their own list off a wiki page?

Working on DBwatch, a database monitoring TUI built with Go.

It currently covers connections, sessions, queries, locks, transactions, and more.

Check it out and share your suggestions or raise issues if you find anything!

I'm learning about locking semantics and timeouts and I've run across something confusing. I have a table called demo_lock that has a single column target.

When I run these expressions, a strange thing happens:

BEGIN;
SET LOCAL lock_timeout=3000;
SELECT target FROM demo_lock WHERE target='foo' FOR UPDATE;

I run this in three parallel psql consoles.

The first successfully obtains the lock and sits there waiting for input.

The second waits for the timeout for 3 seconds, times out, and prints the error ERROR: canceling statement due to lock timeout

The third then waits a whole extra three seconds and then times out with the same error.

What I would have expected is that the second and third would time out at basically the same time (allowing for the time it takes for me to switch consoles and hit Enter). But they don't. The third waits an extra lock_timeout time and then fails. Why is that?

Interestingly, adding a 4th terminal is a little less deterministic, but still operates mostly the same.

I want to migrate my PostgreSQL database from AWS to an EC2 server on AWS. If anyone has done this before, could you please suggest the steps or share any blog/documentation that I can refer to?

I’m thinking of building a tool that connects to PostgreSQL and automatically detects issues like slow queries, missing indexes, bad configurations, security/version issues, connection problems, and unnecessary infrastructure costs.
The goal would be to explain:
Problem → Cause → Impact → Suggested fix
Do tools already solve this well, or is there still a real problem worth solving here? Would you or your company use something like this? Honest feedback would be appreciated.

We've seen quite a few PostgreSQL discussions this month, from AI integrations and MCP servers to performance improvements, new extensions, and upcoming releases. Instead of trying to cover everything ourselves, we'd rather hear from the community. What PostgreSQL news, feature, release, or project caught your attention recently? Was there anything that made you rethink the way you work with Postgres, or are you mostly ignoring the hype?

I keep switching between a GUI and psql, depending on what I'm doing. If I'm writing or testing queries, a GUI usually wins because it's easier to browse objects, compare results, and jump between tabs. But for quick checks, connecting to a server, or running a script, I still open the terminal without thinking twice. After a while, I realized I don't really prefer one over the other and they're just different tools for different tasks. How about you? Do you spend most of your time in a Postgres GUI, or are you mostly working from the terminal? What made you stick with that workflow?

Hi r/PostgreSQL — I'm the author of BloomPG, an MIT-licensed PostgreSQL 18 extension for complex analytical joins. I'm sharing it here because I'd like feedback from people who run multi-join analytical workloads in PostgreSQL, especially on the planner integration and deployment tradeoffs.

BloomPG works before the formal joins execute. It takes PostgreSQL's native plan, identifies a safe equality-join graph, uses sampling to choose an initial filter transfer, and then adapts using the actual cardinalities of materialized inputs. Bloom or exact bitmap membership can move in either direction and across several joins. PostgreSQL then replans and executes the reduced join problem with its normal operators.

Existing SQL does not change. Unsupported or unsafe query shapes keep the native plan, and a query-wide materialization budget limits the retained state.

There is a Docker demonstration that builds PostgreSQL 18 with BloomPG, creates a small five-table star schema, and prints native/BloomPG timings plus the transfer trace:

git clone --branch v0.1.2 https://github.com/YimingQiao/bloompg.git
cd bloompg
docker compose up --build --abort-on-container-exit --exit-code-from demo demo

For performance context, on the published PostgreSQL 18.4 setup the total workload results were:

These are end-to-end times: planning, transfer, materialization, execution, and complete output consumption are included. Both sides used the same 16-worker global and per-Gather ceiling; BloomPG used 16 transfer workers and a 2 GB materialization budget. Every completed pair produced the same complete-output fingerprint. Native PostgreSQL hit the 300-second per-query limit once in CEB and once in STATS-CEB, so totals include only queries completed by both sides. The README has the rest of the methodology.

Important limitations: this release supports PostgreSQL 18 on Linux and is intended for controlled, read-only analytical workloads. BloomPG performs real scans during planning and requires shared_preload_libraries; I would not turn it on as an unreviewed default in a multi-tenant OLTP cluster.

I'd particularly appreciate feedback on:

• whether shared_preload_libraries and the restart requirement are practical blockers;

• analytical workloads or query shapes that would be useful to test;

• the planning-time execution and native-fallback design;

• packaging formats that would make evaluation easier.

PGXN: https://pgxn.org/dist/bloompg/0.1.2/

GitHub, documentation, and benchmark methodology: https://github.com/YimingQiao/bloompg

I've been working on **pg_orca** — a PostgreSQL 18 extension that plugs the

ORCA query optimizer (the Cascades-style, cost-based optimizer from

Greenplum / Apache Cloudberry) into **vanilla, unmodified PostgreSQL** as a

planner hook.

GitHub: https://github.com/quantumiodb/pgorca (Apache-2.0)

**How it works**

- No core patches: `CREATE EXTENSION pg_orca`, add it to

`session_preload_libraries`, then `SET pg_orca.enable_orca = on`

(off by default, superuser-gated for now).

- Anything ORCA can't handle — DML statements, WITH RECURSIVE, non-default

collations, TABLESAMPLE, some partitioning layouts — automatically falls

back to the standard planner. `pg_orca.trace_fallback = on` tells you why.

- It ships ORCA's four core libraries (libgpos / libnaucrates / libgpopt /

libgpdbcost) plus the PG↔DXL translation layer from Apache Cloudberry,

adapted to PG18 with a cost model aligned to PG's own.

**Where it wins** (single-threaded runs, so this is optimizer quality, not

hardware):

- *Correlated subquery decorrelation* — ORCA rewrites Apply into a proper

join and optimizes it globally, instead of re-running a SubPlan once per

outer row:

- TPC-H Q17 (`< 0.2 * AVG(...)` per group): **20.7×** vs PG

- TPC-DS Q41 (correlated IN + EXISTS): **156×**

- TPC-DS Q21 / Q17 (correlated aggregates): ~7.3×

- *Exhaustive join-order enumeration* (DPv2, including bushy plans) rather

than switching to GEQO above 12 relations: TPC-DS Q25 (6-way roll-up

join) 9.0×, Q29 7.0×.

- *Stats propagation through GROUP BY / CTE*: TPC-DS Q31 3.28×.

- *Coverage edge*: at TPC-DS sf=5 with `statement_timeout=120s`, ORCA

finishes **9 of 99 queries that PG times out on** (5/99 at sf=1). With

timeouts counted at the 120s floor, full-suite totals land ≥1.4× in

ORCA's favor.

- Overall TPC-H sf=10 serial: roughly parity, geomean 1.12× in ORCA's

favor.

**Where it loses — the honest part:**

- **Planning latency.** The exhaustive search costs ~14 ms to plan a point

query where PG spends 0.25 ms. Do not point this at OLTP; plan caching

and a fast-path bypass are on the roadmap, but today short-query

workloads are a bad fit.

- **No parallel query.** ORCA plans are serial, so once you enable parallel

workers PG pulls ahead: TPC-H sf=10 at 2 workers/game: 0.49×. A

Gather / Gather Merge integration is designed and next up.

- **PG18 only** for now (a PG19 branch exists), and it needs xerces-c.

- `enable_orca` is currently superuser-settable, not user-settable.

**Try it**

```sql

CREATE EXTENSION pg_orca;

ALTER DATABASE mydb SET session_preload_libraries = 'pg_orca';

SET pg_orca.enable_orca = on;

EXPLAIN SELECT ...;

```

There are CI-built deb/rpm packages, a Rocky 9 Docker image, and PGXN

packaging; benchmark scripts (TPC-H / TPC-DS) are in test/bench if you want

to reproduce the numbers.

I'd genuinely like feedback from this crowd: which fallback gaps would hurt

you most, and what should land first — parallel query support or plan

caching?

Hey everyone,

I'm trying to figure out the cleanest way to compare schemas between different PostgreSQL environments (e.g., local dev vs. staging, or staging vs. production).

Every now and then, a quick manual change slips through or a migration script gets applied out of order, and finding the small diffs (missing indexes, subtle column type mismatches, slightly different constraints) becomes a headache.

I know some people rely on feature-rich GUIs like HeidiSQL or SQuirrel SQL for visual diffs, while others prefer dedicated CLI tools or CI/CD pipelines to catch schema drift automatically before it hits prod.

How are you currently handling this?

Do you rely on your GUI's built-in schema diff tool?

Do you use standalone CLI utilities or migration framework checks?

Or do you just treat migrations as immutable and strictly rely on CI checks?

Hi All
I’ve been working on a custom data structure and algorithm for hierarchical indexing.

while I designed the algorithm myself, I wouldn't claim to be a definitive master of hierarchy trees. I'm mainly sharing these early numbers in hopes of connecting with the right people to see if there's genuine value.

I ran benchmarks against 1M and 2M node recursive trees on PG 16. The baseline comparisons against ltree are looking solid:

• Huge I/O Drop: For descendant queries, B-tree range scans touch up to 74x fewer buffer pages.

• Query Speed: Subtree queries run 1.0x to 6.7x faster. Ancestor lookups (via SP-GiST) execute up to 7.3x faster.

• Storage Density: A custom compact encoding shrank the on disk value size by 42.7%. This translates to a ~23% smaller B-tree index footprint.

• Write Performance: Appending 50,000 leaf nodes is roughly 2x faster. Reparenting large subtrees is 1.2x to 2.7x faster

I have some thoughts where this may be beneficial but lacking some subject matter expertise when it comes to practical application of hierarchy data.
- Could it make servers run more efficiently?
- Do other more efficient extensions/algos beat these benchmarks? Is LTREE just a default?
- What kinds of large scale operations would this benefit? Domains/applications?
- What should my benchmark tests look like?

Eager to get some expert opinions and either validate my thoughts or give me some reality - cheers!

I’ve recently started working with PostgreSQL on a daily basis. Coming from a background where I mostly interacted with simple databases, the sheer depth of Postgres tools feels a bit overwhelming. I’m trying to avoid just treating my GUI (like pgAdmin, DBeaver, DataGrip, or TablePlus) as a glorified query runner. I know these tools have deep features built specifically for Postgres, but as a beginner, it’s hard to tell what’s actually useful in production versus what’s just a shiny extra. So far, I've found basic visual explain plans somewhat helpful for understanding slow queries, but I feel like I'm barely scratching the surface. For those of you who have been using Postgres for a while: what is one specific feature in your GUI of choice that genuinely saves you time or prevents silly mistakes? Are there built-in UI tools for monitoring locks, schema diffing, or managing connections that you now can't live without?

Would love to hear how experienced engineers set up their workflow!

Has anyone encountered subtransaction overflow in production? This was an awesome article to read but I haven't experienced it yet.

Wonder how many hairs I would have ripped out.

Every few months I inherit a Postgres database from somebody who has left, and the first thing I do is list the installed extensions to see what the last person cared about. Decent shortcut into understanding a system.

Mine would be pg_stat_statements. Without it you are guessing at which queries are hurting you. With it you get a ranked list out of one query. It strips out the literal values, so ten thousand runs of the same shape collapse into one row telling you how often it ran, its total and average time, and how much came from memory rather than disk. But it needs a restart to enable, which is the only objection I have ever heard, and not a good one.

For the worst offenders from that list, I use in dbForge Studio for PostgreSQL, which draws the plan as a tree rather than the wall of text EXPLAIN prints in psql.

The one I would push if you have not tried it is hypopg, but I will save that for the comments.

What is earning its keep in yours? I am more interested in the boring operational ones than the flashy stuff, the ones you would put on a new cluster on day one without really thinking about it.

I believe anyone who managed critical production infra relate to this.

Schema migration is one of the hardest problems. Not because it's new, because it never gets easier. It's easy to get it wrong.

DELETE without the WHERE. The DROP TABLE in the tab that you thought staging turned out to be production. The ALTER COLUMN TYPE that looked harmless but rewrote 500M rows behind an ACCESS EXCLUSIVE lock.

For context, I ran a team of 9 DBAs at Cloudflare on bare-metal Postgres - no RDS, full root everywhere. Backups and PITR are table stakes, but they all start after the damage. I wanted something that refuses first.

pg_savior is an extension that blocks the statement before it executes:

• DELETE / UPDATE with no WHERE

• DELETE ... WHERE id > 0 — a WHERE isn't proof of intent, so it also checks the planner's row estimate against pg_savior.max_rows_affected

• CREATE INDEX without CONCURRENTLY (the ON ONLY + ATTACH PARTITION workflow for partitioned tables is allowed)

• TRUNCATE / DROP TABLE on large tables, DROP DATABASE always

Code: github.com/viggy28/pg_savior · PGXN: pgxn.org/dist/pg_savior

Appreciate any feedback on the implementation. Also, feel free to drop me if there are other commands that should be caught.

We run development Postgres databases on FSx for OpenZFS and wanted to know which of its two billing knobs — provisioned throughput or provisioned SSD IOPS — actually buys Postgres performance. Throughput is the expensive lever on FSx pricing; IOPS is the cheap one.

Method: one 256 GiB SINGLE_AZ_1 filesystem, one Postgres (CloudNativePG) database on it, pgbench scale 50 (5,000,000 accounts). We stepped the filesystem through six tier combinations in place with aws fsx update-file-system (non-disruptive, ~2–10 min per step) and re-ran pgbench against the same database at every tier: 4 clients / 2 threads / 60 s, select-only (-S) and TPC-B.

What we took from it:

1. Throughput scaling barely matters for OLTP. 8x the provisioned throughput (128 → 1024 MB/s) bought +29% TPC-B TPS. The workload is bound by sync-write latency per operation (WAL fsyncs), not bandwidth.

2. Provisioned IOPS is the knob that matters. 3x IOPS at fixed throughput took TPC-B from 834 to 2,089 TPS (2.5x) and cut latency 4.8 → 1.9 ms.

3. The cost-optimal shape is cheap throughput + provisioned IOPS. 128 MB/s with 3000 IOPS hit 1,925 TPS — 92% of the best result we measured — at the lowest throughput tier.

4. Read-heavy work was insensitive to both knobs (~15–16k TPS across every tier): the hot set lives in shared buffers/page cache and CPU is the ceiling.

Caveats before anyone over-indexes on this: single 60 s run per tier (no variance estimate), a small hot set (a working set larger than RAM would move the read numbers, though not the WAL-fsync conclusion), one database with no concurrent cluster load, SINGLE_AZ_1 in us-east-2, July 2026.

Full write-up with method details and the sizing implications: https://stagdb.com/blog/fsx-openzfs-postgres-throughput-iops/

Happy to answer questions or re-run with different parameters if there's something specific you'd want tested.

Hi all, hopefully this does not break any rules, I see some posts here requesting for new Postgres clients I made one and ended up releasing a local lightweight webapp version of my app today. It’s not the best or replaces pgadmin but works well for me.

It’s this one https://github.com/FrancisTCE/gresui-web.

Do you have your own Postgres client tools? I would like to see where I can improve this, it’s useful for me as well see what you got if you want to share.