just an fyi: The blog link in your readme does not work.

From the blog about pg_search linked by TFA:

  This is what we did: 

    DB with pg_search: We created a single BM25 index 
    DB without pg_search: We created all these indexes
        GIN index on message (for full-text search)
        GIN index on country (for text-based filtering)
        B-tree indexes on severity, timestamp, and metadata->>'value' (to speed up filtering, ordering, and aggregations)

Perhaps you can argue that creating a fastupdates=on index would have been the right comparison, but you didn't do that in that blog.

> You can always cherry-pick a query and optimize it at the expense of data duplication and complexity. The Neon/ParadeDB benchmarks contained 12 queries in total, and the benchmarks could have:

TFA isn't cherry-picking to show you that one query could have gone faster. TFA is showing that you didn't compare apples to apples. Looking at those 12 queries nothing screams at me that TFA's approach of storing the computed tsvector wouldn't work for those too.

Perhaps pg_search scales better and doesn't require trading off update for search performance, and that would be a great selling point, but why not just make that point?

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 not stored in the index, e.g. queries with weighted text or queries against a lossy GiST index. It's going to be use-case dependent but I would check if your queries need this before using up the additional disk space.

I’ve been a Postgres FTS advocate for over a decade since replacing a Solr search with it and getting easier maintenance, more flexibility with queries and virtually no difference in speed.

It’s pretty great.

Elastic is on a different level for a lot of use cases, but pg is more than enough for the vast majority of workloads.

> I could only assume that someone making this mistake is either doing so to intentionally misrepresent Postgres FTS, or because they haven't read the basic documentation.

Small writing note, I probably would've swapped the order of those. Hanlon's Razor and all. :)

Perhaps the most generous interpretation is that the authors were writing an article for people who do the naïve thing without reading the docs. There are quite a few people in that category.

> I could only assume that someone making this mistake is either doing so to intentionally misrepresent Postgres FTS, or because they haven't read the basic documentation.

vibe sysadminning, bro

I've built a number of systems that run a database and a separate search index (Elasticsearch, Solr, Xapian). The hardest part by far is keeping the search index in sync with the database. I gave a talk about this a while ago: https://simonwillison.net/2017/Aug/16/denormalized-query-eng...

Using the search engine built into PostgreSQL, MySQL or SQLite makes this problem SO MUCH less difficult.

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.

We've been using Elasticsearch + PG and it's pretty nice and fast, but it adds a whole layer of extra stuff to deal with when your data is in PG but then also needs to be indexed into an external server outside of those PG transactions. In our case I'm pretty convinced it hasn't been worth the effort. I think we could've optimized PG to be as fast as we needed with a lot less overhead than dealing with an external search index.

We moved our queues to PG and it cuts out the same kind of overhead to be able to wrap an update and start a job in a transaction. PG has been plenty fast to keep up with our queue demand.

Ultimately I think being able to do things transactionally just avoids a whole class of syncing issues, which are basically caching issues, and cache invalidation is one of the 2 hard things.

You can abstract this to any RDBMS, and the justification is that it makes everything a lot faster & easier.

I just got off a call with a client where their developers were using ORM-style abstractions to manipulate data for downstream processing in code, turning what should have been a few seconds of one custom SQL command into several hours of passing objects around multiple computer systems.

If we can put the FTS engine inside the SQL engine, we can avoid the entire space of APIs, frameworks, 3rd parties, latency, etc that goes along with otherwise integrating this feature.

Modern SQL dialects represent universal computation over arguably the best way we know how to structure complex data. Adding custom functions & modules into the mix is mostly just syntactic sugar over what is already available.

There is ZERO honor in offloading what could have been a SQL view into a shitty pile of nested iterators somewhere. I don't understand where any of this energy comes from. The less code the better. It's pure downside.

Maintaining a new service sucks. Not being able to do atomic commits to both postgres and the other db sucks.

Avoiding distributed systems problems. Distributed systems are so incredibly hard to get right that I will vertically scale postgres until I hit an insurmountable wall before giving in.

