Rendered at 19:43:40 GMT+0000 (Coordinated Universal Time) with Cloudflare Workers.
jillesvangurp 9 hours ago [-]
Searching what you stored is nice. But usually, you want to put some ETL (extract, transform, and load) in between your data base and search indices that enables you to enrich, transform, and improve data. If you are using vector search, this is also where you calculate your embeddings and run your data to elaborate tokenization pipelines. And you might do things like calculate categorizations and other classifications that you might use for filtering.
Search quality tuning inevitably involves iterating on how you index your data. The value of separation of where your database and search index live is that you can try out new indexing strategies and try different strategies.
I've been consulting on how to improve search quality for some time and sorting out ETL is usually the first thing I need to do for clients. A good question is "when did you last reindex your data". If the answer is never, that usually means the clien is probably not able to do this easily and that they probably made a lot of mistakes when they set up their system first. I've helped quite a few clients like that.
ETL doesn't have to mean bringing in a lot of complexity. A few simple python or whatever scripts can do the job. And many people don't actually have a lot of data which means these scripts can run quickly. I've had clients where it only took a few seconds to rebuild their search indices.
ParadeDB seems premised on the notion of just skipping all this. IMHO this is usually a mistake and if you were to do this properly, you'd still end up with a separate db from which you index into ParadeDB. Of course, if you do that, you could question if you could not pick something else instead.
philippemnoel 2 hours ago [-]
Agreed that people need to iterate on their search index. Our users do this a lot. Postgres has a clause called INDEX CONCURRENTLY, which allows you to build an index in the background while the existing index continues to serve queries.
As for embedding, etc., you can of course do this in Postgres as well. The "T" in ETL can still be done in a Postgres-based product like ParadeDB, but you can have it participate in transactions and avoid denormalizing (or denormalize much less), etc.
You'll spend what time you save and more trying to keep all the unnormalized data consistent.
3 hours ago [-]
gopalv 16 hours ago [-]
> Instead of joining the tables completely and sorting at the end, a Rank Join streams its inputs in descending score order (sorted access, or SA), building candidate result-sets as it goes.
Your algorithm feels like a variant of the WAND algorithm the Snowflake Top-K pruning paper [1] pulls from.
You can go distributed with this approach if you have a "gossip" protocol between workers to distribute the heap boundaries periodically.
Before the AI age, a "papers we love" group was a great thing within any company.
Now, Claude or Codex will know the SOTA literature around a topic like this and "uniformly distribute" the past into every single implementation of a similar idea. The future isn't uniformly distributed, but the past is getting the treatment & that's good.
Yes! Search engines like Tantivy have long employed WAND to score across terms. The difference though is that WAND is not joining terms in descending order by score: rather, by doc-id.
This algorithm is closer to Fagin's Threshold Algorithm (also mentioned in your link): the Threshold Algorithm was the first iteration of a rank join.
amluto 15 hours ago [-]
This is neat, but I’m a bit confused by the query that motivates the whole article. If I search for ‘ocaml’, I want to match users’ about_me (the users.about_me ||| 'ocaml' part), but I do really want this to match on the basis of each (completely irrelevant) post and/or comment that gets picked up by the join? I think I would rather match the user, possibly with some user scoring by the overall user score independent of my search term.
I feel like I would probably execute three separate queries and merge the results myself. And the user query wouldn’t join against posts at all. Doing this would end up implicitly doing something like the optimization in the article.
stuhood 3 hours ago [-]
It very much depends which noun you are searching for (e.g., what the shape of your search results will be):
The central query in the blog would likely be the type of query you'd use for the default top-level search box on StackOverflow, which is searching for posts and comments (e.g. questions and answers). The reason to include users in that query is two fold: 1) posts/comments from users who are relevant to your query should get a higher score, 2) you need the users to render the search results page anyway.
If you were doing only user search, then I agree: you probably would only search the users table.
amluto 3 hours ago [-]
If I’m searching for questions and answers, I don’t want an unrelated answer from a very high ranked user who happens to mention the topic in their profile.
stuhood 3 hours ago [-]
Possibly, yea: scoring can be subtle. For a query like this, you'd likely significantly "boost" (e.g. multiply) the scores from the questions and answers to ensure that they are prioritized. But including the user score as essentially a tiebreaker can be helpful.
radiospiel 12 hours ago [-]
Indeed. The query should maybe be a UNION ALL over individual search results instead.
onedognight 4 hours ago [-]
They compare to UNION ALL in passing:
> Getting that original, natural SQL query down to 100 ms on a single core (12x-50x faster than the 84-line UNION ALL rewrite, and within range of a fully denormalized dataset) required rethinking how joins and search indexes interact
anon373839 15 hours ago [-]
ParadeDB is the best! I really like the Django integration (https://github.com/paradedb/django-paradedb). You get proper BM25 scoring, fuzzy matching, hybrid search, metadata filtering while still just using QuerySets. And you're keeping everything in Postgres, so there isn't any need to manage or sync a separate DB/search engine.
jamesgresql 16 hours ago [-]
Any questions please go for it, ParadeDB team is watching.
nehalem 11 hours ago [-]
I would like to try out ParadeDB and potentially retire Typesense but being unable to install it in CloudSQL makes it more than difficult.
isaacvando 5 hours ago [-]
If you want to stay on CloudSQL, you can always setup a ParadeDB cluster as a logical replica. Our cloud offering is in private preview if you'd like to try: https://app.paradedb.com
sorenbs 6 hours ago [-]
This might not be an option for you, but Prisma Postgres ships ParadeDB by default. We have a generous free tier.
oulipo 10 hours ago [-]
In my case I'm not so much interested by the BM25 aspect (I don't really have text to search), but I have collection of objects with many columns (having a small-ish number of values, et categories/tags) and I'd like to have an easy way to facet-filter them efficiently.
Right now with "vanilla Postgres" I have to guess which combination of facets is going to be common and try to define indexes for these. Would ParadeDB help there?
Interesting! I'm using a custom image (because I'm using timescaledb too), can I just add the extra paradedb extension to my image and it will work for normal postgres tables (I assume I can't add a paradedb index on a timescale hypertable?)
dan_thinks 18 hours ago [-]
This looks exciting for some searches (top k), but the headline doesn't really fit. Yes - we still have to denormalize. Just not for some of the use cases paradedb targets. Impressive work.
16 hours ago [-]
bartvk 12 hours ago [-]
The article mainly talks about searching.
What about the use case where data frequently gets updated as well? I assume you then get to update lots of duplicate data.
philippemnoel 2 hours ago [-]
ParadeDB indexes stay in sync in real-time with the underlying table's UPDATEs, INSERTs and DELETEs. No actions needed on your side. We are specifically designed for high update throughput.
nraynaud 10 hours ago [-]
The index is columnar, so the writes are less scattered on disk than in a row-style B tree even when the updated values are very scattered in logical order I think.
KellyCriterion 10 hours ago [-]
Denormalization & Joins are mainly a thing from 80-90s when diskspace & compute was expensive; today its easy just do denormalize everything and not use joins, since diskspace & RAM today is abundant.
(sure, not true for that one verylarge enterpriseDB at megacorpX, but in 95% of uses cases these topics do not matter anymore)
lompad 10 hours ago [-]
That makes it rather annoying to keep data consistent though, and tons of people just won't take enough care to properly update everything. Having had to deal with that kind of half-out-of-date mess, I'd rather not, tbh.
pak9rabid 1 hours ago [-]
See: MongoDB
load-bearing 9 hours ago [-]
But why do it? You yourself are saying that it increases resource usage (which is mentality that left us with electron BTW), and creates additional difficulty with keeping data consistent. What's the upside?
I've never worked on anything, and probably won't ever work, that wouldn't fit in modern machines even in heavily denormalized forms, and have zero desire to do it just for consistency sake. 3NF or up, with very rare exceptions.
weego 9 hours ago [-]
Your assumption that increased resource usage is single concept with one outcome and no nuance is entirely false (where do the problems of engineering a desktop app even overlap with row level schema planning in a database?)
The claim that is somehow causes data consistency issues also seems kind of unsubstantiated.
The upside is that when you model it correctly your data conforms to your domain and business in a way that makes every aspect of its use easier.
lompad 5 hours ago [-]
> The claim that is somehow causes data consistency issues also seems kind of unsubstantiated.
Let me explain that part a bit more. If you have one application, developed by at most a handful of devs, there will not be any consistency issues. But add a few more applications and even a dozen devs (or just simply a decade of time and some staff changes), and there _will_ be misunderstandings/issues identifying the correct rows to update, with exact interpretation differing per application.
Congratulations, you now own a mess. Correcting this after the fact is a major effort.
If you're in 3NF this can't really happen - the exist once and only once. It's always clear which to update and nothing can be forgotten.
williamdclt 3 hours ago [-]
while I mostly agree with you, you're opposing "teams who made mistakes while denormalising" with "teams who made no mistake while normalising". The biggest difference between these two scenarios isn't that one is using denormalisation and the other normalisation, it's that one made mistakes and the other didn't.
In real life, the teams working on a supposedly-normalised data schema will also make mistakes: they will model their domain incorrectly, have information duplication without realising it at first, different interpretation of the same data (accessing the same data doesn't mean they'll interpret it the same way... or that it even _can_ be interpreted in a consistent way if not modelled correctly).
I do agree that denormalisation increases the chances of that happening. But IMO, there's much more issues on data modelling than there is in denormalisation. If you have a solid model that is shared by everyone, you'll have much fewer issues even with denormalisation than if you have a not-solid model that is not-shared by everyone even with normalisation.
vidarh 9 hours ago [-]
Denormalization brings a complexity cost when updating to ensure integrity, though. And if storage and compute is cheap then you can easily get the benefits of denormalization without the downsides by keeping data normalized for consistency and instead build indexes to let you query it dernormalized when it makes sense.
fergie 9 hours ago [-]
> today its easy just do denormalize everything and not use joins
You _can_ take this approach on a read-heavy db, but depending on your usecase you are going to struggle with updates and writes.
dam_jackalopes 28 minutes ago [-]
I manage a pretty heavily denormalized data warehouse and it works pretty well for us, but we have individuals in the org making noise about wanting functionality to write back to the apps. Having to explain why this "simple" functionality will cause significant heartburn to our analytics team isn't at all fun.
At least I didn't design the thing so I have someone to shake my fist at.
meindnoch 8 hours ago [-]
Or keep your data normalized, and maintain materialized views if and when they're needed.
RugnirViking 9 hours ago [-]
I do not agree, I believe normalization leads to a lot of other benefits like making you think about connections between data much more carefully, and thinking about what you need for each request. Denormalized tables have led way too many times in my experience to hapless developers fetching enormous amounts of data just to use a tiny amount far too often.
And db performance absolutely is an issue. Agents mess it up all the time on our codebases...
9 hours ago [-]
fallingbananna 10 hours ago [-]
This is true only if you have *very* specific data.
cmiles74 6 hours ago [-]
...And watch your updates take longer as they now update several columns over several tables instead of just one column in one table, and be sure to ensure that changes don't leave any data in an inconsistent state. I don't think this is an "enterprise" issue, it's about maintaining your data, and that effects 99% of applications that store data in a database.
oulipo 10 hours ago [-]
Not really, as the article mentions: if you simply denormalize everything, each time you need to eg update a user name, you suddenly have to update its whole posts and comments everywhere, making it something quite expensive
KellyCriterion 10 hours ago [-]
a) thats the reason why lot of platforms do not allow to change username after signup
b) absolutely manageable with a solid tooling (and honestly, in 99% of cases this event is trivial as most systems just do not hold enough data to make it like a "eeek this change will run for 3 days for that 1 user")
sgarland 6 hours ago [-]
Experience across multiple companies of varying size and maturity has proven to me that devs are incapable of doing the RDBMS’ job for it; there are always orphaned rows and referential integrity issues when FK Constraints get dropped.
Databases are hard. They’re also some of the best-tested code that exists, so maybe let them do their job.
fauigerzigerk 8 hours ago [-]
>a) thats the reason why lot of platforms do not allow to change username after signup
Nor any of the other user data that is indexed with every single post?
KellyCriterion 7 hours ago [-]
If I look at my youtube comment notification, Id say propably not? (Timestamp belongs to a post as well as likes etc.?)
Search quality tuning inevitably involves iterating on how you index your data. The value of separation of where your database and search index live is that you can try out new indexing strategies and try different strategies.
I've been consulting on how to improve search quality for some time and sorting out ETL is usually the first thing I need to do for clients. A good question is "when did you last reindex your data". If the answer is never, that usually means the clien is probably not able to do this easily and that they probably made a lot of mistakes when they set up their system first. I've helped quite a few clients like that.
ETL doesn't have to mean bringing in a lot of complexity. A few simple python or whatever scripts can do the job. And many people don't actually have a lot of data which means these scripts can run quickly. I've had clients where it only took a few seconds to rebuild their search indices.
ParadeDB seems premised on the notion of just skipping all this. IMHO this is usually a mistake and if you were to do this properly, you'd still end up with a separate db from which you index into ParadeDB. Of course, if you do that, you could question if you could not pick something else instead.
As for embedding, etc., you can of course do this in Postgres as well. The "T" in ETL can still be done in a Postgres-based product like ParadeDB, but you can have it participate in transactions and avoid denormalizing (or denormalize much less), etc.
Your algorithm feels like a variant of the WAND algorithm the Snowflake Top-K pruning paper [1] pulls from.
You can go distributed with this approach if you have a "gossip" protocol between workers to distribute the heap boundaries periodically.
Before the AI age, a "papers we love" group was a great thing within any company.
Now, Claude or Codex will know the SOTA literature around a topic like this and "uniformly distribute" the past into every single implementation of a similar idea. The future isn't uniformly distributed, but the past is getting the treatment & that's good.
[1] - https://arxiv.org/html/2504.11540v1#S5
This algorithm is closer to Fagin's Threshold Algorithm (also mentioned in your link): the Threshold Algorithm was the first iteration of a rank join.
I feel like I would probably execute three separate queries and merge the results myself. And the user query wouldn’t join against posts at all. Doing this would end up implicitly doing something like the optimization in the article.
The central query in the blog would likely be the type of query you'd use for the default top-level search box on StackOverflow, which is searching for posts and comments (e.g. questions and answers). The reason to include users in that query is two fold: 1) posts/comments from users who are relevant to your query should get a higher score, 2) you need the users to render the search results page anyway.
If you were doing only user search, then I agree: you probably would only search the users table.
> Getting that original, natural SQL query down to 100 ms on a single core (12x-50x faster than the 84-line UNION ALL rewrite, and within range of a fully denormalized dataset) required rethinking how joins and search indexes interact
Right now with "vanilla Postgres" I have to guess which combination of facets is going to be common and try to define indexes for these. Would ParadeDB help there?
It sounds like you are in a similar boat to https://www.paradedb.com/customers/moderntreasury: no BM25 scoring, but a need to filter, sort, and aggregate by a variety of columns.
What about the use case where data frequently gets updated as well? I assume you then get to update lots of duplicate data.
(sure, not true for that one verylarge enterpriseDB at megacorpX, but in 95% of uses cases these topics do not matter anymore)
I've never worked on anything, and probably won't ever work, that wouldn't fit in modern machines even in heavily denormalized forms, and have zero desire to do it just for consistency sake. 3NF or up, with very rare exceptions.
The claim that is somehow causes data consistency issues also seems kind of unsubstantiated.
The upside is that when you model it correctly your data conforms to your domain and business in a way that makes every aspect of its use easier.
Let me explain that part a bit more. If you have one application, developed by at most a handful of devs, there will not be any consistency issues. But add a few more applications and even a dozen devs (or just simply a decade of time and some staff changes), and there _will_ be misunderstandings/issues identifying the correct rows to update, with exact interpretation differing per application.
Congratulations, you now own a mess. Correcting this after the fact is a major effort.
If you're in 3NF this can't really happen - the exist once and only once. It's always clear which to update and nothing can be forgotten.
In real life, the teams working on a supposedly-normalised data schema will also make mistakes: they will model their domain incorrectly, have information duplication without realising it at first, different interpretation of the same data (accessing the same data doesn't mean they'll interpret it the same way... or that it even _can_ be interpreted in a consistent way if not modelled correctly).
I do agree that denormalisation increases the chances of that happening. But IMO, there's much more issues on data modelling than there is in denormalisation. If you have a solid model that is shared by everyone, you'll have much fewer issues even with denormalisation than if you have a not-solid model that is not-shared by everyone even with normalisation.
You _can_ take this approach on a read-heavy db, but depending on your usecase you are going to struggle with updates and writes.
At least I didn't design the thing so I have someone to shake my fist at.
And db performance absolutely is an issue. Agents mess it up all the time on our codebases...
b) absolutely manageable with a solid tooling (and honestly, in 99% of cases this event is trivial as most systems just do not hold enough data to make it like a "eeek this change will run for 3 days for that 1 user")
Databases are hard. They’re also some of the best-tested code that exists, so maybe let them do their job.
Nor any of the other user data that is indexed with every single post?