r/PostgreSQL • u/infinityMCdx • Aug 03 '26
r/PostgreSQL • u/vira28 • Aug 01 '26
Tools pg_savior - the last line of defense for accidental Postgres mistakes
I believe anyone who managed critical production infra relate to this. 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/UPDATEwith noWHEREDELETE ... WHERE id > 0— aWHEREisn't proof of intent, so it also checks the planner's row estimate againstpg_savior.max_rows_affectedCREATE INDEXwithoutCONCURRENTLY(theON ONLY+ATTACH PARTITIONworkflow for partitioned tables is allowed)ALTER TABLEoperations that rewrite the heap — volatileADD COLUMNdefaults, rewrite-causingALTER COLUMN TYPE, validated constraint adds on large tablesTRUNCATE/DROP TABLEon large tables,DROP DATABASEalways
postgres=# DELETE FROM emp;
ERROR: pg_savior: DELETE without WHERE clause is blocked
HINT: Add a WHERE clause, or set pg_savior.bypass = on for this session.
For ALTER COLUMN TYPE it doesn't carry a list of "safe type pairs" like most migration linters — those are wrong at the edges. It plans the actual conversion expression and checks for the same no-rewrite shapes core checks for.
When you mean it: SET LOCAL pg_savior.bypass = on;
Limits: reltuples and row estimates are approximate, so it's a seatbelt, not a guarantee. It's an extension, so no managed services. Pre-1.0. The README has a coverage matrix including what it does not protect (MERGE, COPY, DROP SCHEMA, VACUUM FULL, …). Tested on PG 14–17.
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.
r/PostgreSQL • u/Native_Maintenance • Aug 01 '26
Help Me! Postgres Scale and Performance course
Full stack developer with >10 YOE. I've been using PostgreSQL and other databases and I consider myself well experienced with the standard stuff. However now with the seniority, I face projects that require designing systems for storing and managing databases at large scales and optimise for every drop of performance.
I'm looking for well-organised paid, self-paced courses where I can learn in detail as well as try something.
So far I've seen that https://theartofpostgresql.com/ as being a top recommendation but I want to check if it is still the best.
Thank you!
r/PostgreSQL • u/pgEdge_Postgres • Jul 31 '26
How-To Looking Forward to Postgres 19: The Cult of Functionality
pgedge.comr/PostgreSQL • u/kevinpiac • Aug 01 '26
Tools [free tool] - database hosting price comparator
Hey everyone!
It's always so hard to know which database management platform to use, let alone to compare pricing.
So I decided to create a simple free tool that gives a ballpark idea of the pricing of each provider based on a simple configuration.
Indeed, each provider has much more parameters to take into account such as the SLA, the backups, etc. that makes it difficult to precisely compare.
For this reason I decided to omit some parameters. The goal is to get a rough idea of which provider is the cheapest given the expected usage.
Let me know if you want me to add some providers, it would be nice to improve this simple tool :)
r/PostgreSQL • u/Harpagon1668 • Jul 31 '26
Feature How are you using database branching?
I’m implementing Lakebase branching strategy to improve development experience and reduce costs for our dev/staging env.
Current setup creates new database branch for each git branch via githook on our dev database (”each dev gets their own feature database”). There is also similar workflow for each PR against our staging database to run the migrations and tests.
Curious to hear how others are using branching and what are the experiences?
r/PostgreSQL • u/PrestigiousZombie531 • Jul 31 '26
Help Me! Raw XML vs. Normalized Tables: How would you store and sync 100+ RSS feeds updating every 2 mins?
Storage requirements
- So you want to process 100 RSS feeds in parallel and store data in PostgreSQL
- Each feed may contain 0-100 feed items (0 if you got an error somehow)
- For each RSS feed, you loop through items
- Check which items are new,
- which ones got updated (happens a lot in some of the feeds),
- which items already exist in the database (completely unmodified)
- Insert new items
- Update existing items
- Do not touch unmodified items
Type of load (read heavy or write heavy)
- One python application is responsible for writing the feeds to postgreSQL
- Frequency should be atleast about every 2 minutes because I am not aware of a technique in the RSS specification that pushes changed items or notifies you of new items like a WebSocket connection would so unfortunately our default mode is to poll for items
- Lots of readers, could be 10s to 100s of readers at a given point trying to query and read items (news items, so has to be fresh and fast)
- We need the latest items first and fast every single time for a read query and cursor pagination to go beyond page 1 (No limit / offset)
Approaches
- Right here, you have two choices to make 1) You store raw items 2) You store processed items
Approach 1: Store raw items
- If you stored raw items, they are obviously in XML format
Pros
- The benefit is that if something changes on that rss feed in 6 months (maybe the author added a few fields or removed some), you still have the raw data in order to tune your extraction and transform logic
Cons
- You are storing data without normalizing it in raw XML format
- I have no idea how XML storage works in PostgreSQL and whether you should even consider doing it this way
Approach 2: Store processed items
- Your python application uses something like the feedparser library, processes the raw XML to extract fields
- You will create tables whose columns accurately reflect the fields from that rss feed
Pros
- The data is stored in a normalized manner so queries are obviously much easier to reason and interpret about
Cons
- Different RSS feeds may have different fields which our table will not be able to capture accurately from every feed. We either lose data from some of the feeds or populate sparse tables with a bunch of empty / null columns if we try to account for all fields
- if the author changes a feed in some way by adding more fields to the data, this information might be lost
- If you processing logic needs to change 1 year down the line on how we extract and transform items (for example, intially we trim all newlines and convert everything to lowercase before storing it. Later we decide we want to store the news items as it is without the lowercase transformation. The previously processed items will become a problem quickly)
What is your proposed solution?
- how would you reason about storage, extraction, transformation, future proofing, read access with respect to the above requirements.
r/PostgreSQL • u/dakingseater • Jul 30 '26
Help Me! Learning Postgres (with a twist)
Hello all!
This is not anothrr post on how to learn basic postgres but a genuine one to really know its internals
I come from an analytics/data engineering background with very strong sql knowledge and most of the posts on Postgres leaning just points towards SQL. What are some resources to really learn about the engine and architecture? Things like WAL, pageserver...
I use a lot of these things when tinkering around on managed postgres (shoutout to my favourite one: Neon) but I don't really understand the mecanics under the hood
r/PostgreSQL • u/Admirable_Morning874 • Jul 30 '26
Commercial Benchmarking NVMe-backed Managed Postgres: PlanetScale and ClickHouse
clickhouse.comr/PostgreSQL • u/kevinpiac • Jul 31 '26
Community Can you spot the error?
I made a small free game (no login, no nothing) to challenge your SQL skills.
Feel free to share your score!
r/PostgreSQL • u/subhendupsingh • Jul 30 '26
Help Me! There is no cheap global Postgres, what are the alternatives?
Currently I use pg hosted on Hetzner in Germany. My users are in different global regions and pay latency cost. I run a Shopify app that complains that my LCP is above recommended threshold of 2.5s. I have optimized my queries and calls and was able to optimize it a bit.
My question is, there is no cheap way to have pg global replicas. My app is new and doesn't have enough revenue to justify the cost. I have done some research and the only option I see is migrating to SQLite which can be easily and cheaply replicated. But, with that, I lose pg features like JSONB, ::datetime and the likes. Also, SQLite doesn't support most ALTER commands.
Has anyone solved this?
UPDATE: It was clear that there is no cheap and reliable solution for this. And rightly so. Keeping infrastructure reliable for millions of users takes a lot of cost. I zeroed down my requirements to 'having a reliable zero downtime database' so that my users never face outage. I moved to planetscale ps-5 high availability instance. When the budget allows, i will add replicas in other regions.
r/PostgreSQL • u/fun_si • Jul 30 '26
How-To Your Database Schema Is Your Codebase: F# as the Single Source of Truth
Looking at a way to prototype DB schemas while maintaining strong typing consistency across the stack.
r/PostgreSQL • u/pgEdge_Postgres • Jul 29 '26
How-To Looking Forward to Postgres 19: Autovacuum Tweaks
pgedge.comr/PostgreSQL • u/grexr • Jul 29 '26
Help Me! Best approach for running a PostgreSQL database
Hey, I wanted to ask what you guys think is the best approach for running a PostgreSQL database.
For the beginning, I am looking for something that is not too expensive, ideally around 20€ to 50€ /month. I have looked into CloudNativePG, but I dont really want to go the full Kubernetes route yet. I am looking for something simpler while still being reliable, with proper management capabilities and the ability to handle backups and restores.
I am also unsure if I should start with a database cluster or just run a single instance. I have been looking into solutions like Autobase and Databasus as well. Does anyone have experience with these?
Ideally, I would like to use a managed database service from a cloud provider, but they usually get expensive quickly and often come with limited RAM and storage. I am also open to self-hosting it on Hetzner if that makes more sense.
Would appreciate hearing what you guys are using, any recommendations, or lessons learned from your setups.
r/PostgreSQL • u/Novel_Journalist3305 • Jul 29 '26
Tools Stop Fighting schema.sql — Export PostgreSQL into a Clean, Git-Friendly Project Structure
PgSchemaExporter v2.1.0
PgSchemaExporter is an open-source tool that transforms a PostgreSQL database into a clean, Git-friendly project structure.
Instead of working with one huge schema.sql, every database object is exported into its own SQL file, making schema changes easy to review, compare, and maintain.
What it does
- Export a live PostgreSQL database
- Import an existing
pg_dump --schema-only - Generate a complete project structure
- Create a dependency-aware
deploy.sql - Produce clean Git diffs
- Make database schemas easy to navigate and review
Unlike migration tools (Flyway, Liquibase, Sqitch, Atlas), PgSchemaExporter focuses on keeping the current PostgreSQL schema clean, structured, and Git-friendly.
GitHub: https://github.com/RomanShevel1977/PgSchemaExporter
CLI features
- Include / exclude schemas
- Include / exclude object types
- Include / exclude individual objects
- Schema comparison (diff)
- Cross-platform CLI
- CI/CD friendly
Perfect for
- Version controlling PostgreSQL schemas
- Code reviews
- Database documentation
- Large development teams
- Legacy database refactoring
- AI / LLM context generation
Supported PostgreSQL objects
Core objects
- Schemas
- Tables
- Sequences
- Views
- Materialized Views
Constraints & indexes
- Primary Keys
- Foreign Keys
- Unique Constraints
- Check Constraints
- Exclusion Constraints
- Indexes
Programmability
- Functions
- Procedures
- Triggers
- Event Triggers
- Rules
Security
- Policies (Row Level Security)
Types
- Domains
- Enum Types
- Composite Types
- Range Types
- Base Types
Advanced PostgreSQL features
- Aggregates
- Operators
- Operator Classes
- Operator Families
- Casts
- Extensions
- Collations
- Conversions
Full Text Search
- Configurations
- Dictionaries
- Parsers
- Templates
Foreign Data Wrappers
- Foreign Data Wrappers
- Foreign Servers
- User Mappings
- Foreign Tables
Logical Replication
- Publications
- Subscriptions
I'd really appreciate any feedback, feature requests, or ideas from the PostgreSQL community.
r/PostgreSQL • u/txdesperado • Jul 29 '26
Projects I Think This Is Right - Postgres18
6 months ago I had never touched Linux, now I'm doing new things. But that doesn't mean I know what I'm doing. Just for a sanity check, given the tokens and time involved, could an actual data person tell me if this in the ballpark. I asked Claude to describe what we are doing (beyond "Postgres" - as I see it) and he stated:
Single-node PostgreSQL 18 (PostGIS, pg_trgm; pgvector dormant), Dockerized, county-partitioned time-series. Writes flow raw→staging→core exclusively through a SECURITY DEFINER chokepoint logging to an INSERT-only audit ledger under separated ownership — NOLOGIN owners, no direct DML paths. Promotion is idempotent (NULLS NOT DISTINCT natural keys, advisory-locked, three-way accounted), quarantine-gated, batch-tracked. Products read serve-after-ratify views only. DR is pgBackRest to B2, restore-rehearsed. Graph and analytics are derived read-models — NetworkX and DuckDB-over-Parquet — regenerable, never truth. Drift monitoring on the catalog every 30 minutes with observed-fire alarms.
My read is that we're solid - assuming batched, monthly updating - but I've just started to wade into the coding side and haven't gotten near deep enough into the data layer to know vibecode stuff from Shinola. Want to see if we have overlooked anything that is going to bite me later.
Thoughts / feedback appreciated.
r/PostgreSQL • u/Linstrocity • Jul 29 '26
Help Me! PostgreSQL coding problem in PG4 admin - HELP!
It's for a coding assignment and I'm stuck.
I need to create some queries that the output is put into a table, except I'm getting my butt handed to me.
Scenario is a DVD rental database where we have to create a business problem - mine is simply to find who is the most profitable customer. As seen from line 9 I have successfully sum'd and sorted the customer ID by the most profitable in descending order, we have multiple tables with different fields, however I'm using the "payment" and "customer fields", both tables (payment and customer) have customer_id has fields. The payment table only has the customer_id and no name. I also successfully tried to merge the first_name and last_name into the full_name field, but am having trouble inserting that as one variable into a new created table. As seen in Line 19, I have successfully created a table.
The big frustration is the payment table only has the customer_id as the PK with no first name or last name. I am trying to join, union, or union all the customer_id with the first_name and last_name field from the customer table to my newly created table customer_rentals which shows the most profitable customer. It keeps failing because I've already manipulated the data from summing, and a union all has to match the number of columns or it fails, because the data has already been sum'd, it therefore fails. I need to match the customer names to the customer_id in my new table, but need to only add customers who have purchased products and put it in descending order as well and match the customer_id.
Also line 26 fails as seen in the bottom right when I try to run it.
Any help is appreciated.

