Aurora PostgreSQL Limitless Database views - Amazon Aurora

Aurora PostgreSQL Limitless Database views

The following table shows the new views for Aurora PostgreSQL Limitless Database.

Note

The views listed in this table are located in the rds_aurora schema. When using a Limitless Database view, make sure to include the fully qualified object name: rds_aurora.object_name.

Aurora PostgreSQL Limitless Database view Corresponding Aurora PostgreSQL view
limitless_database pg_database
limitless_locks pg_locks
limitless_stat_activity pg_stat_activity
limitless_stat_all_indexes pg_stat_all_indexes
limitless_stat_all_tables pg_stat_all_tables
limitless_stat_database pg_stat_database
limitless_stat_progress_vacuum pg_stat_progress_vacuum
limitless_stat_statements pg_stat_statements
limitless_stat_subclusters None
limitless_stat_statements_info pg_stat_statements_info
limitless_statio_all_indexes pg_statio_all_indexes
limitless_statio_all_tables pg_statio_all_tables
limitless_tables pg_tables
limitless_table_collocations None
limitless_table_collocation_distributions None

The following examples provide details about the Aurora PostgreSQL Limitless Database views. For more information on PostgreSQL views, see Viewing statistics in the PostgreSQL documentation.

Note

Some statistics views can return inconsistent results if you have ongoing transactions.

limitless_database

This view contains information about the available databases in the DB shard group. For example:

postgres_limitless=> SELECT subcluster_id, subcluster_type, oid, datname, datacl FROM rds_aurora.limitless_database;

 subcluster_id | subcluster_type |  oid  |      datname       |                                                         datacl                                                         
---------------+-----------------+-------+--------------------+------------------------------------------------------------------------------------------------------------------------
 2             | router          |     4 | template0          | {=c/rdsadmin,rdsadmin=CTc/rdsadmin}
 2             | router          |     5 | postgres           | 
 2             | router          | 16384 | rdsadmin           | {rdsadmin=CTc/rdsadmin,rds_aurora_limitless_metadata_admin=c/rdsadmin,rds_aurora_limitless_heat_mgmt_admin=c/rdsadmin}
 2             | router          | 16477 | postgres_limitless | 
 2             | router          |     1 | template1          | {=c/rdsadmin,rdsadmin=CTc/rdsadmin}
 6             | shard           |     4 | template0          | {=c/rdsadmin,rdsadmin=CTc/rdsadmin}

The output parameters are the following:

  • subcluster_id (text) – The ID of the subcluster (node)

  • subcluster_type (text) – The type of subcluster (node), router or shard

The rest of the columns are the same as in pg_database.

limitless_locks

This view contains one row per process per node. It provides access to information about the locks held by active processes in the database server.

Example of creating a lock with two transactions

In this example, we run two transactions simultaneously on two routers.

# Transaction 1 (run on router 1)
BEGIN;
SET search_path = public;
SELECT * FROM customers;
INSERT INTO customers VALUES (400,'foo','bar');

# Transaction 2 (run on router 2)
BEGIN;
SET search_path = public;
ALTER TABLE customers ADD COLUMN phone VARCHAR;

The first transaction is run. Subsequent transactions have to wait until the first transaction is completed. Therefore the second transaction is blocked with a lock. To check the root cause of it, we run a command by joining limitless_locks with limitless_stat_activity.

# Run on router 2
SELECT distributed_session_id, state, usename, query, query_start
FROM rds_aurora.limitless_stat_activity
WHERE distributed_session_id in (
SELECT distributed_session_id
FROM rds_aurora.limitless_locks
WHERE relname = 'customers'
);

 distributed_session_id | state               | usename                 | query                                           | query_start
------------------------+---------------------+--------------------------+---------------------------------- -------------+-------------------------------
 47BDE66E9A5E8477       | idle in transaction | limitless_metadata_admin | INSERT INTO customers VALUES (400,'foo','bar'); | 2023-04-13 17:44:45.152244+00
 2AD7F370202D0FA9       | active              | limitless_metadata_admin | ALTER TABLE customers ADD COLUMN phone VARCHAR; | 2023-04-13 17:44:55.113388+00
 47BDE66E9A5E8477       |                     | limitless_auth_admin     | <insufficient privilege>                        |
 2AD7F370202D0FA9       |                     | limitless_auth_admin     | <insufficient privilege>                        |
 47BDE66E9A5E8477       |                     | limitless_auth_admin     | <insufficient privilege>                        |
 2AD7F370202D0FA9       |                     | limitless_auth_admin     | <insufficient privilege>                        |
