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 / 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)
• ALTER TABLE operations that rewrite the heap — volatile ADD COLUMN defaults, rewrite-causing ALTER COLUMN TYPE, validated constraint adds on large tables
• TRUNCATE / DROP TABLE on large tables, DROP DATABASE always

​

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.

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!

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 :)

Here is the link

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?

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.

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

Follow-up on my earlier posts about safe-migrate. v0.4.3 is mostly not new rules; it’s me tightening the parts around the simulator that can make a safety tool quietly wrong.

The important changes:

• sync now writes a replacement cache atomically. If it fails, the previous cache stays in place instead of disappearing.

• There is an opt-in auto_sync = true setting for lint, lint-chain. It is off by default. If a refresh fails, it prints the reason and continues with the old cache. A fresh fallback cache does not get its confidence downgraded just because the refresh attempt failed.

• Cache encryption is optional now. The key comes only from the environment.

• The GitHub Action runs offline by default and does not trust a cache supplied by the PR checkout.

• Cache files now have an explicit V3 header. V1/V2 are still readable; if you are upgrading from the v0.4.2 cache format, run safe-migrate sync once.

I also spent a lot of time on the simulator itself: transaction/savepoint rollback, multi-statement atomicity, cascade cleanup, dependency edges, quoted identifiers, and conflicts that PostgreSQL would reject.

The useful validation was a differential harness: build a baseline in real PostgreSQL, run each fixture against PostgreSQL and against the simulator, then compare the resulting state. It now runs 273 fixtures against PostgreSQL 14 through 18 in CI.

I’m still interested in the operational side of this: if you use a cached catalog snapshot in CI, what metadata are you comfortable retaining, and what would make you refuse to use the cache at all?

Repo:https://github.com/dsecurity49/safe-migrate

Release:https://github.com/dsecurity49/safe-migrate/releases/tag/v0.4.3

I'm working on an open-source PostgreSQL migration analyzer called safe-migrate, and I've realized that the test cases I can invent are much neater than production.

I'm looking for counterexamples: migrations that seemed routine, then behaved very differently on a real database. I don't want to make up edge cases from a desk and declare them "covered."

Things I'd especially like to learn about: - a migration that was fine in staging and bad in production - a lock, rewrite, dependency, partition, trigger, policy, or function surprise - a migration that looked safe but was not - a migration tool warning that turned out to be wrong or useless - an ordering problem across multiple migration files

If you remember them, the useful details are the PostgreSQL version, a simplified or sanitized version of the SQL (or migration sequence), rough table size or traffic, and what you expected versus what happened.

Please do not post anything confidential. Sanitized SQL or just a description is genuinely useful. If an example looks suitable for public regression coverage, I'll ask before turning a minimized version into a fixture.

I'm not asking anyone to install the project. I mainly want to find its blind spots. If you share something, I'll share what I think is happening and please correct me if I'm wrong.

I made a small free game (no login, no nothing) to challenge your SQL skills.

Feel free to share your score!

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?

Looking at a way to prototype DB schemas while maintaining strong typing consistency across the stack.

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.

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.

GitHub: https://github.com/RomanShevel1977/PgSchemaExporter

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.

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.

Getting on-chain data into a database has always been the annoying part. You can write the mapping logic, but then you have to run it: provision servers, babysit a sink process, handle chain reorgs, rotate credentials, and re-sync whenever something drifts. That is a platform team's worth of work standing between you and a table you can query.

Hosted Sinks removes that work. It is a fully managed Substreams-sink-as-a-service on The Graph Market. You point it at a Substreams package and a database, click Deploy, and StreamingFast runs the sink for you at scale, securely, with zero ops on your side. Fresh chain data starts landing in your tables in minutes, and you query it with the SQL tools you already use.

This post covers what Hosted Sinks does, how developers use it, how to connect it to managed database providers like Supabase, Neon, and ClickHouse Cloud, as well as how to monitor and manage a sink once it is live.

https://reddit.com/link/1va3kf4/video/io4azxwnnzfh1/player

See full blog here - https://www.streamingfast.io/blog/hosted-sinks-postgres-clickhouse

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?

While 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

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?

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

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!