Jeetah

CREATE FUNCTION array_distinct(
      anyarray, -- input array 
      boolean DEFAULT false -- flag to ignore nulls
) RETURNS anyarray AS $f$
      SELECT array_agg(DISTINCT x) 
      FROM unnest($1) t(x) 
      WHERE CASE WHEN $2 THEN x IS NOT NULL ELSE true END;
$f$ LANGUAGE SQL IMMUTABLE;

Jeetah

  CREATE FUNCTION count_estimate(query text) RETURNS integer AS $$
DECLARE
  rec   record;
  rows  integer;
BEGIN
  FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP
    rows := substring(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)');
    EXIT WHEN rows IS NOT NULL;
  END LOOP;

Jeetah

SELECT substring(query, 1, 100) AS short_query,                                                                                                                                                                                                                                                                                                            
  round(total_time::numeric, 2) AS total_time,                                                                                                                                                                                                                                                                                                             
  calls,                                                                                                                                                                                                                                                                                                                                

Jeetah

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
     seq_scan + idx_scan \> 0
ORDER BY
     n_live_tup DESC;
     

Jeetah

git log --oneline --pretty=format:"%s (%cn - %ci)" $(git describe --tags --abbrev=0 --match "prod/*" $(git rev-list --tags="prod/*" --max-count=1 --skip=1))..$(git describe --tags --abbrev=0 --match "prod/*")

Jeetah

SELECT table_name,
       pg_size_pretty(total_size) AS size
FROM (
    SELECT table_name,
           pg_total_relation_size(table_schema || '.' || table_name) AS total_size
    FROM information_schema.tables
    WHERE table_schema = 'public' -- Replace with your schema name if needed
) AS table_sizes
ORDER BY total_size DESC;

Jeetah

How to create an HTTPS certificate for localhost domains

This focuses on generating the certificates for loading local virtual hosts hosted on your computer, for development only.

Do not use self-signed certificates in production ! For online certificates, use Let's Encrypt instead (tutorial).

Jeetah

with ld as (
select pid, array_agg(lock_detail) as lock_details
from (
	select l.pid, concat(l.mode, ' on ',  l.locktype, ' (Table: ', t.relname,')') as lock_detail
	from pg_catalog.pg_locks l
	left outer join pg_stat_all_tables t on t.relid = l.relation 
	where t.relname not like 'pg_%'
) as details
group by pid
) 

Jeetah

/* Source and reasoning: https://www.cybertec-postgresql.com/en/get-rid-of-your-unused-indexes/ */
SELECT s.schemaname,
       s.relname AS tablename,
       s.indexrelname AS indexname,
       pg_relation_size(s.indexrelid) AS index_size
FROM pg_catalog.pg_stat_user_indexes s
   JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0      -- has never been scanned
  AND 0 <>ALL (i.indkey)  -- no index column is an expression
  AND NOT i.indisunique   -- is not a UNIQUE index

Jeetah

/* Background and source: https://www.cybertec-postgresql.com/en/index-your-foreign-key/ */
SELECT c.conrelid::regclass AS "table",
       /* list of key column names in order */
       string_agg(a.attname, ',' ORDER BY x.n) AS columns,
       pg_catalog.pg_size_pretty(
          pg_catalog.pg_relation_size(c.conrelid)
       ) AS size,
       c.conname AS constraint,
       c.confrelid::regclass AS referenced_table
FROM pg_catalog.pg_constraint c