Known differences between Serverless SQL Databases and default PostgreSQL

Reviewed on 13 August 2024 • Published on 04 August 2023

Serverless SQL Database is based on PostgreSQL engine and thus provides standard PostgreSQL compatibility for most PostgreSQL features and commands. Due to the nature of a managed service, autoscaling support and connection pooling support, some features are not currently supported. This means either the corresponding SQL command will not be authorized, or will not behave similarly to a standard PostgreSQL instance.

If you require strict compatibility with all PostgreSQL features, you can use Managed Databases for PostgreSQL instead.

Unsupported SQL features

Installing extensions is not yet supported. We plan to support extensions similar to Managed Databases for PostgreSQL available extensions in future releases.
```
CREATE EXTENSION extension_name;
NOTIFY channel, 'message';
```

Notifying and listening channels using the NOTIFY and LISTEN commands can be performed, but message delivery is not guaranteed.

LISTEN channel;
NOTIFY channel, 'message';

Creating temporary tables or views is not supported.

CREATE TEMPORARY TABLE table as {query};
CREATE TEMPORARY VIEW view as {query};

Refreshing materialized view concurrently cannot be performed.

REFRESH MATERIALIZED VIEW CONCURRENTLY view

Note

You can still refresh materialized views without the CONCURRENTLY option.

Declaring a cursor in transactions with WITH HOLD is not supported.

BEGIN;
DECLARE cursor CURSOR WITH HOLD FOR {query};
COMMIT;

Creating subscriptions cannot be performed.

CREATE SUBSCRIPTION subscription CONNECTION 'connection' PUBLICATION publication;

Preparing transactions is not supported.

PREPARE TRANSACTION 'transactionid';

Discarding all session states using the DISCARD ALL command is possible, but results are not guaranteed. Specifically, some session state variables might be kept if multiple SQL clients perform operations concurrently.

DISCARD ALL;

pg_advisory_lock command can be used, but the lock is not guaranteed. Consequently, third-party tools that use this feature, such as Terraform when storing Terraform state using pg backend, are not fully supported. State locking is not guaranteed if multiple Terraform clients edit the same state concurrently.
```
SELECT * FROM pg_advisory_lock(1);
```

Unsupported SQL client features

Grafana: Serverless SQL Databases cannot be added as a PostgreSQL data source in Grafana 10.1 and older versions, as Server Name Indication (SNI) is not supported. However, this feature is supported by Grafana 10.2 or higher.
r2dbc-postgresql (Java): Serverless SQL Databases cannot be queried with r2dbc-postgresql client version before 1.0.5, as Server Name Indication (SNI) is not supported. However, this feature is supported by r2dbc-postgresql 1.0.5. As a consequence, Java frameworks using this client are also limited depending on which client version they use. For instance, Micronaut framework before version 4.4.1 cannot query Serverless SQL Databases using micronaut-r2dbc/r2dbc-postgresql, but can however perform queries using standard jdbc client.
vertx-sql-client (Java): Serverless SQL Databases cannot be queried with vertx-sql-client yet.

The client_encoding option can only be set to UTF8 or UNICODE:

pgcli postgresql://{username}:{password}@{host}:{port}/{databasename}?client_encoding=UTF8|UNICODE

Setting the client-side encoding to a different value, such as SQL_ASCII when connecting with a client is not supported.

ETL tools such as Airbyte, Fivetran or Meltano cannot load data into a Serverless SQL Database, as they require TEMPORARY TABLES, which are currently not supported. However, data can be loaded from a Serverless SQL Database into another target, but not using Change Data Capture (CDC) options, as they require SUBSCRIPTIONS, which are currently not supported.
Terraform: Storing Terraform states using pg backend can be done, but terraform state locking when multiple Terraform clients are editing the same state concurrently is not guaranteed (since this guarantees rely on pg_advisory_lock command usage, which is not supported currently).

Unsupported configuration commands

DDL (Data Definition Language) queries on objects such as databases and users cannot be performed. Corresponding actions can still be performed directly using the Scaleway console, the API, Terraform, and IAM.
```
CREATE/UPDATE/DELETE DATABASE database;
CREATE/UPDATE/DELETE USER username;
GRANT SELECT ON TABLE table TO role;
REVOKE SELECT ON TABLE table FROM role;
```

SET or RESET commands to change run-time configuration parameters can be performed, but will be shared across multiple clients and might be lost when a database switches to an idle state, or during autoscaling operations.

SET TIME ZONE 'Europe/Paris';
RESET timezone;

Security labels cannot be defined or changed.

SECURITY LABEL FOR provider ON TABLE table IS 'label';