HomeServerlessSQL DatabasesReference Content
Known differences between Serverless SQL Databases and PostgreSQL
Jump toUpdate content

Known differences between Serverless SQL Databases and default PostgreSQL

Reviewed on 04 August 2023Published 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

  • Notifying and listening channels using the NOTIFY and LISTEN commands can be performed, but message delivery isn’t 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.

  • Asynchronous commands such as PQsendQuery and PQgetResult cannot be performed.

  • 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';

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';