We recently migrated from on-premise SQL to cloudSQL Postgres, but we have noticed that the performance has decreased significantly. Previously, a query that took less than a second on MSSQL is now taking between 10-16 seconds on cloudSQL Postgres. The query does not have any filter condition, and all the table joins are indexed. It only returns 23k records, and the read consistently takes the same amount of time. The query do a distinct and order by We are wondering if there is a way to enable read cache in Postgres, as we have not been able to locate a parameter for this in Terraform.
PostgreSQL automatically caches data in the shared buffer pool, which is controlled by the shared_buffers configuration parameter. This parameter determines how much memory is dedicated to caching data blocks in memory. While PostgreSQL does not have a āread cacheā feature that can be toggled on or off like some other databases, it effectively caches data through its shared buffer pool.
The shared_buffers setting is part of PostgreSQLās configuration and is not specific to Cloud SQL for PostgreSQL. However, Cloud SQL does allow you to set this parameter through its instance settings.
To adjust shared_buffers in Cloud SQL using Terraform, you would modify the settings block within the google_sql_database_instance resource, as shown in the previous example. This is not enabling a āread cacheā per se but rather tuning the existing caching mechanism of PostgreSQL.
The statement-level cache you mentioned does not exist in PostgreSQL. PostgreSQL does not cache query results at the statement level. Instead, it caches the data blocks that queries access. If the same data is queried frequently, it is likely to be served from the cache, which can improve performance.
The shared cache you referred to is likely the shared buffer pool, which is shared across all sessions and queries.
To set shared_buffers in PostgreSQL, you would indeed modify the postgresql.conf file or use the ALTER SYSTEM command, but keep in mind that Cloud SQL instances are managed, and direct access to the postgresql.conf file is not provided. Instead, you would use the Cloud SQL Admin API or the Google Cloud Console to change this setting.
Lastly, itās important to note that simply increasing shared_buffers is not a guaranteed way to improve performance. Proper analysis of query plans and system performance is necessary to tune PostgreSQL settings effectively.
Shared buffers are enabled by default in Google Cloud Postgres.. The default value for the shared_buffers parameter is typically a percentage of the total memory of the machine, rather than a fixed value. This percentage is often set by the cloud provider, such as Google Cloud, to ensure optimal performance for most workloads.
For Google Cloud SQL, the default value and any adjustments to shared_buffers would be managed by Googleās configuration defaults or by the user through the Google Cloud Console or the Cloud SQL Admin API. Itās important to note that this parameter can only be set at server start, meaning changes require a restart of the database service.
Iām seeing the same issue. Auth Proxy doesnāt help at all. My local machine is 30x slower than my GKE Pod <> Cloud SQL. could you help us to figure out the root cause?