Extension Management and Compatibility

infoMigration

Need to understand available extensions, their impact, and compatibility across PostgreSQL versions or platforms.

Prompt: “We're using several PostgreSQL extensions like PostGIS and pg_stat_statements. How do I check which extensions are installed, their performance impact, and whether they'll work if we upgrade from PostgreSQL 13 to 15?”

Agent Playbook

When an agent encounters this scenario, Schema provides these diagnostic steps automatically.

When evaluating extension compatibility for a PostgreSQL upgrade, start by cataloging what's actually installed and their current versions, then systematically verify compatibility with the target PostgreSQL version before assessing performance impact and platform constraints. The goal is to catch breaking changes early and understand the full scope of what needs to migrate before you're in the middle of an upgrade window.

1Catalog currently installed extensions and their versions
Run `SELECT * FROM pg_extension;` or use `\dx` in psql to get the full list of installed extensions, their versions, and which schemas they're in. Don't rely on memory or old docs—I've seen teams forget about extensions installed years ago that are still loaded and can break upgrades. Cross-reference this with your application dependencies to ensure you know which extensions are actually critical versus remnants from past experiments.
2Verify extension compatibility with PostgreSQL 15
For each extension, check the PostgreSQL 15 release notes and the extension's own documentation for compatibility. PostGIS, for example, requires specific minimum versions for PostgreSQL 15 support—PostGIS 3.2+ works with PG15, but earlier versions don't. Check `pg_available_extensions` on a test PostgreSQL 15 instance to see which versions are packaged, and look for any breaking changes or deprecated features that might affect your usage.
3Assess current performance overhead from extensions
Use `pg_stat_statements` itself to measure its overhead—it typically adds 5-10% query planning overhead at default settings. For other extensions, check `postgresql-queries-duration-max` before and after temporarily disabling non-critical extensions on a replica to establish a baseline. PostGIS functions can be expensive; look for slow queries using geometry operations that might benefit from optimizer improvements in PostgreSQL 15.
4Verify target platform support for your extensions
If you're moving to a managed service (RDS, Cloud SQL, Azure Database), check their extension allow-lists—not all platforms support all extensions, and some require special permissions. For example, AWS RDS supports PostGIS but not all PostGIS versions are available on all RDS PostgreSQL versions. Check `postgresql-database-size` to ensure any platform-specific extensions (like RDS-only tools) aren't critical if you're changing platforms.
5Map out the extension upgrade sequence
Some extensions need to be upgraded before the PostgreSQL upgrade (`ALTER EXTENSION ... UPDATE TO ...`), others after, and some require a full reinstall. PostGIS in particular has a complex upgrade path with intermediate versions sometimes required. Test this on a clone with realistic data—extension upgrades can take significant time on large databases and may require exclusive locks.
6Identify alternatives for incompatible or problematic extensions
If you find extensions that won't work on PostgreSQL 15 or your target platform, research alternatives now rather than during your upgrade window. Sometimes PostgreSQL's built-in features have absorbed extension functionality (e.g., parallel query improvements reducing need for some optimization extensions). Document these alternatives and estimate the effort to migrate—this might push your timeline or change your upgrade strategy.

Technologies

Related Insights

Relevant Metrics

Monitoring Interfaces

PostgreSQL Native
PostgreSQL Prometheus Exporter (pg_exporter)