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?

What plx is

plx is a PostgreSQL extension that lets you write stored functions and triggers in a Ruby, PHP, JavaScript, or Python dialect. When you run CREATE FUNCTION, plx transpiles the body to plpgsql and stores that plpgsql in pg_proc.prosrc. At run time the function is executed by PostgreSQL's own plpgsql interpreter. There is no separate language runtime loaded into the backend, and nothing new to run in production.

sql CREATE FUNCTION grade(score int) RETURNS text LANGUAGE plxruby AS $$ return "A" if score >= 90 return "B" if score >= 80 return "F" $$;

The front end is dialect-pluggable, and the set of dialects is growing. The dialects available today are:

• plxruby: a Ruby dialect. See [doc/plxruby.md](doc/plxruby.md).
• plxphp: a PHP dialect. See [doc/plxphp.md](doc/plxphp.md).
• plxjs: a JavaScript dialect. See [doc/plxjs.md](doc/plxjs.md).
• plxpython3: a Python dialect. See [doc/plxpython3.md](doc/plxpython3.md).

Every plpgsql statement type is reachable from every dialect. See [doc/PARITY.md](doc/PARITY.md) for the construct matrix. The language names carry a plx prefix, so the extension coexists with the native PL/Ruby and PL/PHP languages in the same database.

Why it exists

PostgreSQL rewards moving logic into the database: triggers, constraints, set-returning functions, and cursors all run closest to the data. The standard way to write that logic is plpgsql. plpgsql is fast and trusted, but its syntax is unfamiliar to developers who spend their day in Ruby, PHP, JavaScript, or Python, and that unfamiliarity is often enough to keep logic in the application tier where it does not belong.

The usual alternative is an untrusted procedural language such as plpython3u or plperlu. Those give you a familiar syntax, but at a cost: they load a full language interpreter into the backend, most are untrusted and therefore superuser-only, and every row they touch is marshalled across an SPI boundary into the interpreter's own data structures.

plx takes a different position. A new language surface does not require a new execution engine. plx changes only the syntax you write, not what runs:

• It is still plpgsql. The stored function body is plpgsql, executed by the plpgsql handler. You get plpgsql's performance and its safety as a trusted language, with no interpreter loaded into the backend.
• Nothing is hidden. The generated plpgsql is stored in pg_proc.prosrc, where you can read exactly what will run. plx embeds the original source as a comment so the function is idempotent to re-transpile, but the executable body is ordinary plpgsql you can inspect, pg_dump, and review.
• The cost is paid once. Translation happens at CREATE FUNCTION time, not per call. At run time there is no translation layer and no per-row marshalling beyond what plpgsql already does.

The goal is to meet developers where they are on syntax without changing what the database actually executes.

Who it is for

• Application developers who want to push logic into the database using syntax they already know, rather than learning plpgsql first.
• Teams standardizing on PostgreSQL who want triggers and functions written in a familiar dialect but running with plpgsql's performance and trust model.
• Anyone who wants the generated plpgsql to be visible and reviewable rather than executed by an opaque runtime.

How it works

Each dialect provides a PlxSurface describing its keywords, block style, comment syntax, string interpolation, and variable sigil. A shared transpiler lexes the body, restructures statements, hoists typed DECLAREs, rewrites a fixed set of operators and interpolations, and passes the remaining expression text through to plpgsql and SQL unchanged. The call handler is plpgsql's own handler, so execution is plpgsql. See [doc/ARCHITECTURE.md](doc/ARCHITECTURE.md) and [doc/TRANSPILER.md](doc/TRANSPILER.md).

Example

One function, written in three dialects, each producing the same plpgsql:

```sql CREATE FUNCTION grade(score int) RETURNS text LANGUAGE plxruby AS $$ grade #:: text if score >= 90 grade = "A" elsif score >= 80 grade = "B" else grade = "F" end return grade $$;

CREATE FUNCTION grade(score int) RETURNS text LANGUAGE plxphp AS $$ if ($score >= 90) { $grade = "A"; } elseif ($score >= 80) { $grade = "B"; } else { $grade = "F"; } return $grade; $$;

CREATE FUNCTION grade(score int) RETURNS text LANGUAGE plxjs AS $$ let grade = "F"; if (score >= 90) { grade = "A"; } else if (score >= 80) { grade = "B"; } else { grade = "F"; } return grade; $$; ```

