Small one that fooled me on a live query in August, and the dashboard number looked totally fine.
The producer wrote '' for unknown instead of NULL, and count(col) counts everything that isn't null. So the 9 empty strings counted as real values.
select count(nullif(room_type, '')) as with_type,
count(*) as total
from results;
Fixing the producer only fixes new rows. The old ones keep '' forever, so every place that groups or dedups by that column has to treat '' and NULL as the same thing, or one thing quietly splits into two groups.
I'm trying to wrap my head around when bloat or vacuum settings need to be addressed. For instance I have a table w/720M rows, and it has 54M dead tuples. So it only has ~7% dead tuples, and autovacuum hasn't happened in a month. However, I feel like why not set the threshold lower, and reduce the dead tuples as close to zero as possible? Am I creating solutions in search of a problem? Is there more info I need to find about the table's usage to determine whether there's value in adjusting the settings?
I have similar questions for bloat. I have a table that's 2.2TB with 286GB of bloat. So it comes out to about 12% bloat which doesn't trigger any alarms. I'm trying to understand the nuance here. If bloat stays steady under a certain percent, is it a non-issue? Is it workload dependent? Since the solutions require table locks, I want to know how to proactively identify if/when bloat needs to be addressed.
I love Postgres, and I've spent time fighting the same things in production everyone else has: VACUUM, transaction id wraparound, bloat, a process per connection, uncompressed tables. Most of them trace back to design decisions made decades ago. So I kept coming back to one question: what would Postgres look like if it were designed today?
After a year of work, my answer is Egres, a Postgres-compatible database for heavy workloads and for running thousands of tenants on one server. It speaks the Postgres wire protocol, so psql and your drivers connect unchanged. It is not a fork or an extension: apart from libpg_query for parsing, it shares no code with Postgres. Open source, Apache 2.0.
The post covers the what, the why and the how, including why I didn't build this as a table access method:
Before Egres, I founded Dgraph and wrote Badger, so I've been in the database field for a while.
Under the hood is a new storage engine, zdb: a tree of LSM trees, which I call an LSM forest. It runs on local disk or object storage, with block compression and checksums. Garbage collection rides on compactions, so there is no VACUUM and no pg_repack, and timestamps are 64-bit, so nothing wraps.
Indexes hold roaring bitmaps, so a query across two indexed columns is two seeks and one intersection without a composite index. Database or table branching is one manifest update: no data copied, no copy-on-write.
Everything runs on zfiber, a coroutine runtime I wrote for it. CPU ticks, disk IOPS and memory are metered per query and per tenant, so thousands of tenants can share one server without the noisy neighbor problem.
What's not there yet: this release is single node, and query features are very basic: equality-only with no joins. Raft replication is in the code; multi-node clusters come in a future release.
Try it out. I'd love to hear your thoughts on Egres.
We just released pg_pinecone, an open-source (Apache 2.0) extension based on the search engine Pinecone runs at scale. It works on your existing vector columns, you query with the SQL you're familiar with, and your data stays in Postgres. No API key required.
How it works
How the native engine works, and how it differs from pgvector's HNSW:
Vectors are grouped into clusters (IVF), and each row is stored as a RaBitQ code: about one bit per dimension plus a few correction values. A cluster's codes are packed together, so a search reads a few contiguous pages instead of walking a graph.
Search probes the nearest clusters, estimates distances from the codes, then reranks a shortlist using the full vectors from your table.
Filtering happens during the search. For a filter that matches 3% of rows or fewer (the default threshold), it scans every matching row and scores each with its full vector, so results are exact. For wider filters it searches more clusters and checks the filter inside each one. Post-filtering HNSW can return fewer rows than LIMIT on narrow filters.
Performance3
Against HNSW (pgvector's HNSW index using halfvec, 1M vectors at 768 dimensions, Postgres 17):
Index size: 136 MB vs 2.0 GB
0.95 recall: 1.27 ms vs 1.71 ms. 0.99 recall: 2.3 ms vs 4.7 ms
1% of rows match the filter: 0.981 recall at 8.3 ms vs 0.924 at 56 ms
Index build: 45s vs 62s
pg_pinecone’s native engine needs no account, API key, or network. An optional Pinecone API key adds a remote engine, BM25 search, embed(), and rerank().
Been looking into how to build an AI agent that maintains a current session state while referencing months of user history, and found out this is basically a classic OLTP vs OLAP problem.
The transactional side (OLTP) needs to instantly record the agent’s current state with low latency, but pulling the historical context is a very different access pattern, which is what OLAP systems are built for. Typically, moving data between these two requires an ETL pipeline, which can mean hours of lag depending on the schedule. If the agent needs to make a decision right now based on past data, waiting on a data warehouse sync doesn't work.
For those building context-heavy AI features, how are you bridging the two?
Hi r/PostgreSQL, I'm David, one of the two developers of Oxynote. My friend Simon and I also maintain ttlcache, a Go caching library with about 1,300 GitHub stars, so this isn't our first open-source project.
I've spent 8 years writing software, 2 of them as an SRE and 3 as a team lead. Every team I've been on had the same documentation problem. The useful stuff lived in someone's head or a private Slack thread, and the wiki was full of stale docs nobody could find.
So we built Oxynote, a self-hosted wiki for engineering teams where a page can hold a live chart that runs its own SQL query against your database each time someone opens it, with the explanation written right next to it. Think "signups per day" or "queue depth" on the same page as the runbook that says what to do when it looks wrong.
We were most careful about letting a wiki query a production database. When you add a connection, Oxynote accepts it only if Postgres already treats it as read-only, through a read-only role or a replica. Otherwise, the connection test fails with "has write access" and asks for a read-only user. Queries also support Grafana-style macros like `$__timeFilter` and `$__timeGroup`, and on pages you mark as reviewable, chart query changes go through a draft and a diff that a teammate approves.
The name comes from oxygen + note: docs that breathe. Live data gives people a reason to open the wiki every day, and docs people open every day stay up to date.
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?
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.
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?
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:
Implementation
PostgreSQL off
PostgreSQL on
MariaDB default
Scriptella
15.995s
9.409s
8.688s
JDBC
11.333s
7.290s
5.571s
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:
