I recently moved our 3dp vendor vector database (100M+ vectors) to neon using the lakebase_vector and lakebase_text extension to do hybrid search. Did not observe huge changes in retrieval performance. Interesting bit: the ann side uses ivf+ rabitq quantization instead of hnsw (probably since graph indexon object storage didn't work really well), bm25 is actual block-max wand and both seem quite performant.

Curious to know if anyone else has moved their vector indices to postgres and seen any performance bottlenecks at a larger scale

There is a line of database research (VBASE at OSDI '23, Chimera in PVLDB, AkasicDB at SIGMOD '26 here) arguing that vector search, graph traversal, and relational filtering belong in **one engine with one query plan**, where the top-k is enforced *during* execution so intermediate results never blow up. AkasicDB is the full tri-modal version of that idea. It is also closed. You cannot download it.

So we rebuilt the design in the open, on Postgres. That took three pieces:

* **pgvector** for the vector leg (unmodified).

* **A native graph access method**: adjacency lists in custom-formatted pages through the buffer manager, WAL-logged with GenericXLog. Deliberately *not* an edges join table; topology gets its own storage, but it lives in the same process, same transaction manager, same WAL as everything else.

* **A fused operator extension** that streams ANN candidates out of pgvector's index and applies graph reachability plus relational predicates per candidate, with VBASE-style early termination. One call, one plan, no cross-system round trips.

The part we did not expect: we started on the lineage's research fork (Microsoft's MSVBASE, PG 13.4) to prove the mechanism, then re-homed everything onto stock PG 17. **Stock Postgres ran the identical fused query 2x faster than the fork** (0.27 ms to 0.14 ms at matched recall). We retired the fork as a launch vehicle on the spot.

Numbers, all with published method docs and one-command repros: 23.7x vs a tuned Milvus + Neo4j + Postgres stack at matched recall on a 1M corpus; injected mid-write failures tore the multi-store 42 out of 42 times vs 0 for one WAL. And the honest one: plain pgvector + a links-table CTE inside one Postgres comes within 16 microseconds of our operator on anchored queries. We published that too. Most of the win is one-system-vs-three; Postgres itself was never the bottleneck.

MIT, spec and evidence docs in the repo: https://github.com/ConsultingFuture4200/tridb

Edit: Hey guys, my name is Dustin, I'm new to software development and have been learning through the "project based learning" method, using a stack of Frontier and local LLMs as my build partner and tutor. All of this code was generated as a result of extensive Spec Driven Development. A group of people far more experienced than I built the inspiration for this project, and I set out to see if I could recreate their solution.

Any and all feedback and project contributions are welcome

Hey PostgreSQL community!

I'm Kevin, the maker of FluentDB.

I have been working with Postgres for years and using many Postgres client apps, but none of them have satisfied me lately.

So I built the tool I wanted: a native macOS database client that is fast, lightweight, and AI-driven from the ground up.

A few things:

• Native Swift app designed for speed (not another Electron super-slow app).
• Bring your own AI: OpenAI, Anthropic, Claude Code, Codex, or local models with Ollama.
• Privacy by default: AI only sees your schema unless you explicitly approve sending data. Every AI-generated SQL query also requires approval by default.
• Support for PostgreSQL (of course) and others (SQLite, MySQL, SQL Server, and more)

FluentDB is still in its early days, and I'd genuinely love your feedback.

It's 100% free to try, no credit card required, and I am more than happy to distribute coupons for those who actually want to use it further!

For now, it only supports macOS 26+, but I am actively working on a port for Linux and Windows.

Can't wait to get your feedback :)

TLDR; Migrata is a CLI that lets you inspect, diff, and apply changes using plain SQL. It's like Terraform, but for your database.

I built this tool because of the pain I experienced working with migration files at my job. These databases are shared across many teams and even more applications.

Like many organizations, we follow a file-based migration approach: a folder for each change set, files with targeted changes per component, all tracked in Git. There are strict processes for approvals and promotions. The problem is that this approach results in thousands of migration files piling up over time. To mitigate this, we have a change approval board whose sole job is to coordinate with database admins to apply these scripts.

Most of their time is spent understanding what's changing and if there are any conflicts. It's not uncommon for two separate scripts from different change sets to modify the same things. Centralizing these changes would drastically lower the time needed to review and approve them.