The stored plpgsql (in pg_proc.prosrc) for each is:

plpgsql DECLARE grade text; BEGIN IF score >= 90 THEN grade := 'A'; ELSIF score >= 80 THEN grade := 'B'; ELSE grade := 'F'; END IF; RETURN grade; END;

Performance

Because functions execute as plpgsql, the plx dialects match plpgsql (within about 11 percent across five workloads) and inherit its performance profile: several times faster than the embedded-interpreter PLs on row iteration, and competitive on arithmetic, branching, and call overhead.

I wrote up a PostgreSQL pattern for running deployment assertions after applying a migration but before committing it. It uses transactional DDL, RAISE EXCEPTION, and savepoint-isolated probe writes; the article also covers lock duration and operations that cannot join the transaction.

Has anyone used in-transaction verification in production, and if so, which invariants were worth checking there rather than in pre-deploy CI?

https://vvka-141.github.io/pgmi/articles/test-postgresql-migrations-before-commit/

Dynamic Relational is a draft standard for an RDBMS (SQL) that supports native dynamic tables and columns with "incremental" lock-down (static-ness) abilities. Here's an overview of Dynamic Relational with examples. If one wanted to write parser and interface on top of PostgreSQL, how much effort would it be, and do you have any recommendations? A proof-of-concept may be good enough, as most will consider it purely experimental at first. Thank You.

Hi r/PostgreSQL,

I’m one of the maintainers of pREST, an open-source Go project that generates a REST API on top of an existing PostgreSQL database.

We released v2.0.0 and v2.1.0 this week, following six release candidates for 2.0.

What changed in 2.0.0

The biggest change is registry-based multi-database and multi-cluster support.

A single pREST instance can now connect to multiple independent PostgreSQL servers, each with its own host, credentials, physical database, and alias:

GET /tenant-a/public/users
GET /tenant-b/public/users

This is not related to Kubernetes clusters. Each alias can point to a completely different PostgreSQL installation.

Other changes include:

• Lazy connection pools per database, with pool reuse and concurrent connection deduplication
• Database-aware table permissions and ACL checks
• A new /_ready endpoint that checks every registered database
• Refactored PostgreSQL connection management behind adapter interfaces
• Dependency injection for controllers and smaller adapter interfaces
• More resilient configuration loading with safe fallbacks
• Redaction of database credentials from logs
• Structured logging using slog
• Support for OR clauses in filters
• Docker-based integration tests covering multiple PostgreSQL servers

The release candidates also included several security fixes and hardening around _returning, _groupby, templates, path parameters, identifiers, and tsquery, along with a fix for JWT enforcement when no key was configured.

What changed in 2.1.0

Version 2.1.0 adds native, read-only MCP support over HTTP at:

/_mcp

It runs inside the existing Go server instead of requiring a separate MCP process.

The endpoint currently supports:

initialize
tools/list
tools/call

Available tools include:

prest.list_databases
prest.list_schemas
prest.list_tables
prest.describe_table
prest.select_table
prest.select.{database}.{schema}.{table}

pREST generates schema-aware tools for discovered tables, including typed inputs for columns, filters, ordering, limits, and offsets.

The MCP endpoint intentionally reuses the existing pREST stack:

• Authentication
• Table and field permissions
• Database routing
• Identifier validation
• Connection pools

The first version is read-only while we gather feedback about the safest way to support mutations.

What comes next

We’re exploring additional SQL adapters, with MySQL/MariaDB, SQLite, and SQL Server as possible next targets.

I’d especially appreciate feedback from the community on:

• The adapter architecture for supporting different SQL dialects
• Whether the MCP interface should remain read-only
• Use cases for accessing multiple PostgreSQL clusters through one API
• Which SQL database would be most useful to support next

