Oliver 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).

	DROP FUNCTION IF EXISTS array_sort(anyarray);
	CREATE FUNCTION
	array_sort(
	array_vals_to_sort anyarray
	)
	RETURNS TABLE (
	sorted_array anyarray
	)
	AS $BODY$
	BEGIN

	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;


	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;

	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;

	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;

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

	/* 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