,

Metadata Operations on Unity Catalog Tables Are Slow

Posted by

Introduction

Unity Catalog in Databricks provides centralized metadata management, governance, and fine-grained access control. However, some users experience slow metadata operations, such as:

  • Slow table listing (SHOW TABLES or SHOW CATALOGS).
  • Long execution times for DESCRIBE TABLE or SHOW COLUMNS.
  • Delays when switching catalogs (USE CATALOG).
  • Metadata queries (e.g., SELECT * FROM information_schema.tables) taking too long.

🚨 Common causes of slow metadata operations:

  • Large number of tables, schemas, or catalogs in Unity Catalog.
  • Excessive permission checks and access control enforcement.
  • Databricks metastore latency or cloud storage metadata issues.
  • Concurrency bottlenecks when multiple users access Unity Catalog.

This guide explores causes, troubleshooting steps, and best practices to improve metadata performance in Unity Catalog.


1. Identify the Bottleneck in Unity Catalog Metadata Operations

Symptoms:

  • Running SHOW CATALOGS; takes too long.
  • Listing tables with SHOW TABLES IN my_catalog.my_schema; is very slow.
  • DESCRIBE TABLE my_table; freezes for several seconds or minutes.
  • Querying metadata tables like information_schema.tables takes longer than expected.

Causes:

  • Large metadata volume (many tables, schemas, or catalogs).
  • Permission checks causing delays for each metadata request.
  • Slow metastore performance (AWS Glue, Azure Metastore).

Fix:

Check the number of tables in Unity Catalog:

SELECT count(*) FROM information_schema.tables WHERE table_catalog = 'my_catalog';

Check metastore response time by running:

EXPLAIN SHOW TABLES IN my_catalog.my_schema;

Test Unity Catalog performance compared to Hive Metastore:

SHOW TABLES IN hive_metastore.default;

If Hive Metastore is faster, Unity Catalog metadata handling might be the issue.


2. Optimize Table Metadata Queries

Symptoms:

  • SHOW TABLES, SHOW COLUMNS, and DESCRIBE TABLE take too long.
  • Queries on information_schema are very slow.

Causes:

  • Unity Catalog does fine-grained permission checks for each table.
  • information_schema queries scan all tables in a catalog.

Fix:

Limit metadata queries to specific schemas instead of querying all tables:

SELECT * FROM information_schema.tables WHERE table_schema = 'my_schema';

Use SHOW TABLES LIKE to filter metadata searches:

SHOW TABLES IN my_catalog.my_schema LIKE 'customer_%';

Optimize DESCRIBE TABLE performance by limiting output:

DESCRIBE TABLE EXTENDED my_catalog.my_schema.my_table;

Avoid using SELECT * FROM information_schema.columns on large catalogs.


3. Reduce Permission Check Overhead

Symptoms:

  • Metadata queries are fast for admins but slow for standard users.
  • Queries fail with permission errors after long delays.
  • Users with different Unity Catalog roles experience different speeds.

Causes:

  • Unity Catalog checks permissions at a very granular level.
  • Large organizations have complex ACLs, slowing metadata access.
  • External identity providers (Azure AD, AWS IAM) introduce authentication latency.

Fix:

Check and optimize permissions using SQL commands:

SHOW GRANTS ON CATALOG my_catalog;
SHOW GRANTS ON SCHEMA my_catalog.my_schema;
SHOW GRANTS ON TABLE my_catalog.my_schema.my_table;

Grant users broader access to reduce excessive permission checks:

GRANT USE CATALOG ON CATALOG my_catalog TO `user@example.com`;
GRANT USE SCHEMA ON SCHEMA my_catalog.my_schema TO `user@example.com`;
GRANT SELECT ON TABLE my_catalog.my_schema.my_table TO `user@example.com`;

If using Azure AD, ensure Databricks has optimal role assignments in Azure.
For AWS, check IAM role policies and reduce unnecessary restrictions.


