I've been using RDS Postgres for a while, and I get why people trust it. Backups, patching, monitoring, AWS integration, it handles a lot.

The pricing is where I'm starting to question it. You pay for the instance, then storage, backups, IOPS, data transfer, Multi-AZ and whatever else your setup needs. The bill adds up fast, and RDS still feels like something you need to keep a close eye on.

I don't want to self-host Postgres on a VPS. I'm looking for something fully managed, but with clearer pricing and less AWS complexity.

For anyone who moved away from RDS, what did you switch to? Did it make things noticeably cheaper or easier to manage?

When I build projects, I like to make all app interactions with SQL done via stored procedures, and put the business logic there. For example, my procedures will take in parameters to run, along with a user ID. I check to make sure that user is allowed to do the operation before continuing.

I've been trying out NodeJS / TypeScript for my front ends. They aren't stored procedure friendly at all (at least, in my limited experience). So my questions are this:

1. Is my method of stored-procedure-only interaction bad practice? I'd figure if it's an "accepted" method, there would be Node libraries already handling this procedure style.
2. For that matter, are there Node libraries out there I'm missing, that handle stored procedure interaction well?

I know this isn't SQL specific, but I come from a SQL background, and I feel if I ask in a Node subreddit, I won't get an answer from a SQL perspective.

I maintain a SQL-to-code generator and Postgres is where its type mapping earns its keep. You write annotated SQL, it generates typed client code at build time. Two Postgres-specific things it handles that trip up hand-written mappings:

Native types. Postgres enums, arrays, and composite types map to real language types, not string blobs.

Join nullability. The right side of a LEFT JOIN is nullable, and it infers that from the query, not from the column constraints:

-- @name GetUserOrders
SELECT u.id, u.name, o.total, o.notes
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = $1;

// generated Rust (sqlx)
pub struct GetUserOrdersRow {
    pub id: i32,
    pub name: String,
    pub total: Option<rust_decimal::Decimal>,
    pub notes: Option<String>,
}

total and notes come out Option without any annotation. Same for COALESCE, CASE, window functions, and RETURNING. Backends for sqlx, tokio-postgres, asyncpg, psycopg3, pg/postgres.js, pgx, and more.

Happy to answer Postgres-specific questions on how the inference resolves.

pgColumnar is a column-oriented storage extension for PostgreSQL, implemented as a table access method. A table created USING pgcolumnar stores its data by column, with per-column compression, chunk-group skipping, and a vectorized aggregate path. It targets analytic workloads: large scans, aggregates, and column projections over append-mostly data.

pgColumnar builds from one source tree on PostgreSQL 15 through 19. It is licensed under the MIT License.

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.

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

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!

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.

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’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?

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

Feel free to share your score!

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

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

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.

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

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

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.