ilhnctn/queries.md

## queries.md

      
    Raw
  

              queries.md
            
          
    This document tries to combine basic/general query and commands that can be useful for database maintenance and monitoring.
Get Info About Current Active Connections

Here you can list active/iddle connections to the database and see the actual queries that hit the DB.
Note: If there are too much concurrent requests to your database server (not to a single DB instance), than you should consider reducing iddle connections. Especially if there are too much old iddle connections.
SELECT * FROM pg_stat_activity;
List Indexes Usages

IMPORTANT! If the hit rate is less than 90% you have to re-think your index!.
/**
* List of indexes with hit percentages
*/
SELECT 
  relname, 
  100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, 
  n_live_tup rows_in_table
FROM 
  pg_stat_user_tables
WHERE 
    n_live_tup > 0 AND seq_scan + idx_scan > 0 
ORDER BY 
  percent_of_times_index_used DESC;
Add A new Cronjob

SELECT cron.schedule('CRONTAB-SCHEDULE', 'YOUR-SQL-QUERY');

Get Failed Cronjobs

SELECT *
FROM cron.job_run_details
WHERE status <> 'succeeded';
Get Total Size of a Relation (table/view/index etc)

/**
* Following query will give the total storage consumption of the table and it's indexes
* SELECT pg_size_pretty(pg_total_relation_size('predictions.delivered_predictions_timeseries'));
*/
SELECT pg_size_pretty(pg_total_relation_size('SCHEMA_NAME.RELATION_NAME'));
Custom Sequence IDs

postgres=> select max(id_key) from schema_name.model_data;
 max  
------
 N 
(1 row)

postgres=> select nextval('schema_name.table_name_id_key_seq');
 nextval 
---------
  -- Ideally -> N+1 (May be different!!)
(1 row)


BEGIN;
LOCK TABLE schema_name.model_data IN EXCLUSIVE MODE;
-- Update the sequence to the next value of current MAX id
SELECT setval('schema_name.table_name_id_key_seq', COALESCE((SELECT MAX(id_key) + 1 FROM schema_name.model_data), 1), false);

COMMIT;

postgres=> select nextval('schema_name.table_name_id_key_seq'); -- or use curval instead of nextval for validation
 nextval 
---------
    N+1

Get active queries running longer than 1 minute

(kudos: link)
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  usename,
  query,
  state,
  wait_event_type,
  wait_event
FROM pg_stat_activity
WHERE state = 'active' and (now() - pg_stat_activity.query_start) > interval '1 minute';
Alter Constraint on Table

BEGIN;
  ALTER TABLE schame.table
    ADD CONSTRAINT whatever_fields_underscored UNIQUE (field1, field2, ...);
  
  ALTER TABLE schame.table DROP CONSTRAINT previous_index_name;
COMMIT;