4. Improve Performance for Large Catalogs

Symptoms:

  • SHOW CATALOGS; takes several minutes to return results.
  • Queries slow down as the number of Unity Catalog objects increases.

Causes:

  • High number of tables, schemas, or catalogs in Unity Catalog.
  • Databricks UI struggles to display large metadata lists.

Fix:

Use paginated metadata queries to limit result sets:

SELECT * FROM information_schema.tables WHERE table_catalog = 'my_catalog' LIMIT 100;

Partition large schemas into smaller, more manageable schemas.
Use indexing where possible to improve metadata query speeds.
Avoid querying all schemas at once (SHOW TABLES across large catalogs).


5. Reduce Metadata Latency for Delta Tables

Symptoms:

  • Delta Lake table metadata queries (DESCRIBE DETAIL my_table) are slow.
  • OPTIMIZE and VACUUM cause Unity Catalog performance issues.
  • Delta tables take too long to load metadata.

Causes:

  • Too many small files in Delta tables increasing metadata load.
  • Old transaction logs (Delta History) slow down table queries.
  • Excessive metadata operations in Unity Catalog and Delta Lake.

Fix:

Optimize Delta tables before querying metadata:

OPTIMIZE my_catalog.my_schema.my_table ZORDER BY (primary_key);

Run VACUUM regularly to clean up old metadata logs:

VACUUM my_catalog.my_schema.my_table RETAIN 168 HOURS;

Check Delta table history size:

DESCRIBE HISTORY my_catalog.my_schema.my_table;

Reduce excessive metadata logging by limiting Delta versions:

databricks.delta.retentionDurationCheck.enabled = false

6. Improve Metastore Performance (AWS Glue, Azure Metastore)

Symptoms:

  • Unity Catalog metadata queries slow down during peak usage.
  • Cross-region AWS Glue Metastore introduces delays.
  • Azure Databricks experiences metastore timeouts.

Causes:

  • Metastore region mismatch causes latency.
  • AWS Glue throttles Unity Catalog queries.
  • Azure Databricks Key Vault-based authentication slows metadata access.

Fix:

Ensure AWS Glue Metastore is in the same region as Databricks:

aws glue get-databases --region us-east-1

Use AWS PrivateLink or Azure Private Endpoints for better metastore performance.
For Azure, optimize Key Vault-backed secret retrieval:

az keyvault set-policy --name my-keyvault --spn <service-principal> --secret-permissions get list

Step-by-Step Troubleshooting Guide

1. Measure Query Performance Using EXPLAIN

EXPLAIN SHOW TABLES IN my_catalog.my_schema;
  • Identify which operations are slow.

2. Reduce Metadata Query Load

SHOW TABLES IN my_catalog.my_schema LIKE 'sales_*';
  • Avoid listing all tables unnecessarily.

3. Optimize Delta Table Performance

OPTIMIZE my_catalog.my_schema.my_table ZORDER BY (id);
  • Improve query efficiency for large Delta tables.

4. Monitor Unity Catalog Performance in Databricks UI

  • Check cluster logs for metadata query latency.
  • Use Databricks System Tables to track slow queries.

Best Practices to Improve Unity Catalog Metadata Performance

Limit Metadata Query Scope

  • Use filters (LIKE, WHERE clauses) instead of querying all tables.

Optimize Permissions for Faster Metadata Access

  • Grant broader permissions to reduce excessive access checks.

Optimize Delta Tables for Faster Metadata Retrieval

  • Run OPTIMIZE and VACUUM regularly.

Use Efficient Storage and Compute Resources

  • Ensure metastore and Databricks workspace are in the same region.

Conclusion

Unity Catalog metadata performance issues often stem from excessive metadata size, permission checks, slow Delta Lake transactions, and cloud metastore latency. By optimizing metadata queries, improving table management, and tuning Databricks configurations, you can significantly reduce metadata operation delays and improve overall system performance.

guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x