After working with Terraform for years, I knew there were better ways to track state and apply changes. But unlike the cloud, databases already have a schema language to describe their state: plain SQL. Instead of inventing a new DSL, my tool treats your SQL schema as the source of truth and diffs against it directly.

In practice, this gives you the best of both worlds: readable SQL and a declarative schema, with each component defined once. Changes are centralized, not scattered across layers of migration files.

I've been working on this particular project for the past 17 months on evenings and weekends, and finally feel like it's at a state where it's good enough to share

Core Feature:

• Comparing two schema sources and generating a clear diff
• See exactly what will change before anything is applied
• Manually approve and apply changes

How it works:

1. Run the "inspect" command to inspect your live database schema and sync it to your local file system.
   1. This does deliberate scans on your target schemas; no psql dumps needed.
2. Make a single or multiple changes in your local editor
3. Run the "diff" command to detect differences and generate a plan in the console.
   1. Internally, we build up a dependency graph so we know how and when to apply a change and who's affected.
4. Manually approve and apply changes

Features:

• Better Diffs
  • This tool shows the user modifications and query plans side-by-side. You always know where a change is coming from and why. Traceability is paramount
• Impact Analysis:
  • The diff includes a list of downstream components affected by a change
• Risk Classification:
  • Every planned migration is labeled Safe, Warning, or Destructive so you can assess impact at a glance. Warnings and destructive changes are summarized separately with counts.
• Full column lifecycle management:
  • Changes to columns are analyzed, and safe multi-step patterns are used to transform columns. Indexes & constraints are always preserved after changes
• Custom Organization:
  • You can organize your schema locally however you like (single file or deeply nested directories). It loads everything and automatically builds a dependency graph no matter how you organize your schemas
• Local Validation:
  • The tool will spin up an ephemeral database in docker where it can execute the migration plan before running against your live database.
• Advisory Locks:
  • Concurrent migration protection using PostgreSQL advisory locks

Security Features:

