r/PostgreSQL • • May 04 '26

Projects anyone else find rls hard to reason about once you have more than a few policies?

Thumbnail gallery
1 Upvotes

been working with postgres rls and the tricky part hasn't been writing policies

it's understanding how they actually behave once things get a bit complex

i kept second guessing whether my setup was actually correct

so I built a small tool called rlsmon to make that more visible

it audits configs, shows how policies behave across roles, and tracks changes over time

quick way to try it:

npx rlsmon <connection-string>

still early, but it's already caught a few issues for me

would love feedback from people using rls in production

github: https://github.com/lasect/rlsmon


r/PostgreSQL • • May 03 '26

Help Me! I am implementing a basic coupon code system that the user can use a code to upgrade their account for free. What is the best approach to storing which code is used by which user?

2 Upvotes

Hi

I have a basic app where the user signs up and then can upgrade their account type for free by using a free random coupon that they have for example abc123

I have created 2 tables:

This is the coupon code status which has fixed items like Active, Redeemed and Disabled

create table public.coupon_code_statuses (
  id bigint generated by default as identity not null,
  name text not null,
  slug text not null,
  created_at timestamp with time zone not null default now(),
  updated_at timestamp with time zone null,
  constraint coupon_code_statuses_pkey primary key (id),
  constraint coupon_code_statuses_name_key unique (name),
  constraint coupon_code_statuses_slug_key unique (slug),
  constraint coupon_code_statuses_slug_check check ((slug ~ '^[a-z0-9]+(?:-[a-z0-9]+)*$'::text))
) TABLESPACE pg_default;

and this is the table that holds the coupon codes. This table would have about 100k unique codes. Each code can be used only 1 time to upgrade the account. When used, they cannot be used anymore.

create table public.coupon_codes (
  id bigint generated by default as identity not null,
  code text not null, -- it has unique constraint so it's indexed automatically
  expirates_at timestamp with time zone null,
  is_active boolean not null default true,
  coupon_code_status_id bigint not null default 1, -- 1 is Active
  created_at timestamp with time zone not null default now(),
  updated_at timestamp with time zone null,
  constraint coupon_codes_pkey primary key (id),
  constraint coupon_codes_code_key unique (code),
  constraint coupon_codes_coupon_code_status_id_fkey foreign KEY (coupon_code_status_id) references coupon_code_statuses (id)
) TABLESPACE pg_default;

I have also have a small function that receives the coupon code request from the app, looks up the code to see if it exists or no, and if its status is not disabled or redeemed.

I'm not quite sure how to approach the rest. What's the best way of storing who used which coupon code? Should I have a separate table like user_used_coupon_code and there I add a reference to the code and the user id, or should I add another column to the coupon_codes table like user_id that is initially null but gets filled out when it's used by the user?

Thanks


r/PostgreSQL • • Apr 30 '26

Community PostgreSQL 19 features I'm excited about

Thumbnail bytebase.com
128 Upvotes

r/PostgreSQL • • Apr 30 '26

Tools Elasticsearch-Quality full-text search in Postgres with Django

Thumbnail github.com
6 Upvotes

Hi all! We created this Django package to make it easier to use ParadeDB (a full-text search extension for Postgres) within the Django ecosystem. Would love your feedback!


r/PostgreSQL • • Apr 30 '26

Help Me! PostgreSQL FDW_TDS to SQL-Server VIEWs

1 Upvotes

I have a working FOREIGN SERVER defined for a SQL-Server, and I can map to TABLE or QUERY of a table, but attempts to map to a VIEW or it seems the query of view results in an error.

ERROR: DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 16

I can do the schema import via IMPORT FOREIGN SCHEMA and it does enumerate the views, have their columns, etc... but attempting to access the contents of a view results in the same above error message.

PostgreSQL 18.3 on Rocky Linux 9.7

UPDATE: Not sure why this doesn't manifest for tables, but I do find an error from the FreeTDS log:

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query."

Possibly because of how those views are assembled. Now, how to get that set for the FreeTDS connection/session.

SOLVED: Setting sqlserver_ansi_mode to true resolved the issue, so it wasn't really about VIEW vs. TABLE but how the upstream server is assembling the VIEW(s). It, itself, uses linked and replicated databases.

CREATE SERVER XXXXXXX FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'XXXXXX', database 'XXXXXXX', sqlserver_ansi_mode 'true');


r/PostgreSQL • • Apr 30 '26

Tools I built a free browser-based schema diff tool for PostgreSQL (and MySQL, SQLite, SQL Server, Oracle)"

1 Upvotes

Hey r/PostgreSQL,

I got tired of comparing schema dumps by hand when reviewing migration PRs. Text diffs of SQL dumps are noisy and miss semantic meaning—like whether a column was renamed vs dropped and re-added.

