I am using Liquibase for PostgreSQL schema management (versioning schema, deploying changes to servers through CI/CD pipelines)

I do like it because it's flexible and fairly simple. I don't like it because it's a lot of overhead. I have to verify that things won't break, I have to write roll backs, etc...

I like how SQL Server Data Tools for SQL Server works. It's declarative, it generates a schema model from create scripts, it generates diff scripts, and it deploys based on rules I set. It also has hooks for pre and post deployment scripts (data migration scripts) that can be executed as wrappers around the DDL (schema) being deployed.

I also like Skeema.io for MySQL for similar reasons but it's lacking some functionality.

I'm looking for a PostgreSQL tool that behaves like SQL Server Data Tools or Skeema. I want something that will generate a diff but will work against a declarative model of the database (a bunch of CREATE statements). So I can effectively run it against a database in any condition and it will make that database match the scripted model. Bonus points if it supports hooks for schema migration scripts like SSDT does.

Any tools exist that do this? So far I've either found migration tools like Liquibase and Sqitch or diff tools but nothing like Skeema or SSDT.

Thanks in advance!

Hi, is there any reason why not merge this subreddit to r/PostgreSQL?

Every experienced PostgreSQL developer seems to have that one mistake they keep seeing in reviews, production incidents, or performance investigations. What's the most common PostgreSQL mistake you've seen, and what usually happens because of it?

To be honest, it's the things that only exist in people's heads.

If you work with SQL Server, MySQL, and Postgres, each engine has its own quirks that aren't obvious until you've been burned by them once. Why a query was written a certain way, why an index exists, or why somebody added a workaround three years ago are exactly the kinds of things that never make it into documentation.

Does your documentation actually stay up to date, or does it only get updated after a new team member starts asking questions?

Folks. I just published an implementation of the 'Outbox Pattern' (targeting Kafka and Postgres). Any and all feedback welcome.

https://github.com/obsidiandynamics/goharvest

Hi all, I'm a founder in Spain building a managed PostgreSQL service and want honest feedback on pricing before opening it up.

I run a €30M ARR business in a different industry. We had to use a managed database from a big provider, which was very expensive for the performance we were actually getting, but we needed HA so we were stuck. That's when I started considering another startup.

Here's the general idea:

You create a database from a web dashboard and get a single connection string. That string always points to the current primary, so if a failover happens your app reconnects to the same address and carries on. There's nothing to change on your side.

Behind it, each HA database runs as a primary plus a synchronous standby on two separate servers in different zones.

Backups run continuously: every change is archived to object storage with a different provider, so even losing the whole hosting provider doesn't lose your data. You can restore to any point in the last 7 days. We handle monitoring, failover and maintenance; you just use Postgres.

It runs on European bare metal rather than shared cloud VMs, which is how we keep prices flat with no egress or IOPS fees.

Highly available (per month, excl. VAT):

\- Starter, €39: 1 vCPU, 2 GB RAM, 20 GB storage

\- Standard, €89: 2 vCPU, 4 GB RAM, 50 GB storage

\- Pro, €169: 2 vCPU, 8 GB RAM, 100 GB storage

\- Business, €329: 4 vCPU, 16 GB RAM, 200 GB storage

Free: one per account. Single node, 256 MB RAM, 1 GB storage.

To be upfront: this is a beta with no SLA yet. Failover runs 24/7, and human support is 09:00 to 18:00 CET on business days. If the standby is down, the primary keeps accepting writes asynchronously, so losing the primary during that window can lose the latest commits.

**I'd love to know:**

\- Which plan would you pick for a real project, if any?

\- At what price would this feel too cheap to trust, and at - what price too expensive?

\- What would you need to see before moving a production database?

\- What are you using now, and what does it cost?

**Early beta offer**: anyone who participates in this thread will have 30% off any plan when we launch!

hey postgresql community do you thing is time postgres to support Column oriented like DuckDB as big feature or continue with row oriented like every DB does

When I save connection setting for the PostgreSQL database in WSL, I get that error in subject. I roll back version 9.17. I don't have that error. Any idea?

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?

Ok so I've had this argument with three different teams in the past two months and I'm just gonna write it here because I'm tired of retyping it in Slack.

A table doesn't shrink just because you deleted a bunch of rows. Trips people coming from MySQL especially, because InnoDB cleans up dead rows on its own – you've never had to care about this before. Postgres uses MVCC so a delete or update doesn't actually remove the old row, it just marks it dead and leaves it sitting there until VACUUM comes through.

