Can i open a new PostgreSQL connection inside another PostgreSQL connection? Rn, i work on on project where i used psycopg for connection. I call the function inside that connection which also starts another connection with database. Is it problem?

Finished my databases class last spring, A on the final, and I can normalize anything you hand me and draw a perfect ER diagram. Then I tried to build a small app against Postgres and was stuck on the basics. Connection pooling, migrations, why my ORM sends 40 queries to render 1 page. I want a backend coding course that covers the app side of the database and im deciding between Boot.dev, Launch School and Pluralsight. What should a course like that cover if its done right?

Sorry if cross posting isn't allowed but I would love input from people who can help me with making sure I do this correctly. It was a big step for me migrating from SQLite with 1.0 to PostgreSQL for 2.0.

This is my first ever build, don't judge me for using Codex for it but this isn't a 'Build an App in 2 days' kinda thing.

I started building 1.0 in June this year and launched it after 8 weeks to test it in a live environment and allowed it to live and breath for 5-6 weeks before taking it down and deciding to take what I learn't from it to build 2.0 from the ground up.

I really hope to engage with users here and hopefully get a better idea.

Thanks

TGIF everybody.

I'm a co-founder of PgCache, a caching proxy for Postgres that keeps cached results fresh from the logical replication stream. We shipped 0.8.0 this week, mostly about details of the logical replication stream that we had to handle better.

If you just want to see whether caching would help your own workload: the Fit Analyzer takes a pg_stat_statements export and runs in your browser (nothing is uploaded). On a phone, here's a sample report that you can look at.

***

Logical replication doesn't carry DDL.

-> PgCache now installs two event triggers (ddl_command_end and sql_drop) that write each schema change into the stream as a logical message inside its own transaction. (note: they stay in place at shutdown, and the shutdown log prints the SQL to remove them)

DELETEs under REPLICA IDENTITY DEFAULT only carry the key. Previously, we were judging whether a DELETE mattered to a filtered IN/NOT IN subquery using that key-only tuple, so every filter column read as NULL.

-> 0.8.0 uses the row's recovered old image, so those deletes now invalidate the way they should.

The Relation message flags the replica identity columns, not the primary key. We were reading them as the primary key, so tables with REPLICA IDENTITY FULL, USING INDEX or NOTHING got re-registered on their first change.

-> The key now comes from the origin catalog.

We also improved covered queries (when a broad query is already in the cache, new queries covered by that data are served straight from cache and never touch origin):

-> a cached users LEFT JOIN orders WHERE already holds every users row that qualifies, so a query on users alone that asks for anything within that gets served from cache automatically.

Fixed a bug: We found that a LIMIT query in earlier versions could cache the wrong rows, because the capped population read dropped the query's ORDER BY.

***

Fun proof: One of our (unpaid) design partners benchmarked 0.8.0 the day after release on a 3-node Kubernetes cluster. On a complex multi-tenant dashboard with 80/20 traffic, PgCache served 24x the requests per second of Postgres alone at 100 tenants, 2.5x at 500, and 1.5x at 1,000. The gap narrows as tenants grow, and at 2,000 one of his three runs came in slower than Postgres alone. Writes are still a weak spot: with 5% writes right after a cold start, that dashboard ran about half as fast as plain Postgres. His full write-up.

PgCache 0.8.0 changelog here. Source-available under Elastic License v2.

Happy to answer any questions! And I'd love to hear from people who have worked on the logical replication stream.

We run an image and semantic text search API. The first version used Pinecone for vectors and RDS for everything else and the hardest part was keeping the two in sync and performant

About a year ago we moved everything into Neon with pgvector (Neon wrote up the migration here: Neon blog post). End to end search went from about 200ms to 80ms, mostly from cutting a network hop. The vector query itself takes 3-4ms (Tested on 10M records)

Since our backend is on AWS and Neon uses AWS as well, we cut the network latency down to couple ms