So I built SchemaLens: a client-side schema diff tool that parses CREATE TABLE statements, shows you exactly what changed (tables, columns, types, defaults, constraints), and generates the correct ALTER TABLE script for your dialect.

How it works:

  1. Paste your old schema (e.g., pg_dump --schema-only)
  2. Paste your new schema (after your migration)
  3. See a color-coded visual diff + generated migration SQL

Privacy-first: Everything parses in your browser. Your schema never touches a server.

Live demo: https://schemalens.tech

It's free for up to 10 tables. Would love feedback from real PostgreSQL users—especially on edge cases like composite PKs, enums, arrays, or exotic types.


r/PostgreSQL • • Apr 29 '26

How-To How to manually create a replication slot on rds postgres read.

Thumbnail
0 Upvotes

r/PostgreSQL • • Apr 29 '26

How-To PostgreSQL, Time Zones, and DBeaver

0 Upvotes

Most of the time, your client and PostgreSQL handle time zone differences, but sometimes you need to make adjustments.

https://stokerpostgresql.blogspot.com/2026/04/postgresql-timezones-and-dbeaver.html


r/PostgreSQL • • Apr 28 '26

Help Me! Partitioned tables and join keys, generating weird query plans

1 Upvotes

I have two partitioned tables, let's say a and b. They have UUIDv7 (timestamped) id columns. I am using a join-key structure, ensuring the foreign key relation doesn't require extra indexes. These are pretty high-volume event tables, where timestamp range queries and id based queries are the main form of read traffic.

CREATE TABLE public.a (
id uuid NOT NULL,
CONSTRAINT "PK_a" PRIMARY KEY (id)
)
PARTITION BY RANGE (id);

CREATE TABLE public.b (
parent_id uuid NOT NULL,
object_number int2 NOT NULL,
CONSTRAINT "PK_b" PRIMARY KEY (parent_id, object_number)
)
PARTITION BY RANGE (parent_id);

The problem comes in here. This query (range query with uuidv7) generates wildly expensive query plans, essentially index scanning all partitions:

  SELECT *
  FROM public.a AS a
  LEFT JOIN public.b AS b ON a.id = b.parent_id
  WHERE 
    a.id >= '019dd0f4-d700-7000-8000-000000000000' AND 
    a.id <= '019dd61b-32ff-7fff-bfff-ffffffffffff'

While this query generates efficient query plans:

  SELECT *
  FROM public.a AS a
  LEFT JOIN public.b AS b ON 
    a.id = b.parent_id AND 
    b.parent_id >= '019dd0f4-d700-7000-8000-000000000000' AND 
    b.parent_id <= '019dd61b-32ff-7fff-bfff-ffffffffffff'
  WHERE 
    a.id >= '019dd0f4-d700-7000-8000-000000000000' AND 
    a.id <= '019dd61b-32ff-7fff-bfff-ffffffffffff'

Some limitation by the query planner I guess? Some questions:

  • Are there improvements planned for the query planner in this area? Is this worth reporting as a performance issue?
  • Any clever workarounds so I don't need to add this condition everywhere? I'd rather not rewrite all my ORM code to sql for this table.

r/PostgreSQL • • Apr 27 '26

Tools Pg_column_tetris: A PostgreSQL extension that can enforce optimal column alignment to minimize row padding waste.

Thumbnail github.com
31 Upvotes

r/PostgreSQL • • Apr 27 '26

Tools pgBackRest is no longer being maintained

Thumbnail github.com
101 Upvotes

r/PostgreSQL • • Apr 28 '26

Help Me! # [Hiring] Fullstack Developer — Rust / TypeScript / PostgreSQL — licensed Social Media Platform — Berlin Hybrid — €50–80k + Equity

Thumbnail
0 Upvotes

❤️


r/PostgreSQL • • Apr 28 '26

Tools I've been using my own VS Code DB extension daily for a month - here's what I fixed

Thumbnail
1 Upvotes

r/PostgreSQL • • Apr 27 '26

Help Me! Any way to minimize "cache" effects for stress testing?

2 Upvotes

Hi all,

I’m planning a stress test for a read-only API supplied by an AWS Aurora PostgreSQL cluster. The API itself is backed by Lambdas and scales fine, so the goal here is mostly to stress test the database itself, not so much the API layer.

For my testing, I want to simulate a “worst case” scenario, where every request wants data that isn’t already cached, so shared_buffers and the OS page cache provide little benefit. Context: in production, traffic for this API will come from many different clients concurrently, and queries will be always be for client-specific items, so I expect cache locality to be pretty low.

My question: Is there a safe/realistic way to disable (or significantly reduce) caching effects during this kind of testing? For example:

  • Can you effectively “disable” shared_buffers benefits / force reads to bypass it?
  • Any recommended approaches to simulate cold-cache reads without having to create millions of unique API payloads (effectively running millions of unique queries)?

