--- title: "Balancing query time vs relevancy with text search" publication: "Postgres Weekly" issue: 427 published_at: "2021-10-20" canonical_url: "https://postgresweekly.com/issues/427" --- Plus function pipelines for Postgres, projecting revenue run rates with SQL, and pgmetrics now supports Postgres 14. | #​427 — October 20, 2021 [Web Version](https://postgresweekly.com/issues/427) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/kraydsj5otbilupwhdvl.jpg)](https://blog.timescale.com/blog/function-pipelines-building-functional-programming-into-postgresql-using-custom-operators/) [Function Pipelines: Building Functional Programming into Postgres?](https://blog.timescale.com/blog/function-pipelines-building-functional-programming-into-postgresql-using-custom-operators/) — The latest release from the Timescale folks (who are behind the time-series focused [TimescaleDB](https://github.com/timescale/timescaledb) extension) provides a way to write more _functional_ SQL for analyzing data through composing functions together. The first example given shows off the idea pretty well. David Kohn (Timescale) [Postgres Text Search: Balancing Query Time and Relevancy](https://about.sourcegraph.com/blog/postgres-text-search-balancing-query-time-and-relevancy/) — Postgres’s [pg_trgm](https://www.postgresql.org/docs/current/pgtrgm.html) provides ways to query and search text for specific strings of characters (as opposed to FTS which is more word oriented). Stephen demonstrates some of the performance concerns when using pg_trgm in particular and why tradeoffs are needed. Stephen Gutekanst (Sourcegraph) [![\](https://copm.s3.amazonaws.com/33432e4a.png)](https://pganalyze.com/index-advisor?utm_source=PostgresWeeklyPrimary10202021) [A Better Way to Index Your Postgres Database: pganalyze Index Advisor](https://pganalyze.com/index-advisor?utm_source=PostgresWeeklyPrimary10202021) — We all love [explain.depesz.com](https://explain.depesz.com) for sharing EXPLAIN plans. So we thought: Why not make a similar free tool, specifically for getting index advice on a query? Check out our free pganalyze Index Advisor and learn what the best index for your query is. pganalyze sponsor [OtterTune: An Automated Database Tuning Service for RDS Postgres](https://news.ycombinator.com/item?id=28868382) — I’ve linked to the Hacker News post as it has more content, but you could go to [OtterTune’s homepage](https://ottertune.com/) direct, if you prefer. OtterTune is a database optimization service built on concepts explained in [this 2017 paper](https://www.cs.cmu.edu/~ggordon/van-aken-etal-parameters.pdf) and it’s now available for the public to try (on RDS deployments only). Here’s [a five-minute explanation.](https://ottertune.com/blog/ottertune-explained-in-five-minutes) Andy Pavlo et al. [Upgrading Amazon RDS and Aurora Postgres v9.6 Databases](https://aws.amazon.com/blogs/database/upgrade-amazon-rds-and-amazon-aurora-postgresql-version-9-6/) — If you use either of AWS’s managed Postgres services and are using Postgres 9.6, you want to continue your upgrade story ASAP. Otherwise, AWS will _automatically_ upgrade you in early 2022 which could cause problems if you’re not prepared. Chandra sekhar Pathivada (Amazon Web Services) [Projecting Monthly Revenue Run Rate in Postgres](https://blog.crunchydata.com/blog/postgres-queries-for-projecting-monthly-revenue-run-rate) — If you’ve ever run a business or done reports for one, you’ll know how important those monthly recurring revenue (MRR) and annual recurring revenue (ARR) rates are, and you can calculate them from data stored with Postgres using the right SQL queries. Jonathan S. Katz [📈 How to Perform Data Evaluation Tasks with Postgres & TimescaleDB](https://tsdb.co/data-evaluation-with-postgres) Timescale sponsor [`WITH HOLD` Cursors and Transactions](https://www.cybertec-postgresql.com/en/with-hold-cursors-and-transactions-in-postgresql/) — This article describes how cursors and transactions interact, how `WITH HOLD` can overcome the limitations, plus some caveats and tricks. Laurenz Albe [Does QGIS Work with Postgres 14?](https://elephanttamer.net/?p=68#qgispg14) — In short, yes. Elephant Tamer Tools & Code [pgmetrics 1.12: Collect and Display Stats from Running Postgres Servers](https://pgmetrics.io/) — Written in Go, and here’s [an example of its output.](https://pgmetrics.io/docs/index.html#example) v1.12 adds Postgres 14 and PgBouncer 1.16 support, including collecting new PG14 specific stats. [GitHub repo](https://github.com/rapidloop/pgmetrics). RapidLoop [pg_timetable 4.2 Released: Advanced Job Scheduling for Postgres](https://github.com/cybertec-postgresql/pg_timetable) — The latest [4.2.0 release](https://github.com/cybertec-postgresql/pg_timetable/releases/tag/v4.2.0) fixes a bug in logging functionality and any users are encouraged to upgrade immediately. CYBERTEC PostgreSQL International GmbH [Visualizing Postgres Vacuum Progress](http://dtrace.org/blogs/dap/2019/05/22/visualizing-postgresql-vacuum-progress/) — This is a ‘golden oldie’ post I wanted to highlight again! If your database’s vacuum procedure is taking an extensive amount of time (as it is here) being able to visualize its progress is surely desirable and in this case a picture truly is worth a thousand words. Dave Pacheco **Got a new email?** You can [change your address here](https://postgresweekly.com/edit_subscription/*|UID|*). --- **Bored of us?** [Click here to stop getting Postgres Weekly](https://postgresweekly.com/leave/*|UID|*). --- **Got a link for us?** Reply and tell us. We can't include everything but we'll look at anything you send. Thanks! --- Published by Cooper Press Ltd. Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada. [Stop getting this newsletter.](https://postgresweekly.com/leave/*|UID|*)