Database Cheetsheet

db_cheatsheet.md

Task	Postgres	MySQL	sqlite
help	\?		.help
list databases	psql -l, \l	show databases;	.databases
list tables/views/seq	\d
list roles	\du
list tables	\dt		.tables
describe table	\d <table>		.schema
expanded output	\x	\G
list schemas	\dn
change database.	\c <database>	use <database
whoami	select user

Postgres:

SELECT * from pg_stat_activity;

MySQL:

SHOW PROCESSLIST;

SHOW PROCESSLIST

mysql.md

Time based histogram

SELECT
  DATE(expire_date),
  COUNT(*),
  LPAD('', COUNT(*) / 1000, '|')
FROM django_session
WHERE expire_date BETWEEN CURDATE() - INTERVAL 90 DAY AND CURDATE() + INTERVAL 90 DAY
GROUP BY 1
ORDER BY 1;

postgresql.md

SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name='mytable'

SELECT usename AS role_name,
  CASE 
     WHEN usesuper AND usecreatedb THEN 
	   CAST('superuser, create database' AS pg_catalog.text)
     WHEN usesuper THEN 
	    CAST('superuser' AS pg_catalog.text)
     WHEN usecreatedb THEN 
	    CAST('create database' AS pg_catalog.text)
     ELSE 
	    CAST('' AS pg_catalog.text)
  END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;

Time-based Histogram

5 minute buckets, timestamp field is ts. Has a problem with gaps in data.

SELECT
  to_timestamp(floor(extract(epoch from (ts)) / (5 * 60)) * 5 * 60),
  COUNT(*),
  REPEAT('|', COUNT(*)::int)
FROM "public"."table" 
WHERE "ts" BETWEEN '2021-08-05T12:00Z' AND '2021-08-05T15:30Z' 
GROUP BY 1
ORDER BY 1;

Time bucketed aggregates

1 hour buckets

SELECT
	time_bucket,
	MAX(lag),
	AVG(lag),
	COUNT(*)
FROM (
	SELECT
		event_time_ts,
		inserted_ts - event_time_ts lag,
		to_timestamp(floor(extract(epoch FROM (event_time_ts)) / (60 * 60)) * 60 * 60) time_bucket
	FROM
		"public".table_name"
	WHERE
		"event_time_ts" > NOW() - INTERVAL '1 DAY') events
GROUP BY
	time_bucket;