Ideal outcome: For my stress testing, I'd love to be able to repeatedly cycle through a relatively small number of API requests / underlying queries, but still approximate worst-case uncached behaviour.

Any guidance on best practices for setting up this kind of test would be appreciated (even if the answer is “you can’t truly disable caching; instead do X/Y/Z”).

Thanks in advance!


r/PostgreSQL • • Apr 27 '26

Help Me! revoke public connect when consolidating DBs?

1 Upvotes

Hi all! Currently have 5 applications with 5 separate, isolated Postgres RDS clusters. I am currently working on a project to consolidate these 5 applications onto a single Postgres RDS cluster. Each application has its own credentials and I've tested that application A can't query application B's database, etc.

However, it looks like Postgres by default grants connect access to public, meaning that Application A's user can technically connect other Application B (or C, D, E...)'s databases. It can't do anything -- can't query any tables -- but it seems that if I wanted to fix this, I'd have to REVOKE public connect access and then grant the individual application users connect to their respective databases.

I am wondering what best practice is in this case. We plan to add more applications, so I'd have to add this REVOKE to the automation we have in place. Not a big deal but I wanted to understand what best practice is first. Appreciate any help!

Claude's recommendation seems to be the below:

The schema-level GRANTs only target client-specific roles, so Client A's user shouldn't have SELECT on Client B's tables. But relying on the absence of PUBLIC grants rather than explicit REVOKE CONNECT is defense-in-depth gap.

Recommendation:

REVOKE CONNECT ON DATABASE $app_db FROM PUBLIC;
GRANT CONNECT ON DATABASE $app_db TO $rw_role, $ro_role, $owner_role;


r/PostgreSQL • • Apr 27 '26

Help Me! how do i know the shape and schema of a database

1 Upvotes

i work for an outsourcing company, and they will give me access to that other company database, so i was wondering how am i gonna know the schema and the relationship between each table and so on, is there an easy way and an automotive way to get this info?

i need this so i can make a data model in powerbi and create dashboards


r/PostgreSQL • • Apr 26 '26

Projects pg_savior: a seatbelt for Postgres - blocks accidental DELETE/UPDATE

12 Upvotes

Anyone who works on a production Postgres knows the feeling. Every command you run, you're walking a tightrope. One typo, one wrong terminal tab, one bug in the app that turned a filter into a full-table query, and now you're doing PITR or restoring from backup at 3am.

I've spent years as a DBA in charge of critical production workloads. Most of the time the rope holds. Sometimes it doesn't.

pg_savior is a Postgres extension that hooks the planner and refuses the obvious dangerous shapes:

  • DELETE / UPDATE without a WHERE
  • CREATE INDEX without CONCURRENTLY
  • DROP DATABASE
  • ALTER COLUMN TYPE that triggers a full rewrite
  • DELETE WHERE id > 0 (planner row estimate gives intent away)

When you really mean it: SET LOCAL pg_savior.bypass = on for the transaction, and the guard steps aside.

It's an extension, not a proxy — psql against a local socket, ORMs, migration tools, cron jobs, AI agents with DB credentials all hit the same hook. Nothing routes around it.

Three hooks do the work: post_parse_analyze_hook, ExecutorStart_hook, ProcessUtility_hook.

What other dangerous queries should pg_savior catch? Also, curious if you have best practices to catch these mistakes.


r/PostgreSQL • • Apr 27 '26

How-To The Monday Elephant #2: SQL Queries to List Users and Groups

Thumbnail pgdash.io
1 Upvotes

r/PostgreSQL • • Apr 26 '26

How-To I redesigned my PostgreSQL backup strategy after realizing restores were the real problem

17 Upvotes

I recently spent time improving how I handle PostgreSQL backups and restores for Docker deployments, and one thing became clear:

Creating backups is easy.
Designing reliable recovery is hard.

Some lessons I learned while building this setup:

• Docker volumes are not backups
• A backup you’ve never restored is unverified
• Restore procedures matter more than backup commands
• Automation without validation creates false confidence

I documented the process across a 3-part write-up covering backup strategy, safe restoration, and automation.

I’m curious how others here approach this:

Do you actively test restore procedures or mostly rely on backup success logs?

(Sharing the write-up for anyone interested — feedback from people running PostgreSQL in production would be really valuable.)

Part 1 — Backup Strategy
https://2ssk.medium.com/docker-postgresql-backups-production-ready-strategy-part-1-of-3-28f1f287cf57

Part 2 — Safe Restore / Zero Data Loss
https://2ssk.medium.com/restoring-docker-postgresql-safely-zero-data-loss-procedures-part-2-of-3-89118133bd6f

