View Postgres Current Transactions

Michael Shepanski
August 30, 2015
SELECT datid, xact_start, 
    date_trunc('second', current_timestamp - xact_start) AS xact_runtime, 
    age(date_trunc('second', current_timestamp), date_trunc('second', state_change)),
    state, query 
  FROM pg_stat_activity 
  ORDER BY xact_start;

View Postgres Vacuum Health

Michael Shepanski
August 30, 2015
  dead > av_threshold AS av_needed,
  CASE WHEN reltuples > 0 THEN round(100.0 * dead / reltuples) ELSE 0 END AS pct_dead,
  CASE WHEN av_threshold > 0 THEN round(100.0 * dead / av_threshold) ELSE 0 END AS pct_threshold
      n.nspname || '.' || c.relname AS tablename,
      pg_stat_get_tuples_inserted(c.oid) AS inserted,
      pg_stat_get_tuples_updated(c.oid) AS updated,
      pg_stat_get_tuples_deleted(c.oid) AS deleted,
      pg_stat_get_live_tuples(c.oid) AS live,
      pg_stat_get_dead_tuples(c.oid) AS dead,
      round(current_setting('autovacuum_vacuum_threshold')::INTEGER +
        current_setting('autovacuum_vacuum_scale_factor')::NUMERIC * c.reltuples)::INTEGER AS av_threshold,
      c.reltuples::INTEGER AS reltuples,
      age(date_trunc('second', current_timestamp), date_trunc('second', greatest(ut.last_vacuum, last_autovacuum))) AS last_vacuum,
      ut.vacuum_count + ut.autovacuum_count as vacuums,
      age(date_trunc('second', current_timestamp), date_trunc('second', greatest(last_analyze, last_autoanalyze))) AS last_analyze,
      ut.analyze_count + ut.autoanalyze_count as analyzes
    FROM pg_class c
    LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
    LEFT JOIN pg_stat_user_tables ut ON ut.relname = c.relname
    WHERE C.relkind IN ('r', 't')
      AND n.nspname NOT IN ('pg_catalog', 'information_schema')
      AND n.nspname !~ '^pg_toast'
  ) AS av
  ORDER BY pct_threshold DESC;

View Postgres Indexes and Usage

Michael Shepanski
August 30, 2015
SELECT i.schemaname AS schema,
    i.relname AS tablename,
    i.indexrelname AS indexname,
    i.idx_scan AS scans,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS indexsize,
    SUBSTRING(k.indexdef FROM '\((.*?)\)') AS indexcolumns
  FROM pg_stat_user_indexes i
  JOIN pg_index j ON i.indexrelid = j.indexrelid
  JOIN pg_indexes k ON k.indexname = i.indexrelname
  WHERE j.indisunique IS FALSE
    AND i.relname = 'api_metrics_metric'
    pg_relation_size(i.indexrelid) DESC;

Dynamic DNS Using DigitalOcean's API

Michael Shepanski
August 1, 2013
Dns, Vps

Another host another dynamic DNS Script to update the IP to point to your home address. The steps are fairly easy:

  1. Create a new A record on the domain of your choice. I named mine "home".
  2. Update the script below with your DigitalOcean CLIENTID, APIKEY, DOMAIN, and RECORD.
  3. Setup a cronjob to run the script once an hour or so.

Use Dot Notation to Build Django Contexts

Michael Shepanski
February 5, 2012

One thing about Django views that I personally find a bit annoying is using dictionary notation when building the context to pass to my templates. I don't find this a big deal for smaller views, but sometimes I have quite a bit of variables to pass back to my template. Organization, readability and maintainability often do not come in the syntactic form of a large dictionary of random objects.