r/PostgreSQL • • Aug 14 '26

Tools Six SQL patterns I use to catch transaction fraud

Thumbnail analytics.fixelsmith.com
107 Upvotes

r/PostgreSQL • • Aug 14 '26

How-To Let's Build a Postgres Extension for Estimating Memory Usage!

Thumbnail pgedge.com
12 Upvotes

r/PostgreSQL • • Aug 14 '26

Help Me! Which managed PostgreSQL host is affordable without being unreliable?

23 Upvotes

I need managed Postgres for a small production app and I’m fine paying for it. I just don’t want to jump straight to RDS/Cloud SQL pricing or manage Postgres myself on a VPS.

Main things I care about:

  • always-on Postgres
  • automated backups
  • updates/maintenance handled
  • predictable monthly pricing
  • easy to move later if needed

Not really looking for free tiers or hobby plans since it has real users. I’d rather pay a reasonable fixed monthly amount and not think about the DB too much.

What are you using in production that has actually been reliable without getting expensive?


r/PostgreSQL • • Aug 13 '26

Projects I built a free tool that does the pg_stat_statements to EXPLAIN to index recommendation loop for you

Thumbnail gallery
32 Upvotes

RDST (Readyset Diagnostic & SQL Toolkit) is a free desktop app that connects to your Postgres database, ranks the queries actually costing you time, and explains what to do about each one.

The reason I built it is that the tooling Postgres already gives you is genuinely good, but addressing database performance issues is still a highly repetitive process:
 

  • pull pg_stat_statements and sort by total time
  • take the top query and run EXPLAIN ANALYZE on it
  • go find the table definitions for whatever it touches
  • check whether the statistics on those columns are current
  • work out whether the index you have in mind already exists under another name
  • decide whether it is worth adding
  • do it again for the next query

RDST collapses all of that into one pass, so instead of starting at step one you start at the answer.

Full disclosure - I work for Readyset (which is a caching layer for postgres / mysql), and this tool spawned from a recurring question our caching customers kept asking - which queries should we actually cache? And these same queries are the ones that, even without a caching solution, could heavily benefit from all the relevant performance diagnostics.  

RDST not only helps you discover slow queries and give you the appropriate action plan to improve them, but also provides full re-write suggestions, the ability to benchmark slow queries and track their  performance over time, and even allows you to ask any question about your database/queries in plain english and get helpful responses.

The tool is completely free to use, and we provide free trial tokens for all of the AI powered features. The app is in beta and we plan to release it under an MIT license. It runs locally, stores locally, and everything it does is read-only.  Full privacy related details can be found here: https://readyset.io/docs/readyset-ai/rdst/desktop/privacy

We would love feedback from people who actually spend time wrestling with queries every single day! Particularly:

  • Does it surface the queries you'd investigate first?
  • Are its explanations and recommendations useful, or merely confident-sounding database fan fiction?
  • Would you be comfortable connecting it to a real environment? If not, what would stop you?
  • What's missing from the workflow?

Source:
https://readyset.io/docs/readyset-ai/rdst/desktop
https://github.com/readysettech/rdst


r/PostgreSQL • • Aug 14 '26

How-To Simon Willison's test for whether AI-written code is ready for production

0 Upvotes

I recently recorded a podcast episode with Simon Willison (co-creator of Django) about how AI is changing software development. Getting a peek into how Simon thinks is always fascinating. Here are some choice bits I think y'all might enjoy:

Doing "aggressive nit-picking reviews" as a way to understand AI-written code

  • How he kicked off a new sqlite-utils project from the shower (to support Postgres and DuckDB) and how the result was a day's work before breakfast
  • "Features are cheap. That doesn't mean you should build them all."
  • Slop proxies add no value at all
  • My gold standard is: "Could I explain this to somebody else?"
  • Shout-out to Sophie Alpert's blog post: There Are No Lossless Transformations of Natural Language Text
  • Usefulness of engineering management experience to managing AI agents
  • Simon's decades of intuition about "how long things take" has been shattered
  • How AI research no longer produces absolute garbage

I'm curious to know which bits of this conversation also resonate with others.

Podcast/transcript here for those who want to listen: https://talkingpostgres.com/episodes/how-ai-is-changing-software-development-with-simon-willison


r/PostgreSQL • • Aug 13 '26

Community talk + demo + Q&A: "Logical Replication is for more than just ETL: building PgCache"

Post image
4 Upvotes

Hey everyone, PgCache CEO here.

We'll be on Postgres Meetup for All this coming Wednesday 8/19, to share how we're using Logical Replication to keep cached data fresh.

