robsmith1776

declare @tsql varchar(max) = 'SELECT 
               a.code, b.customer_name
           FROM table_a a 
           INNER JOIN table_b b ON a.code = b.code
           WHERE NOT EXISTS (SELECT 1
                             FROM table_c c
                             WHERE a.code = c.code)'

exec ('create view vwtmp as ' + @tsql)

robsmith1776

SELECT DISTINCT v.oid::regclass AS matrialized_view,
        ns.nspname AS schema,                           -- mview schema,
        d.refobjid::regclass AS ref_table               -- name of table in relation
        FROM pg_depend AS d                             -- objects that depend on a table
            JOIN pg_rewrite AS r                        -- rules depending on a table
                ON r.oid = d.objid
            JOIN pg_class AS v                          -- views for the rules
                ON v.oid = r.ev_class
            JOIN pg_namespace AS ns                     -- schema information
                ON ns.oid = v.relnamespace

robsmith1776

import io
import psycopg2
import boto3

resource = boto3.resource('s3')
conn = psycopg2.connect(dbname=db, user=user, password=pw, host=host)
cur = conn.cursor()

def copyFun(bucket, select_query, filename):
    query = f"""COPY {select_query} TO STDIN \

robsmith1776

#Systemd - How to read live-tail logs of multiple services with journalctl

sudo journalctl --follow _SYSTEMD_UNIT=docker.service + _SYSTEMD_UNIT=apache2.service

robsmith1776

FROM python:3-alpine

# install requirements for psycopg2
RUN apk update && apk add -u \
    postgresql-dev \
    gcc \
    python3-dev \
    musl-dev

RUN pip3 install psycopg2

robsmith1776

Mac install Nerd Font (Fire code)

brew cask
brew tap homebrew/cask-fonts 
brew install font-fira-code
brew install font-Fira-Code-nerd-font
brew install font-hack-nerd-font

robsmith1776

Optimizing postgresql table for more than 100K inserts per second

• Create UNLOGGED table. This reduces the amount of data written to persistent storage by up to 2x.
• Set WITH (autovacuum_enabled=false) on the table. This saves CPU time and IO bandwidth on useless vacuuming of the table (since we never DELETE or UPDATE the table).
• Insert rows with COPY FROM STDIN. This is the fastest possible approach to insert rows into table.
• Minimize the number of indexes in the table, since they slow down inserts. Usually an index on time timestamp with time zone is enough.
• Add synchronous_commit = off to postgresql.conf.
• Use table inheritance for fast removal of old data:

robsmith1776

// Convert Javascript date to Pg YYYY MM DD HH MI SS

function pgFormatDate(date) {
  /* Via http://stackoverflow.com/questions/3605214/javascript-add-leading-zeroes-to-date */
  function zeroPad(d) {
    return ("0" + d).slice(-2)
  }

  var parsed = new Date(date)

robsmith1776

with table_stats as (
select psut.relname,
  psut.n_live_tup,
  1.0 * psut.idx_scan / greatest(1, psut.seq_scan + psut.idx_scan) as index_use_ratio
from pg_stat_user_tables psut
order by psut.n_live_tup desc
),
table_io as (
select psiut.relname,
  sum(psiut.heap_blks_read) as table_page_read,

robsmith1776

require 'pg'
db = PG.connect dbname: 'postgres'
db.exec("DROP DATABASE IF EXISTS just_fkn_around;")
db.exec("CREATE DATABASE just_fkn_around;")
db = PG.connect dbname: 'just_fkn_around'

define_method(:sql) { |sql| db.exec(sql).to_a }

sql <<-SQL
-- some data to query