It's because postgres is in fact good at a lot of vaguely database-looking things. Even if it weren't the best for anything, if it does 80% of things at 80% best possible — it is reasonable to have postgres as "first thing to reach for" by default.

That said, it's easy to forget to check if you're in either of those 20% (or both.) There's probably a whole bunch of postgres usage where really something else should be used, and people just never checked.

Because PG is a fantastic platform for doing everything you need in SQL and it performs real well. Add PostgREST and you've got a REST API almost for free (you have to design a schema of what to expose, so not entirely free, but still, pretty close). Also, PG moves _fast_ and has a very active developer and user community, which means you'll be getting more awesome functionality in the coming future. (E.g., I've been following the AIO thread and the performance improvements coming from that patch set will be hefty.)

When you're starting something new and the amounts of data is still small, it's often better early on to focus on the product than optimizing for theoretical performance optimization that may never pan out (either because the project will fail or that the bottlenecks may ultimately not be what you thought or expected).

At my current gig, we used to shove everything (including binary data) into postgres because it was easy and all our code plugged into it anyways. When it started to become uneconomical (mostly due to RDS storage costs), we then started shunting data to S3, DynamoDB, etc.

Also, not everybody can be on a cloud with easy access to all the fancy products for queuing, caching, etc. Sometimes it's better overall to have to deal with one complex beast (that you'd have to maintain anyways) than spending time deploying Kafka, MongoDB, etc (even though it can sometimes be easier than ever with pre-built manifests for K8s) as well as securing and keeping them all up to date.

I do strongly encourage people to treat code that deals with these things with as much abstraction as possible to make migrations easier later on, though.

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/

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.

I am going to hazard a guess that it's because the closer the services are to your data, the easier they are to implement, and you often get great speed too. FTS in Postgres has been fantastic for me, and combining it with vector search and RAG gives you a pretty sweet deal for low effort.

Disclaimer, I have no experience with this kind of thing. However, theoretically, less tools is better for an organization - see [0] - and if your job adverts say just "postgres" instead of "postgres, elasticsearch, tool x, tool y, tool z" etc, you don't need to find (or train) a unicorn that is up to speed on all of them.

That said, "postgres" is a very broad subject if you take all of those into consideration, if you need to specialize your search for someone who knows how to do X in PG specifically you're almost back at the same spot. (I say almost because I'm sure it's easier to learn a specialization in Postgres if you're already familiar with Postgres than it is to learn a completely new tool)

And caveat, there's a high golden hammer risk there. I'd start questioning things when needing to query JSON blobs inside a database.

[0] https://mcfunley.com/choose-boring-technology

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."

All these replies have me so confused, the reason to shove everything into your database when you can is because you can transact across them. That's thing you can't get once you have a second system.

It’s free, it’s stable, it’s excellent, and it’s everywhere. The question should be “why don’t people put everything in Pg?”

The only things you shouldn’t put in Pg are things where there’s an obviously better alternative that’s so much better as to make running an extra service worth it. There definitely are cases where that’s true (Redis when you need exceptionally quick responses, for example) but it’s a high bar to clear.

PostgreSQL hasn't let me down in 20+ years. It's not perfect but it's really damn good for all your data cases (may require tuning)

Because the vast majority of people on HN or in the real world don’t need to scale beyond 10 concurrent users. It’s insane how much infrastructure script kiddies add to their projects when it could be done in the database and scale well.

Why not? Pareto principle. It’ll get you 80% of the way there for most things… then when you need a highly optimized solution you pivot to that.

There are good reasons mentioned already, but additionally, there’s a real strong cargo cult developing around Postgres these days.

The segments themselves being immutable doesn't mean that Tantivy is incompatible with Postgres - it just means that Tantivy needs to be made compatible with Postgres' concurrency control mechanisms (MVCC) and storage format (block storage). This blog post explains the latter: https://www.paradedb.com/blog/block_storage_part_one

Sounds like the issue was just co-location of search index + other transactional data in the same table. If you had a table acting as your search index only then would insert lag on that table matter? I could maybe see connections piling up, but with proper batching I bet it'd be fine.

Only the ranking function at the end of TFA is custom. Everything until there is standard postgres.