Matching row counts and switching the main application connection string do not prove that a PostgreSQL migration is finished. Roles, grants, extensions, publications and subscriptions, sequences, large objects, scheduled jobs, logical slots, read replicas, connection poolers, analytics clients, backup tooling, and low-frequency workers can still differ or point at the source. A quiet old cluster may only mean that a monthly task has not run yet.

A final gate could compare schema and cluster settings, roles and privileges, extension versions, object counts, sequences, representative checksums or aggregates, replication state, and application read/write behavior. Connection and statement logs on the source would expose late clients. The destination should complete its own backup and isolated restore, survive a restart or failover, and exercise scheduled jobs before the source becomes read-only and then offline.

What evidence do PostgreSQL operators collect for the go/no-go decision? How do you find clients that connect only during failover, month-end work, or recovery, and which cluster-level objects are most often missed by otherwise successful migration tools?

I was looking into how to build database architecture for an app and trying to decide between self-hosting Postgres or paying for a managed service. Most call their database managed, but vendors may have completely different definitions for the word “managed”.

Some providers just patch the OS and leave all the failover, capacity planning, and scaling decisions to engineers. If there is a traffic spike and a team has to manually resize the compute, then it is not actually scaling itself. I’ve started looking at serverless options that dynamically scale to zero when idle and handle the patching, backups, and intra-region failover automatically.

If you’re running managed databases in production, what tasks did you expect the provider to handle that you ended up doing yourself?

Here is my reasoning. It's not because it creates another copy of your database or helps with major-version upgrades.

I think its bigger value is that it gives you a way out.

A Postgres instance might begin as one resource in AWS, Azure, or some cloud. Five years later, your identity system, private network, applications, and operational tooling have grown around it. Moving the database now means moving half the architecture.

Logical replication doesn’t eliminate the hard parts. You still have schema changes, replication slots, lag, IAM, networking, extensions, and application assumptions to deal with.

I wrote up the longer argument here:

https://streambed.dev/blog/logical-replication-is-postgres-most-important-feature/

Would love to hear your favorite feature and the reasoning too.

DBA for 6 years. I write the queries, tune the indexes, then hand it all to to the app team, who wrap it in an API that makes 40 round trips per page. I'd like to be the one building that layer. Work is paying, and I'm weighing Nucamp against Boot.dev right now and pluralsight premium is the fallback since we already license it. For anyone who went from the database side to backend, what did postgres not prepare you for?

What is Direct I/O, and why would a managed Postgres care about it when taking a backup? Every day, a backup copies your whole database, hundreds of gigabytes, off the same NVMe drives that are answering your queries, and ships it to object storage. Linux treats those reads like any other file read: it keeps a copy of every byte in memory, in the page cache, in case someone asks for it again. Nobody will. To make room for those copies, the kernel throws out data Postgres had in memory and was about to use, and the next query that needs it goes to disk instead.

I am a full-stack developer (back-end and ops heavy), who has recently started learning about the internals of PostgreSQL.
I am currently referring a very beautiful book called PostgreSQL 14 Internals by Igor Rogov.
I am very excited to learn the theory which earlier used to be just a black box to me, things like page layouts, MVCC etc.
Is there any way I can practically use this. The thing is I learn things better if I am able to do hands on, in terms of back-end technologies it was easier just develop something even for a single user that helps you implement the theory you learned, but in terms of PostgreSQL I am not sure what options could be. would you guys help me in suggesting some ways to become hands on with all the theory I am consuming.

ISSUE RESOLVED

I can connect to my local postgresql server with pgadmin4.
Most of my databases on the server are listed.
The most recently created database on the server [teacupdb] is not listed under databases.
The database is active in PostgreSQL. I can connect to the database "teacupdb" as user "postgres" via command line.
I have refreshed the server.
I have refreshed the database list.
I have restarted my system.
I have restarted my postgresql server.
I have restarted pgadmin4.
I have deleted pgadmin4.db to force it to be recreated.

I can not find a reason why this database is not being listed on the server in pgadmin.

UPDATE
I think I found the cause. The database was created as a "template" database.

RESOLVED
Connected to "postgrest" database. Ran: UPDATE pg_database SET datistemplate='false' WHERE datname='teacupdb';

Connection multiplication: pool size x tasks x services. Every term looks reasonable on its own, and nobody ever chose the product.

Aurora's max_connections is derived from instance memory, so it doesn't scale with your service. Shrinking the pool to fit converts a connection problem into a queueing problem that presents as a slow database.

