Postgres compatibility

Important

This feature is in Public Preview in the following regions: westus, westus2, eastus, eastus2, northeurope, westeurope, australiaeast, brazilsouth, canadacentral, centralindia, centralus, southcentralus, southeastasia, uksouth.

This page describes how a Lakebase database instance is compatible with Postgres. As a managed Postgres service, there are some differences and functionality limitations.

Important

Azure Databricks database instances only support Postgres 16.

Optimize queries

The pg_stat_statements extension provides detailed information on query performance to help identify slow queries and optimize them.

Users can see the query texts of their queries and any roles from which they inherit privileges. If you have the ADMIN privilege on a role and you cannot see the query texts for that role, grant yourself membership in that role. If you have no privileges on a role, you cannot see its query texts for security reasons.

Postgres parameter settings

As a managed Postgres service, the database parameters are set based on the instance size. See all the parameter settings using:

SHOW ALL;

You can configure parameters that have a user context at the session, database, or role level. You cannot configure parameters at the instance level.

• Show parameters that can be set at the session, database, or role level.

  SELECT name
  FROM pg_settings
  WHERE context = 'user';
  

• Set a parameter for the session.

  SET maintenance_work_mem='1 GB';
  

• Set a parameter for all sessions connected to a database

  ALTER DATABASE databricks_postgres SET maintenance_work_mem='1 GB';
  

• Set a parameter for all sessions from a given user.

  ALTER USER "john@company.com" SET maintenance_work_mem='1 GB';
  

Supported encoding and collation

By default, the C.UTF-8 collation is used. C.UTF-8 supports the full range of UTF-8 encoded characters.

The UTF8 encoding (Unicode, 8-bit variable-width encoding) is supported.

To view the encoding and collation for your database, run the following query.

SELECT
    pg_database.datname AS database_name,
    pg_encoding_to_char(pg_database.encoding) AS encoding,
    pg_database.datcollate AS collation,
    pg_database.datctype AS ctype
FROM
    pg_database
WHERE
    pg_database.datname = 'your_database_name';

Note

In Postgres, you can't change a database's encoding or collation after it has been created.

Functionality limitations

Roles and permissions

• You can't access the host operating system.
• You can't connect using Postgres superuser.
  • Any functionality that requires superuser privileges or direct local file system access is not allowed.
  • The databricks_superuser takes the place of the Postgres superuser role. For information about the privileges associated with this role, see Database role types and permissions.

Replication

Creating replication slots, subscriptions, or publications is not supported.

Event triggers

Postgres event triggers are not supported.

Client query cancellation

The Postgres cancel protocol cannot be used to cancel in-progress queries.

Instead, create a new connection and run pg_cancel_backend(pid):

SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid() AND
query = '{The query you want to cancel}';

Supported extensions

Extension	Version
address_standardizer	3.3.3
address_standardizer_data_us	3.3.3
autoinc	1
bloom	1
btree_gin	1.3
btree_gist	1.7
citext	1.6
cube	1.5
dict_int	1
earthdistance	1.1
fuzzystrmatch	1.2
hll	2.18
hstore	1.8
insert_username	1
intagg	1.1
intarray	1.5
isn	1.2
lo	1.1
ltree	1.2
moddatetime	1
pg_graphql	1.5.9
pg_jsonschema	0.3.3
pg_prewarm	1.2
pg_stat_statements	1.1
pg_trgm	1.6
pgcrypto	1.3
pgrouting	3.4.2
pgrowlocks	1.2
plpgsql	1
postgis	3.3.3
postgis_raster	3.3.3
postgis_sfcgal	3.3.3
postgis_tiger_geocoder	3.3.3
postgis_topology	3.3.3
refint	1
seg	1.4
tablefunc	1
tcn	1
tsm_system_rows	1
tsm_system_time	1
unaccent	1.1
uuid-ossp	1.1
vector	0.8.0
xml2	1.1