Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)

blog.vectorchord.ai

Post reply on HN
Print view View original

21–30 of 90 posts

Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)

#21

Post by brightball »

sgarland wrote: post #15
aobdev wrote: post #3

Earlier quoted context omitted.

This is not my area of expertise so take this with a grain of salt, but my initial instinct was to question why you would need to store the tsvector both in the table and in the index (because the tsvector values will in fact be stored losslessly in a GIN index). The PG docs make it clear that this only affects row rechecks, so this would only affect performance on matching rows when you need to verify information no…

If only Postgres had Virtual Generated Columns. Not being snarky; MySQL has had them for ages, and they are a perfect fit for this: takes up essentially zero disk space, but you can index it (which is of course stored). It is, in my mind, the single biggest remaining advantage MySQL has. I used to say that MySQL’s (really, InnoDB) clustering index was its superpower when yielded correctly, but I’ve done some recent b…

I mean, technically any database with triggers can have generated columns, but PostgreSQL has had generated columns since version 13. Current version is 17.

https://www.postgresql.org/docs/current/ddl-generated-column...

I can’t think of any advantage of a virtual generated column over a generated column for something like a search index where calculating on read would be very slow.

Postgres has been able to create indexes based on the output of functions forever though, which does the job here too.

Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)

#22

Post by bmelton »

nostrebored wrote: post #6

I'm legitimately curious -- why do people want to put EVERYTHING into postgres? I don't understand this trend (vector search, full text search, workload orchestration, queues, etc.)

If you can avoid adding an extra service without paying too much penalty, it means not having to acquire an extra skill or hire another devops person or keep yet another service in sync / maintained / etc.

The cost of adding services to an app is so much higher than people give it credit for at organizations of every size, it's shocking to me that more care isn't done to avoid it. I certainly understand at the enterprise level that the value add of a comprehensive system is worth the cost of a few extra employees or vendors, but if you could flatten all the weird services required by all the weird systems that use them in 30,000+ employee enterprises and replace them with one database and one web/application server, you'd probably save enough money to justify having done it.

Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)

#23

Post by codesnik »

aobdev wrote: post #3
danpalmer wrote: post #2

> Mistake #1: Calculating tsvector On-the-Fly (Major issue) I'm shocked that the original post being referred to made this mistake. I recently implemented Postgres FTS in a personal project, and did so by just reading the Postgres documentation on FTS following the instructions. The docs lead you through the process of creating the base unoptimized case, and then optimising it, explaining the purpose of each step and…

This is not my area of expertise so take this with a grain of salt, but my initial instinct was to question why you would need to store the tsvector both in the table and in the index (because the tsvector values will in fact be stored losslessly in a GIN index). The PG docs make it clear that this only affects row rechecks, so this would only affect performance on matching rows when you need to verify information no…

It's not only an additional disk space, it also a need to sync it with main column, using triggers or whatever, and have much bigger backups. Why "initial instinct was to question", though? I don't see downsides still, unless yeah, weighted queries or need to search in joined tables etc.

Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)

#24

Post by [deleted] »

sgarland wrote: post #15
aobdev wrote: post #3

Earlier quoted context omitted.

This is not my area of expertise so take this with a grain of salt, but my initial instinct was to question why you would need to store the tsvector both in the table and in the index (because the tsvector values will in fact be stored losslessly in a GIN index). The PG docs make it clear that this only affects row rechecks, so this would only affect performance on matching rows when you need to verify information no…

If only Postgres had Virtual Generated Columns. Not being snarky; MySQL has had them for ages, and they are a perfect fit for this: takes up essentially zero disk space, but you can index it (which is of course stored). It is, in my mind, the single biggest remaining advantage MySQL has. I used to say that MySQL’s (really, InnoDB) clustering index was its superpower when yielded correctly, but I’ve done some recent b…

[deleted]

Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)

#25

Post by sgarland »

VoVAllen wrote: post #20
sgarland wrote: post #15

Earlier quoted context omitted.

If only Postgres had Virtual Generated Columns. Not being snarky; MySQL has had them for ages, and they are a perfect fit for this: takes up essentially zero disk space, but you can index it (which is of course stored). It is, in my mind, the single biggest remaining advantage MySQL has. I used to say that MySQL’s (really, InnoDB) clustering index was its superpower when yielded correctly, but I’ve done some recent b…

