Which Postgres extensions Amazon RDS supports, how to list them with SHOW rds.extensions, who can run CREATE EXTENSION, which ones need shared_preload_libraries, how rds.allowed_extensions restricts them, and how to keep extensions in migrations.

Amazon RDS for PostgreSQL offers a fixed set of extensions per engine version, and nothing outside that list installs. pg_trgm, pgcrypto, citext and pgvector are all there, but the errors get confusing once an extension needs a server library or the instance restricts installs.
This guide covers how to check what an instance supports, who can run CREATE EXTENSION, which extensions need a parameter group change, and how to keep extensions in migrations that Encore runs both locally and on RDS.
The permission and parameter rules come from the RDS guide to PostgreSQL extensions.
The first query below returns the allowed extension names for that instance, and the second shows the versions the server can install and the ones already installed in the current database.
SHOW rds.extensions;
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE name IN ('pg_trgm', 'citext', 'vector', 'pg_cron', 'postgis')
ORDER BY name;
AWS notes that an extension added in a minor version release can show up inaccurately in rds.extensions, so for a planned dependency, check the release notes table for your exact version too. Extensions are per database: enabling pg_trgm in orders does nothing for billing on the same instance.
On RDS for PostgreSQL 12 and earlier, installing an extension takes rds_superuser, which the master user created with the instance holds. On 13 and later, a role with CREATE on the database can install any trusted extension, which covers common ones like pg_trgm, citext, pgcrypto and hstore, while untrusted ones still need rds_superuser. The practical rule is to run migrations that create extensions as a role that can, and to keep the application role at the privileges it needs for queries.
Some extensions are a library that the server loads at start, not only SQL objects. On RDS the default parameter group typically loads only pg_stat_statements, so anything else goes into a custom DB parameter group. pg_cron is the usual example:
aws rds modify-db-parameter-group \
--db-parameter-group-name app-pg17 \
--parameters "ParameterName=shared_preload_libraries,ParameterValue='pg_stat_statements,pg_cron',ApplyMethod=pending-reboot"
aws rds reboot-db-instance --db-instance-identifier app-db
After the reboot, CREATE EXTENSION pg_cron; works in the database named by cron.database_name, which defaults to postgres. Running it anywhere else, or before the reboot, is where the "must be loaded via shared_preload_libraries" error comes from.
rds.allowed_extensions is a parameter that lists the only extensions that can be installed on the instance, which suits shared instances where you want to stop a role with CREATE from adding anything trusted it likes. Once it is set, every extension a migration creates has to be on the list, including pg_cron after you preload it, and a missing entry fails the migration on that instance while it passes everywhere else.
A minor or major engine upgrade can ship a newer extension version, and existing databases keep the old one until you update it. Check with pg_available_extensions and then update in a migration, so staging and production move together:
ALTER EXTENSION vector UPDATE;
ALTER EXTENSION pg_trgm UPDATE;
Major version upgrades also check extension compatibility before they run, and some extensions have to be updated, or dropped and recreated, before the upgrade can start, as the RDS major version upgrade docs describe.
Encore brings infrastructure from code, so the database is a declaration in the service that uses it and the extension is a line in that database's first migration. encore run starts Postgres locally and applies the migration, using a build that includes extensions such as pg_trgm, citext, pgcrypto and vector, which the Postgres extensions docs list with versions. On deploy to AWS, Encore provisions RDS for PostgreSQL in your account, as the AWS infrastructure docs describe, and runs the same migration there, so the extension has to be on the RDS list for that engine version too.

Run in your terminal to get started locally.
-- customers/migrations/1_create_customers.up.sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS citext;
CREATE TABLE customers (
id BIGSERIAL PRIMARY KEY,
email CITEXT NOT NULL UNIQUE,
name TEXT NOT NULL
);
CREATE INDEX customers_name_trgm ON customers USING gin (name gin_trgm_ops);
The service declares the database and uses the trigram operator in a search endpoint:
// customers/customers.go
package customers
import (
"context"
"encore.dev/storage/sqldb"
)
var db = sqldb.NewDatabase("customers", sqldb.DatabaseConfig{
Migrations: "./migrations",
})
type SearchParams struct {
Q string `query:"q"`
}
type Customer struct {
ID int64 `json:"id"`
Email string `json:"email"`
Name string `json:"name"`
}
type SearchResponse struct {
Customers []Customer `json:"customers"`
}
//encore:api public method=GET path=/customers/search
func Search(ctx context.Context, p *SearchParams) (*SearchResponse, error) {
rows, err := db.Query(ctx, `
SELECT id, email::text, name
FROM customers
WHERE name % $1
ORDER BY similarity(name, $1) DESC
LIMIT 10`, p.Q)
if err != nil {
return nil, err
}
defer rows.Close()
out := []Customer{}
for rows.Next() {
var c Customer
if err := rows.Scan(&c.ID, &c.Email, &c.Name); err != nil {
return nil, err
}
out = append(out, c)
}
return &SearchResponse{Customers: out}, rows.Err()
}
citext makes the unique constraint on email case-insensitive, and the GIN trigram index serves the % similarity operator, so a search for jon smth still finds Jon Smith. A coding agent that needs fuzzy search adds the extension and the index in the migration it is already writing, runs it locally with encore run, and the same file runs on every environment instead of a manual psql session against production.
Extensions that need shared_preload_libraries are a server setting rather than a migration. For scheduled work, an Encore cron job declared in Go is the in-code option, and it is managed by the Encore platform rather than by pg_cron on the database.
Best fit: Go or TypeScript backends on AWS that want extensions such as pg_trgm, citext or pgvector enabled by migrations that run the same way locally and on RDS.
Connect to the instance and run SHOW rds.extensions; to list the extensions that engine version allows, and SELECT name, default_version, installed_version FROM pg_available_extensions; to see versions. AWS also publishes a table per major version in the RDS for PostgreSQL release notes, and notes that extensions added in a minor release can show up inaccurately in rds.extensions.
On RDS for PostgreSQL 12 and earlier, installing an extension requires the rds_superuser role, which the master user has. On version 13 and later, any role with CREATE permission on the database can install trusted extensions, and other extensions still need rds_superuser.
pg_cron is a library that has to be loaded at server start, so add pg_cron to shared_preload_libraries in a custom DB parameter group, reboot the instance, and then run CREATE EXTENSION pg_cron in the database named by cron.database_name. If the instance uses rds.allowed_extensions, pg_cron also has to be on that list.
Put CREATE EXTENSION IF NOT EXISTS in a migration of the database declared with sqldb.NewDatabase. encore run applies it to the local Postgres, which ships extensions such as pg_trgm, citext and vector, and a deploy runs the same migration on the RDS database in your AWS account.
Deploy to your cloud on AWS and Google Cloud
without managing infrastructure
Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.