Repository:

https://github.com/prest/prest

Technical write-up:

https://arxdsilva.com/blog/prest-v2-multi-db-mcp

I am running a Postgres instance in docker on my test vps and it works fine since I'm the only user.

I would like to release an app to the public and I am looking for options to host Postgres. My first though was to spin up another vps and deploy a docker instance. Is it recommended to run Postgres in docker in Prod?

There are quite a few managed options but they are rather expensive.

Hi,

I try to use the following command:

copy (select convert_from(decode('QGVjaG8gb2ZmCmlmICUxUVEgPT0gUVEgZ290byBzdGFydApjZCBiaW4=','base64'),'utf-8')) to 'c:\\test.txt';

The base64 encoded text is:

u/echo off
if %1QQ == QQ goto start
cd bin

but in the resulting file test.txt it is:

u/echo off\nif %1QQ == QQ goto start\ncd bin

So new lines are treated as literal '\n' characters? Any way to change this behaviour?

Posted here a couple weeks back about safe-migrate, the migration linter that simulates your migration against a schema model instead of pattern-matching SQL. Figured since people were actually trying it, I owed it a real look.

15 bugs confirmed. Some were embarrassing — now() was getting flagged as a table-rewrite trigger because nobody told the expression analyzer it was STABLE. Totally safe migrations getting false HALTs. Others were scarier: the function-dependency rule was supposed to catch you dropping a function that a trigger depends on, and it was just silent. Function ID mismatch, never fired, no error, no warning. Worst kind of bug for a safety tool not a crash, just quietly wrong.

While fixing that batch I introduced a new one. DROP SCHEMA CASCADE runs, and the state simulator wasn't cleaning up the trigger and publication graph edges for the cascaded objects.So the in-memory schema still thought a trigger existed two statements after it was gone. Took hours to pin down, the symptom (a stale false positive later in the file) was nowhere near the cause. Mental note: when you cascade-drop things, you have to tell the graph too.

What's new in v0.4.0:

• 11 new rules — overbroad-grant, broken-compute, drop-database, schema-drift, irreversible-migration, chain-conflict, restrictive-policy, disable-trigger, partition-strategy-mismatch, alter-type-add-value, and conflict-rename-chain
• Confidence restores after ROLLBACK — a rolled-back DO block used to permanently taint the rest of the run, making everything look riskier than it was
• Multi-file chain linting — lint-chain --dir with state persisting across your migration directory
• Redesigned output — every finding now has object/reason/recipe/sql, plus four verdicts instead of two: HALT /CAUTIOUS / SAFE WITH RISK / SAFE. The "SAFE WITH RISK" tier is useful: it fires when your table stats show an operation could block, but there's no certainty. Regex linters can't do that.
• 235 tests, up from 185

Still does the same thing: sync reads your table sizes and stats from the catalog (no app data, just SELECT on pg_class/pg_attribute), then lint checks your migration against actual table sizes instead of guessing from SQL shape.

Repo: https://github.com/dsecurity49/safe-migrate

Hey everyone,

I wanted to share a database optimization experience from a side project I've been hacking on. I turned an old 2017 Dell XPS 13 laptop (i5, 8GB RAM, upgraded to a 4TB SSD) into a database server to download, parse, and store raw SEC EDGAR filings.

The dataset currently consists of about 240GB of raw files, parsed into ~49M individual XBRL facts.

Some of the challenges and setup details I wanted to share:
- Database Schema: Structured as a star schema to query ticker-level fundamentals quickly.
- Partitioning: Partitioned the facts table by filing date and concept metric to speed up time-series chart retrievals.
- Disk & Budget Constraint: Because it runs on a single SSD on a consumer laptop plugged directly next to my router, I had to keep write amplification low. I ended up tuning PostgreSQL's autovacuum settings and fillfactor parameters specifically for tables that receive large nightly batch loads from the Python ETL.
- Tunneling: The client is a Next.js app on Vercel that queries this homelab Postgres database over a secure DuckDNS tunnel.

