Extension Management and Compatibility

Tokens

Latency

Turns

Tool Calls

Schema Calls

Response

vs

I'll help you check your PostgreSQL extensions and assess their upgrade compatibility. Here's how to get this information: ## Check Currently Installed Extensions Run this SQL query to see all installed extensions: ```sql SELECT e.extname AS extension_name, e.extversion AS version, n.nspname AS schema, c.description FROM pg_extension e LEFT JOIN pg_namespace n ON n.oid = e.extnamespace LEFT JOIN pg_description c ON c.objoid = e.oid ORDER BY e.extname; ``` Or for a simpler view: ```sql \dx ``` ## Check Extension Performance Impact For **pg_stat_statements** specifically: ```sql -- Check overhead settings SHOW shared_preload_libraries; SHOW pg_stat_statements.track; SHOW pg_stat_statements.max; -- See actual query statistics SELECT calls, total_exec_time, mean_exec_time, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; ``` For **PostGIS**: ```sql -- Check PostGIS version and configuration SELECT PostGIS_version(); SELECT PostGIS_full_version(); -- Check if any spatial indexes exist (can impact write performance) SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE indexdef LIKE '%USING gist%'; ``` ## Check Version Compatibility for Upgrade (13 → 15) ```sql -- Check available extension versions in your current system SELECT name, default_version, installed_version FROM pg_available_extensions WHERE name IN ('postgis', 'pg_stat_statements') ORDER BY name; ``` ## PostgreSQL 13 → 15 Extension Compatibility **Good news**: Both PostGIS and pg_stat_statements are well-supported in PostgreSQL 15: - **pg_stat_statements**: Built-in extension, fully compatible. Minor schema changes between versions but handles upgrades automatically. - **PostGIS**: Version 3.x supports both PG 13 and 15. You'll need PostGIS 3.1+ for PG 15. ## Pre-Upgrade Checklist Before upgrading, run this query to prepare: ```sql -- Save current extension versions CREATE TABLE extension_backup AS SELECT extname, extversion FROM pg_extension WHERE extname NOT IN ('plpgsql'); -- Check for any custom types or functions that might conflict SELECT n.nspname, p.proname, pg_get_functiondef(p.oid) FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid WHERE n.nspname NOT IN ('pg_catalog', 'information_schema') AND p.proname LIKE '%geometry%' OR p.proname LIKE '%geography%'; ``` ## Testing the Upgrade 1. **After upgrade**, update extensions: ```sql ALTER EXTENSION postgis UPDATE; ALTER EXTENSION pg_stat_statements UPDATE; ``` 2. **Verify functionality**: ```sql -- Test PostGIS SELECT ST_AsText(ST_Point(0, 0)); -- Test pg_stat_statements SELECT count(*) FROM pg_stat_statements; ``` ## Performance Impact Notes - **pg_stat_statements**: ~5-10% overhead on high-throughput systems. Controlled by `pg_stat_statements.max` (default 5000). - **PostGIS**: Minimal overhead unless using spatial indexes heavily. GIST indexes can slow down writes but dramatically speed up spatial queries. Would you like me to create a script to check all of this automatically, or help you set up a test environment for the upgrade?

I'll help you check your PostgreSQL extensions, their impact, and upgrade compatibility. Here are the key queries and steps: ## Check Installed Extensions ```sql -- List all installed extensions with versions SELECT extname AS extension_name, extversion AS version, nspname AS schema FROM pg_extension e JOIN pg_namespace n ON e.extnamespace = n.oid ORDER BY extname; -- More detailed info including dependencies SELECT e.extname, e.extversion, n.nspname, c.description FROM pg_extension e LEFT JOIN pg_namespace n ON e.extnamespace = n.oid LEFT JOIN pg_description c ON e.oid = c.objoid ORDER BY e.extname; -- Check available (but not installed) extensions SELECT * FROM pg_available_extensions ORDER BY name; ``` ## Check Performance Impact **For pg_stat_statements:** ```sql -- Check if it's tracking queries SELECT * FROM pg_stat_statements LIMIT 5; -- See overhead settings SHOW shared_preload_libraries; SHOW pg_stat_statements.track; SHOW pg_stat_statements.max; -- Memory usage (in shared_buffers) SELECT pg_size_pretty(sum(pg_stat_statements_info.dealloc)) FROM pg_stat_statements_info; ``` **For PostGIS:** ```sql -- Check PostGIS version and modules SELECT PostGIS_full_version(); -- Find tables using PostGIS types SELECT schemaname, tablename, attname, typname FROM pg_attribute a JOIN pg_class c ON a.attrelid = c.oid JOIN pg_namespace n ON c.relnamespace = n.oid JOIN pg_type t ON a.atttypid = t.oid WHERE typname IN ('geometry', 'geography', 'raster', 'topology') AND n.nspname NOT IN ('pg_catalog', 'information_schema'); ``` ## Check Upgrade Compatibility (13 → 15) ```sql -- Check current PostgreSQL version SELECT version(); -- Verify extension update paths exist SELECT name, installed_version, default_version, CASE WHEN installed_version = default_version THEN 'Up to date' ELSE 'Update available' END as status FROM pg_available_extensions WHERE installed_version IS NOT NULL ORDER BY name; ``` ## Key Upgrade Considerations **PostGIS (13 → 15):** - PostGIS 3.1+ supports PostgreSQL 15 - If you're on PostGIS 2.x, upgrade to 3.x first - Use `SELECT PostGIS_version();` to check your version **pg_stat_statements:** - Fully compatible, but reset stats during upgrade - Backup your stats if needed: `SELECT * FROM pg_stat_statements;` **Before upgrading:** ```bash # Check extension compatibility pg_dump --schema-only your_database | grep "CREATE EXTENSION" # Or from psql: \dx # Check for deprecated features \df *postgis* ``` **Upgrade steps for extensions:** ```sql -- After PostgreSQL upgrade, update extensions: ALTER EXTENSION postgis UPDATE; ALTER EXTENSION pg_stat_statements UPDATE; -- Verify updated versions \dx ``` ## Performance Impact Notes - **pg_stat_statements**: ~5-10% overhead when tracking all queries; minimal if tracking only top-level - **PostGIS**: No runtime overhead unless actively querying spatial data; adds ~20-30MB to shared memory Would you like me to create a script that runs all these checks and generates a compatibility report for your upgrade?

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.

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.

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.

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.

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.

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.