# Functions and views for Aurora PostgreSQL Limitless Database
Aurora PostgreSQL Limitless Database has added functions and views. They're based on the corresponding Aurora PostgreSQL functions and views.
**Note**
Some statistics can return inconsistent results if there are transactions in progress.
**Topics**
+ [Aurora PostgreSQL Limitless Database functions](limitless-monitoring-functions.md)
+ [Aurora PostgreSQL Limitless Database views](limitless-monitoring-views.md)
# Aurora PostgreSQL Limitless Database functions
The following table shows the new functions for Aurora PostgreSQL Limitless Database.
**Note**
The functions listed in this table are located in the `rds_aurora` schema. When using a Limitless Database function, make sure to include the fully qualified object name: `rds_aurora`.`object_name`.
| Aurora PostgreSQL Limitless Database function | Corresponding Aurora PostgreSQL function |
| --- | --- |
| [limitless\$1backend\$1dsid](#limitless_backend_dsid) | pg\$1backend\$1pid |
| [limitless\$1cancel\$1session](#limitless_cancel_session) | pg\$1cancel\$1backend |
| [limitless\$1stat\$1clear\$1snapshot](#limitless_stat_clear_snapshot) | pg\$1stat\$1clear\$1snapshot |
| [limitless\$1stat\$1database\$1size](#limitless_stat_database_size) | pg\$1database\$1size |
| [limitless\$1stat\$1get\$1snapshot\$1timestamp](#limitless_stat_get_snapshot_timestamp) | pg\$1stat\$1get\$1snapshot\$1timestamp |
| [limitless\$1stat\$1prepared\$1xacts](#limitless_stat_prepared_xacts) | pg\$1prepared\$1xacts |
| [limitless\$1stat\$1relation\$1sizes](#limitless_stat_relation_sizes) | pg\$1indexes\$1size, pg\$1relation\$1size, pg\$1table\$1size, pg\$1total\$1relation\$1size |
| [limitless\$1stat\$1reset](#limitless_stat_reset) | pg\$1stat\$1reset |
| [limitless\$1stat\$1statements\$1reset](#limitless_stat_statements_reset) | pg\$1stat\$1statements\$1reset |
| [limitless\$1stat\$1system\$1waits](#limitless_stat_system_waits) | aurora\$1stat\$1system\$1waits |
| [limitless\$1terminate\$1session](#limitless_terminate_session) | pg\$1terminate\$1backend |
| [limitless\$1wait\$1report](#limitless_wait_report) | aurora\$1wait\$1report |
The following examples provide details about the Aurora PostgreSQL Limitless Database functions. For more information on PostgreSQL functions, see [Functions and operators](https://www.postgresql.org/docs/15/functions.html) in the PostgreSQL documentation.
**limitless\$1backend\$1dsid**
The `limitless_backend_dsid` function returns the distributed session ID for the current session. A distributed session runs on a router in a DB shard group and involves backend processes on one or more shards in the DB shard group.
The following example shows how to use the `limitless_backend_dsid` function.
```
SELECT rds_aurora.limitless_backend_dsid();
limitless_backend_dsid
------------------------
8CACD7B04D0FC2A5
(1 row)
```
**limitless\$1cancel\$1session**
The `limitless_cancel_session` function works similarly to `pg_cancel_backend`, but it tries to cancel all backend processes related to the provided distributed session ID by sending a `SIGINT` (interruption signal).
The input parameter is the following:
+ `distributed_session_id` (text) – The ID of the distributed session to be canceled.
The output parameters are the following:
+ `subcluster_id` (text) – The ID of the subcluster to which this process belongs.
+ `pid` (text) – The backend process ID.
+ `success` (boolean) – Whether the cancellation was successful.
The following example shows how to use the `limitless_cancel_session` function.
```
SELECT * FROM rds_aurora.limitless_cancel_session('940CD5C81E3C796B');
subcluster_id | pid | success
---------------+-------+---------
1 | 26920 | t
(1 row)
```
**limitless\$1stat\$1clear\$1snapshot**
The `limitless_stat_clear_snapshot` function discards the current statistics snapshot or cached information on all nodes.
The following example shows how to use the `limitless_stat_clear_snapshot` function.
```
SELECT rds_aurora.limitless_stat_clear_snapshot();
```
**limitless\$1stat\$1database\$1size**
The `limitless_stat_database_size` function returns the sizes of a database in the DB shard group.
The input parameter is the following:
+ `dbname` (name) – The database for which to get the sizes.
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`.
+ `db_size` – The size of the database in this subcluster in bytes.
The following example shows how to use the `limitless_stat_database_size` function.
```
SELECT * FROM rds_aurora.limitless_stat_database_size('postgres_limitless');
subcluster_id | subcluster_type | db_size
---------------+-----------------+----------
1 | router | 8895919
2 | router | 8904111
3 | shard | 21929391
4 | shard | 21913007
5 | shard | 21831087
(5 rows)
```
**limitless\$1stat\$1get\$1snapshot\$1timestamp**
The `limitless_stat_get_snapshot_timestamp` function returns the timestamp of the current statistics snapshot, or `NULL` if no statistics snapshot has been taken. A snapshot is taken the first time cumulative statistics are accessed in a transaction if `stats_fetch_consistency` is set to `snapshot`. Returns a consolidated view of snapshot timestamps from all nodes. The `subcluster_id` and `subcluster_type` columns show which node the data is from.
The following example shows how to use the `limitless_stat_get_snapshot_timestamp` function.
```
SELECT * FROM rds_aurora.limitless_stat_get_snapshot_timestamp();
subcluster_id | subcluster_type | snapshot_timestamp
---------------+-----------------+--------------------
1 | router |
2 | router |
3 | shard |
4 | shard |
5 | shard |
(5 rows)
```
**limitless\$1stat\$1prepared\$1xacts**
The `limitless_stat_prepared_xacts` function returns information about transactions on all nodes that are currently prepared for two-phase commit. For more information, see [pg\$1prepared\$1xacts](https://www.postgresql.org/docs/current/view-pg-prepared-xacts.html) in the PostgreSQL documentation.
The following example shows how to use the `limitless_stat_prepared_xacts` function.
```
postgres_limitless=> SELECT * FROM rds_aurora.limitless_stat_prepared_xacts;
subcluster_id | subcluster_type | transaction_id | gid | prepared | owner_id | database_id
---------------+-----------------+----------------+------------------------------+-------------------------------+------------+--------------------
8 | shard | 5815978 | 7_4599899_postgres_limitless | 2024-09-03 15:51:17.659603+00 | auroraperf | postgres_limitless
12 | shard | 4599138 | 7_4599899_postgres_limitless | 2024-09-03 15:51:17.659637+00 | auroraperf | postgres_limitless
(2 rows)
```
**limitless\$1stat\$1relation\$1sizes**
The `limitless_stat_relation_sizes` function returns the different sizes of a table in the DB shard group.
The input parameters are the following:
+ `relnspname` (name) – The name of the schema containing the table.
+ `relname` (name) – The name of the table.
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`.
+ `main_size` – The size in bytes of the main data fork in this node.
+ `fsm_size` – The size in bytes of the free space map for the table in this node.
+ `vm_size` – The size in bytes of the visibility map for the table in this node.
+ `init_size` – The size in bytes of the initialization of the table in this node.
+ `toast_size` – The size in bytes of the toast table associated with the table in this fork.
+ `index_size` – The size in bytes of all of the indexes for the table in this node.
+ `total_size` – The size in bytes of all of the segments of the table in this node.
The following example shows how to use the `limitless_stat_relation_sizes` function (some columns are omitted).
```
SELECT * FROM rds_aurora.limitless_stat_relation_sizes('public','customers');
subcluster_id | subcluster_type | main_size | fsm_size | vm_size | toast_size | table_size | total_size
---------------+-----------------+-----------+----------+---------+------------+------------+------------
1 | router | 0 | 0 | 0 | 0 | 0 | 0
2 | router | 0 | 0 | 0 | 0 | 0 | 0
3 | shard | 4169728 | 4177920 | 1392640 | 1392640 | 11132928 | 11132928
4 | shard | 4169728 | 4177920 | 1392640 | 1392640 | 11132928 | 11132928
5 | shard | 3981312 | 4227072 | 1409024 | 1409024 | 11026432 | 11026432
(5 rows)
```
**limitless\$1stat\$1reset**
The `limitless_stat_reset` function resets all statistics counters for the current database to zero (0). If `track_functions` is enabled, the `stats_reset` column in `limitless_stat_database` shows the last time statistics were reset for the database. By default, `limitless_stat_reset` can be run only by a superuser. Other users can be granted permission by using the `EXECUTE` privilege.
The following example shows how to use the `limitless_stat_reset` function.
```
SELECT tup_inserted, tup_deleted FROM pg_stat_database
WHERE datname = 'postgres_limitless';
tup_inserted | tup_deleted
--------------+-------------
896 | 0
(1 row)
SELECT rds_aurora.limitless_stat_reset();
limitless_stat_reset
---------------------
(1 row)
SELECT tup_inserted, tup_deleted FROM pg_stat_database
WHERE datname = 'postgres_limitless';
tup_inserted | tup_deleted
-------------+-------------
0 | 0
(1 row)
```
**limitless\$1stat\$1statements\$1reset**
The `limitless_stat_statements_reset` function discards statistics gathered so far by `limitless_stat_statements` corresponding to the specified `username`, `dbname`, `distributed_query_id`, and `queryid` parameters. If any of the parameters aren't specified, the default value `""` or `0` (invalid) is used for each of them, and the statistics that match with other parameters are reset. If no parameter is specified, or all the specified parameters are `""` or `0` (invalid), the function discards all statistics. If all statistics in the `limitless_stat_statements` view are discarded, the function also resets the statistics in the `limitless_stat_statements_info` view.
The input parameters are the following:
+ `username` (name) – The user that queried the statement.
+ `dbname` (name) – The database where the query was run.
+ `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.
+ `queryid` (bigint) – The query ID of the statement.
The following example shows how to use the `limitless_stat_statements_reset` function to reset all of the statistics gathered by `limitless_stat_statements`.
```
SELECT rds_aurora.limitless_stat_statements_reset();
```
**limitless\$1stat\$1system\$1waits**
The `limitless_stat_system_waits` function returns a consolidated view of the wait event data from `aurora_stat_system_waits`, which reports system wide wait activity in an instance, from all nodes. The `subcluster_id` and `subcluster_type` columns show which node the data is from.
The following example shows how to use the `limitless_stat_system_waits` function.
```
postgres_limitless=> SELECT *
FROM rds_aurora.limitless_stat_system_waits() lssw, pg_catalog.aurora_stat_wait_event() aswe
WHERE lssw.event_id=aswe.event_id and aswe.event_name='LimitlessTaskScheduler';
subcluster_id | subcluster_type | type_id | event_id | waits | wait_time | event_name
---------------+-----------------+---------+-----------+--------+--------------+------------------------
1 | router | 12 | 201326607 | 677068 | 616942216307 | LimitlessTaskScheduler
2 | router | 12 | 201326607 | 678586 | 616939897111 | LimitlessTaskScheduler
3 | shard | 12 | 201326607 | 756640 | 616965545172 | LimitlessTaskScheduler
4 | shard | 12 | 201326607 | 755184 | 616958057620 | LimitlessTaskScheduler
5 | shard | 12 | 201326607 | 757522 | 616963183539 | LimitlessTaskScheduler
(5 rows)
```
**limitless\$1terminate\$1session**
The `limitless_terminate_session` function works similarly to `pg_terminate_backend`, but it tries to end all backend processes related to the provided distributed session ID by sending a `SIGTERM` (end signal).
The input parameter is the following:
+ `distributed_session_id` (text) – The ID of the distributed session to be ended.
The output parameters are the following:
+ `subcluster_id` (text) – The ID of the subcluster to which this process belongs.
+ `pid` (text) – The backend process ID.
+ `success` (boolean) – Whether the process was successfully ended.
The following example shows how to use the `limitless_terminate_session` function.
```
SELECT * FROM rds_aurora.limitless_terminate_session('940CD5C81E3C796B');
subcluster_id | pid | success
---------------+-------+---------
1 | 26920 | t
(1 row)
```
**limitless\$1wait\$1report**
The `limitless_wait_report` function returns the wait event activity over a period of time from all nodes. The `subcluster_id` and `subcluster_type` columns show which node the data is from.
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`.
The rest of the columns are the same as in `aurora_wait_report`.
The following example shows how to use the `limitless_wait_report` function.
```
postgres_limitless=> select * from rds_aurora.limitless_wait_report();
subcluster_id | subcluster_type | type_name | event_name | waits | wait_time | ms_per_wait | waits_per_xact | ms_per_xact
---------------+-----------------+-----------+------------+-------+-----------+-------------+--------------- +-------------
1 | router | Client | ClientRead | 57 | 741550.14 | 13009.652 | 0.19 | 2505.237
5 | shard | Client | ClientRead | 54 | 738897.68 | 13683.290 | 0.18 | 2496.276
4 | shard | Client | ClientRead | 54 | 738859.53 | 13682.584 | 0.18 | 2496.147
2 | router | Client | ClientRead | 53 | 719223.64 | 13570.257 | 0.18 | 2429.810
3 | shard | Client | ClientRead | 54 | 461720.40 | 8550.378 | 0.18 | 1559.86
```
# 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\$1database](#limitless_database) | pg\$1database |
| [limitless\$1locks](#limitless_locks) | pg\$1locks |
| [limitless\$1stat\$1activity](#limitless_stat_activity) | pg\$1stat\$1activity |
| [limitless\$1stat\$1all\$1indexes](#limitless_stat_all_indexes) | pg\$1stat\$1all\$1indexes |
| [limitless\$1stat\$1all\$1tables](#limitless_stat_all_tables) | pg\$1stat\$1all\$1tables |
| [limitless\$1stat\$1database](#limitless_stat_database) | pg\$1stat\$1database |
| [limitless\$1stat\$1progress\$1vacuum](#limitless_stat_progress_vacuum) | pg\$1stat\$1progress\$1vacuum |
| [limitless\$1stat\$1statements](#limitless_stat_statements) | pg\$1stat\$1statements |
| [limitless\$1stat\$1subclusters](#limitless_stat_subclusters) | None |
| [limitless\$1stat\$1statements\$1info](#limitless_stat_statements_info) | pg\$1stat\$1statements\$1info |
| [limitless\$1statio\$1all\$1indexes](#limitless_statio_all_indexes) | pg\$1statio\$1all\$1indexes |
| [limitless\$1statio\$1all\$1tables](#limitless_statio_all_tables) | pg\$1statio\$1all\$1tables |
| [limitless\$1tables](#limitless_tables) | pg\$1tables |
| [limitless\$1table\$1collocations](#limitless_table_collocations) | None |
| [limitless\$1table\$1collocation\$1distributions](#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](https://www.postgresql.org/docs/15/monitoring-stats.html#MONITORING-STATS-VIEWS) in the PostgreSQL documentation.
**Note**
Some statistics views can return inconsistent results if you have ongoing transactions.
**limitless\$1database**
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\$1locks**
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 | |
2AD7F370202D0FA9 | | limitless_auth_admin | |
47BDE66E9A5E8477 | | limitless_auth_admin | |
2AD7F370202D0FA9 | | limitless_auth_admin | |
(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\$1stat\$1activity**
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,
is_sso_query
FROM
rds_aurora.limitless_stat_activity
WHERE
distributed_session_id in ('D2470C97E3D07E06', '5A3CD7B8E5FD13FF')
order by distributed_session_id;
subcluster_id | subcluster_type | distributed_session_id | distributed_session_state | datname | distributed_query_id | is_sso_query
---------------+-----------------+------------------------+---------------------------+--------------------+----------------------+--------------
2 | router | 5A3CD7B8E5FD13FF | coordinator | postgres_limitless | | f
3 | shard | 5A3CD7B8E5FD13FF | participant | postgres_limitless | 6808291725541680947 |
4 | shard | 5A3CD7B8E5FD13FF | participant | postgres_limitless | 6808291725541680947 |
2 | router | D2470C97E3D07E06 | coordinator | postgres_limitless | | t
3 | shard | D2470C97E3D07E06 | participant | postgres_limitless | 4058400544464210222 |
(5 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.
+ `is_sso_query` (text) – This lets us know whether the query is single shard optimized or not.
The rest of the columns are the same as in `pg_stat_activity`.
**limitless\$1stat\$1all\$1indexes**
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\$1stat\$1all\$1tables**
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\$1stat\$1database**
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\$1stat\$1progress\$1vacuum**
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\$1stat\$1statements**
This view provides a means for tracking planning and running statistics of all SQL statements run on all nodes.
You must install the [pg\$1stat\$1statements](https://www.postgresql.org/docs/current/pgstatstatements.html) 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,
sso_calls
FROM
rds_aurora.limitless_stat_statements;
subcluster_id | subcluster_type | distributedqueryid | username | dbname | sso_calls
---------------+-----------------+----------------------+-------------------------------------+--------------------+-----------
2 | router | | postgres | postgres_limitless | 0
2 | router | | postgres | postgres_limitless | 0
2 | router | | postgres | postgres_limitless | 0
2 | router | | postgres | postgres_limitless | 0
2 | router | | postgres | postgres_limitless | 0
2 | router | | postgres | postgres_limitless | 1
3 | shard | -7975178695405682176 | 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.
+ `sso_calls` (name) – The number of times statement was single-shard optimized.
The rest of the columns are the same as in [pg\$1stat\$1statements](https://www.postgresql.org/docs/current/pgstatstatements.html).
**limitless\$1stat\$1statements\$1info**
This view contains statistics for the `limitless_stat_statements` view. Each row contains data for the [pg\$1stat\$1statements\$1info](https://www.postgresql.org/docs/current/pgstatstatements.html#id-1.11.7.41.7) 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\$1stat\$1statements\$1info](https://www.postgresql.org/docs/current/pgstatstatements.html#id-1.11.7.41.7).
**limitless\$1stat\$1subclusters**
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\$1statio\$1all\$1indexes**
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\$1statio\$1all\$1tables**
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\$1tables**
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\$1table\$1collocations**
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\$1table\$1collocation\$1distributions**
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)
```