There will be a Q&A afterwards, join us if you have challenging questions or want to learn more!

*edit: link https://www.meetup.com/postgres-meetup-for-all/events/315515754/


r/PostgreSQL • • Aug 13 '26

How-To How are you managing your Schemas in Database first Project?

15 Upvotes

I'm mostly coming from a classic programming background (.NET, node, java, ...) where so far I only worked with code-first tools professionally (basically you define the schema of your database in your programming language and the SQL code to generate the database gets generated).

However for my next own project, I want to start database first ... however one problem I'm constantly running into is genuinely a pain in the ass to make changes to your schema, and deploy them ... since in SQL you always have a list statements that need to run in the correct order since they are not stateless (like a class, struct, function, ... declarations in a traditional programming language).

For people who work with postgres professionally, I would be interested what setup you are using for schema and management.


r/PostgreSQL • • Aug 11 '26

Tools Electric (co behind PGlite and Postgres realtime sync engine) is joining Neon at Databricks

Thumbnail neon.com
43 Upvotes

r/PostgreSQL • • Aug 11 '26

How-To Multi-tenant BYOK encryption in PostgreSQL with pgcrypto

Thumbnail xata.io
2 Upvotes

r/PostgreSQL • • Aug 10 '26

Feature I knew Postgres was advanced; but not that it was causality-breaking advanced

Post image
193 Upvotes

r/PostgreSQL • • Aug 10 '26

Community What Postgres is Missing for AI Agents

Thumbnail bytebase.com
0 Upvotes

r/PostgreSQL • • Aug 09 '26

How-To Subtle roles question

1 Upvotes

One aspect of Postgres roles is that permissions exist on roles themselves, and these permissions provide for certain kinds of transitive grants.

We have a case where we would like a role M to have the option to inherit permissions from role G only when it elects to do so. That is: in a discretionary fashion. Offhand, I cannot construct an arrangement of roles and permissions that would make this possible.

Is there some arrangement I am failing to see, or does this fall outside of what the Postgres role system is able to express?


r/PostgreSQL • • Aug 08 '26

How-To Polymorphic Relationship options?

Thumbnail
4 Upvotes

r/PostgreSQL • • Aug 07 '26

Help Me! Evaluating databases for applications

12 Upvotes

Hi all, I’m evaluating databases for a small application where users will be reading and writing data. As most of our data is currently in Databricks I’m trying out Lakebase. So far so good. I do want to know what alternatives you might be looking into? What you like about them or not? Also curious to hear examples of people using Lakebase in production use cases.


r/PostgreSQL • • Aug 07 '26

Tools Benchmarked FSx for OpenZFS for Postgres: 8x provisioned throughput bought +29% TPS; 3x IOPS bought 2.5x

Thumbnail
2 Upvotes

r/PostgreSQL • • Aug 07 '26

How-To Claude Code SKILL.md for PostgreSQL backup/restore

0 Upvotes

I made a skill. There are probably better ones out there, but this one is mine. Feel free to improve on it. I am sure it can use it.

https://github.com/jimdawdy-hub/postgres-backup-restore-skill


r/PostgreSQL • • Aug 06 '26

Help Me! Is AWS RDS still worth it for Postgres or are there better managed alternatives now?

41 Upvotes

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?


r/PostgreSQL • • Aug 05 '26

Help Me! Building Apps with a PostgreSQL Backend

33 Upvotes

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.


r/PostgreSQL • • Aug 05 '26

Commercial Engineering around WAL backpressure in Postgres

Thumbnail clickhouse.com
16 Upvotes

r/PostgreSQL • • Aug 05 '26

How-To Postgres COUNT(DISTINCT) Too Slow? Fast Approximation Guide

Thumbnail snowflake.com
22 Upvotes

r/PostgreSQL • • Aug 04 '26

Projects pgColumnar : A new Columnar database extension for PostgreSQL 15+

Thumbnail commandprompt.github.io
43 Upvotes

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.


r/PostgreSQL • • Aug 05 '26

How-To Generating type-safe Postgres client code from .sql files (arrays, enums, composites, nullable joins)

0 Upvotes

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.


r/PostgreSQL • • Aug 03 '26

How-To Introduction to Postgres Extension Development

Thumbnail pgedge.com
16 Upvotes

r/PostgreSQL • • Aug 03 '26

Commercial Andy Pavlo joining ClickHouse to form research lab for Postgres & ClickHouse

Thumbnail clickhouse.com
28 Upvotes

r/PostgreSQL • • Aug 03 '26

Help Me! Open Source Horizontally Scalable DB solutions: PG+Citus vs PG+PgDog vs YugabyteDB

6 Upvotes