RetoolDB request: Enable pg_search with Analytics Features

đź’¬ Feature Requests
1

Hi RetoolDB team,

I would like to request that the full pg_search extension from the ParadeDB project be enabled for all RetoolDB users. I am looking for version 0.18 or later, which includes:

  • The BM25 search functionality
  • The analytics features formerly in pg_analytics (for example, Parquet FDW, approx_count_distinct, quantile, topn)

The goal is to support both:

  • Advanced search such as faceted search and relevance ranking
  • OLAP‑style analytics such as semantic modeling and pre‑aggregated views

I believe enabling this for all RetoolDB users would help more teams fully leverage Postgres in an HTAP (Hybrid Transactional/Analytical Processing) model. Combining OLTP and OLAP workloads in the same database can reduce latency and complexity by eliminating the need to sync data to a separate analytics store. For most companies that do not operate at extreme OLAP scale, this approach can deliver faster time‑to‑insight and simpler operations compared to exporting data to systems like Elasticsearch or ClickHouse, even though those systems can outperform Postgres for very large analytical scans. I could just do this directly on a Postgres resource but I also expect that keeping these capabilities directly on RetoolDB will provide additional agility and simplicity for Retool users.

Current status:

  • pg_search appears in pg_available_extensions on RetoolDB
  • But it is not listed in shared_preload_libraries
  • Running CREATE EXTENSION pg_search; returns:
    pg_search must be loaded via shared_preload_libraries. Add 'pg_search' to shared_preload_libraries in postgresql.conf and restart Postgres.
3

I probably wouldn't suggest it, but have you tried

ALTER SYSTEM SET shared_preload_libraries = "pg_search";

I'd suggest running

SHOW shared_preload_libraries;

first to get the current shared_preload_libraries value then manually add 'pg_search' to ensure you don't change anything important.... so if you ran that command and it listed "pg_stat_statements" you'd want to use

ALTER SYSTEM SET shared_preload_libraries = "pg_stat_statements, pg_search";

I'm not too sure how you'll get the server to restart though

4

Hi @MTarter,

Very interesting request, thank you for the detailed write up.

I can definitely pass this along to our engineering team with a feature request.

Let me know how @bobthebear's suggestion goes as well if you try that out :sweat_smile:

8

Thank you both. I'm going to hold off on that command for now if it's not recommended. In the meantime, I plan to manually execute the SQL-only portions of pg_analytics without loading the extension. As a stop-gap, I could just add the metrics I need directly to a query, but I think aggregate views will offer better performance, reusability, and a cleaner development workflow. Unfortunately, I won’t be able to take advantage of any pg_search features without the extension.

IMO, great custom software begins and ends with great data modeling, and the out-of-the-box usability of RetoolDB has been a critical accelerant to that process for me so far, so I'm a bit reluctant to explore alternative patterns when Postgres is already well suited to meet these needs.

I would love it if you could keep me informed with any feedback the dev team may have on the request, and ultimately the prioritization if approved. I’m particularly curious to know if this aligns or conflicts with RetoolDBs product strategy. ie: is enabling these more costly workloads on a free & fully managed cloud service likely to happen at all?

9

Hi @MTarter,

Yes I fully agree, the more tooling for data and DBs the better.

Retool DB is a great place to get started but does run into limitations at scale, and we often direct larger enterprise orgs to move away from Retool DB to self-hosted DBs that can have extensions and additional tooling for more complex use cases.

I don’t think this request conflicts with our roadmap, we just have a ton of things on our roadmap :sweat_smile: I can keep you informed on any news I hear from the team on the ticket I created for you.

The last thing on the ticket from one of our engineers seems to indicate that this could be a straightforward issue to resolve.

They said “I think we can just ask Neon to do this for us. Not sure if there is a cost concern with extra compute, it's probably fine, and maybe a question that Neon could answer.”

10

Great, glad to hear its at least not directionally conflicting. No surprises here around Retool’s aggressive roadmap - I figured this would need to be a trivial add to even be considered, hopefully it turns out to be!

1 Like