tbls VS postgres-elasticsearch-fdw

Autogenerated database documentation is often pretty hit and miss but tbls[1] does a pretty good job in that space. Especially when you comment on your tables, fields, views, functions etc (which is a good habit anyway!) the output is quite useful

[1] https://github.com/k1LoW/tbls

In-database comments combined with something like https://github.com/k1LoW/tbls make for very cheap database documentation.

No affiliation with tbls except that I'm a big fan

Check out tbls. You can create a ERD in one command

My experience with Postgres FTS (did a comparison with Elastic a couple years back), is that filtering works fine and is speedy enough, but ranking crumbles when the resulting set is large.

If you have a large-ish data set with lots of similar data (4M addresses and location names was the test case), Postgres FTS just doesn't perform.

There is no index that helps scoring results. You would have to install an extension like RUM index (https://github.com/postgrespro/rum) to improve this, which may or may not be an option (often not if you use managed databases).

If you want a best of both worlds, one could investigate this extensions (again, often not an option for managed databases): https://github.com/matthewfranglen/postgres-elasticsearch-fd...

Either way, writing something that indexes your postgres database into elastic/opensearch is a one time investment that usually pays off in the long run.

I used a foreign data wrapper to query elasticsearch indexes from within postgres.[0]

It pushed alot of complexity down away from higher-level app developers not familiar with ES patterns.

[0]: https://github.com/matthewfranglen/postgres-elasticsearch-fd...