databricks-lakebase

name	databricks-lakebase
description	Manage Lakebase Postgres Autoscaling projects, branches, and endpoints via Databricks CLI. Use when asked to create, configure, or manage Lakebase Postgres databases, projects, branches, computes, or endpoints, or to check database load, connection health, and activity (via Postgres catalog views over psql).
compatibility	Requires databricks CLI (>= v0.294.0)
metadata	{"version":"0.1.0"}
parent	databricks-core

Lakebase Postgres Autoscaling

FIRST: Use the parent databricks-core skill for CLI basics, authentication, and profile selection.

Lakebase is Databricks' serverless Postgres-compatible database (similar to Neon). It provides fully managed OLTP storage with autoscaling, branching, and scale-to-zero.

Manage Lakebase Postgres projects, branches, endpoints, and databases via databricks postgres CLI commands.

Resource Hierarchy

Project (top-level container)
  └── Branch (isolated database environment, copy-on-write)
        ├── Endpoint (read-write or read-only)
        ├── Database (standard Postgres DB)
        └── Role (Postgres role)

• Project: Top-level container. Creating one auto-provisions a production branch and a primary read-write endpoint.
• Branch: Isolated database environment sharing storage with parent (copy-on-write). States: READY, ARCHIVED.
• Endpoint (called Compute in the Lakebase UI): Compute resource powering a branch. Types: ENDPOINT_TYPE_READ_WRITE, ENDPOINT_TYPE_READ_ONLY (read replica).
• Database: Standard Postgres database within a branch. Default: databricks_postgres.
• Role: Postgres role within a branch. Manage roles via databricks postgres create-role -h.

Resource Name Formats

Resource	Format
Project	projects/{project_id}
Branch	projects/{project_id}/branches/{branch_id}
Endpoint	projects/{project_id}/branches/{branch_id}/endpoints/{endpoint_id}
Database	projects/{project_id}/branches/{branch_id}/databases/{database_id}

All IDs: 1-63 characters, start with lowercase letter, lowercase letters/numbers/hyphens only (RFC 1123).

CLI Discovery — ALWAYS Do This First

Note: "Lakebase" is the product name; the CLI command group is postgres. All commands use databricks postgres ....

Do NOT guess command syntax. Discover available commands and their usage dynamically:

# List all postgres subcommands
databricks postgres -h

# Get detailed usage for any subcommand (flags, args, JSON fields)
databricks postgres <subcommand> -h

Run databricks postgres -h before constructing any command. Run databricks postgres <subcommand> -h to discover exact flags, positional arguments, and JSON spec fields for that subcommand.

Create a Project

Do NOT list projects before creating.

databricks postgres create-project <PROJECT_ID> \
  --json '{"spec": {"display_name": "<DISPLAY_NAME>"}}' \
  --profile <PROFILE>

• Auto-creates: production branch + primary read-write endpoint (1 CU min/max, scale-to-zero)
• Long-running operation; the CLI waits for completion by default. Use --no-wait to return immediately.
• Run databricks postgres create-project -h for all available spec fields (e.g. pg_version).

After creation, verify the auto-provisioned resources:

databricks postgres list-branches projects/<PROJECT_ID> --profile <PROFILE>
databricks postgres list-endpoints projects/<PROJECT_ID>/branches/<BRANCH_ID> --profile <PROFILE>
databricks postgres list-databases projects/<PROJECT_ID>/branches/<BRANCH_ID> --profile <PROFILE>

Autoscaling

Endpoints use compute units (CU) for autoscaling. Configure min/max CU via create-endpoint or update-endpoint. Run databricks postgres create-endpoint -h to see all spec fields.

Scale-to-zero is enabled by default. When idle, compute scales down to zero; it resumes in seconds on next connection.

Branches

Branches are copy-on-write snapshots of an existing branch. Use them for experimentation: testing schema migrations, trying queries, or previewing data changes -- without affecting production.

databricks postgres create-branch projects/<PROJECT_ID> <BRANCH_ID> \
  --json '{
    "spec": {
      "source_branch": "projects/<PROJECT_ID>/branches/<SOURCE_BRANCH_ID>",
      "no_expiry": true
    }
  }' --profile <PROFILE>

Branches require an expiration policy: use "no_expiry": true for permanent branches.

When done experimenting, delete the branch. Protected branches must be unprotected first -- use update-branch to set spec.is_protected to false, then delete:

