Skip to content

Instantly share code, notes, and snippets.

@codeinthehole
codeinthehole / userdata.sh
Created February 2, 2016 21:21
Terraform config for an EC2 instance with a replaceable EBS volume
#!/bin/bash
DEVICE=/dev/$(lsblk -n | awk '$NF != "/" {print $1}')
FS_TYPE=$(file -s $DEVICE | awk '{print $2}')
MOUNT_POINT=/data
# If no FS, then this output contains "data"
if [ "$FS_TYPE" = "data" ]
then
echo "Creating file system on $DEVICE"
@jberkus
jberkus / gist:6b1bcaf7724dfc2a54f3
Last active January 7, 2024 21:26
Finding Unused Indexes
WITH table_scans as (
SELECT relid,
tables.idx_scan + tables.seq_scan as all_scans,
( tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del ) as writes,
pg_relation_size(relid) as table_size
FROM pg_stat_user_tables as tables
),
all_writes as (
SELECT sum(writes) as total_writes
FROM table_scans
@rgreenjr
rgreenjr / postgres_queries_and_commands.sql
Last active June 20, 2024 14:04
Useful PostgreSQL Queries and Commands
-- show running queries (pre 9.2)
SELECT procpid, age(clock_timestamp(), query_start), usename, current_query
FROM pg_stat_activity
WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;
-- show running queries (9.2)
SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'