Upgrading Cloud SQL Postgres PROJ and GEOS libraries

[blakgeek]

I'm running PostGIS on Cloud SQL for PostgreSQL 16 and experiencing significant performance issues with spatial operations compared to my local PostgreSQL environment. After investigation, I've found that the underlying geometry libraries are outdated:

Cloud SQL PostGIS:

Instance Type: 16 vCPU, 104GB RAM, 500GB SSD
PostGIS: 3.5.2
GEOS: 3.11.4
PROJ: 6.3.2
Local PostgreSQL (on less powerful hardware):

Hardware: Mac laptop with fewer resources than the cloud instance
PostGIS: 3.4.4
GEOS: 3.12.2
PROJ: 9.2.1
Performance Issue
The same spatial query runs 3-4x slower on Cloud SQL despite Cloud SQL having significantly more powerful hardware. My local machine is running with fewer CPU cores and less RAM, yet consistently outperforms the cloud instance. I've confirmed through EXPLAIN ANALYZE that each ST_Intersects operation takes approximately 6-7ms on Cloud SQL versus 2ms locally, which adds up significantly when processing thousands of operations.

Question
Is there a way to upgrade the GEOS and PROJ libraries on Cloud SQL to the latest versions? Or alternatively, is there a recommended approach for running PostgreSQL with the latest PostGIS libraries on GCP while maintaining a managed database experience?

I've already:

Updated PostGIS to version 3.5.2
Optimized database parameters
Ensured proper statistics and indexing
Verified the query plan is reasonable
Any guidance on getting access to newer versions of these spatial libraries in a GCP environment would be greatly appreciated.

[greb]

Hi @blakgeek,

Welcome to Google Cloud Community!

It seems that the older versions of GEOS and PROJ installed on Cloud SQL are likely creating a bottleneck in performance compared to PostGresSQL running locally. Because Cloud SQL handles these libraries, directly upgrading them is not possible, but below are some action items that may help.

1. Check Your Versions:
   Make sure to execute this prior to making any adjustments:
   

   SELECT PostGIS_full_version();

2. Upgrade PostGIS (If Possible):
   To change your current version of PostGIS, run the following command:
   

   ALTER EXTENSION postgis UPDATE TO 'latest_supported_version';

   You may check the Cloud SQL extension documentation for their latest supported versions.
   
   
3. Watch for Google’s Updates:
   Cloud SQL increments their library versions periodically so keep tabs on maintenance changelog for updates.
   
   
4. Consider GCE for Full Control:
   Running PostgreSQL on Google Compute Engine (GCE) is the best choice if you want complete autonomy with new GEOS/PROJ, but you will have to handle getting updates and scaling them by yourself.
   
   
5. Optimize Queries:
   * Check if GIST indexes exist on geometry columns.
   * Use the slow query EXPLAIN ANALYZE to optimize.
   * If needed, partition large datasets.

If performance is still subpar, reach out to Google Cloud Support as they may offer other solutions.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.

[blakgeek]

I have done that and it is definitely the related to the versions of the
libraries. How often does Google update these libraries? The current
versions are extremely old.