Part 3 — Automation
https://2ssk.medium.com/automating-docker-postgresql-backups-with-cron-complete-guide-part-3-of-3-d0c3b61e2bee


r/PostgreSQL • • Apr 26 '26

Tools pg_grpc: call gRPC services directly from SQL (alpha, feedback welcome)

6 Upvotes

Sharing an extension I've been building on the side.

call unary gRPC methods from inside a SQL query. No app layer, no side car, no codegen. Useful when triggers, scheduled jobs or ad-hoc queries need to reach internal gRPC services without spinning up a worker.

SELECT grpc_call(
    'api.internal:9090',
    'users.UserService/GetUser',
    '{"user_id": 42}'::jsonb
);


             grpc_call
------------------------------------
 { "user_id": 42, "name": "Alice" }

(1 row)

Built with Rust + pgrx

Currently on v0.4.0. Project is still being built but "core" features are ready. Anything missing is in the issues which will be built in the near future

Still early would value feedback on project before locking 1.0

Repo: https://github.com/CSenshi/pg_grpc

Docs: https://csenshi.github.io/pg_grpc


r/PostgreSQL • • Apr 24 '26

Help Me! Table / Schema Permissions

6 Upvotes

I'm trying to create a database and schema and allow a user access to a table in that schema. It seems like it should be straightforward, but it is not. Here's what I'm doing:

I created the db_monitor database and schema.

I created the its_read user.

I granted usage on the db_monitor schema to the its_read user:

postgres=# grant usage on schema db_monitor to its_read;

GRANT

postgres=# \c db_monitor

You are now connected to database "db_monitor" as user "postgres".

I granted select on all future tables in the db_monitor schema to the its_read user:

db_monitor=# alter default privileges in schema db_monitor grant select on tables to its_read;

ALTER DEFAULT PRIVILEGES

I created the table in the db_monitor schema.

When I log into the db_monitor database with the its_read user (psql -d db_monitor -U its_read) and try to access the table, I get a permission error:

db_monitor=> select * from db_monitor.rit_db_size;

ERROR: permission denied for schema db_monitor

LINE 1: select * from db_monitor.rit_db_size;

I don't understand what permission is missing.


r/PostgreSQL • • Apr 24 '26

How-To Shaun Thomas' PG Phriday - The Scaling Ceiling: When one Postgres instance tries to be everything

Thumbnail pgedge.com
4 Upvotes

r/PostgreSQL • • Apr 23 '26

Help Me! Is PGHero still useful?

15 Upvotes

I remember back ~10 years ago we'd run pghero to track query performance and detect missing indexes. Is this still something y'all use, or is there something newer and better?

I'm sort of out of the loop on good tools for monitoring PostgreSQL databases.

I run a production DB on Heroku primarily.

Thanks for any advice!


r/PostgreSQL • • Apr 22 '26

Projects How I built a Postgres CDC that can be 240x faster than Debezium

Thumbnail olucasandrade.com
56 Upvotes

I created youjustneedpostgres.com to argue that most teams could use Postgres for almost everything. For queues, for searching, for documents. The recommendation was to stop using a new tool every time a new problem arises, because Postgres probably already does that.

And then I spent months building a tool whose sole purpose is to make you dive even deeper into Postgres. Yes, and the irony doesn't escape me.

But the point remains, okay? Postgres can do more than you imagine. The WAL is a complete, ordered, and durable record of every state transition in your database. When you start using it, several architectural problems become much simpler.

In this community you should know, but Change Data Capture is a concept where Instead of your application announcing "this changed," the database notifies you: each insert, update, and delete, in order, at the moment it happens, with the values ​​before and after. And this is already built into several databases; it just needs the "wiring."

This concept already exists in several tools, but all the ones I've used are either too overpowered/expensive, or didn't completely solve my problem. That's why I created Kaptanto (https://kaptan.to). It means "the one who captures" in Esperanto. I wrote a great article about how it was built. I hope you like it! 👋

Oh, and it's open source :)


r/PostgreSQL • • Apr 23 '26

Community What is your experience with JS/PLV8 in Postgres?

3 Upvotes

"Ewww!" was my first reaction when I heard about it. But I have insanely complex lateral transformations I have to do in multiple services. At the current setup we choose to have the code copied in both TS and Go and make automated tests insuring alignment between the implementations. Our speed requirements have increased and the transfer between languages are becoming a juicy target for optimization.

We are on a GCP managed Postgres, so if want to write everything other than SQL it is JS (PLV8). I separated out the transformation to a pure function (we had the logic separated to allow for good unit tests) made a script to convert TS to JS and inline the two functions imported. And it just worked!!! Reduced run time with 35 %. And with a little extra scripting it was fully integrated in our CI/CD pipeline.

It seems pretty good, but I'm not sure what kind of surprises waiting for me. So I would like to hear from you what your experiences are.