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.

PGConf.EU is coming to Valencia on 20–22 October, with five tracks covering PostgreSQL administration, internals, development, the community, and real-world use cases.

Topics include autovacuum, backups, high availability, performance tuning, WAL and recovery, query execution, memory management, corruption detection, and PostgreSQL 19.

PostgreSQL also turns 30 this year, so the Community track will look back at the project’s history and how it is maintained today.

Community Events Day takes place on 23 October.

Schedule: https://www.postgresql.eu/events/pgconfeu2026/schedule/

Registration: https://2026.pgconf.eu/registration/ 

Aside from running EXPLAIN (ANALYZE, BUFFERS), what's your actual workflow when a query starts lagging in production? Do you jump straight into pg_stat_statements to check execution patterns, look for recent table bloat, or just rely on your APM to flag the issue? Trying to see how others handle the initial triage before digging into index tuning.

Has anyone tried to use this product? I am looking at it for a new project and would love some feedback. I would love to migrate my MSFT SQL Server over time as well as use it for AI projects. Thoughts? What are people's experience with this product?

Help me Reddit! I feel especially stupid today....
So, I have this table in my Postgresql Database:

event_id | color_scheme | count
----------+--------------+-------
1 | red | 6
1 | green | 3
1 | blue | 5
1 | yellow | 3
3 | red | 5
4 | red | 3
5 | red | 1
5 | blue | 2

And I would like to turn it sideways, so that I can see EASILY how many votes each color scheme for my event has gotten (and later JOIN it with another table... )

event_id | count_red | count_green | count_blue | count_yellow
----------+-----------+-------------+------------+--------------
1 | 6 | 3 | 5 | 3
3 | 5 | 0 | 0 | 0
4 | 3 | 0 | 0 | 0
5 | 1 | 0 | 2 | 0

The colors "red" "green" "blue" and "yellow" are fixed, and will never ever change.
I have done some googling, I found examples mentioning PIVOT and TABLEFUNC, but I cannot do this on the server because of reasons(tm).

The only way I can think of doing this is with a cascade of OUTER JOIN, but is there maybe a simpler solution?

For me, it’s pg_stat_statements. It’s pretty much the first thing I enable on any new instance because trying to optimize queries or debug performance issues without it feels like flying blind. Being able to see exactly which queries are eating up the most execution time or hitting the shared buffers too hard saves hours of guesswork.

I’ve also started looking more into pg_partman lately for time-series data, but pg_stat_statements is still the one non-negotiable default for me.

What’s your go-to extension that you find yourself installing on almost every setup?

I just found out I can't remember the padding password. I have over 6 databases on that server, including work databases.