To enable pg_stat_statements, add pg_stat_statements to shared_preload_libraries in the DB parameter group

SELECT
	query,
	calls,
	total_exec_time,
	rows
FROM
	pg_stat_statements
ORDER BY
	total_exec_time DESC
LIMIT
	5;

в RDS Aurora нужно включить предварительно pg_stat_statements, добавив в группе параметров pg_stat_statements в список shared_preload_libraries

SELECT
	query,
	calls,
	total_exec_time,
	rows
FROM
	aurora_stat_statements()
ORDER BY
	total_exec_time DESC
LIMIT
	5;

SELECT * FROM pg_stat_activity WHERE state = ‘active’;
SHOW max_connections;

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

https://aws.amazon.com/blogs/database/determining-the-optimal-value-for-shared_buffers-using-the-pg_buffercache-extension-in-postgresql/
Оценить загруженность таблиц можно по тому, сколько памяти для них используется в shared buffers.

CREATE EXTENSION pg_buffercache;
SELECT c.relname,
pg_size_pretty(count(*)*8192) AS buffer_size,
pg_size_pretty(pg_relation_size(c.oid)) as relation_size,
Round(100.0 * Count(*) / (SELECT setting
FROM pg_settings
WHERE name = 'shared_buffers') :: INTEGER, 2) AS buffers_percent,
ROUND(count(*)*8192*100/ pg_relation_size(c.oid)::numeric, 2 ) AS relation_percent,
CASE
WHEN c.relkind = 'r' THEN 'table'
WHEN c.relkind = 'i' THEN 'index'
WHEN c.relkind = 'S' THEN 'sequence'
WHEN c.relkind = 't' THEN 'TOAST table'
WHEN c.relkind = 'v' THEN 'view'
WHEN c.relkind = 'm' THEN 'materialized view'
WHEN c.relkind = 'c' THEN 'composite type'
WHEN c.relkind = 'f' THEN 'foreign table'
WHEN c.relkind = 'p' THEN 'partitioned table'
WHEN c.relkind = 'I' THEN 'partitioned index'
ELSE 'Unexpected relkind'
END as relation_type
FROM pg_class c
INNER JOIN pg_buffercache b
ON b.relfilenode = c.relfilenode
INNER JOIN pg_database d
ON ( b.reldatabase = d.oid
AND d.datname = Current_database() )
GROUP BY c.relname, c.oid
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 10;