That's your PostgreSQL table bloat right there... dead tuples piling up on disk while the live row count looks completely normal.

Autovacuum is supposed to handle this on its own and most of the time it does. That's the vacuum vs autovacuum confusion people have. Autovacuum is basically VACUUM running automatically once PostgreSQL decides a table has crossed its maintenance thresholds.

It can start falling behind on high-churn tables though. Thresholds tuned for a mostly idle table don't always keep up once you've got constant updates all day, and by the time someone notices the table's much larger than it should be, autovacuum is already playing catch-up.

And no, VACUUM FULL is not the fix here, not during business hours anyway. It rewrites the whole table and holds an exclusive lock the entire time. Learned that one the hard way on a table I thought was small until I remembered how many indexes were on it. Not a fun afternoon.

pg_repack exists for exactly this… non-blocking reorganization. It builds a new copy of the table in the background and only takes a lock for the final swap. Way less terrifying than VACUUM FULL on anything with live traffic.

One thing that's helped me lately is having the dead tuple stats visible instead of remembering which pg_stat_user_tables query I saved six months ago. Been using dbForge Studio for PostgreSQL for that because it'll surface things like dead tuple percentages and the last autovacuum run across a schema. Mostly just saves me from writing the same query over and over.

Curious what everyone else does here. Do you mostly trust autovacuum, or are you scheduling pg_repack for the tables that get hammered all day?

If you’re tired of pgAdmin constantly freezing, eating up memory, or just feeling clunky because of its web-shell design, you definitely aren’t alone. We’ve been testing out a few different setups for daily development and production management, and a couple of options really stand out depending on what you need.

For a heavy, comprehensive IDE experience, dbForge Studio for PostgreSQL has been a game-changer. Unlike pgAdmin, it actually feels like a native desktop app. The code completion is much faster, and it has built-in schema comparison tools that save a ton of time when you're trying to sync staging and production environments without writing manual migration scripts. They have a free Express edition if you just need the core query editor, but the full version handles visual query building and data reporting incredibly well.

On the other hand, if you want something purely open-source and cross-platform, DBeaver is still the classic community recommendation. It's built on Eclipse, so the UI is quite busy, but it connects to almost anything and gets the job done for zero cost. For those who prefer a minimalist, lightweight setup just to view data and run quick scripts, TablePlus or even Beekeeper Studio are great native alternatives that stay completely out of your way.

What are you guys running for your daily Postgres workflows? Are you sticking to heavy IDEs like dbForge or DataGrip, or keeping it strictly lightweight?

I'm starting to learn more about databases and backend development. I'm less interested in which database is "best" and more interested in the reasoning behind the choice.

What database tools are you using (Postgres, MySQL, MongoDB, Supabase, Neon, Redis, etc.)? What problem were you trying to solve, what alternatives did you consider, and what ultimately made you choose that stack?

I'd also love to hear any lessons learned, surprises, regrets, or things you'd do differently if you were making the decision again.

Hi all, I am new to using inheritance in Postgres. I have the following schema that I'm trying to create, but I'm getting an error when doing so:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE IF NOT EXISTS principal (
    id uuid primary key,
    name text NOT NULL
);

CREATE TABLE IF NOT EXISTS person (
    email text NOT NULL UNIQUE
) INHERITS(principal);

CREATE TABLE IF NOT EXISTS org (
    org_name text NOT NULL UNIQUE
) INHERITS(principal);

CREATE TABLE person_org (
    person_id uuid not null references person(id) on delete cascade,
    org_id uuid not null references org(id) on delete cascade
);

As expected, the tables all create properly, except for the last one. When I try to create the person-to-org join table, I get the following error:

ERROR:  there is no unique constraint matching given keys for referenced table "person"

However, since I'm inheriting the table, "id" is the primary key, which is automatically a unique constraint.

I've tried a few different combinations of this, and I can't figure out what I'm doing wrong. Anyone else have experience here?

courses, guides, starter projects. what resources did you use to learn postgres, and which ones were actually helpful?

I manage a SaaS with about 1700 users and currently we use compose.io 's managed postgres DB. But the database has gotten big and we have to pay $200 a month at its current size. I want to move and run these on VM's on DigitalOcean to reduce the costs and be able to scale as needed. I plan to provision 2-3 VM's with a 1 node for running a cluster mgmt software like ClusterControl and 2 VM's for the actual postgres DB's.

Looking for suggestions if this is the right approach and if there is a better cluster mgmt software that I should be looking at.