It's coming in the Postgres 18. https://www.depesz.com/2025/02/28/waiting-for-postgresql-18-...

Yes (very exciting!), but you won’t be able to index them, and that’s really where they shine, IMO.

Still, I’m sure they’ll get there. Maybe they’ll also eventually get invisible columns, though tbf that’s less of a problem for Postgres as it is for MySQL, given the latter’s limited data types.

Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)

#26

Post by tianqi »

nostrebored wrote: post #6

I'm legitimately curious -- why do people want to put EVERYTHING into postgres? I don't understand this trend (vector search, full text search, workload orchestration, queues, etc.)

I agree with one of the points from The Art of PostgreSQL by Dimitri Fontaine, which is appropriate for answering this question:

"So when designing your software architecture, think about PostgreSQL NOT as storage layer, but rather as a concurrent data access service. This service is capable of handling data processing."

Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)

#27

Post by sgarland »

brightball wrote: post #21
sgarland wrote: post #15

Earlier quoted context omitted.

If only Postgres had Virtual Generated Columns. Not being snarky; MySQL has had them for ages, and they are a perfect fit for this: takes up essentially zero disk space, but you can index it (which is of course stored). It is, in my mind, the single biggest remaining advantage MySQL has. I used to say that MySQL’s (really, InnoDB) clustering index was its superpower when yielded correctly, but I’ve done some recent b…

I mean, technically any database with triggers can have generated columns, but PostgreSQL has had generated columns since version 13. Current version is 17. https://www.postgresql.org/docs/current/ddl-generated-column... I can’t think of any advantage of a virtual generated column over a generated column for something like a search index where calculating on read would be very slow. Postgres has been able to create i…

The advantage is when you want to store something for ease of use, but don’t want the disk (and memory, since pages read are loaded into the buffer pool) hit. So here, you could precompute the vector and index it, while not taking the double hit on size.

Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)

#28

Post by hmaxdml »

nostrebored wrote: post #6

I'm legitimately curious -- why do people want to put EVERYTHING into postgres? I don't understand this trend (vector search, full text search, workload orchestration, queues, etc.)

It is very good at doing the job that people over-eagerly offload to specialized services. Queues, notifications, scheduled jobs. And can be specialized with extensions.

Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)

#29

Post by aobdev »

codesnik wrote: post #23
aobdev wrote: post #3

Earlier quoted context omitted.

This is not my area of expertise so take this with a grain of salt, but my initial instinct was to question why you would need to store the tsvector both in the table and in the index (because the tsvector values will in fact be stored losslessly in a GIN index). The PG docs make it clear that this only affects row rechecks, so this would only affect performance on matching rows when you need to verify information no…

It's not only an additional disk space, it also a need to sync it with main column, using triggers or whatever, and have much bigger backups. Why "initial instinct was to question", though? I don't see downsides still, unless yeah, weighted queries or need to search in joined tables etc.

It's a generated column, so there's no overhead to keep it up to date, but all generated columns in PG are stored. The corpus for text search will be stored 1x in the source column, 1x as a tsvector in the index, and an additional 1x in the generated column if you do so. That's a 50% increase in disk space.

Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)

#30

Post by sgarland »

nostrebored wrote: post #6

I'm legitimately curious -- why do people want to put EVERYTHING into postgres? I don't understand this trend (vector search, full text search, workload orchestration, queues, etc.)

Tbf everything you mentioned, MySQL also has (in the latest version, for vectors).

But either way, the answer is simplicity and cost. I assume you’ve heard of Choose Boring Technology [0]? Postgres is boring. It’s immensely complex when you dive into it, but in return, you get immense performance, reliability, and flexibility. You only have to read one manual. You only have to know one language (beyond your app, anyway – though if you do write an app in pure SQL, my hat is off to you). You only have to learn the ins and outs of one thing (again, other than your app). ES is hard to administer; I’ve done it. Postgres is also hard to administer, but if I have to pick between mastering one hard thing and being paged for it, or two hard things and getting paged for them, I’ll take one every day.

[0]: https://boringtechnology.club/

Post reply on HN