CloudSQL SUPERUSER

Google Cloud Database
1

CloudSQL with PostgreSQL very slow performance (compared to PostgreSQL performance on a physical machine, three times more slowly)

effective_io_concurrency has a default value of 1 and I would like change it but this parametr is not available as Cloud SQL flag, ALTER SYSTEM is not supported

alter system set effective_io_concurrency = 150
ERROR: must be superuser to execute ALTER SYSTEM command

Is possible create SUPERUSER in CloudSQL ?

I have found information:

"Unsupported features for Cloud SQL for PostgreSQL

  • Any features that require SUPERUSER privileges

    An exception to this rule is made for the CREATE EXTENSION statement, but only for supported extensions.

  • Custom background workers

  • The psql client in Cloud Shell doesn’t support operations that require a reconnection, such as connecting to a different database using the \c command.

  • Low-Level Virtual Machine (LLVM) Just-in-Time (JIT) compilation.

Notable differences between PostgreSQL and Cloud SQL for PostgreSQL

How to increase storage efficiency or change effective_io_concurrency or create superuser ?

2

Google Cloud SQL is a fully managed relational database service for MySQL, PostgreSQL, and SQL Server. It is designed to automate much of the database management, including backups, patch management, failover, and other tasks that can be complex to handle. Because of this managed nature, certain features and privileges that you may be used to having with a self-managed PostgreSQL instance may not be available in Cloud SQL. Specially Changing**effective_io_concurrency**and Creating a SUPERUSER.

However, there are some general strategies you can use to improve performance on Cloud SQL:

  1. Optimize your queries: Sometimes, the root cause of slow performance isn’t the database configuration, but inefficient queries. Use EXPLAIN ANALYZE to understand how your queries are being executed and identify any potential issues.

  2. Indexing: Ensure that your tables are properly indexed. Indexing can significantly speed up query execution.

  3. Vertical Scaling: Increase the resources of your Cloud SQL instance. This includes increasing CPU, RAM, and storage.

  4. Horizontal Scaling: Implement read replicas to distribute read traffic, which can help to improve performance.

  5. Connection Pooling: Use Cloud SQL’s connection pooling feature to manage database connections more efficiently.

You might refer to the following link for additional tips:

https://cloud.google.com/sql/docs/postgres/admin-api/how-tos/performance

1 Like
3

Thank you !