(6 rows)
Example of creating a lock explicitly

In this example, we create a lock explicitly, then use the limitless_locks view to see the locks (some columns are omitted).

BEGIN;
SET search_path = public;
LOCK TABLE customers IN ACCESS SHARE MODE;
SELECT * FROM rds_aurora.limitless_locks WHERE relname = 'customers';

 subcluster_id | subcluster_type | distributed_session_id | locktype |      datname       | relnspname |  relname  | virtualtransaction |  pid  |      mode
---------------+-----------------+------------------------+----------+--------------------+------------+ ----------+--------------------+-------+-----------------
             1 | router          | 7207702F862FC937       | relation | postgres_limitless | public     | customers | 28/600787          | 59564 | AccessShareLock
             2 | router          | 7207702F862FC937       | relation | postgres_limitless | public     | customers | 28/600405          | 67130 | AccessShareLock
             3 | shard           | 7207702F862FC937       | relation | postgres_limitless | public     | customers | 15/473401          | 27735 | AccessShareLock
             4 | shard           | 7207702F862FC937       | relation | postgres_limitless | public     | customers | 13/473524          | 27734 | AccessShareLock
             5 | shard           | 7207702F862FC937       | relation | postgres_limitless | public     | customers | 13/472935          | 27737 | AccessShareLock
             6 | shard           | 7207702F862FC937       | relation | postgres_limitless | public     | customers | 13/473015          | 48660 | AccessShareLock
(6 rows)
limitless_stat_activity

This view contains one row per process per node. It can be used to track overall system health and triage processes that are taking a long time. For example:

postgres=# SELECT
    subcluster_id,
    subcluster_type,
    distributed_session_id,
    distributed_session_state,
    datname,
    distributed_query_id
FROM
    rds_aurora.limitless_stat_activity
WHERE
    distributed_session_id='7E4CBBD3E1AF9ABA';

 subcluster_id | subcluster_type | distributed_session_id | distributed_session_state |      datname       | distributed_query_id
---------------+-----------------+------------------------+---------------------------+--------------------+ ----------------------
             1 | router          | 7E4CBBD3E1AF9ABA       | coordinator               | postgres_limitless |                     
             2 | router          | 7E4CBBD3E1AF9ABA       | participant               | postgres_limitless | -8224553981424021413
             3 | shard           | 7E4CBBD3E1AF9ABA       | participant               | postgres_limitless | -8224553981424021413
             4 | shard           | 7E4CBBD3E1AF9ABA       | participant               | postgres_limitless | -8224553981424021413
             5 | shard           | 7E4CBBD3E1AF9ABA       | participant               | postgres_limitless | -8224553981424021413
             6 | shard           | 7E4CBBD3E1AF9ABA       | participant               | postgres_limitless | -8224553981424021413
(6 rows)

The output parameters are the following:

  • subcluster_id (text) – The ID of the subcluster to which this process belongs.

  • subcluster_type (text) – The type of subcluster to which this process belongs: router or shard.

  • distributed_session_id (text) – The ID of the distributed session to which this process belongs.

  • distributed_session_state (text) – Whether this is a coordinator, participant, or standalone/nondistributed process (shown as NULL).

  • datname (text) – The database to which this process is connected.

  • distributed_query_id (bigint) – The query ID of the parent query from the coordinator node. This column is NULL if it's the parent query. The coordinator node pushes down the distributed query ID to the participant nodes. So for the participant nodes, the values for distributed query ID and query ID are different.

The rest of the columns are the same as in pg_stat_activity.

limitless_stat_all_indexes

This view contains usage statistics on indexes in the DB shard group. For example:

postgres_limitless=> SELECT schemaname, relname, indexrelname, idx_scan
  FROM rds_aurora.limitless_stat_all_indexes
  WHERE relname LIKE 'orders_ts%' ORDER BY indexrelname LIMIT 10;

 schemaname |    relname     |    indexrelname     | idx_scan
------------+----------------+---------------------+----------
 ec_sample  | orders_ts00001 | orders_ts00001_pkey |   196801
 ec_sample  | orders_ts00002 | orders_ts00002_pkey |   196703
 ec_sample  | orders_ts00003 | orders_ts00003_pkey |   196376
 ec_sample  | orders_ts00004 | orders_ts00004_pkey |   197966
 ec_sample  | orders_ts00005 | orders_ts00005_pkey |   195301
 ec_sample  | orders_ts00006 | orders_ts00006_pkey |   195673
 ec_sample  | orders_ts00007 | orders_ts00007_pkey |   194475
 ec_sample  | orders_ts00008 | orders_ts00008_pkey |   191694
 ec_sample  | orders_ts00009 | orders_ts00009_pkey |   193744
 ec_sample  | orders_ts00010 | orders_ts00010_pkey |   195421
(10 rows)
limitless_stat_all_tables

This view contains statistics about all tables in the current database in the DB shard group. This is useful when tracking vacuum operations and Data Manipulation language (DML) operations. For example:

postgres_limitless=> SELECT subcluster_id, subcluster_type, relname, n_ins_since_vacuum, n_tup_ins, last_vacuum
  FROM rds_aurora.limitless_stat_all_tables
  WHERE relname LIKE 'orders_ts%' ORDER BY relname LIMIT 10;

 subcluster_id | subcluster_type |    relname     | n_ins_since_vacuum | n_tup_ins | last_vacuum
---------------+-----------------+----------------+--------------------+-----------+-------------
 5             | shard           | orders_ts00001 |              34779 |    196083 |
 5             | shard           | orders_ts00002 |              34632 |    194721 |
 5             | shard           | orders_ts00003 |              34950 |    195965 |
 5             | shard           | orders_ts00004 |              34745 |    197283 |
 5             | shard           | orders_ts00005 |              34879 |    195754 |
 5             | shard           | orders_ts00006 |              34340 |    194605 |
 5             | shard           | orders_ts00007 |              33779 |    192203 |
 5             | shard           | orders_ts00008 |              33826 |    191293 |
 5             | shard           | orders_ts00009 |              34660 |    194117 |
 5             | shard           | orders_ts00010 |              34569 |    195560 |
(10 rows)

The output parameters are the following:

  • subcluster_id (text) – The ID of the subcluster to which this process belongs.

  • subcluster_type (text) – The type of subcluster to which this process belongs: router or shard.

  • relname (name) – The name of the table.

The rest of the columns are the same as in pg_stat_all_tables.

limitless_stat_database

This view contains statistics about all databases in the DB shard group. Returns one row per database per node. For example:

postgres_limitless=> SELECT
    subcluster_id,
    subcluster_type,
    datname,
    blks_read,
    blks_hit
FROM
    rds_aurora.limitless_stat_database
WHERE
    datname='postgres_limitless';
 subcluster_id | subcluster_type |      datname       | blks_read | blks_hit
---------------+-----------------+--------------------+-----------+----------
             1 | router          | postgres_limitless |       484 | 34371314
             2 | router          | postgres_limitless |       673 | 33859317
             3 | shard           | postgres_limitless |      1299 | 17749550
             4 | shard           | postgres_limitless |      1094 | 17492849
             5 | shard           | postgres_limitless |      1036 | 17485098
             6 | shard           | postgres_limitless |      1040 | 17437257
(6 rows)

The output parameters are the following:

  • subcluster_id (text) – The ID of the subcluster to which this process belongs.

  • subcluster_type (text) – The type of subcluster to which this process belongs: router or shard.

  • datname (name) – The name of the database.

The rest of the columns are the same as in pg_stat_database.

limitless_stat_progress_vacuum

This view contains information about ongoing vacuuming operations. For example:

postgres_limitless=> SELECT * FROM rds_aurora.limitless_stat_progress_vacuum;

