Postgres compatibility

Preview

This feature is in Public Preview in the following regions: us-east-1, us-west-2, eu-west-1, ap-southeast-1, ap-southeast-2, eu-central-1, us-east-2, ap-south-1.

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

Databricks database instances only support Postgres 16.

Postgres parameter settings

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

Psql

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.

  Psql

  SELECT name
  FROM pg_settings
  WHERE context = 'user';

• Set a parameter for the session.

  Psql

  SET maintenance_work_mem='1 GB';

• Set a parameter for all sessions connected to a database

  Psql

  ALTER DATABASE databricks_postgres SET maintenance_work_mem='1 GB';

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

  Psql

  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.

Psql

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):

Psql

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