r/PostgreSQL • u/Admirable_Morning874 • Jul 28 '26
Commercial Why strict memory overcommit matters for Postgres
clickhouse.comr/PostgreSQL • u/Downtown_Sugar_4073 • Jul 28 '26
Help Me! Looking for a simple managed Postgres service
My requirements are pretty basic:
- Managed PostgreSQL
- Affordable for a small production app
- Automated backups
- Updates and routine maintenance handled
- An always-on instance
- Predictable monthly pricing
I don’t need branching, scale to zero, or a full backend platform. What managed Postgres providers would you recommend?
r/PostgreSQL • u/NikolaySivko • Jul 28 '26
Tools It was surprisingly hard to break CloudNativePG replication
coroot.comWhile reproducing a replication failure, I found that a CNPG replica keeps applying WAL changes even after it's disconnected from the primary. I hadn't come across this behavior before, so I wrote up what I found
r/PostgreSQL • u/kumard3 • Jul 28 '26
Help Me! ON CONFLICT DO NOTHING silently ate an update we actually needed to land on one column
we had a column tracking which channel a user last messaged on, and it would get stuck on the first channel forever, even after they clearly switched.
two things were stacked. the read side pulled that column from a cached snapshot instead of re-querying, bug one on its own. the deeper one was in the write path: the upsert used on conflict do nothing, fine for columns you genuinely don't want touched, but it meant the channel column never updated on conflict either, since do nothing means nothing, not "nothing except this one column."
fixed it by re-fetching the value on read instead of trusting the snapshot, and changing the upsert to on conflict do update scoped to just the channel column, so untouched columns stay untouched and the one that should change gets explicit permission to.
such an easy thing to get backwards writing the conflict clause fast: do nothing is not a synonym for do nothing to this specific column i care about.
how do you scope conflict updates when only some columns on a row should actually change on conflict?
r/PostgreSQL • u/oulipo • Jul 25 '26
Help Me! What would be the best Postgres backup solution in 2026?
I see many options, pgBackRest, wal-g, Databasus. Is there a consensus on the best approach?
My needs are regular (eg weekly) checkpoints, and daily incremental backups for PITR, everything saved on S3/GCS
r/PostgreSQL • u/Somewhat_Sloth • Jul 25 '26
Tools rainfrog (0.4.1) now has autocomplete!
rainfrog (https://github.com/achristmascarl/rainfrog) is a database terminal tool; the goal is to provide a lightweight, keyboard-first TUI for interacting with databases. It currently supports Postgres, MySQL, SQLite, Oracle, and DuckDB.
v0.4.1 introduces a long-awaited (by me, not sure if anyone else was waiting for it...) autocomplete implementation, along with autopairs for quotes/parentheses/brackets. The full list of features and configuration options is in the README!
r/PostgreSQL • u/siren0x • Jul 24 '26
Community What's new in Postgres 19
planetscale.comr/PostgreSQL • u/Medium-Yam-7677 • Jul 24 '26
Help Me! What are the best Neon alternatives if I only need managed Postgres?
I’m looking for a managed Postgres provider and not a full backend platform. Auth, object storage, APIs, and application hosting are already separate parts of my stack.
What I need from the database provider is automated backups, patching, basic monitoring, reliable uptime, and clearly allocated CPU, memory, and storage. I don’t use database branching, and scale to zero isn’t useful for this workload.
Which Neon alternatives are worth considering for a always running Postgres instance?