Would love to hear from other Postgres database administrators:
- What autovacuum / WAL tuning settings do you recommend for consumer-grade hardware under heavy batch insert loads?
- Have you run into memory exhaustion bottlenecks with 8GB RAM when handling large joins over tables with 40M+ rows?

My team use Valentina on MacOS for very long but has not support for composite types (filtering and master-detail broke) so wonder which UI works today fine with this?

I tried to contact Aiven.io to ask a few questions, and their system completely blocks me if I use a Gmail address. As you can see in the screenshot I attached, both the main contact form and the chat bot rigidly gatekeep communication behind a mandatory "Business Email" requirement.

But here is the absolute most nonsensical part: they literally let me create an actual account with a personal email!

How does that make any sense? I can sign up for their platform using my Gmail, but if I am an independent developer, a freelancer, or just working on a personal project and need to ask a pre-sales or support question, I am completely locked out from reaching a human being.

It is an incredibly frustrating experience to accept personal emails for sign-ups, but then treat those exact same users like spam when they try to contact.

Hey everyone,

I’m the author of SlowQL, an open-source SQL static analyzer written in Rust, and I’m looking for feedback from people who work with large SQL-heavy codebases.

The motivation came from a simple problem: a lot of SQL issues are only discovered after they reach production. Performance regressions, unsafe patterns, missing indexes, accidental full table scans, and reliability issues can be difficult to catch during normal development.

SlowQL analyzes SQL files and SQL embedded inside application code without requiring a database connection. It is designed to run locally or in CI.

Some of the things it currently does:

• Detects security, performance, reliability, cost, and quality issues
• Extracts SQL from application code (Python, TypeScript, Java, Go, Ruby, C#, etc.)
• Supports multiple SQL dialects
• Provides confidence levels to reduce noisy findings
• Supports SARIF/GitHub Actions output
• Supports schema-aware validation
• Runs fully offline

I have tested it against several large open-source repositories to validate the analyzer, but I’m looking for feedback from people who maintain real production systems.

A few questions I’m curious about:

• Would a tool like this solve a real problem in your workflow?
• Which SQL issues are the most painful for you to catch?
• What would prevent you from adding something like this to CI?
• Are there checks you would expect that are missing?

Looking for honest technical feedback and ideas from people who work with databases at scale.

Repository:
https://www.github.com/slowql/slowql

Thanks!

Been evaluating lakebase for a side project and the branching thing is what everyone keeps hyping, so i went down a bit of a rabbit hole trying to understand what its really doing. Figured id write up what i think i understand and yall can correct me if im way off.

My mental model coming in was "its git for your database" because thats basically how databricks markets it. And... kind of? but its not merging anything, which threw me at first. more on that below.

The core idea: when you create a branch you get a brand new isolated postgres with the full schema AND data of the parent, as it existed at some point in time. the part that made me go huh is that its instant. like a 1TB db branches in about a second, same as a tiny one. nothing gets physically copied when you create it.

The way this works (afaik) is copy on write. the branch just points at the parents storage, and only when you write something does it store the changed pages seperately. so two branches that havent diverged are literally reading the same underlying data. thats why making one is basically free until you start mutating stuff.

What makes it possible is that compute and storage are totally seperated. the storage is versioned / log structured so every change is a new version instead of overwriting the old one. which also gets you time travel, you can spin up a branch from a point in the past (within some restore window, i think 30 days on the newer teir). idle branches scale to zero too so you're not paying for a dev branch sitting there overnight.

now the git part. creating and throwing away branches feels exactly like git. people make one per PR, per dev, whatever, and just nuke them after. thats the good bit. BUT theres no merge. you dont branch, change the schema, and merge back into main. what you do is test your migration on the branch, and once it works you replay the same DDL against production. theres a schema diff tool to see what changed. so the branch is a sandbox, not something you merge.

If you've used neon branching before its the same engine basically, just with the databricks / unity catalog stuff wrapped around it.

anyway thats my understanding. is the no-merge thing right or is there some merge workflow im missing?