# Step 1 — unprotect
databricks postgres update-branch projects/<PROJECT_ID>/branches/<BRANCH_ID> \
  --json '{"spec": {"is_protected": false}}' --profile <PROFILE>

# Step 2 — delete (run -h to confirm positional arg format for your CLI version)
databricks postgres delete-branch projects/<PROJECT_ID>/branches/<BRANCH_ID> \
  --profile <PROFILE>

Never delete the production branch — it is the authoritative branch auto-provisioned at project creation.

App Service Principal Permissions — CRITICAL

When a Databricks App connects to Lakebase with CAN_CONNECT_AND_CREATE, the app's service principal (SP) gets a Postgres role that can:

• Connect to the database
• Create new schemas and tables (which the SP will own)

The SP cannot access schemas or tables created by other roles (human users, pipelines, etc.) unless explicitly granted. This is the #1 cause of permission denied for schema X errors after deployment.

⚠️ Resource Removal Revokes All Grants

If the postgres resource is ever removed from the app (even temporarily — e.g. via a partial databricks apps update --json that omits resources, or a bundle deploy that drops the resource), the platform revokes the SP's Postgres role entirely. Re-adding the resource creates a fresh role, but all manually-applied SQL GRANTs are lost and must be re-applied.

This means:

• databricks apps update --json with incomplete payloads can silently wipe the postgres resource (it does full replacement, not merge)
• A databricks.yml change that temporarily removes a resource and redeploys will revoke grants
• Schema-level grants (GRANT USAGE ON SCHEMA ...) are not managed by the platform — they are SQL-level and must be restored manually after any role recreation

Always include ALL resources when using databricks apps update --json. Prefer databricks.yml + bundle deploy to manage resources declaratively.

Discovering the SP Role Name

The SP's Postgres role name is its client ID (a UUID). Find it from the app:

databricks apps get <APP_NAME> --profile <PROFILE> -o json
# → .service_principal_client_id is the Postgres role name

Granting Cross-Schema Access

When an app needs to read/write schemas it did not create (e.g. public, gold, or any schema populated by pipelines or human users), you must connect as the schema owner and grant access to the SP role.

Step 1 — Generate credentials and connect as the owner:

databricks postgres generate-database-credential \
  projects/<PROJECT_ID>/branches/<BRANCH_ID>/endpoints/<ENDPOINT_ID> \
  --profile <PROFILE>

Use the returned token as the password for psql:

PGPASSWORD=<token> psql "host=<ENDPOINT_HOST> dbname=<DB_NAME> sslmode=require user=<YOUR_EMAIL>"

Step 2 — Check schema ownership (different schemas may have different owners):

SELECT schema_name, schema_owner FROM information_schema.schemata;

Tables synced by pipelines are typically owned by a databricks_writer_XXXXX role, not by a human user. You must run ALTER DEFAULT PRIVILEGES for each distinct owner role to cover future tables. Check table ownership with:

SELECT tablename, tableowner FROM pg_tables WHERE schemaname = '<schema>';

Gotcha: You may not have permission to run ALTER DEFAULT PRIVILEGES FOR ROLE "databricks_writer_XXXXX" (requires SET ROLE). In that case, GRANT SELECT ON ALL TABLES covers existing tables, but you'll need to re-run it after new tables are synced. The human-user ALTER DEFAULT PRIVILEGES only covers tables the human creates directly.

Step 3 — Grant permissions (replace <SP_CLIENT_ID> with the UUID from step above):

-- Read-only access to a schema (e.g. gold, synced from lakehouse)
GRANT USAGE ON SCHEMA gold TO "<SP_CLIENT_ID>";
GRANT SELECT ON ALL TABLES IN SCHEMA gold TO "<SP_CLIENT_ID>";
ALTER DEFAULT PRIVILEGES FOR ROLE "<OWNER_ROLE>" IN SCHEMA gold
  GRANT SELECT ON TABLES TO "<SP_CLIENT_ID>";

-- Read-write access to a schema (e.g. public)
GRANT USAGE ON SCHEMA public TO "<SP_CLIENT_ID>";
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO "<SP_CLIENT_ID>";
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO "<SP_CLIENT_ID>";
ALTER DEFAULT PRIVILEGES FOR ROLE "<OWNER_ROLE>" IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE ON TABLES TO "<SP_CLIENT_ID>";
ALTER DEFAULT PRIVILEGES FOR ROLE "<OWNER_ROLE>" IN SCHEMA public
  GRANT USAGE ON SEQUENCES TO "<SP_CLIENT_ID>";

