for the same reason we used ElasticSearch for vector search, because it's just one part of a query. Everything else we already had (filters etc), just stay and can be combined with vector search.
Postgres is a database server, InnoDB is a storage engine. Comparing apples to oranges. MySQL and MariaDB both can utilize different storage engines that bring different things to the table.
> This write amplification is large enough that our efforts to tune indexing throughput have started to hit diminishing returns.
> don't key on the ANN address. That is precisely the change turbopuffer v3 makes. As you can imagine, it is not a trivial change.
This is a direct parallel to how Postgres and Mysql built indexes.
Your design choice went from a Postgres design pattern to a Mysql one. The difference is the reindexing cost vs the lookup cost - Postgres optimized for lookup and Mysql does for indexing on writes. Or more accurately, Postgres was better with good schema design using joins & mysql was optimized for a bad design with less normalization where many indexes exist for the same table.
Postgres always points an index to a row-id within postgres which is an arbitrary value which changes on each update.
Mysql, always assuming the storage engine is pluggable, points to the primary index entry and adds an extra indirection to the lookup.
This means that you point the mysql index to a stable id, so unless you go update the primary key for a row, you won't have to update the indexes for all the attribute lookups you might have made to data.
I don't do databases any more that much, but the design for NIMBLE file format has a lot of quirks which are relevant to this specific idea.
But the old Uber post about switching from Postgres to Mysql to prevent index amplification[1] is a direct mirror to this post.
Many of the commenters on this site are also obviously LLMs. I'd imagine that quite a few of the entities quoting aren't necessarily people. Keep an eye on where they slide mentions of other products that a marketing team would like to promote.
On facebook I have school classmates and colleagues as aquintances.
I wish facebook made this data available for other purposes (e.g. on HN), so people could look up human connection chains from them to me (and I could do the same).
(OTOH such a system would exclude all the people who don't have these contacts for some reason or another)
I feel like we've already reached the point where HN users have discovered 100 of the last 5 LLM commenters. It's the new way to disagree by not having to engage with the argument at all. Just say that a certain sentence structure or word means it's an LLM and move on.
One of the things HN has been missing for a very long time is an etiquette policy around this very thing. Accusing people of being "russian bots" and such is downright poorly mannered, and dang's policy of "less is more" succumbs to mob mentality. The proliferation of mob mentality is one of the things that has eroded the quality of HN over the last... I want to say 10 years, but it might be going too far back.
However, such a policy requires enforcing otherwise its like the rest of guidelines - vapid shit. Maybe now that HN is infused with LLM-Powered Moderation™, dang can do a bit better.
> Your design choice went from a Postgres design pattern to a Mysql one. The difference is the reindexing cost vs the lookup cost - Postgres optimized for lookup and Mysql does for indexing on writes. Or more accurately, Postgres was better with good schema design using joins & mysql was optimized for a bad design with less normalization where many indexes exist for the same table
You are right that MySQL does better when you have lots of indexes, but I don't think the tradeoff is that the overall Postgres architecture is better with good schema design.
Having secondary indexes point the primary key enables things like undo logging, which obviates the need for vacuums - vacuums being the most painful part of Postgres. On top of that your primary key index will be mostly cached so the cost of the indirection is much smaller than it may first appear
I think OP is just alluding to the fact that Postgres needs to do less work to go from secondary index to table data, since the tid is a direct pointer to the exact page and slotted entry while MySQL needs a b-tree walk.
> primary key index will be mostly cached so the cost of the indirection is much smaller than it may first appear
Not sure I follow. If it's in-memory you save having to read from disk, but you still have to walk the b-tree to go from PK to data.
MySQL was generally (pre 8) optimized for point queries on primary keys. So rows are stored in the PK index, the PK index is a clustered index. Everything more or less falls out of this.
Sure, I'm only taking about innodb. The join strategies supported before 8.0 (5.7 and lower) were basically nested loop and that's it. Which only really works well for a couple of query patterns: point queries with a handful of joined tables all using indexed FKs, or a big sorted scan over one single table and everything joined to it, with pagination through where clause and sorting all on the primary key, and possibly a straight_join to make sure it doesn't start the execution plan anywhere other than the spine table.
> only really works well for a couple of query patterns
To be fair this probably covered probably 9x% of production queries, like high-selectivity index-covered filters and sorts (with a limit). Even today most planners will still use an inner loop.
It's been interesting to watch how different planners have evolved over the years. SQL Server 7 in 1998 launched with features that MySQL and Postgres wouldn't catch up to until the late 2010s, but they had other features and quality-of-life improvements that made these edge-case optimizations hardly noticable.
> I think OP is just alluding to the fact that Postgres needs to do less work to go from secondary index to table data, since the tid is a direct pointer to the exact page and slotted entry while MySQL needs a b-tree walk.
Yes, this is true, but they framed this as "the Postgres approach is better when you have a good schema design", but that's not true. There are plenty of ways the MySQL approach is better even when you have a really good schema.
> Not sure I follow. If it's in-memory you save having to read from disk, but you still have to walk the b-tree to go from PK to data.
The point I was trying to make is that going to disk is going to be orders of magnitude slower than doing an in-memory B-tree traversal. Because of that, the cost of doing an extra b-tree traversal to find the page you're looking for is a relatively small cost compared to reading the page in the first place
.. and undo logs make rollbacks and crash recoveries slower. It's a trade off. MySQL storage engine architecture is nice, postgresql extension mechanism is nice.. and so on.
> Having secondary indexes point the primary key enables things like undo logging
That's neither here nor there. Heap-oriented tables can have undo logging, too; and index-oriented tables don't strictly require undo logging.
It's just that you're much more likely to want something like undo logging for index-oriented tables, because maintaining uniqueness for rows that are not in-place updated in the index becomes very expensive as more and more versions of the same key value may need to be checked for visibility and liveness, and removing old deleted versions becomes a maintenance hassle, too. It can be done without undo logging, but apparently that wasn't a sufficiently robust (or performant) design.
MSSQL (Clustered Indexes) and Oracle (Index Organized Tables) among others let you chose because there are advantages and disadvantages for different situations.
Not having true clustered indexes in PG is something I miss coming from MSSQL, it helps performance when the majority of access is always primary index avoid indirection from index lookup then tuple lookup and it also saves space if its the only index.
An automatically generated internal ID: (segment ID, doc ID). The user-provided primary key (the field called `id` in the document) turns into a secondary index at the storage layer.
Vector databases were always more about retrieval than either vectors or data storage. But the term stuck all too well and companies held on to it a tad too long. Sorry :)
That's just a search engine, but then you're competing with traditional players like Elasticsearch and Vespa who all have built-in vector support by now, and you have to compete on attributes like price, performance, features, and who can mention 'AI' the most times on their web page.
Anyone using pure vector dbs at this day and age is shooting themselves in the foot, but there is more to it than just deprecating vectors.
I built a corrective memory layer for our agents which is using filtering, hybrid/ranked fusion search and strongly typed predicates to provide the LLMs context to correct themselves in case of errors.
I struggled quite a bit relying on pure vector DBs, so this is a welcome change. You still need vectors to reach close enough areas to fetch the context though.
Im not full read up on RAG pipelines, but has anyone ever tried to make the database a neural net itself? I.e get rid of any sort of traditional databases, and then you basically just have some sort of autoencoder?
There's been quite a bit of research into this over the past 3-4 years under the name "generative retrieval." The general approach is to use a transformer and treat the weights as the index. You input the query, and then used constrained decoding to generate the document ID.
We've realized this a long time ago at TopK and built a flexible serverless search engine from scratch. Supports dense/sparse vectors, late interaction, lexical search, indexed regex, filtering, and custom scoring in one query.
I've really liked lancedb for similar use cases. Not just that it is OSS. But Lance treats ANN as a secondary index similar to what turbopuffer v3 does. Rows sit in fragments, and the vector index never moves them.
Is it time to kill the database and replace it with a LLM optimized compiled version that simply implements the required API directly in (Rust) code, without any dynamic overhead? It probably will still be based of off a base design or a base file format.
Ultimately this system will encompass the whole OS, of course, but the DB might be the best place to start.
Databases, like filesystems, are one of those pieces of infrastructure you do not want to build yourself unless you have a really good reason. They're complex, and you will not catch all the bugs yourself.
I have seen this happening already at two different companies. And I'm also doing it as well. Particularly for search indexes where there's no risk of data loss.
There'd be no benefit. They're good general purpose databases but for any specific workload you can do significantly better on your own. For search in particular even if you use Postgres you wouldn't want to use Postgres FTS you'd want to at least use Tiger Data's extension.
Instead of a database, the LLM will expose an api endpoint and build a database on demand?
That's interesting. Maybe to decrease latency the LLM could "cache" it's build of it's database and reuse in between instances. It could host this artifact on a "hub" of git trees and then any new use cases that come up, can be added to this git tree. Then it can possibly be reused in different use cases.
Yes you got it. Get rid of all the abstractions we've gotten so used to and effectively approach this as an embedded project, where every line of code has to be justified.
It tends to make sure you understand the system fully, as no foreign concepts need to be imported and deferred to. That should make your organization run better.
turbopuffer is founded by some of the smartest people I ever worked with in past jobs. I strongly doubt they used an LLM in the writing of this article.
I kept asking myself "is this ai written" while reading it to. Nothing to do wit h the em dashes. Phrasing like:
> RIP, primary vector index.
> The solution to these problems is simple: don't key on the ANN address. That is precisely the change turbopuffer v3 makes. As you can imagine, it is not a trivial change."
Cute heading, followed by wordy opening sentence that feels like it's repeating stuff even when it's not
I’m developing a local “code graph mcp tool” (not yet published) and followed a similar path, though I may have been able to go further since I have fewer vectors in my database (even on projects with 50M LOC).
At first, I tried all those popular vector databases and was disappointed with their performance. In the end, the best and fastest solution turned out to be building a multi-database system on SQLite, compiled with everything related to multi-client operations removed. Only exclusive mode was left. Everything is as binary as possible. The index is completely separate — an IVF with pre-training — and is built on the GPU (250K vectors are built, processed, and saved in 4 seconds). Right now, my biggest problem is frequent data changes, and I need to implement optimizations to reduce recalculations.
So far, I haven’t seen any vector database implementations that are heading in the right direction. Maybe only Lancedb looks promising, but it’s too heavy for my needs.
Dashboard was last updated on September 7, it started on September 5. Bug? Or no progress? It's linked in the blog post so would expect it to work: https://turbopuffer.com/v3
It is working! Check back soon for progress updates. The graphs will update once we have the companion dev log entry explaining the perf optimization.
As you can see from the dates we're a few weeks behind our publishing schedule. Hard to pull ourselves away from the perf hacking to write the dev log entries.
I have a deja-vu with the NoSQL databases, gladly to keep using classical SQL databases for data, while they get the features that matter after the dust settles.
I never bet on vector database tech (and I don't like RAG in general). Reasons:
- You create a new problem to solve an already tough problem (information retrieval)
- You bill people for storing vectors and searching when they are already paying for information storage and search
- You overcomplicate a retrieval problem that has well-defined solutions
- All the BS HYPE
The LLM wiki is one good option.
At WAIC, I saw a company change the filesystem to store the document index as you save documents so they never go out of sync.
87 comments
[ 5.3 ms ] story [ 276 ms ] threadIf you still have issues, try https://web.archive.org/web/20261001100105/https://turbopuff...
And what's with the throwaway account for this one comment? Is this becoming reddit with throwaway shills now?
> don't key on the ANN address. That is precisely the change turbopuffer v3 makes. As you can imagine, it is not a trivial change.
This is a direct parallel to how Postgres and Mysql built indexes.
Your design choice went from a Postgres design pattern to a Mysql one. The difference is the reindexing cost vs the lookup cost - Postgres optimized for lookup and Mysql does for indexing on writes. Or more accurately, Postgres was better with good schema design using joins & mysql was optimized for a bad design with less normalization where many indexes exist for the same table.
Postgres always points an index to a row-id within postgres which is an arbitrary value which changes on each update.
Mysql, always assuming the storage engine is pluggable, points to the primary index entry and adds an extra indirection to the lookup.
This means that you point the mysql index to a stable id, so unless you go update the primary key for a row, you won't have to update the indexes for all the attribute lookups you might have made to data.
I don't do databases any more that much, but the design for NIMBLE file format has a lot of quirks which are relevant to this specific idea.
But the old Uber post about switching from Postgres to Mysql to prevent index amplification[1] is a direct mirror to this post.
[1] - https://www.uber.com/us/en/blog/postgres-to-mysql-migration/
I wish facebook made this data available for other purposes (e.g. on HN), so people could look up human connection chains from them to me (and I could do the same).
(OTOH such a system would exclude all the people who don't have these contacts for some reason or another)
I feel like we've already reached the point where HN users have discovered 100 of the last 5 LLM commenters. It's the new way to disagree by not having to engage with the argument at all. Just say that a certain sentence structure or word means it's an LLM and move on.
However, such a policy requires enforcing otherwise its like the rest of guidelines - vapid shit. Maybe now that HN is infused with LLM-Powered Moderation™, dang can do a bit better.
You are right that MySQL does better when you have lots of indexes, but I don't think the tradeoff is that the overall Postgres architecture is better with good schema design.
Having secondary indexes point the primary key enables things like undo logging, which obviates the need for vacuums - vacuums being the most painful part of Postgres. On top of that your primary key index will be mostly cached so the cost of the indirection is much smaller than it may first appear
> primary key index will be mostly cached so the cost of the indirection is much smaller than it may first appear
Not sure I follow. If it's in-memory you save having to read from disk, but you still have to walk the b-tree to go from PK to data.
To be fair this probably covered probably 9x% of production queries, like high-selectivity index-covered filters and sorts (with a limit). Even today most planners will still use an inner loop.
It's been interesting to watch how different planners have evolved over the years. SQL Server 7 in 1998 launched with features that MySQL and Postgres wouldn't catch up to until the late 2010s, but they had other features and quality-of-life improvements that made these edge-case optimizations hardly noticable.
Yes, this is true, but they framed this as "the Postgres approach is better when you have a good schema design", but that's not true. There are plenty of ways the MySQL approach is better even when you have a really good schema.
> Not sure I follow. If it's in-memory you save having to read from disk, but you still have to walk the b-tree to go from PK to data.
The point I was trying to make is that going to disk is going to be orders of magnitude slower than doing an in-memory B-tree traversal. Because of that, the cost of doing an extra b-tree traversal to find the page you're looking for is a relatively small cost compared to reading the page in the first place
That's neither here nor there. Heap-oriented tables can have undo logging, too; and index-oriented tables don't strictly require undo logging.
It's just that you're much more likely to want something like undo logging for index-oriented tables, because maintaining uniqueness for rows that are not in-place updated in the index becomes very expensive as more and more versions of the same key value may need to be checked for visibility and liveness, and removing old deleted versions becomes a maintenance hassle, too. It can be done without undo logging, but apparently that wasn't a sufficiently robust (or performant) design.
Not having true clustered indexes in PG is something I miss coming from MSSQL, it helps performance when the majority of access is always primary index avoid indirection from index lookup then tuple lookup and it also saves space if its the only index.
I built a corrective memory layer for our agents which is using filtering, hybrid/ranked fusion search and strongly typed predicates to provide the LLMs context to correct themselves in case of errors.
Small plug, if anyone wants to try it out- https://polign.com/recall
I struggled quite a bit relying on pure vector DBs, so this is a welcome change. You still need vectors to reach close enough areas to fetch the context though.
We've realized this a long time ago at TopK and built a flexible serverless search engine from scratch. Supports dense/sparse vectors, late interaction, lexical search, indexed regex, filtering, and custom scoring in one query.
- https://www.topk.io/blog/vector-dbs-are-the-wrong-abstractio... - https://www.topk.io/blog/topk-embed-v1
Ultimately this system will encompass the whole OS, of course, but the DB might be the best place to start.
look at the sibling comment, people are already doing this. with time only more people will.
an era is ending. there is a time and a season for everything.
Data loss is the obvious concern. Are you perhaps reusing parts of sqlite or postgres?
That's interesting. Maybe to decrease latency the LLM could "cache" it's build of it's database and reuse in between instances. It could host this artifact on a "hub" of git trees and then any new use cases that come up, can be added to this git tree. Then it can possibly be reused in different use cases.
It tends to make sure you understand the system fully, as no foreign concepts need to be imported and deferred to. That should make your organization run better.
Just like Rust does, btw.
Thx.
turbopuffer is founded by some of the smartest people I ever worked with in past jobs. I strongly doubt they used an LLM in the writing of this article.
Cute heading, followed by wordy opening sentence that feels like it's repeating stuff even when it's not
At first, I tried all those popular vector databases and was disappointed with their performance. In the end, the best and fastest solution turned out to be building a multi-database system on SQLite, compiled with everything related to multi-client operations removed. Only exclusive mode was left. Everything is as binary as possible. The index is completely separate — an IVF with pre-training — and is built on the GPU (250K vectors are built, processed, and saved in 4 seconds). Right now, my biggest problem is frequent data changes, and I need to implement optimizations to reduce recalculations.
So far, I haven’t seen any vector database implementations that are heading in the right direction. Maybe only Lancedb looks promising, but it’s too heavy for my needs.
As you can see from the dates we're a few weeks behind our publishing schedule. Hard to pull ourselves away from the perf hacking to write the dev log entries.
The post itself says ANN becomes “just another” secondary index, and that v3 should make vector search faster along with text and regex.
“RIP, vector-primary index” would have been accurate. “RIP, vector database” is marketing.
- You create a new problem to solve an already tough problem (information retrieval) - You bill people for storing vectors and searching when they are already paying for information storage and search - You overcomplicate a retrieval problem that has well-defined solutions - All the BS HYPE
The LLM wiki is one good option. At WAIC, I saw a company change the filesystem to store the document index as you save documents so they never go out of sync.
Think harder boys.