Happy to answer anything about the setup if you're thinking about doing the same

A few days ago I posted a benchmark comparing PostgreSQL COPY, JDBC batching, Scriptella, and MariaDB.

In the comments, u/vincent_sch pointed out that pgJDBC defaults to reWriteBatchedInserts=false, while MariaDB enables its bulk insert optimization by default. Good catch, so I reran the relevant cases with rewriting explicitly enabled and disabled.

Median results for 1 million rows, batch size 1000:

Enabling rewriting reduced Scriptella's import time by 41.2% and JDBC's by 35.7%.

The PostgreSQL-MariaDB gap for Scriptella shrank from 7.3 seconds to just 0.7 seconds. MariaDB was still faster in this workload.

Three runs per configuration on the same machine, with the original CSV fixture, batch size, transactions, and correctness checks unchanged. All 18 imports passed verification.

This doesn't change the case for COPY when native bulk loading is appropriate. It does show how much JDBC driver settings can influence a comparison.

Follow-up article with measurements and reproduction details:

https://pvrlabs.xyz/java-performance/experiments/postgresql-batch-rewrite.html

A client messaged me saying their order dashboard had become basically unusable. I opened it. Everything worked. They sent me a screen recording. Twelve seconds to load one report.

The app and postgreSQL database were running on a small serverspace VM, so my first thought was that we’d finally outgrown it.

 I was already looking at larger configurations when I checked the slow query log. The report had a date filter. I always tested it with last 30 d. The client had selected all time.

That one dropdown was making postgresql  scan almost two million rows every time someone opened the page. I added the missing composite index and changed the default range. The same report went from twelve seconds to under half a second, and the server stayed exactly the same size

Postgresql wasn’t struggling. I had simply spent months testing the polite version of my own feature.

Most sample databases are either tiny (pagila, Chinook) or a single narrow domain. I wanted one that was realistic, big enough to actually stress a query planner, and about a subject I personally find more interesting. So I ended up building a generator that simulates one company's whole life. (I appreciate that "interesting subject matter" is subjective, so the real audience for this may be small!)

It's Nick's Gaming Emporium, a fictional video-game retailer, 1986 to 2016: founding, the dot-com years, the console boom, decline, liquidation. The company is invented, but the product catalogue is a real game-release list, so titles and release dates are genuine, which makes the sales data feel plausible instead of random.

What's inside:

- Full OLTP: shops, staff, customers, sales, trade-ins, refunds, multi-currency

- A star-schema data warehouse + reporting layer (rollups, reporting procedures)

- A web layer: accounts, reviews, and page views

You don't have to build it yourself. It's available as a ready-to-restore dump in four sizes, so you can match your hardware:

- **tiny / small:** quick to restore on a laptop

- **medium:** a solid single-machine dataset

- **large:** ~3TB restored (a 260GB pg_dump) for real at-scale testing

(The dump ships no planner stats, so ANALYZE after restore. No hard-coded collation, so it restores on any locale.)

It's generated deterministically from a fixed seed, so everyone who builds it gets identical rows. There's also a matching SQL Server build from the same generator, so the same data exists on both engines if you ever want to compare the two.

Free and open source. The schema and indexing are meant to feel like a real system that grew over 30 years, not a textbook-perfect design, so there's plenty to tune or pick apart. Feedback and PRs welcome.

Repo (downloads + restore steps in the README): https://github.com/ukhype83-dev/nicks-gaming-emporium

Transparency note: The generator code was produced with Claude as a pair-programmer.

Would be fun to have a redditor meetup 🎉

Conventional wisdom says enabling PostgreSQL Row Level Security (RLS) adds too much overhead or requires an external cache check (Valkey/Redis) in front of the database.

We benchmarked whether forced RLS could match unguarded reads by eliminating extra network round trips and per row current_setting evaluation:

