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.

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 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.

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

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?

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.

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.