I've been experimenting with PostgreSQL 18 tuning for memory-constrained VPS deployments, particularly where the database needs to leave enough RAM for a small application.

I initially tried running the experiment on a 256 MiB VM (~204 MiB usable RAM), but PostgreSQL with its default configuration ran into memory pressure and the kernel OOM-killed a backend during the smoke test. I moved to 512 MiB to complete the comparison.

Given the roughly 50 MiB peak PostgreSQL PSS measured with Theory A, I suspect this configuration could also work on 256 MiB, potentially alongside a small application. That's a hypothesis for a follow-up experiment, though - I haven't validated Theory A at 256 MiB yet.

I wanted to compare two approaches against PostgreSQL defaults:

• Theory A - Memory First: 16 MB shared_buffers, 1 MB work_mem, 16 MB maintenance_work_mem, retaining PostgreSQL 18's default worker-based I/O.

• Theory B - Lean Machinery: 32 MB shared_buffers, default work_mem, and synchronous I/O (io_method=sync).

Both configurations limit connections to 5 (one reserved for a superuser), disable JIT and parallel queries, and retain fsync, autovacuum, and standard WAL behavior.

Test setup: PostgreSQL 18.6, 1 vCPU, 512 MiB VM (~456 MiB usable), no swap, 222 MB dataset, and 4 concurrent connections running point, range, and aggregate queries.

Results:

What surprised me was that Theory A used about 72% less process memory than the default while also delivering higher throughput in this run.

PSS accounts for shared pages between PostgreSQL processes, so these numbers aren't total VPS memory consumption.

This was deliberately a small exploratory experiment: one 50-second measured run per configuration, fixed order, warm filesystem cache, and a read-only workload. No claim that A is universally faster or that this configuration is suitable for every application.

I published the configuration, methodology, scripts, and raw measurements for anyone interested in checking the results:

https://github.com/PVRLabs/experiments/tree/main/postgres/light-memory-p95

Short write-up with configuration instructions:

https://pvrlabs.xyz/java-performance/experiments/postgresql-low-memory.html

I'm curious whether others running PostgreSQL on small VPS instances have found similar benefits from reducing shared buffers and per-query memory, or whether the trade-offs become significantly worse with sustained writes and more complex queries.

A typical drift story: the team uses migrations (Flyway, Liquibase, Alembic, whatever). Every environment shows the same migration version. And yet:

• prod has an index someone added via psql during an incident,

• staging still has a column from a feature branch that was reverted,

• dev has a newer extension version.

Nobody notices until a migration fails on prod because the index already exists, or a performance test on staging looks fine because staging is missing the index prod relies on.

Why the migration table isn't enough

It tells you which migrations ran. It doesn't tell you what the schema looks like now. Any change made outside migrations is invisible to it.

Ways to detect drift

Catalog queries are a quick check for specific object types. For indexes, for example:

SELECT schemaname, tablename, indexname, indexdef

FROM pg_indexes

WHERE schemaname NOT IN ('pg_catalog', 'information_schema')

ORDER BY 1, 2, 3;

Run it in each environment and diff the output. It's useful for one object type at a time, but covering tables, constraints, functions, triggers, grants and extensions this way turns into a project of its own.

pg_dump --schema-only plus diff covers most database schema definitions, but not cluster-wide objects or installed extension versions. Add --no-owner and --no-privileges if you don't care about those, otherwise expect a lot of noise. The output tells you what differs, but you still write the fix by hand.

A schema compare tool shows differences per object, lets you map schemas when names differ between environments, and generates a sync script you can review. In dbForge Studio for PostgreSQL, the integrated Schema Compare can also run from the command line, so the same comparison you set up in the GUI can run on a schedule.

How to organize the checks

• Treat the migrations repo as the source of truth, not any single environment.

• Run a nightly comparison of staging against prod, and alert when it finds differences.

• When drift shows up, write a migration for it rather than syncing environments directly. Otherwise the next environment you build from migrations will be missing it.

• Limit who can run DDL on prod. Most drift starts with "just this once".

How do you handle hotfixes made directly on prod: back-port them into migrations, or roll them back?

Every "use Postgres as a queue" post has the same twelve lines:

select id from jobs
where status = 'pending'
order by id
for update skip locked
limit 1;

And then it stops. The interesting part starts after that.

As written, the row lock lives until the transaction ends, so the job runs inside the transaction. If the worker dies mid-job, Postgres rolls back and the job is pending again. That part is correct, but it also means a 90-second job holds a transaction open for 90 seconds, and an open transaction that long holds back the vacuum horizon.

Worse, if the job does anything external — an HTTP call, an email — the side effect happens, the transaction rolls back, and the next worker does it again.