-[ RECORD 1 ]----------+------------------
subcluster_id          | 3
subcluster_type        | shard
distributed_session_id | A56D96E2A5C9F426
pid                    | 5270
datname                | postgres
nspname                | public
relname                | customer_ts2
phase                  | vacuuming heap
heap_blks_total        | 130500
heap_blks_scanned      | 100036
heap_blks_vacuumed     | 0
index_vacuum_count     | 0
max_dead_tuples        | 11184810
num_dead_tuples        | 0

-[ RECORD 2 ]----------+------------------
subcluster_id          | 3
subcluster_type        | shard
distributed_session_id | 56DF26A89EC23AB5
pid                    | 6854
datname                | postgres
nspname                | public
relname                | sales_ts1
phase                  | vacuuming heap
heap_blks_total        | 43058
heap_blks_scanned      | 24868
heap_blks_vacuumed     | 0
index_vacuum_count     | 0
max_dead_tuples        | 8569523
num_dead_tuples        | 0

The output parameters are the following:

  • subcluster_id (text) – The ID of the subcluster to which this process belongs.

  • subcluster_type (text) – The type of subcluster to which this process belongs: router or shard.

  • distributed_session_id (text) – The identifier for the session that initiated the vacuuming operation.

  • datname (name) – The database where the vacuuming is being done.

  • nspname (name) – The name of the schema of the table that is being vacuumed. It's null if the table being vacuumed is not in the same database as the one to which the user is connected.

  • relname (name) – The name of the table that is being vacuumed. It's null if the table being vacuumed is not in the same database as the one to which the user is connected.

The rest of the columns are the same as in pg_stat_progress_vacuum.

limitless_stat_statements

This view provides a means for tracking planning and running statistics of all SQL statements run on all nodes.

Note

You must install the pg_stat_statements extension to use the limitless_stat_statements view.

-- CREATE EXTENSION must be run by a superuser
CREATE EXTENSION pg_stat_statements;

-- Verify that the extension is created on all nodes in the DB shard group
SELECT distinct node_id
    FROM rds_aurora.limitless_stat_statements
    LIMIT 10;

The following example shows the use of the limitless_stat_statements view.

postgres_limitless=> SELECT
 subcluster_id,
 subcluster_type,
 distributedqueryid,
 username,
 dbname
FROM
 rds_aurora.limitless_stat_statements;

 subcluster_id | subcluster_type | distributedqueryid  |            username             |       dbname
---------------+-----------------+---------------------+---------------------------------+-------------------
             1 | router          |                     | postgres                        | postgres_limitless
             1 | router          |                     | postgres                        | postgres_limitless
             1 | router          |                     | postgres                        | postgres_limitless
             1 | router          | 5103284549693281378 | aurora_limitless_metadata_admin | postgres_limitless
             1 | router          |                     | postgres                        | postgres_limitless
             1 | router          |                     | aurora_limitless_metadata_admin | postgres_limitless
             1 | router          |                     | postgres                        | postgres_limitless
             1 | router          |                     | postgres                        | postgres_limitless
[...]

The output parameters are the following:

  • subcluster_id (text) – The ID of the subcluster to which this process belongs.

  • subcluster_type (text) – The type of subcluster to which this process belongs: router for or shard.

  • distributedqueryid (bigint) – The query ID of the parent query from the coordinator node. This column is NULL if it's the parent query. The coordinator node pushes down the distributed query ID to the participant nodes. So for the participant nodes, the values for distributed query ID and query ID are different.

  • username (name) – The user that queried the statement.

  • dbname (name) – The database where the query was run.

The rest of the columns are the same as in pg_stat_statements.

limitless_stat_statements_info

This view contains statistics for the limitless_stat_statements view. Each row contains data for the pg_stat_statements_info view from each node. The subcluster_id column identifies each node.

postgres_limitless=> SELECT * FROM rds_aurora.limitless_stat_statements_info;

 subcluster_id | subcluster_type | dealloc |          stats_reset
---------------+-----------------+---------+-------------------------------
             1 | router          |       0 | 2023-06-30 21:22:09.524781+00
             2 | router          |       0 | 2023-06-30 21:21:40.834111+00
             3 | shard           |       0 | 2023-06-30 21:22:10.709942+00
             4 | shard           |       0 | 2023-06-30 21:22:10.740179+00
             5 | shard           |       0 | 2023-06-30 21:22:10.774282+00
             6 | shard           |       0 | 2023-06-30 21:22:10.808267+00