• No email or account required to use it
• Fully Local (No LLM's or calls to third-party services)
• Privacy-first (your schema never leaves your computer)

Who's this for:

• Anyone who values working with declarative workflows
• Developers who prefer working with plain SQL because they need full control
• Teams with databases shared across multiple applications and languages

What's in it for me:

This is a free tool to use without any account or limits. My goal is to build a tool that's so useful, that it would be a no-brainer to adopt into your stack. The end goal is to get adopted by businesses who use this tool and want to pay for advanced auditing, governance, and collaboration features.

Wait, isn't this just like the atlas cli?

Yes, this is a direct competitor to the tool, except I don't believe in gating critical features behind paywalls. Better tooling means fewer outages and safer deployments for everyone.

If you want to know more about what sets migrata apart, I've written a whole blog post outlining the high-level differences:

https://migrata.io/blog/migrata-vs-atlas

Here’s the site if you want to learn more:

https://migrata.io

And if you want to learn more about me, you can visit the about page, where I have my background and links to my personal LinkedIn and Github Profile. I'm proud to stand behind my work

https://migrata.io/about

i want to learn postgresSql, but i don't know which content or resource should i go with. please help me to find free resources for postgressql for dev

A few months ago I realized I'd never actually tested one of my backups.

The backups were completing successfully, but I'd never restored one into a clean environment to see if it was actually usable.

That bothered me enough that I built a small MIT-licensed CLI to automate the process.

The flow is simple:

- restore the latest backup into a disposable Postgres container
- verify schema, table counts and basic integrity
- sample-check stored files
- destroy the environment

I'm mainly looking for architecture feedback, not product feedback.

There are three design decisions I'm still unsure about:

1. If the restore environment is missing an extension, should the drill fail outright, or report that the backup itself is probably fine but the verification environment isn't?

2. File verification defaults to checksum sampling because reading every object back can be expensive. Would you make full verification the default instead?

3. Beyond "restore succeeded", what additional checks would give you confidence that a backup is actually recoverable in production?

For example:

- application smoke tests?
- row-level checksums?
- custom SQL assertions?
- something else?

If you've built or operated restore verification at scale, I'd really appreciate hearing what worked (or what failed).

GitHub:
https://github.com/backupdrill/cli

(Full disclosure: this is also the restore engine behind a hosted product I'm building, but the CLI itself is MIT and works independently.)

Every new serverless infra company is launching with Postgres and ONLY Postgres! (Some exceptions are KV stores)

Koyeb (wraps Neon), Render, railway, Vercel, fly.io amongst sooo many others. Nobody’s shipping mysql, mongo, first it’s always pg.

What are your thoughts ? Is it the diversity of use cases of covers? The extensibility?

Hello Reddit friends,

I am a rube and trying to learn SQL and need some guidance on dealing with this persistent error. Whenever I try to open PostgreSQL or pgAdmin 4 I get this fatal error. I've tried opening Run and deleting pgAdmin there, adding C:\Program Files\PostgreSQL\<VERSION>\bin to the Path folder with no success. I've also followed Youtube videos on this and it will not budge. Any assistance is highly appreciated.

I host a Postgres podcast called Talking Postgres, and I recently recorded a conversation with Panagiotis Antonopoulos, a Distinguished Engineer who spent 13 years working on SQL Server before moving onto Postgres.

One thing that surprised me was how little of the conversation was about "which database is better."

His perspective was that the concepts are extremely similar. Transactions, storage, & more—the high-level knowledge transfers surprisingly well.

A few things you might find interesting:

• The architectural cleanliness of the Postgres codebase.
• How LLMs make it easier to understand the years of design discussions which are publicly available.
• He shared his perspective on why Postgres has become the default answer for so many workloads and why more people seem to be asking, "Why not Postgres?"
• We also talked about shared-storage architectures and some of the work he's doing in Azure HorizonDB.

One quote that stuck with me:

"That was a shocking experience for me. I could understand new areas in Postgres much faster than I could for SQL."

For people who have worked across multiple database systems (Oracle, SQL Server, MySQL, Postgres, etc.), I'm curious whether you've had a similar experience—or a completely different one.

Podcast/transcript here if anyone is interested: https://talkingpostgres.com/episodes/working-on-postgres-after-13-years-on-sql-server-with-panagiotis-antonopoulos

This has been lately my favorite command from the xata CLI. Basically it creates a branch on the fly and runs the psql session. When you exit psql, it deletes the branch.

This means you (or your agent) have no way of impacting the main branch, it's a completely safe way to query the DB and test various things.

Postgres migrations that look harmless but take an ACCESS EXCLUSIVE lock (non-concurrent index builds, NOT NULL + default on older versions, column type changes, etc.) are a recurring fear for me — especially now that AI tools generate a lot of them and they read fine in review.

For those running Postgres at real scale: - How do you catch these before they lock a big table in prod? - Do you run migrations against a prod-sized copy first, or rely on review + tools like a linter? - Any war stories where one slipped through?

Trying to understand what people actually do in practice vs. in theory.

So I'm interviewing at tiger data and the recruiter recommended me to use the product a bit as they have a free version. This role is a customer facing role. Now some background about me.

I have a lot of high level technical understanding across many areas. I learn very quickly and know SQL basics. I spent the day importing a data set into tiger data. But I struggled to see what I could do with it. Sure I can use queries to manipulate and expand the data but that isn't exciting.

So I found a roll, ReTool and made a webpage dashboard with the data. The data itself is my job searching. I built a dashboard with charts and graphics, statistics, etc. Similar to Looker Studio. But I like Looker, ReTool can write to the database. So I moved all of my workflows to it.

Now I have an application form built in, an interview logging flow (added the tables to the DB). Automated reminders and triggers and the ability to edit the data easily. I feel like this would be a good practical use for me and show off my skills. But most of the work was built by using AI to write the code I needed. I can edit basic java, HTML and SQL syntax but I can't really write and articulate it from scratch.

I'm curious if I went the wrong direction and they'll be more interested in me in a terminal or using the UI and writing long queries to pull and manipulate data. Whereas the UI has built in queries that were developed by AI for me.

We are benchmarking PostgreSQL for our OTLP workload with following setup:

• cloud deployment with primary in different AZ than standby,
• synchronous_commit=on,
• network latency between AZs is <1ms,
• 64 vCore machines,
• connections pooled via client side library
• one transaction = one insert/update

For 15k sustained inserts/sec + 15k updates/sec on the same table observed e2e latencies for both operations were in the range of 15-25ms.

When we tried to generate 20k inserts/sec + 20k updates/sec avg latencies went to 60-70ms and observed throughput couldnt reach target goal, we could process roughly 16-18k of both inserts + updates per second (simultaneously).

At first we thought maybe WAL flushing on primary is bottleneck but analyzing pg_stat_activity showed there are hundreds of sessions at any given time waiting on SyncRep event (both IPC and LWLock).

After disabling replication latencies went down to ~5ms (10x improvement!) and we reached stable 20k inserts + 20k updates per sec.

Is such latency impact of synchronous replication expected? This is cloud managed PostgreSQL so I have no visibility into standby metrics but looks like primary without replication easily handles such throughput, but with sync rep it starts to struggle.

Hi all, this is Burak. I have built an open-source CLI tool that allows replicating data from Postgres CDC changelog into 20+ destinations: https://github.com/bruin-data/ingestr

The overall idea is that:

• You have your prod postgres DB
• You want to replicate them to analytical databases for analytics purposes, e.g. to Snowflake, BigQuery, Databricks, or Redshift
• You have two ways:
  • You can either run a batch load using tools like ingestr, Airbyte, or Fivetran
  • If you cannot run batch workloads for some reason, e.g. due to the latency requirements, or not having proper cursor columns, you need to run CDC replication using tools like Debezium and Kafka

The problem with CDC using those tools is that they require a buy-in into their ecosystem, which is generally quite invasive, such as being able to run Debezium only with Kafka reliably, or having to deal with their Java client libraries if you ever wanted to integrate them elsewhere, tolerate their high resource requirements, etc.

I never liked running them on production. We have been working on ingestr for quite some time already for batch sources, and CDC became an obvious target.

ingestr has quite a few niceties:

• You don't need any extra services or tooling to run it: just put your credentials in the URI, and you are good to go.
• It is a simple and fast Go binary that runs anywhere, even in your GitHub Actions pipeline.
• It supports both batch and streaming modes in the same binary, which allows changing the deployment modes as your requirements grow. Run locally, deploy on Airflow, or put it in an EC2 server in a streaming mode if you want to.

It is open-source, and you can run it anywhere you like.

It supports:

• PostgreSQL CDC
• MySQL CDC
• SQL Server CDC
• SQL Server Change Tracking
• MongoDB CDC

Give it a try and let me know if you have any questions!

https://gist.github.com/corporatepiyush/b12d6facac54e5eb045f12f008dacd93

1. Basic Unit of Storage
2. Relative (Related) Data Storage
3. Normalization (3NF/4NF/5NF) & Complex Joins
4. Graph / Highly Relational Data
5. Partitioning of Data
6. MVCC (Multi-Version Concurrency Control)
7. ACID
8. Unique Index (Single & Composite)
9. B-tree Index (Single & Composite)
10. Partial / Functional Index
11. Index-Only Scans
12. Text Index (Full-Text Search)
13. Wildcard Index (Dynamic Schemas)
14. Geospatial Index
15. Vector Type & Vector Search
16. TTL (Automatic Data Expiry)
17. Building Indexes Without Blocking Writes
18. Complex Computation Across Tables
19. Vertical Storage Scaling (Storage Layout Control)
20. Storage Compression
21. In-Memory Tables
22. Views
23. Materialized Views
24. Spill to Disk When Query Exceeds RAM
25. Custom Functions (UDFs)
26. Stored Procedures
27. Queue / Topic for Pub-Sub
28. Query Cost Analyzer
29. Replication
30. Cluster / Sharding Setup
31. File / Object Storage (Large Binary Data)
32. Working with Record Files (CSV, JSON, Parquet, Arrow, Avro & Binary Formats)
33. Columnar Storage
34. Time Series
35. Parallel Query Execution
36. Engine Extensions / Pluggability
37. Connection Model
38. Memory Cache Architecture
39. WAL / Journaling / Durability
40. Network Compression
41. Production Hardening & General Maintenance
42. Architecture Summary — Capabilities and Limits
43. Hard Limits and Size Ceilings
44. Exclusive Features

Lakebase is Databricks' managed Postgres. It has copy-on-write branching, a point-in-time fork of a database you can write to on isolated compute, then throw away. Wrote this up because it made one workflow I worked on much cleaner so thought it might help someone else in the community.

My challenge was adding a NOT NULL column + backfill to a big orders table. It behaved fine on seed data, but I didn't actually know about lock duration or backfill time until I had prod-shaped rows.

My model: Project -> Branch -> Endpoint. A branch is a CoW (copy on write) snapshot of another branch - no upfront storage duplication you pay only for what diverges. New branches have no compute, so you create an endpoint when you need to connect.

Steps:

# fork prod

databricks postgres create-branch projects/my-app dev \

--json '{"spec": {"source_branch": "projects/my-app/branches/production", "no_expiry": true}}' -p prof

# attach compute (0.5 CU min, scales to zero when idle)

databricks postgres create-endpoint projects/my-app/branches/dev read-write \

--json '{"spec": {"endpoint_type": "ENDPOINT_TYPE_READ_WRITE", "autoscaling_limit_min_cu": 0.5, "autoscaling_limit_max_cu": 2.0}}' -p prof

Connect + run it (direct psql with a 1h OAuth token; databricks psql doesn't work on the autoscaling tier):

HOST=$(databricks postgres list-endpoints projects/my-app/branches/dev -p prof -o json | jq -r '.[0].status.hosts.host')

TOKEN=$(databricks postgres generate-database-credential projects/my-app/branches/dev/endpoints/read-write -p prof -o json | jq -r '.token')

EMAIL=$(databricks current-user me -p prof -o json | jq -r '.userName')

PGPASSWORD=$TOKEN psql "host=$HOST port=5432 dbname=shop user=$EMAIL sslmode=require" -c "

ALTER TABLE orders ADD COLUMN region VARCHAR(20);

UPDATE orders SET region = 'unknown' WHERE region IS NULL;

ALTER TABLE orders ALTER COLUMN region SET NOT NULL;

"

It helped me work with isolated compute, left prod untouched. I was able to time the backfill, saw the single big UPDATE was a problem and switched to a batched one, then re-ran on the same branch.

Cleanup: databricks postgres delete-branch projects/my-app/branches/dev -p prof — cascades to endpoints, diverged storage goes away.

Hope this helps someone else!

Thoughts on this are very welcome. I am still experimenting right now.

I am setting up a PostgreSQL replication environment with one primary server and one standby server using synchronous streaming replication.

As expected, when the standby server is available, transactions on the primary commit successfully after the WAL records are acknowledged by the standby.

However, the issue arises when the standby server goes down. In this case, transactions on the primary enter the SyncRep wait state and remain blocked until the standby comes back online. This is the expected behavior of synchronous replication, but it does not meet my requirement.

My requirement is that if the standby is unavailable, the transaction should not wait indefinitely. Instead, after a configurable timeout, I want the transaction to fail and roll back automatically, allowing the application to handle the failure rather than remaining blocked.

I have looked for a way to configure a timeout specifically for the SyncRep wait, but I have not found any suitable option.

Is there a PostgreSQL configuration or mechanism that allows timing out the SyncRep wait and automatically rolling back the transaction? If not, are there any recommended approaches or workarounds to achieve this behavior while still using synchronous streaming replication? Edit: Alredy tried statement_timeout, it's not working chatgpt says it works for actively executing SQL statement.

I work on an ERP software called ERPNext. Currently, we use MariaDB with REPEATABLE READ . We have been working on adding Postgres support to it but we have reached a roadblock.

Recently on our cloud platform we updated to MariaDB 11.8 from 10.6. Post that we received a barrage of support tickets of people complaining of snapshot violation errors. Now given the number of tickets and the severity of something like an ERP software not functioning ideally and the constant nagging of enterprise customers, we just turned off snapshot isolation for now.

Now with Postgres and REPEATABLE READ , there is no option like MariaDB to just turn off snapshot violation errors. We believe once Postgres support hits production, we are again going to be hit with another set of similar serialization errors.

Initially, I recommended to use READ COMMITTED but senior engineers at the company shot it down, the reason being:

1. Our entire codebase is built with REPEATABLE READ in mind.
2. If it does not work, debugging issues stemming from READ COMMITTED will be very hard to debug.
3. READ COMMITTED has its own set of problems like gap locks, phantom reads etc.
4. Most business apps use REPEATABLE READ as an industry standard.

They instead suggested retrying transactions with jitter but I honestly feel READ COMMITTED is infact better suited in general for a highly concurrent ERP like ours. Note that we have implemented row locking everywhere it was warranted.

I am looking for confirmation of my theory from the community.

1. I found only 2 ERPs using REPEATABLE READ - Microsoft Dynamic 365 Business Central and Odoo. Rest are mostly READ COMMITTED only.
2. I have also implemented Advisory Locks to counter this problem but I don't know how effective will that actually be.
3. Claude and ChatGPT also both suggest READ COMMITTED as well.
4. Is READ COMMITTED actually a better solution or should we go with retrying transactions?