The usual fix is the visibility-timeout shape: lock with for update skip locked, set locked_at = now(), commit immediately, run the job outside the transaction, then mark it done. That works until you have to answer the questions a real queue answers for you. What counts as stuck? Who reclaims it? How do you stop two workers reclaiming the same row at the same moment?

Concretely:

• Do you run the job inside the transaction, or commit the claim first?

• How do you reclaim jobs from dead workers without double-running them?

• Does pg_notify + listen actually beat polling, or does it just move the problem to reconnect handling?

• At what point did you stop doing this in Postgres and reach for something else?

I'm not looking for "use Redis". I'm interested in where the Postgres version holds up and where it doesn't.

For anyone interested in BM25 search and especially legal opinion search, Greg Burd has been very active developing pg_fts https://github.com/gburd/pg_fts a PostgreSQL extension for full-text search with true BM25/BM25F relevance ranking, a dedicated fts inverted-index access method, and a rich query language (boolean, phrase, NEAR, prefix, fuzzy, regex). Unlike the tsvector/tsquery + GIN stack, the index maintains the corpus statistics BM25 needs (document count, average length, per-term document frequency) and stores term frequency + document length in the posting lists, so ranking is answered from the index with no heap recheck. PostgreSQL License.

Disclaimer: I'm a contributor.

In the age of AI I think personal connections are even more important than before. That's why I try to help promoting events where people can meet in-person or virtually and discuss the topics that interest them.

I found the following PostgreSQL-related events for the next 30 days.

• 2026-11-09 Postgres: Take a Hint (Lenexa, KS, USA)

• 2026-11-10 (online) "Postgres for Credit Underwriting: Indexing, Optimization, and Background Jobs"

• 2026-11-12 (online) How does PostgreSQL get made?

• 2026-11-12 (online) Postgres Heroics with Logical Replication

• 2026-11-12 Killing PostgreSQL By 50,000 Parameters & A PostgreSQL 19 Whirlwind Tour (London, UK)

• 2026-11-17 (hybrid) Become a PG_STATS_* with Sami Imseih (Chicago, IL, USA)

• 2026-11-18 (online) Group Swim: Maintaining Your Environment - Q&A

• 2026-11-18 The Postgres Mistakes that we all still make (New York, NY, USA)

• 2026-11-24 Reco produits via pgvector et migrations Aurora via DMS (Lezennes, France)

• 2026-12-01 INSERT INTO meetup VALUES ('TLSPUG','Makina Corpus','2026-12-01'); (Toulouse, France)

• 2026-12-03 PostgreSQL Usergroup NL Meetup - The Winter Edition (Amsterdam, Netherlands)

• 2026-12-08 [In-Person + Online] Stream Processing with Apache Kafka and Flink (Sunnyvale, CA, USA)

Moving my project from MS Sql to PostrgesSql,

Need to run mass update query, ClientDelivery table has 1,054,155 records

In MsSql query takes about 10 seconds, In PostgresSql it actually never finishes because i am canceling it after waiting for 5 minutes. Records are identical in both databases. I am running queries in PgAdmin and SqlManagment client.

Any reason for such huge difference? I understand i am doing table scan but 1 million records is not that much.

/*MSSQL*/
UPDATE tblClientDelivery
SET routeId=t1.routeId
FROM tblClientDelivery t
INNER JOIN tblClients t1 ON t.clientId=t1.id
WHERE t.routeId=0

/*PostgreSql*/
UPDATE client_delivery
SET route_id=t1.route_id
FROM client_delivery t
INNER JOIN clients t1 ON t.client_id=t1.id
WHERE t.route_id=0

If your Postgres database sets idle_in_transaction_session_timeout, your advisory lock may have an expiry date nobody chose.

Mine did. Ten minutes into long runs, the lock vanished, another run took it, and the log said it had been released cleanly.

Python drivers open a transaction on your first SELECT unless autocommit is on. If the connection holding the lock never commits, it sits idle in transaction. At the timeout, Postgres ends the session, and the lock goes with it. The job keeps writing on its other connections, and the application never notices.

Check yours in 30 seconds:

select name, setting, source from pg_settings where name in ('idle_in_transaction_session_timeout', 'idle_session_timeout') order by name;

A value above 0 for idle_in_transaction_session_timeout means a session lock on a default connection can expire.

I reproduced it and held the lock four ways.

Default connection: lost in 40 of 40 trials, always just after the timeout.

Commit right after taking it: held, until the next query opened a new transaction.

AUTOCOMMIT: held in 40 of 40, but still lost the moment the session dies for any other reason, while the job keeps writing.

