Postgres compatibility

Beta

Lakebase Postgres (Autoscaling Beta) is the next version of Lakebase, available for evaluation only. For production workloads, use Lakebase Public Preview. See choosing between versions to understand which version is right for you.

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

Postgres version support

Lakebase Autoscaling Beta supports Postgres 16 and Postgres 17.

Postgres extension support

Lakebase supports numerous Postgres extensions. For the full list, see Postgres extensions.

Session, memory, and storage

Session context

The Lakebase scale-to-zero feature automatically closes idle connections after a period of inactivity.

When connections are closed, anything in the session context, such as temporary tables, prepared statements, advisory locks, and NOTIFY and LISTEN commands, is lost.

To avoid losing session-level contexts, you can disable scale-to-zero. However, doing so means your compute runs 24/7.

Memory

SQL queries and index builds can generate large volumes of data that may not fit in memory. The size of your compute determines the amount of memory available.

Unlogged tables

Unlogged tables are tables that do not write to the Postgres write-ahead log (WAL). These tables are stored in compute local storage and are not persisted across compute restarts or when a compute scales to zero. This is unlike standard Postgres, where unlogged tables are only truncated in the event of abnormal process termination. Additionally, unlogged tables are limited by compute local disk space. Lakebase computes allocate 20 GiB of local disk space or 15 GiB times the maximum compute size (whichever is highest) for temporary files used by Postgres.

Temporary tables

Temporary tables exist only for the lifetime of a session (or optionally a transaction). Like unlogged tables, they are stored in compute local storage and limited by local disk space.

Postgres logs

Access to Postgres logs is not supported.

Statistics collection

Statistics collected by the Postgres cumulative statistics system are not saved when a compute (where Postgres runs) scales to zero. To avoid losing statistics, you can disable the scale-to-zero feature. However, disabling scale to zero also means that your compute will run 24/7.

Postgres parameter settings

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

PostgreSQL

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.

  PostgreSQL

  SELECT name
  FROM pg_settings
  WHERE context = 'user';

• Set a parameter for the session.

  PostgreSQL

  SET maintenance_work_mem='1 GB';

• Set a parameter for all sessions connected to a database.

  PostgreSQL

  ALTER DATABASE lakebase SET maintenance_work_mem='1 GB';

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

  PostgreSQL

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

Database encoding and collations

Database encoding

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 also supported.

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

PostgreSQL

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.

Collations

A collation is an SQL schema object that maps an SQL name to locales provided by libraries installed in the operating system.

By default, Lakebase uses the C.UTF-8 collation. Another provider supported by Lakebase is icu, which uses the external ICU library.

Lakebase provides a full series of predefined icu locales in case you require locale-specific sorting or case conversions.

• View all predefined locales:

PostgreSQL

SELECT * FROM pg_collation;

• Create a database with a predefined icu locale:

PostgreSQL

CREATE DATABASE my_arabic_db
LOCALE_PROVIDER icu
icu_locale 'ar-x-icu'
template template0;

• Specify a locale for individual columns:

PostgreSQL

CREATE TABLE my_ru_table (
    id serial PRIMARY KEY,
    russian_text_column text COLLATE "ru-x-icu",
    description text
);

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 Manage roles.

Replication

Postgres logical replication is not supported in Lakebase Postgres (Autoscaling Beta). Replicating data to or from a Lakebase database using native Postgres replication is not available.

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

PostgreSQL

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

Tablespaces

Lakebase doesn't support Postgres tablespaces. Attempting to create a tablespace with the CREATE TABLESPACE command results in an error. This is because of Lakebase's managed cloud architecture, which doesn't permit direct file system access for custom storage locations.

If you have existing applications or scripts that use tablespaces for organizing database objects across different storage devices, you must remove or modify these references when migrating to Lakebase.