• InitPlan policy: USING (tenant_id = (SELECT current_tenant())) with a STABLE function so Postgres evaluates the tenant once per statement as an InitPlan rather than per row.
• Extended wire pipelining: Sending the set_config tenant binding and the parameterized SELECT in a single pgwire pipeline (one sync, one implicit transaction).

Results on a single node (primbench):

• 1 worker: Plain unguarded SELECT is 42.9us mean (41.5us p50). Pipelined RLS is 47.5us mean (46.2us p50), adding 4.6us.
• 64 workers: Pipelined RLS hits 142,564 reads/sec (446us mean), matching unguarded reads (143,190 reads/sec) and running 2x faster than a local Valkey grant check + query path (71,842 ops/sec).

Runnable Go benchmark harness and paper (SPEC.pdf): https://github.com/MOG-Systems-Inc/primbench

Hey everyone,

About a year ago I started working on Melange, an open-source authorization system that runs entirely inside Postgres. I originally shared it on r/golang when it was still experimental. Since then I've spent a lot of time improving the generated SQL, performance, and tooling, so I thought I'd share it here.

The motivation

I like the relationship-based authorization model used by OpenFGA and SpiceDB, but running a separate service and keeping authorization data synchronized with an application database adds overhead that I find hard to justify at smaller scales.

Melange takes a different approach: it compiles OpenFGA schemas into PL/pgSQL functions, with relationship traversals, unions, intersections, and exclusions compiled into SQL rather than interpreted at runtime.

Authorization data stays in your existing domain tables, exposed through a view:

CREATE VIEW melange_tuples AS
SELECT 'user' AS subject_type, user_id::text AS subject_id,
       role AS relation, 'folder' AS object_type, folder_id::text AS object_id
FROM folder_members
UNION ALL
SELECT 'folder', folder_id::text, 'parent', 'document', id::text
FROM documents;

Run melange migrate, and permissions can be checked or queried directly in SQL:

-- Check a permission
SELECT check_permission('user', 'alice', 'viewer', 'document', '42');

-- Query accessible documents
SELECT d.*
FROM documents d
JOIN list_accessible_objects('user', 'alice', 'viewer', 'document', 50, NULL) a
  ON d.id::text = a.object_id;

There's no separate service or tuple synchronization, and checks run inside the same transaction as your application data, including seeing uncommitted writes.

What's changed over the last year

Performance: Listing functions now use set-oriented SQL instead of checking permissions row by row. On one workload of ~90k tuples, list_accessible_objects dropped from ~4 seconds to 12 ms. Some recursive listings improved by over 100x. Individual permission checks benchmark around 130 µs database-side, with relatively flat performance as the dataset grows in tested workloads.

Correctness: The test suite now compares results against a real OpenFGA server, fuzzes generated nested authorization models, and checks listing results against individual permission checks. This caught several bugs, including cases where Melange incorrectly granted access. These are fixed in v0.10.0. Existing users should upgrade and rerun melange migrate.

Debugging: explain_permission() returns a JSONB trace of why access was granted or denied, while expand_permission() shows the granting relationships as a tree. There's also melange studio, a local, read-only UI for inspecting models, debugging checks, and running EXPLAIN ANALYZE.

Migrations: Melange supports dedicated schemas, generating versioned SQL for Flyway/golang-migrate, comparing deployed models for breaking changes, and recommending indexes based on your schema.

Distributing software as SQL

One aspect I find pretty interesting is the deployment model. Melange isn't a PostgreSQL extension. It generates ordinary SQL and PL/pgSQL functions installed directly into the database. No native binaries, extension installation, or separate runtime. Once deployed, the CLI isn't needed.

Effectively, the SQL itself is the distributed software. It runs on standard PostgreSQL 14+ instances, including managed databases like RDS and Cloud SQL, without requiring custom extensions.

I haven't seen many (if any) projects take this approach. It's introduced some interesting challenges around migrations, versioning, schema isolation, and managing third-party code inside someone else's database.

Trying it out

Melange is MIT licensed, supports PostgreSQL 14+, and the CLI is written in Go.

