Postgres compatibility

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 supports Postgres 16, Postgres 17, and Postgres 18. Postgres 17 is the default version. To use Postgres 18, select it when creating a new project.

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

Postgres maintains two kinds of statistics:

  • Planner statistics: ANALYZE generates these and stores them in the pg_statistic catalog and in the reltuples and relpages columns of pg_class. The query planner uses them to estimate row counts and choose execution plans. Planner statistics are stored durably with your data and are preserved when a compute restarts or scales to zero.
  • Cumulative statistics: The Postgres cumulative statistics system collects these and exposes them through views such as pg_stat_user_tables. They include counters of inserted, updated, and deleted rows that autovacuum uses to decide when to vacuum or analyze a table.

Cumulative statistics are preserved when a compute scales to zero or restarts without a failure. They are reset if the compute stops unexpectedly, such as after a failure or a high-availability failover, or if the statistics data exceeds 16 MB. Cumulative statistics are not used by the query planner, so a reset does not affect query plans. After a reset, autovacuum might take longer to process tables because the modification counters start from zero. You can run VACUUM or ANALYZE manually if needed.

Query statistics collected by the pg_stat_statements extension are stored separately and are not preserved when a compute scales to zero or restarts. For more information, see Data persistence.

To make sure tables have accurate planner statistics, see Postgres planner statistics.

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:

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

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:

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:
SELECT * FROM pg_collation;
  • Create a database with a predefined icu locale:
CREATE DATABASE my_arabic_db
LOCALE_PROVIDER icu
icu_locale 'ar-x-icu'
template template0;
  • Specify a locale for individual columns:
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

You can replicate data to or from a Lakebase database using native Postgres logical replication. For more information, see Logical replication.

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.

  • Last updated on