-- Full access to an app-managed schema (e.g. support_console)
GRANT USAGE, CREATE ON SCHEMA support_console TO "<SP_CLIENT_ID>";
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA support_console TO "<SP_CLIENT_ID>";
GRANT USAGE ON ALL SEQUENCES IN SCHEMA support_console TO "<SP_CLIENT_ID>";
ALTER DEFAULT PRIVILEGES FOR ROLE "<OWNER_ROLE>" IN SCHEMA support_console
  GRANT ALL ON TABLES TO "<SP_CLIENT_ID>";

<OWNER_ROLE> is typically your email (the human user who created the schema), but for pipeline-synced schemas check the actual owner (e.g. databricks_writer_XXXXX). You need ALTER DEFAULT PRIVILEGES for each owner role that creates tables in that schema.

Step 4 — ALTER DEFAULT PRIVILEGES is essential. Without it, new tables created by pipelines or migrations (under the owner role) will not be visible to the SP. This prevents the permissions from breaking again when data is re-synced.

Ownership vs Grants

• Ownership transfer (ALTER ... OWNER TO) requires SET ROLE privilege, which Lakebase may not grant between human and SP roles. Use GRANT instead.
• AppKit cache: AppKit creates an appkit schema at startup for persistent caching. If a human user previously created it, the SP gets permission denied or must be owner. Fix: DROP SCHEMA appkit CASCADE so the SP recreates it on next deploy (the cache is ephemeral and safe to drop).

What's Next

Build a Databricks App

After creating a Lakebase project, scaffold a Databricks App connected to it.

Step 1 — Discover branch name (use .name from a READY branch):

databricks postgres list-branches projects/<PROJECT_ID> --profile <PROFILE>

Step 2 — Discover database name (use .name from the desired database; <BRANCH_ID> is the branch ID, not the full resource name):

databricks postgres list-databases projects/<PROJECT_ID>/branches/<BRANCH_ID> --profile <PROFILE>

Step 3 — Scaffold the app with the lakebase feature:

databricks apps init --name <APP_NAME> \
  --features lakebase \
  --set "lakebase.postgres.branch=<BRANCH_NAME>" \
  --set "lakebase.postgres.database=<DATABASE_NAME>" \
  --run none --profile <PROFILE>

Where <BRANCH_NAME> is the full resource name (e.g. projects/<PROJECT_ID>/branches/<BRANCH_ID>) and <DATABASE_NAME> is the full resource name (e.g. projects/<PROJECT_ID>/branches/<BRANCH_ID>/databases/<DB_ID>).

For the full app development workflow, use the databricks-apps skill.

Other Workflows

Connect a Postgres client Get the connection string from the endpoint, then connect with psql, DBeaver, or any standard Postgres client.

databricks postgres get-endpoint projects/<PROJECT_ID>/branches/<BRANCH_ID>/endpoints/<ENDPOINT_ID> --profile <PROFILE>

Manage roles and permissions Create Postgres roles and grant access to databases or schemas.

databricks postgres create-role -h   # discover role spec fields

Add a read-only endpoint Create a read replica for analytics or reporting workloads to avoid contention on the primary read-write endpoint.

databricks postgres create-endpoint projects/<PROJECT_ID>/branches/<BRANCH_ID> <ENDPOINT_ID> \
  --json '{"spec": {"type": "ENDPOINT_TYPE_READ_ONLY"}}' --profile <PROFILE>

Query Performance Diagnostics

Lakebase ships with pg_stat_statements enabled by default. Use these queries to identify slow queries, cache efficiency, and currently running operations.

Connecting via psql

Generate a short-lived credential and connect:

databricks postgres generate-database-credential \
  projects/<PROJECT_ID>/branches/<BRANCH_ID>/endpoints/<ENDPOINT_ID> \
  --profile <PROFILE>

PGPASSWORD=<token> psql "host=<ENDPOINT_HOST> dbname=<DB_NAME> sslmode=require user=<YOUR_EMAIL>"

Database load and health

When the user asks about database load, how busy the DB is, connection health, or runtime health (short of slow-query tuning), answer with a quick psql snapshot using the connection steps above. Resolve <ENDPOINT_HOST> via databricks postgres list-endpoints or get-endpoint; default database name is usually databricks_postgres; user is the Databricks account email (same as in the OAuth subject).

Interpretation

