Extensions

July 1, 2025

PostgreSQL extensions are add-on modules that extend the functionality of your database beyond the core features. They provide additional data types, functions, operators, indexes, and other capabilities without requiring modifications to the PostgreSQL source code.

Extensions work by packaging related database objects (functions, data types, operators, etc.) into a single installable unit. When you install an extension, PostgreSQL loads the necessary code and creates the required database objects in your database schema. This modular approach allows you to add only the functionality you need while keeping your database lightweight and maintainable.

Types of Extensions

PostgreSQL extensions generally fall into two main categories:

Native Extensions

These are extensions that ship with PostgreSQL itself and are maintained by the PostgreSQL core team. Examples include:

• Data type extensions: citext, hstore, uuid-ossp for specialized data types
• Index extensions: btree_gin, btree_gist for advanced indexing capabilities
• Text search extensions: unaccent, pg_trgm for full-text search enhancements
• Utility extensions: pgcrypto for cryptographic functions, tablefunc for table manipulation

Community Extensions

These are developed and maintained by the community or third-party organizations. Popular examples include:

• Vector search: pgvector for AI/ML vector operations
• Query optimization: pg_hint_plan for query plan hints
• Partitioning: pg_partman for automated table partitioning

Managing Extensions

Installing an Extension

To install an extension in your database, use the CREATE EXTENSION command:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

Removing an Extension

To remove an extension from your database, use the DROP EXTENSION command:

DROP EXTENSION IF EXISTS pgcrypto;

Updating an Extension

To update an extension to a newer version, use the ALTER EXTENSION command:

ALTER EXTENSION pgcrypto UPDATE;

Viewing Installed Extensions

To see all extensions currently installed in your database, use this query:

SELECT name, default_version, installed_version, comment 
FROM pg_available_extensions 
WHERE installed_version IS NOT NULL 
ORDER BY name;

To view all available extensions (both installed and available for installation):

SELECT name, default_version, comment 
FROM pg_available_extensions 
ORDER BY name;

Installing Extensions in Specific Schemas

By default, extensions are installed in the public schema. To install an extension in a specific schema:

CREATE EXTENSION IF NOT EXISTS pgcrypto SCHEMA my_schema;

To move an existing extension to a different schema:

ALTER EXTENSION pgcrypto SET SCHEMA new_schema;

Note: Not all extensions support being installed in non-public schemas. Most utility and data type extensions work fine, but some system-level extensions must remain in the public schema.

Troubleshooting

Common Extension Issues

Extension not found

ERROR: extension "extension_name" is not available

This means the extension is not installed on the server or not supported. Check the supported extensions list below.

Permission denied

ERROR: permission denied to create extension "extension_name"

Extensions typically require database owner or superuser privileges. Contact support if you encounter permission issues.

Extension already exists

ERROR: extension "extension_name" already exists

Use CREATE EXTENSION IF NOT EXISTS extension_name; to avoid this error, or check installed extensions first.

Dependency conflicts

ERROR: required extension "dependency_name" is not installed

Some extensions depend on others. Install the required dependency first, then retry installing your desired extension.

Supported Extensions

Supported Native Extensions

Supported Community Extensions

Need Additional Extensions?

If you require an extension that is not currently supported, please reach out to support. We regularly evaluate and add new extensions based on customer needs and security considerations.

Configuring Extensions in the Dashboard

Some extensions require explicit configuration through the PlanetScale dashboard and may consume additional resources on your database instances. These extensions often have configurable parameters that need to be set according to your specific use case.

Dashboard Configuration

To configure extensions through the dashboard:

1. From the PlanetScale organization dashboard, select the desired database
2. Navigate to the Cluster configuration page from the menu on the left
3. Choose the branch whose parameters you'd like to configure in the "Branch" dropdown
4. Select the Extensions tab
5. Enable the desired extensions and configure their parameters
6. Click Queue extension changes to apply the configuration
7. Once you're ready to apply the changes, click "Apply changes"

Extensions with Dashboard Configuration

pg_cron

• Can be enabled through the dashboard
• Has no additional parameters

pg_hint_plan

• Requires enabling through the dashboard
• Configurable parameters include:
  • pg_hint_plan.enable_hint - Enable/disable hint functionality
  • pg_hint_plan.enable_hint_table - Enable hint table functionality
  • pg_hint_plan.parse_messages - Control parsing message output
  • pg_hint_plan.debug_print - Enable debug output
  • pg_hint_plan.message_level - Set message verbosity level

pg_stat_statements

• Requires enabling through the dashboard
• Configurable parameters include:
  • pg_stat_statements.max - Maximum number of statements tracked
  • pg_stat_statements.track - Which statements to track
  • pg_stat_statements.track_utility - Track utility statements
  • pg_stat_statements.track_planning - Track planning statistics
  • pg_stat_statements.save - Save statistics across server restarts

Note: Some extensions like pgextwlist and pginsights are always enabled for PlanetScale databases and don't require manual configuration.

Need help?

Get help from the PlanetScale Support team, or join our GitHub discussion board to see how others are using PlanetScale.