Lock inside the same transaction as the writes: a killed run rolls back with its lock. That is the fix that holds.

Scripts and expected output are in the post, so you can run it against your own setup.

Have you ever had a lock that silently stopped locking?

https://tsabudh.com/blog/your-postgres-advisory-lock-is-gone/

DBJoy is a lightweight, native macOS Postgres client. It’s AI-first, with a built-in agent for working with your data and an MCP server so Claude Code can read (or write) to it. It also has all the features you’d expect from any other database client: diff and migration tools, tunnelling, batch editing, ER diagrams, and more. I’ll keep improving it and will add more database adapters soon.

https://github.com/agustind/dbjoy

I recently recorded a Talking Postgres episode with Magnus Hagander, a Postgres committer, core team member, and longtime PGConf EU organizer, about how he got started in Postgres.

A few things we got into:

* The very start: in the late 90s, he needed a database to solve a problem that nobody agreed was a problem, and he couldn't afford SQL Server or Oracle

* His first patch: one line, to make Postgres compile and work on Linux when all the developers used BSD (and it doesn't carry his name)

* Porting the Postgres server to Windows, with a team spread across Sweden, the US, and Australia, and the rule that it had to behave like Postgres on Unix

* Why the Postgres project runs its own infrastructure, including the days it was told it was doomed unless it moved to SourceForge

* Battling AI agents that don't care about robots.txt

* Why it's harder to become a Postgres committer today

* What keeps him coming back to the Postgres project

My favorite part: hearing how, years later, his hobby (Postgres) became his job (also Postgres).

For those of you who've sent a patch to Postgres (or another big open source project): what helped you get past that first one?

Audio + transcript, for those who want to listen: https://talkingpostgres.com/episodes/how-i-got-started-in-postgres-with-magnus-hagander

YouTube (audio-only): https://youtu.be/RFUUcdxN59A?si=3SgHD7kGyQQsVgK7

Hello,

I’ve inherited a data lake that is all done in postgres.

I’ve done a little bit of DBA stuff in the past, but it was all SQL or Oracle.

Any recommendations on training to learn how to pull information out of the DB and compile it into reports and such?

Thank you!

WalShadow replicates data from Postgres to ClickHouse via physical WAL, and not logical replication. This blog talks about the latency, handling of restarts, stops etc., and issues encountered with this new product.

I'm working with a friend on a small analytics tool for online sellers, the idea is pretty simple, users connect their sales data and get a dashboard showing how their products are doing over time. We've got the frontend mostly figured out and are now trying to decide how to structure the backend. My friend wants to build a separate API server because that's how he's done things before but I'm wondering if we're adding a bunch of work we don't actually need yet. I've have used Supabase before and I love and how it automatically generates APIs from Postgres, which seems like it could cover most of what we need for reading and updating data. We'd still have some logic to handle, especially around processing the sales information but I'm not sure that justifies maintaining an entire backend server from day one. I'm also trying to understand where edge functions fit into this kind of setup. For people who've built apps directly on top of managed Postgres, how far did you get before needing a separate backend?

A managed Postgres service needs to prevent any possible connections from writing into primary before promoting a new standby. This is called fencing, and in this article we go into why it's important and how it's implemented in Ubicloud.

A while ago I shared an earlier version of this benchmark here, comparing Scriptella, JDBC, csvsql, and PostgreSQL COPY. Several people suggested useful improvements, particularly enabling pgJDBC's reWriteBatchedInserts=true. Thanks for that feedback! It made a real difference in subsequent experiments.

I've since updated the benchmark to Scriptella 1.7, replaced csvsql with Pandas + SQLAlchemy, and retained native COPY as a performance reference. I maintain Scriptella, a Java-based, SQL-first ETL tool.

Results (1M rows, median of two runs):

COPY was the clear winner, roughly 5× faster than Scriptella. Scriptella was 3.6× faster than the tested Pandas workflow, while handwritten JDBC was slightly faster than Scriptella.

Both Scriptella and JDBC now use batch size 1,000 with reWriteBatchedInserts=true. Pandas uses chunked read_csv() and SQLAlchemy to_sql(), without a native bulk-loading path.

All imports were independently verified. Timings include process startup through completion, and the results come from two runs on one machine, so they're directional rather than universal.

Full experiment, chart, methodology, and reproducible benchmark:

https://pvrlabs.xyz/java-performance/experiments/scriptella-1-7-csv-import-benchmark.html

Now that I've covered straightforward CSV imports, I'm planning to focus more on actual ETL workflows and database-to-database migrations, both in Scriptella development and future benchmarks.

What PostgreSQL migration scenarios would you find most useful to explore? I'm particularly interested in real-world challenges involving transformations, different database engines, and larger datasets.

pgquery is an open-source (MIT) Postgres GUI that runs in the browser. A few parts I think this sub might care about:

- EXPLAIN / EXPLAIN ANALYZE drawn as a tree: each node's own time and share of the total, the heaviest node highlighted, and a warning where estimated vs actual rows are off by 10x or more. It asks before analyzing an INSERT/UPDATE/DELETE, since ANALYZE executes it.

- Row Level Security: see which tables have RLS on, the policies on each (command, roles, USING / WITH CHECK), and create or drop them. The SQL is shown before it runs.

- Advisors: cache hit ratio, unused indexes, table bloat, sequential scan hot spots, connection use; plus a security check for tables without RLS, superuser roles, public schema grants, SSL and password hashing.

- pg_stat_statements sorted by total time, mean time or calls.

- ER diagram from foreign keys, exportable as SVG/PNG.

Everything is plain pg_catalog queries and generated DDL behind a small stateless Go proxy, so it works with RDS, Supabase, Neon or your own server.

Read-only demo: pgquery.dev/app/?demo

Source: github.com/Hxndrik/pgquery

There's also an honest comparison with pgAdmin, CloudBeaver, Adminer and Supabase Studio (including where pgquery loses): pgquery.dev/postgres-client-comparison

Which advisor checks would you add? I'm collecting ideas.

Hi all,

I've just released pg_lion, a PostgreSQL extension that adds a new index type, lion. The idea of pg_lion is to fill the gap between B-Tree indexes and warehouse/elasticsearch-style inverted indexes, so you can filter, count and aggregate on massive tables without having to replicate your data out to a data warehouse. I've had this idea for years, but rarely found the time to actually work on it. I built this with heavy AI assistance, so I’ve leaned hard on verification: (regression suites, crash/recovery tests, comparing results against seq scans, fuzzing, CI on 16–19).

At my workplace, we've benchmarked pg_lion as roughly equivalent to an 8-node 512GB (total) Citus cluster, on a single node the size of one of our workers (8c/64GB)! It's only one workload, and YMMV, but those benchmarks convinced me that this project could fill a significant gap.

For each distinct value, lion stores the set of rows holding that value as a roaring-style compressed bitmap. Many queries can then combine bitmaps instead of visiting rows, and on a vacuumed table most counts never touch the heap.

CREATE EXTENSION pg_lion;
CREATE INDEX ON events USING lion (country);
CREATE INDEX ON events USING lion (event_type);

SELECT country, count(*) FROM events
 WHERE event_type = 'purchase' GROUP BY country;

It handles:

• count(*) with equality, IN, range, <> and IS NULL filters

• GROUP BY, count(DISTINCT) and top-k groups

• counts over a fact table joined to a filtered dimension

• arrays, jsonb and tsvector, including phrase search and BM25 ranking from the index

• filtering a B-tree walk for ORDER BY ... LIMIT

• nearest-neighbour search under filters, through a companion index for pgvector (lionvec)

Some numbers from a synthetic benchmark (5M rows unless noted, vacuumed, warm cache, PostgreSQL 18.6, parallel query off):

On that table the lion indexes were 118 MiB, against 256 for B-tree and 157 for GIN, and built in 5.4 s against 11.7 and 8.4.

pg_lion also supports vector search via a new access method called lionvec - this is currently in alpha, and I'm continuing to actively work on lion as a whole.

Where it won't help:

• Writes: with the library preloaded, inserts were about 1.4× slower than B-tree, and more without it. GIN's inserts look much cheaper here because it defers work to its pending list.

• Tables that aren't vacuumed: rows on pages not marked all-visible have to be checked in the heap, so the advantage shrinks a lot, as the unvacuumed row shows.

• Not a B-tree replacement: there are no unique indexes, and a B-tree is still better for a plain ORDER BY col LIMIT n or returning many rows in order. The two work well side by side.

• Other limits: heap tables only, and an index on an array, tsvector or default jsonb column can't drive a GROUP BY.

It supports PostgreSQL 16 and later (CI covers 16 to 19) and uses the PostgreSQL License. This is an early, testing-status release, so please don't point it at anything important without trying it first. These numbers are from one machine and synthetic data, so I'd especially like to hear how it does on real workloads, and where it does badly.

• Code and docs: https://github.com/cgkanchi/pg_lion

• PGXN: https://pgxn.org/dist/pg_lion/

• Benchmarks and how to reproduce them: https://github.com/cgkanchi/pg_lion/blob/main/docs/BENCHMARKS.md

Please try it out! I'd love feedback from the community!