• pg_stat_activity: point in time — counts sessions by state (active, idle, idle in transaction, etc.) for the current database. Use this for “how many connections right now?” and whether anything is stuck waiting (wait_event_type / wait_event on active rows).
• pg_stat_database: cumulative since stats_reset (often NULL / long-lived) — commits, rollbacks, buffer hits vs reads, tuple counters, deadlocks, temp file usage. Use this for cache hit ratio and whether the instance has seen stress patterns over time, not for instant CPU %.
• Not in Postgres catalogs: sustained CPU, memory, disk IOPS, and CU utilization — those live in Databricks workspace / Lakebase metrics if the user needs time series or autoscaling pressure.

Connections by state

SELECT state, COUNT(*) AS connections
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state
ORDER BY connections DESC;

Other active sessions and waits (excluding your own psql backend)

SELECT
  COUNT(*) FILTER (WHERE state = 'active' AND pid <> pg_backend_pid()) AS other_active,
  COUNT(*) FILTER (WHERE wait_event_type IS NOT NULL AND state = 'active') AS active_waiting
FROM pg_stat_activity
WHERE datname = current_database();

Database-wide cumulative stats (current database row)

SELECT
  now() AS sampled_at,
  numbackends AS backends,
  xact_commit AS commits,
  xact_rollback AS rollbacks,
  blks_read,
  blks_hit,
  ROUND(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS buffer_hit_pct,
  tup_returned,
  tup_fetched,
  tup_inserted,
  tup_updated,
  tup_deleted,
  deadlocks,
  temp_files,
  temp_bytes,
  stats_reset
FROM pg_stat_database
WHERE datname = current_database();

Optional: session_time, active_time, and related fields on the same row summarize session time in milliseconds (PostgreSQL 14+).

For which queries are expensive, use pg_stat_statements below, not these views alone.

Slow Queries (pg_stat_statements)

Top queries by mean execution time — the primary tool for finding performance bottlenecks:

SELECT
  LEFT(query, 120) AS query_preview,
  calls,
  ROUND(total_exec_time::numeric, 2) AS total_ms,
  ROUND(mean_exec_time::numeric, 2) AS mean_ms,
  ROUND(min_exec_time::numeric, 2) AS min_ms,
  ROUND(max_exec_time::numeric, 2) AS max_ms,
  rows,
  shared_blks_hit,
  shared_blks_read
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
  AND query NOT LIKE '%EXPLAIN%'
  AND query NOT LIKE '%pg_settings%'
  AND query NOT LIKE '%pg_database_size%'
  AND query NOT LIKE '%pg_logical_slot%'
  AND query NOT LIKE '%pg_replication_slot%'
  AND query NOT LIKE '%__db_system%'
  AND query NOT LIKE '%__db_prog%'
ORDER BY mean_exec_time DESC
LIMIT 20;

IMPORTANT: Always include all exclusion filters above. Lakebase runs internal queries for replication, CDC, and catalog management that will dominate results if not filtered out. Focus on application queries only.

Highest Total Time (cumulative impact)

Queries that consume the most total database time across all invocations:

SELECT
  LEFT(query, 120) AS query_preview,
  calls,
  ROUND(total_exec_time::numeric, 2) AS total_ms,
  ROUND(mean_exec_time::numeric, 2) AS mean_ms,
  rows
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
  AND query NOT LIKE '%EXPLAIN%'
  AND query NOT LIKE '%pg_settings%'
  AND query NOT LIKE '%pg_database_size%'
  AND query NOT LIKE '%pg_logical_slot%'
  AND query NOT LIKE '%pg_replication_slot%'
  AND query NOT LIKE '%__db_system%'
  AND query NOT LIKE '%__db_prog%'
ORDER BY total_exec_time DESC
LIMIT 20;

A query with 2ms mean but 100K calls may matter more than one with 200ms mean and 4 calls.

Sequential scans and index candidates (pg_stat_user_tables)

When the user wants performance optimization, indexing opportunities, or tables that do too many sequential scans, query pg_stat_user_tables. This surfaces tables where Postgres reads many rows via full table scans (seq_tup_read) instead of index-driven lookups—often a sign of missing or unused indexes on filter/join columns.

Default report: top 10 tables by seq_tup_read, public schema only (adjust schemaname if the user cares about other schemas). Include seq_scan, idx_scan, and optional scan/tuple mix columns so the reader can see seq-heavy access. Keep written summaries brief; n_live_tup is an estimate (stale stats can mislead).

SELECT
  schemaname || '.' || relname AS table_name,
  seq_tup_read,
  seq_scan,
  idx_scan,
  CASE WHEN seq_scan + idx_scan > 0
    THEN round(100.0 * seq_scan / (seq_scan + idx_scan), 1)
    END AS pct_scans_seq,
  CASE WHEN seq_tup_read + COALESCE(idx_tup_fetch, 0) > 0
    THEN round(100.0 * seq_tup_read / (seq_tup_read + idx_tup_fetch), 1)
    END AS pct_tuples_seq,
  n_live_tup::bigint AS est_rows
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY seq_tup_read DESC
LIMIT 10;

How to read it: very high seq_tup_read with a high pct_scans_seq (or idx_scan near zero for that table’s workload) points to index candidates on columns used in WHERE, JOIN, and ORDER BY. Confirm with EXPLAIN (ANALYZE, …) on real application queries before creating indexes. idx_scan = 0 does not mean “no indexes exist”—only that no index scan was recorded for that statement window (e.g. only the primary key exists but filters use unindexed columns).

Pair with pg_stat_statements (above) to tie slow queries to tables.

Cache Hit Ratio

Healthy databases serve >99% of reads from shared buffers. A low ratio means queries are hitting disk:

SELECT
  ROUND(
    100.0 * SUM(shared_blks_hit) /
    NULLIF(SUM(shared_blks_hit) + SUM(shared_blks_read), 0), 2
  ) AS cache_hit_pct
FROM pg_stat_statements;

Currently Running Queries

Find long-running or stuck queries in real time:

SELECT
  pid,
  NOW() - query_start AS duration,
  state,
  LEFT(query, 120) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

Explain a Specific Query

Get the execution plan for a slow query to understand sequential scans, missing indexes, or join strategy:

EXPLAIN (ANALYZE, VERBOSE, BUFFERS, FORMAT JSON) <your_query>;

Drop ANALYZE to get the plan without actually executing the query.

Reset Statistics

After deploying a fix, reset stats to measure the improvement from a clean baseline:

SELECT pg_stat_statements_reset();

Unity Catalog Grants for Apps with Analytics + Lakebase

Apps that use both the analytics plugin (SQL warehouse queries via config/queries/*.sql) and lakebase need two layers of grants for the SP:

1. Unity Catalog grants — for typegen (DESCRIBE) and SQL warehouse query execution at build time and runtime
2. Lakebase Postgres grants — for tRPC/pool queries at runtime

UC grants use the SP's application ID (UUID) as the principal. The CAN_CONNECT_AND_CREATE permission on the Lakebase resource does not grant UC access — you must set both independently.

# Find the SP application ID
databricks apps get <APP_NAME> --profile <PROFILE> -o json
# → .service_principal_client_id

# Grant UC catalog access (required for typegen to DESCRIBE queries)
databricks grants update catalog <CATALOG> \
  --json '{"changes": [{"add": ["USE_CATALOG"], "principal": "<SP_CLIENT_ID>"}]}' \
  --profile <PROFILE>

# Grant UC schema access
databricks grants update schema <CATALOG>.<SCHEMA> \
  --json '{"changes": [{"add": ["USE_SCHEMA", "SELECT"], "principal": "<SP_CLIENT_ID>"}]}' \
  --profile <PROFILE>

Without UC grants, typegen fails with INSUFFICIENT_PERMISSIONS during the platform build, producing empty types ({}), which breaks the TypeScript compilation.

Troubleshooting

Error	Solution
cannot configure default credentials	Use --profile flag or authenticate first
PERMISSION_DENIED	Check workspace permissions
permission denied for schema X	App SP lacks access to a pre-existing schema. See App Service Principal Permissions above
permission denied for schema appkit	AppKit cache schema owned by a previous SP or human user. DROP SCHEMA appkit CASCADE so the new SP recreates it
INSUFFICIENT_PERMISSIONS / USE CATALOG during typegen	SP lacks Unity Catalog grants. See Unity Catalog Grants above
permission denied for table X (schema grants exist)	Table-level grants are separate from schema grants. Re-run GRANT SELECT ON ALL TABLES IN SCHEMA ... — the table may have been created after the original grant
must be owner of table X	Object was created by a different role. DROP and let the SP recreate, or GRANT ALL to the SP
Grants lost after apps update or resource change	Removing/re-adding a postgres resource revokes the SP role and all grants. Re-apply all SQL GRANTs. See Resource Removal Revokes All Grants above
Protected branch cannot be deleted	update-branch to set spec.is_protected to false first
Long-running operation timeout	Use --no-wait and poll with get-operation