(6 rows)

The output parameter is the following:

  • subcluster_id (text) – The ID of the subcluster to which this process belongs.

The rest of the columns are the same as in pg_stat_statements_info.

limitless_stat_subclusters

This view contains network statistics between routers and other nodes. It contains a row per pair of router and other node, for example:

postgres_limitless=> SELECT * FROM rds_aurora.limitless_stat_subclusters;

 orig_subcluster | orig_instance_az | dest_subcluster | dest_instance_az | latency_us |       latest_collection       | failed_requests | received_bytes | sent_bytes | same_az_requests | cross_az_requests |     stat_reset_timestamp      
-----------------+------------------+-----------------+------------------+------------+-------------------------------+-----------------+----------------+------------+------------------+-------------------+-------------------------------
 3               | us-west-2b       | 2               | us-west-2a       |        847 | 2024-10-07 17:25:39.518617+00 |               0 |       35668633 |   92090171 |                0 |            302787 | 2024-10-05 12:39:55.239675+00
 3               | us-west-2b       | 4               | us-west-2b       |        419 | 2024-10-07 17:25:39.546376+00 |               0 |      101190464 |  248795719 |           883478 |                 0 | 2024-10-05 12:39:55.231218+00
 3               | us-west-2b       | 5               | us-west-2c       |       1396 | 2024-10-07 17:25:39.52122+00  |               0 |       72864849 |  172086292 |                0 |            557726 | 2024-10-05 12:39:55.196412+00
 3               | us-west-2b       | 6               | us-west-2c       |        729 | 2024-10-07 17:25:39.54828+00  |               0 |       35668584 |   92090171 |                0 |            302787 | 2024-10-05 12:39:55.247334+00
 3               | us-west-2b       | 7               | us-west-2a       |       1702 | 2024-10-07 17:25:39.545307+00 |               0 |       71699576 |  171634844 |                0 |            556278 | 2024-10-05 12:39:52.715168+00
 2               | us-west-2a       | 3               | us-west-2b       |        868 | 2024-10-07 17:25:40.293927+00 |               0 |       35659611 |   92011872 |                0 |            302817 | 2024-10-05 12:39:54.420758+00
 2               | us-west-2a       | 4               | us-west-2b       |        786 | 2024-10-07 17:25:40.296863+00 |               0 |      102437253 |  251838024 |                0 |            895060 | 2024-10-05 12:39:54.404081+00
 2               | us-west-2a       | 5               | us-west-2c       |       1232 | 2024-10-07 17:25:40.292021+00 |               0 |       71990027 |  168828110 |                0 |            545453 | 2024-10-05 12:39:36.769549+00

The output parameters are the following:

  • orig_subcluster (text) – The ID of the router where the communications originate

  • orig_subcluster_az (text) – The Availability Zone (AZ) of the originator router

  • dest_subcluster (text) – The ID of the destination node

  • dest_subcluster_az (text) – The last collected AZ of the destination node

  • latency_us (bigint) – The last collected network latency between nodes, in microseconds. The value is 0 if the node is unreachable.

  • latest_collection (timestamp) – The timestamp of the latest collection of AZ and latency for the destination node

  • failed_requests (bigint) – The cumulative count of failed internal requests

  • received_bytes (bigint) – The estimated cumulative number of bytes received from this node

  • sent_bytes (bigint) – The estimated cumulative number of bytes sent to this node

  • same_az_requests (bigint) – The cumulative number of internal DB requests to this node when it's in the same AZ as the originator router

  • cross_az_requests (bigint) – The cumulative number of internal DB requests to this node when it's in a different AZ from the originator router

  • stat_reset_timestamp (timestamp) – The timestamp when the cumulative statistics for this view were last reset

limitless_statio_all_indexes

This view contains input/output (I/O) statistics for all indexes in the DB shard group. For example:

postgres_limitless=> SELECT * FROM rds_aurora.limitless_statio_all_indexes WHERE relname like'customers_ts%';

 subcluster_id | subcluster_type | schemaname |      relname      |            indexrelname             | idx_blks_read | idx_blks_hit
---------------+-----------------+------------+-------------------+-------------------------------------+ --------------+--------------
             3 | shard           | public     | customers_ts00002 | customers_ts00002_customer_name_idx |             1 |            0
             3 | shard           | public     | customers_ts00001 | customers_ts00001_customer_name_idx |             1 |            0
             4 | shard           | public     | customers_ts00003 | customers_ts00003_customer_name_idx |             1 |            0
             4 | shard           | public     | customers_ts00004 | customers_ts00004_customer_name_idx |             1 |            0
             5 | shard           | public     | customers_ts00005 | customers_ts00005_customer_name_idx |             1 |            0
             5 | shard           | public     | customers_ts00006 | customers_ts00006_customer_name_idx |             1 |            0
             6 | shard           | public     | customers_ts00007 | customers_ts00007_customer_name_idx |             1 |            0
             6 | shard           | public     | customers_ts00008 | customers_ts00008_customer_name_idx |             1 |            0
(8 rows)
limitless_statio_all_tables

This view contains input/output (I/O) statistics for all tables in the DB shard group. For example:

postgres_limitless=> SELECT
    subcluster_id,
    subcluster_type,
    schemaname,
    relname,
    heap_blks_read,
    heap_blks_hit
FROM
    rds_aurora.limitless_statio_all_tables
WHERE
    relname LIKE 'customers_ts%';

 subcluster_id | subcluster_type | schemaname |      relname      | heap_blks_read | heap_blks_hit
---------------+-----------------+------------+-------------------+----------------+---------------
             3 | shard           | public     | customers_ts00002 |            305 |         57780
             3 | shard           | public     | customers_ts00001 |            300 |         56972
             4 | shard           | public     | customers_ts00004 |            302 |         57291
             4 | shard           | public     | customers_ts00003 |            302 |         57178
             5 | shard           | public     | customers_ts00006 |            300 |         56932
             5 | shard           | public     | customers_ts00005 |            302 |         57386
             6 | shard           | public     | customers_ts00008 |            300 |         56881
             6 | shard           | public     | customers_ts00007 |            304 |         57635
(8 rows)
limitless_tables

This view contains information about tables in Aurora PostgreSQL Limitless Database.

postgres_limitless=> SELECT * FROM rds_aurora.limitless_tables;

 table_gid | local_oid | schema_name | table_name  | table_status | table_type  | distribution_key 
-----------+-----------+-------------+-------------+--------------+-------------+------------------
         5 |     18635 | public      | placeholder | active       | placeholder | 
         6 |     18641 | public      | ref         | active       | reference   | 
         7 |     18797 | public      | orders      | active       | sharded     | HASH (order_id)
         2 |     18579 | public      | customer    | active       | sharded     | HASH (cust_id)
(4 rows)
limitless_table_collocations

This view contains information about collocated sharded tables.

In the following example, the orders and customers tables are collocated, and the users and followers tables are collocated. Collocated tables have the same collocation_id.

postgres_limitless=> SELECT * FROM rds_aurora.limitless_table_collocations ORDER BY collocation_id;

 collocation_id | schema_name | table_name 
----------------+-------------+------------
              2 | public      | orders
              2 | public      | customers
              5 | public      | users
              5 | public      | followers
(4 rows)
limitless_table_collocation_distributions

This view shows the key distribution for each collocation.

postgres_limitless=> SELECT * FROM rds_aurora.limitless_table_collocation_distributions ORDER BY collocation_id, lower_bound;

 collocation_id | subcluster_id |     lower_bound      |     upper_bound      
----------------+---------------+----------------------+----------------------
              2 |             6 | -9223372036854775808 | -4611686018427387904
              2 |             5 | -4611686018427387904 |                    0
              2 |             4 |                    0 |  4611686018427387904
              2 |             3 |  4611686018427387904 |  9223372036854775807
              5 |             6 | -9223372036854775808 | -4611686018427387904
              5 |             5 | -4611686018427387904 |                    0
              5 |             4 |                    0 |  4611686018427387904
              5 |             3 |  4611686018427387904 |  9223372036854775807
(8 rows)