View Postgres Current Transactions

Michael Shepanski
August 30, 2015
Aug
30
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
Aug
30
SELECT *,
  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
  FROM (SELECT
      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
Aug
30
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'
  ORDER BY
    i.idx_scan,
    pg_relation_size(i.indexrelid) DESC;

Dynamic DNS Using DigitalOcean's API

Michael Shepanski
August 1, 2013
Dns, Vps
Aug
1

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
Feb
5

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.