To identify queries requiring more than 2 seconds to run (change this value as needed), run the following command via psql:
SELECT now() - query_start as "runtime", usename, datname, wait_event, wait_event_type, state, query FROM pg_stat_activity WHERE now() - query_start > '2 seconds'::interval and state = 'active' ORDER BY runtime DESC;
For the tables identified in the above queries, examine their schema to determine if indexes are set properly:
\d TABLE_NAME
For external articles about database indexes, see:
- https://semaphoreci.com/blog/2017/05/09/faster-rails-is-your-database-properly-indexed.html
- https://devcenter.heroku.com/articles/postgresql-indexes
- http://rny.io/rails/postgresql/2013/08/20/postgresql-indexing-in-rails.html
Healthcare Blocks Postgres databases default to 100 max connections. This value can be customized for your environment (please open a help desk ticket as needed). To view the number of current connections, run the following query via psql:
select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal from (select count(*) used from pg_stat_activity) t1, (select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) t2, (select setting::int max_conn from pg_settings where name=$$max_connections$$) t3;