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.

Disclosure: I work at Pinecone. I'm posting because we'd like your feedback and input on pg_pinecone.

Repo: https://github.com/pinecone-io/pg_pinecone
Blog post: https://www.pinecone.io/blog/introducing-pg-pinecone/

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().

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:

https://manishrjain.com/egres/

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.