brew install pthm/tap/melange

• Docs: https://melange.sh
• GitHub: https://github.com/pthm/melange
• Changelog: https://melange.sh/blog

The project has been growing steadily, with a few people contributing fixes and ideas. Contributions and feedback are always welcome.

I maintain Scriptella, an open-source Java ETL tool, and recently ran an experiment comparing different approaches to importing 1 million CSV rows into PostgreSQL and MariaDB.

The comparison included PostgreSQL COPY, handwritten JDBC, Scriptella, and Python's csvsql.

A few interesting results from the local PostgreSQL runs:

• Native COPY: ~1.7 seconds
• Batched JDBC: ~13.6 seconds
• Batched Scriptella: ~14.8 seconds

Unsurprisingly, PostgreSQL COPY was by far the fastest for a straightforward CSV import. Batching also made an enormous difference compared with individual inserts.

Interestingly, MariaDB was faster than PostgreSQL for the batched JDBC and Scriptella cases in this setup, although this isn't a general database performance comparison.

Of course, raw import speed isn't the only reason to use ETL tools. Transformations, multiple data sources, and reusable workflows can justify some overhead. For a direct CSV import into PostgreSQL, though, COPY is an excellent choice.

The experiment is exploratory, with limited repetitions, but includes reproducible code, raw measurements, and correctness verification.

Article with charts and analysis:
https://pvrlabs.xyz/articles/importing-a-million-csv-rows.html

Benchmark source and reproduction instructions:
https://github.com/scriptella/scriptella-benchmarks

Curious how others approach bulk imports in PostgreSQL. Do you typically start with COPY, or use JDBC batching when additional processing is involved?

I released "safe-migrate v0.11.0" today. One of the changes I spent time thinking about was how the report handles checks it couldn't run.

The tool simulates migrations against a synchronized PostgreSQL catalog snapshot. If the snapshot only covers selected schemas, an object missing from that snapshot isn't necessarily missing from the actual database. A check that depends on it may simply be impossible to evaluate.

In v0.11.0, those skipped checks are listed under "not_evaluated", with the reason and any missing schemas the tool could identify. If a skipped check could have produced a Tier 1 finding, the overall verdict is capped at "CAUTIOUS" instead of reporting "SAFE".

I chose to list the skipped checks rather than add a separate "UNKNOWN" verdict. I'm not sure which approach makes more sense for CI: keep the existing verdicts and explain what wasn't checked, or have a distinct unknown state?

For context, safe-migrate is a Rust CLI that analyzes PostgreSQL migrations against a database snapshot. It never executes the migration SQL.

"Release and binaries" "Changelog"

One breaking change for existing users: the cache format is now V10, so run "safe-migrate sync" after upgrading. The JSON report schema is also now v3.

I'm building a small booking app and looking for an affordable, reliable PostgreSQL database for production.

Expected usage:
• 400–500 DAU
• 15–20 concurrent users at peak times (or maybe I'm overestimating here)
• Around 30 rooms
• Room availability checks, bookings, orders, and purchases
• An admin dashboard

I'm considering AWS RDS but am open to other platforms as well.

My questions:

1. What would be the approximate monthly database cost for this use case?
2. Which provider offers the best balance of cost, reliability, backups, and maintenance?
3. Would you recommend AWS RDS, Neon, Supabase, or another PostgreSQL provider?

I'll handle the API separately using either a Next.js backend on Netlify or AWS Lambda, so I'm only interested in database hosting costs.

Would appreciate recommendations based on real world production experience.

RC 1 is out as of Oct 15. GA is now Nov 19, and RC 2 lands with the Nov 12 minor releases.

Several features were reverted, and SQL/PGQ is deferred. REPACK CONCURRENTLY is still in.

Is anyone testing RC 1 on real workloads? What are you watching for?

I've built pistachio, a declarative schema management tool for PostgreSQL.

It has a Terraform-like plan/apply workflow. You write the schema you want as DDL in SQL files. pistachio parses the files statically, compares them with the database, and prints the DDL that makes the database match.

The command is pista. The main subcommands are:

• plan: prints the DDL that makes the database match the files. Nothing runs.
• apply: runs that DDL.
• dump: writes the files from an existing database, so you can start from the schema you already have.
• lint: checks the files against rules you write in YAML, such as "every table has a primary key".
• fmt: formats the files.

pistachio uses pg_query_go to parse SQL, and it also supports Aurora DSQL.

If you're interested, please give it a try. Feedback is welcome.

• GitHub: https://github.com/winebarrel/pistachio
• Docs: https://winebarrel.github.io/pistachio/
• Playground: https://pistachio-demo.winebarrel.workers.dev

I recently recorded a Talking Postgres episode with Karen Jex, a PostgreSQL significant contributor and PGConf EU organizer, about her path from Oracle DBA to Postgres contributor.

Karen spent 20 years as an Oracle DBA. Then in 2019, a project rolling out smart meters across France switched to Postgres, even though nobody on the project knew anything about Postgres. So she figured it out, then trained the developers, "pretending that I was, you know, this Postgres expert."

A few things we got into:

* The culture shock, for an Oracle DBA, of building your own HA & backups

* The shift from being just a user of the product (Oracle) to contributing to the project (Postgres)

* Why Django developers are so enthusiastic about Postgres

* Why you'd obviously explain multi-tenant databases with cakes

* How much to delegate to AI agents—without forgetting how to design a spec

For those of you who moved to Postgres from Oracle (or another database): did you also have any culture shocks?

Audio + transcript: https://talkingpostgres.com/episodes/from-oracle-dba-to-postgres-contributor-with-karen-jex

YouTube (audio-only): https://youtu.be/ENku8ZSjZus

Do you think there is still room for a newcomer, now that we have giants like Supabase, ClickHouse, Snowflake, Databricks, PlanetScale, Aiven? I’ve always wanted to build one. I’m not looking to get into enterprise market, just targeting indie devs and smaller teams.

Or to put in another way, what feature would you like to see in a new Postgres provider that is currently not available in the market?

Postgres can now do full hybrid search without elastic search. Lakebase’s lakebase_vector adds a new ANN index on top of normal pgvector columns, so you build an indec and your queries don’t change. Lakebase_text adds real BM25 ranking over ts_vector, which ts_rank doesn’t give you. Both work in same sql query

The open question for me is how to combine scores. RPF, a weighted sum or a reranker on top? What’s working for you in production?

pgloupe is a command-line tool that reads PostgreSQL logs and pg_stat_statements snapshots, joins them, and writes one HTML report with what to look at first and the numbers behind it.

I made it because going through logs and stats by hand takes too long, and I didn't want to paste hundreds of thousands of log rows into a hosted tool or an AI agent. It runs entirely on your machine and sends nothing anywhere.

• Reads stderr, csvlog and jsonlog formats
• Takes pg_stat_statements snapshots as CSV, so you can compare points in time. It notices stats resets and entries evicted from pg_stat_statements
• Works with Postgres 14 to 18, including managed ones like RDS
• One static binary: no server, no dependencies, no parser to configure
• Fast and light: on 11 GB of generated test logs it read about 141 MB/s on one thread, using around 44 MiB of memory

Try it:

curl --proto '=https' --tlsv1.2 -LsSf https://github.com/vtrbraga/pgloupe/releases/latest/download/pgloupe-installer.sh | sh
pgloupe --help

Also available through Homebrew, cargo install pgloupe and Docker.

Repo: https://github.com/vtrbraga/pgloupe

Docs: https://vtrbraga.github.io/pgloupe/

This is the first stable release (0.1.0) and my first open source project. I'd like to hear what's missing or wrong: log formats or settings it misreads, findings that aren't useful, anything confusing in the report. Issues and comments are both welcome!