System tables and commands in Aurora DSQL

See the following sections to learn about the supported system tables and catalogs in Aurora DSQL.

System tables

Aurora DSQL is compatible with PostgreSQL, so many system catalog tables and views from PostgreSQL also exist in Aurora DSQL.

Important PostgreSQL catalog tables and views

The following table describes the most common tables and views you might use in Aurora DSQL.

Supported and unsupported catalog tables

The following table indicates which tables are supported and unsupported in Aurora DSQL.

Supported and unsupported system views

The following table indicates which views are supported and unsupported in Aurora DSQL.

The sys.jobs view

sys.jobs provides status information about asynchronous jobs. For example, after you create an asynchronous index, Aurora DSQL returns a job_uuid. You can use this job_uuid with sys.jobs to look up the status of the job.

SELECT * FROM sys.jobs;

Aurora DSQL returns a response similar to the following.

           job_id           |  status   | details |  job_type   | class_id | object_id |    object_name    |       start_time       |      update_time
----------------------------+-----------+---------+-------------+----------+-----------+-------------------+------------------------+------------------------
 wqhu6ewifze5xitg3umt24h5ua | completed |         | INDEX_BUILD |     1259 |     26433 | public.nt2_c1_idx | 2025-09-25 22:07:31+00 | 2025-09-25 22:07:46+00
 kkngzf33dndl3daacxehpx5eba | completed |         | ANALYZE     |     1259 |     26419 | public.nt         | 2025-09-25 21:57:05+00 | 2025-09-25 21:57:27+00
 fyopxjb6ovdn7po6lrkj63cyea | completed |         | DROP        |     1259 |     26422 |                   | 2025-09-25 22:05:57+00 | 2025-09-25 22:06:03+00

The following table describes the columns in the sys.jobs view.

The sys.iam_pg_role_mappings view

The view sys.iam_pg_role_mappings provides information about the permissions granted to IAM users. For example, if DQSLDBConnect is an IAM role that gives Aurora DSQL access to non-admins and a user named testuser is granted the DQSLDBConnect role and corresponding permissions, you can query the sys.iam_pg_role_mappings view to see which users are granted which permissions.

SELECT * FROM sys.iam_pg_role_mappings;

Useful system metadata queries

Use these queries to get table statistics and metadata without performing expensive operations like full table scans.

Get estimated row count for a table

To get the approximate count of rows in a table without performing a full table scan, use the following query:

SELECT reltuples FROM pg_class WHERE relname = 'table_name';

The command returns output similar to the following:

  reltuples
--------------
 9.993836e+08

This approach is more efficient than SELECT COUNT(*) for large tables in Aurora DSQL.

The ANALYZE command

The ANALYZE command collects statistics about the contents of tables in the database and stores the results in the pg_stats system view. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.

In Aurora DSQL, you can't run the ANALYZE command within an explicit transaction. ANALYZE isn't subject to the database transaction timeout limit.

To reduce the need for manual intervention and keep statistics consistently up to date, Aurora DSQL automatically runs ANALYZE as a background process. This background job is triggered automatically based on the observed rate of change in the table. It is linked to the number of rows (tuples) that have been inserted, updated, or deleted since the last analyze.

ANALYZE runs asynchronously in the background and its activity can be monitored in the system view sys.jobs with the following query:

SELECT * FROM sys.jobs WHERE job_type = 'ANALYZE';

Key considerations

Background ANALYZEjobs, triggered automatically, collect the same types of statistics as a manual ANALYZE and apply them by default to user tables. System and catalog tables are excluded from this automated process.