Search code examples
postgresqlamazon-web-servicesfull-text-searchamazon-rdstsvector

How to Full Text Search - Extension "tsvector" is not supported by Amazon RDS

I'm using Postgres v12.8 on AWS RDS. Installng TSVECTOR I get the error:

SQL Error [22023]: ERROR: Extension "tsvector" is not supported by Amazon RDS Detail: Installing the extension "tsvector" failed, because it is not on the list of extensions supported by Amazon RDS. Hint: Amazon RDS allows users with rds_superuser role to install supported extensions. See: SHOW rds.extensions;

enter image description here

The reason is because AWS RDS Postgres doesn't support the Extension: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.Extensions

And it doesn't look like AWS RDS are going to add it: https://dba.stackexchange.com/a/111481/9328

So I have two questions,

  1. Was TSVECTOR supported in the past in v9 (as per On AWS RDS Postgres, how to have dictionaries and unaccented full-text search?)? If that's the case what are these people doing now as AWS have warned for over a year they would automatically upgrade v9 Postgres db's on the 18th Jan 2022?

  2. What can I use to achieve Full Text Search in Postgres RDS? I don't want to have to spin up an EC2 or a Container for access to install TSVECTOR, surely there must be an alternative?

The documentation says RDS Postgres supports Full Text Search: https://aws.amazon.com/rds/postgresql/features/

Full Text Search Dictionaries – PostgreSQL supports Full Text Searching that provides the capability to identify natural-language documents that satisfy a query, and optionally to sort them by relevance to the query.

Solution

  • Perhaps Amazon did something strange, but in PostgreSQL there is no tsvector extension. tsvector is a built-in data type, and you don't have to install any extensions to use full-text search.