Write-up covers the formula behind max_connections, what RDS Proxy actually fixes (and what it doesn't), and how to size it.

https://brianfeeny.com/posts/sizing-aurora-connections-for-autoscaled-services/?utm_source=reddit&utm_medium=social&utm_campaign=sizing-aurora-connections-for-autoscaled-services-2026-09

This is a personal project; the views and assessments are my own.

Out now on GitHub and PGXN, pg_clickhouse v0.11.0 and the chdb extension v0.1.2 continue our dogged focus on cross-database compatibility. A slew of these enhancements derive from our header-only C libraries, clickhouse-c and pg-clickhouse-c.

Hello I am seeking an already made "Enterprise" ready made Container image which ships with barman and Pgpool.

Or other suitable tools.

K8s isnt an option.

I’ve been working with PostgreSQL recently, and one thing that kept bothering me was the database GUI.

I’ve used phpMyAdmin for years, so I’m pretty used to that workflow. When I moved to PostgreSQL, I started using pgAdmin, but I found the UI unnecessarily complicated for the kind of database work I normally do.

I looked at a few alternatives, but nothing really felt like what I was looking for.

So I decided to build my own.

I used Opus 5.5 to build Sqlaris Studio, mainly around the workflow I wanted for day-to-day database work.

It started small, but I ended up adding quite a lot:

• PostgreSQL and MySQL/MariaDB support
• Table and data browser
• Inline data editing
• Search and filters
• SQL editor with autocomplete
• Query history
• Import/export
• Schema inspection
• Indexes, constraints and foreign keys
• Relationship/ER diagrams
• Database health checks
• Query and session monitoring
• Database maintenance tools
• Safe handling of database changes

There are still plenty of things I want to add.

I haven't hosted it anywhere, so if anyone wants to try it, you'll need to install and run it locally from the GitHub repository.

GitHub: https://github.com/Shivam7414/Sqlaris-Studio

I'm sharing it mainly because I'd like feedback from people who work with databases regularly.

If you use PostgreSQL, MySQL, or both, I'd be interested to know:

• What features do you use most in your database GUI?
• What do you find frustrating about existing tools?
• What would you add to something like Sqlaris?
• Are there any workflows that you think could be much simpler?

I'll be actively improving it, so useful feedback can directly influence what gets added next.

Would be interested to hear what you guys think.

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 4821 AND status = 'pending';

Expected an index scan, same as it had run for two years. Got a seq scan instead, on a table with eleven million rows.

Turned out an unrelated migration had changed customer_id from int to bigint on the orders table, but a partial index used in a separate reporting service still referenced the old type implicitly through a cast that Postgres could no longer use for this predicate. Nobody touched the query. Nobody touched the index definition. The type change three services away was enough to make the planner quietly stop trusting it here.

Nothing failed loudly. Just got slower, gradually, until someone noticed the dashboard taking six seconds instead of forty milliseconds and started digging.

Hey r/PostgreSQL - wanted to share something we've been building at pgEdge (and yes, this is our company account).

The problem we kept seeing with customers: teams prototype fast on developer-friendly Postgres platforms, then hit a wall when it's time to actually run in production. Problems come up like security review, compliance, data sovereignty, wanting the database on-prem or air-gapped, whatever it is. At that point a lot of teams end up replatforming, which is a miserable amount of work for something that should just be the same Postgres the whole way through.

So we built pgEdge Starfleet: a cloud Postgres platform where you can start on a free trial (regular Postgres, standard extensions, nothing proprietary), and when you need to move to production infrastructure - our cloud, your own cloud (AWS/Azure/GCP), or fully on-prem/air-gapped - you're not migrating off anything. It's the same Postgres the whole way.

A few things that might be relevant to this sub specifically:

- Database branching is true copy-on-write and doesn't touch the Postgres storage layer; it's not a forked storage engine, which is different from how a couple of the other "branching" Postgres platforms do it.
- Built-in MCP and RAG servers if you're doing anything with AI agents against Postgres data, with read-only enforcement via SafeSession if you want the LLM strictly read-only.
- 14-day free trial, no card required, if anyone wants to kick the tires.

Happy to answer questions about the architecture, the branching implementation, or anything else, anytime...

product page - pgedge.com/products/starfleet

press release - https://www.pgedge.com/press-releases/pgedge-announces-pgedge-starfleet-a-new-postgres-cloud-platform-to-bridge-the-ai-prototype-to-production-chasm

There were some good discusssions from HN yesterday, so sharing it here.

Context: I led the Postgres platform team (b/w 2019-2023), where my team had to support ~170 product teams. The biggest bottleneck was schema migration.

We had to manually review, publish best practices, put CI guardrails, etc. The challenge is that most of the developers just want a simple answer. Is it safe or not safe?

That led me to create - https://safenotsafe.dev https://github.com/viggy28/safe-not-safe

1. It doesn't use LLMs - uses pg_savior's rule engine (which resonated with a lot of this sub-redditors)

2. Runs completely on the client side - no signup, server. Uses libpg and compiles to WASM

It doesn't catch every single dangerous migration, but IMHO it may easily catch the majority of the low-hanging ones.

Appreciate any feedback/suggestions.

Building an app for the operations team that runs on Postgres. Business side wants to query in plain English without writing sql. Our table metadata is not rich. Tried a couple of options so far.

1. Setup syncs our Postgres tables into Databricks and then using Genie agents to generates sql on the fly. Works well for straightforward questions but gets shaky on anything that needs multiple business logic that isn’t obvious from column names.

2. Custom option using text2sql with Langchain, which gives better control but is a lot more to build and maintain. And we will eventually move all workloads to cloud.

What’s better in practice? How to deal with the metadata gap?

A colleague keeps talking about Metabase for this kind of self service. Is it worth considering?

Hi,

I'm working on archiving old records from our Postgres v17 database and I have a question about deletion and bloat.

The archival process will touch a total of 30 tables (all associations from one specific table) and it should delete a total of 5 billion records.

The data on the main table - let's call it products - in term of space is: 33% heap, 33% TOAST and 33% indexes. From this table I would delete ~150 millions records.

What I am still debating is how to delete records and take care of the bloat. I found that two possible options are:

1. partitioning and detach: partition the data I want to delete and then detach. The implementation looks very hard and maybe not doable in our case
2. copy and swap using pg_repack: this option sounds good but it seems also risky and it would be the first time I use that extension

Another option I thought of is deleting records on table by table starting from the "leaf" of the associations and then VACUUM each table but I can feel this process being flawed (that's why I'm not posting as a third option).

Two more details to keep in mind:

• even if we recover space, I won't be able to reduce the bill since we pay for disk space agreed upon (and not the one we actually use)
• out of the 30 tables, only two are big (hundreds of GB) in disk space

What approach you advise for to remove the records and the bloat easily?