Skip to content

Instantly share code, notes, and snippets.

View begriffs's full-sized avatar

Joe Nelson begriffs

1.2k followers · 0 following
View GitHub Profile
@begriffs
begriffs / single-machine.sh
Created March 22, 2017 00:37
Quick single-machine cluster
pg_dropcluster --stop 9.6 main
alias forall="echo -n 'master worker1 worker2' | xargs -d ' ' -n 1 -I '{}'"
forall pg_createcluster --locale en_US.UTF-8 --start 9.6 {}
forall pg_conftool 9.6 {} set shared_preload_libraries citus
forall pg_conftool 9.6 {} set listen_addresses '*'
forall pg_ctlcluster 9.6 {} restart
forall psql --cluster 9.6/{} -c "CREATE EXTENSION citus;"
@begriffs
begriffs / autovacuum.md
Last active November 3, 2016 23:57
Autovacuum is Not the Enemy
title Autovacuum is Not the Enemy
date 2016-10-20
author Joe Nelson
tags
syndicate true

It’s a common misconception that high volume read-write workloads in PostgreSQL inevitably causes database inefficiency. We’ve heard of cases where users encounter slowdowns doing only a few hundred writes per second and turn to systems like Dynamo or Cassandra out of frustration. However PostgreSQL can handle these workloads without a problem as long as it is configured correctly.

@begriffs
begriffs / pg-count-perf.md
Created October 12, 2016 00:32
Faster PostgreSQL Counting
title Faster PostgreSQL Counting
date 2016-10-12
author Joe Nelson
tags
syndicate true

Everybody counts, but not always quickly. This article is a close look into how PostgreSQL optimizes counting. If you know the tricks

@begriffs
begriffs / application_function__benchmark.sql
Created September 24, 2016 17:35 — forked from deinspanjer/application_function__benchmark.sql
Some Postgres audit logging customizations for use in PostgREST
-- Deploy myproject:application_function__benchmark to pg
-- requires: application__schema
BEGIN;
SET search_path TO application;
CREATE TYPE _bench_result AS (
function_name TEXT,
runtime REAL,
@begriffs
begriffs / local-join.md
Last active July 19, 2016 23:55
Workaround to join locale and distributed tables

Problem: JOIN a local and a distributed table

Attempting to execute a JOIN between a local and a distributed table causes an error:

ERROR: cannot plan queries that include both regular and partitioned relations

In Citus Community and Enterprise editions there is a workaround. You can replicate the local table to a single shard on every worker and push the join query down to the workers. Suppose we want to join tables here and there, where there is already distributed but here is on the master database.

-- Allow "here" to be distributed
@begriffs
begriffs / distinct.md
Created July 19, 2016 22:19
Count distinct on non-distribution column

Problem: SELECT DISTINCT (non-distribution column)

Citus supports SELECT DISTINCT col but only when col is the shard distribution column. If it is not, use GROUP BY as a simple workaround:

select col from table group by col;
@begriffs
begriffs / select-into.md
Last active July 19, 2016 23:44
Workaround for SELECT INTO

Problem: SELECT ... INTO

Citus does not support inserting the results of a query into a distributed table with the standard SELECT INTO command. One workaround is to use two database connections to stream the query results to master and then distribute them to the shards.

psql -c "COPY (query) TO STDOUT" | psql -c "COPY table FROM STDIN"

This does incur network cost. If this workaround is too slow please contact Citus Data support. We can assist you in parallelizing the table insertion across all workers using a more complicated technique.

@begriffs
begriffs / subquery.md
Last active July 19, 2016 23:41
Workarounds for subquery in WHERE clause

Problem: WHERE clause containing subquery

Take this query for example.

select c1
  from table_main
 where c2 in (select c2_inner from table_subquery where c1_inner > 300)
   and c3 = 20;
@begriffs
begriffs / gist:a4fb7d0133e7818827fe4a652f8ae788
Created July 2, 2016 21:20
haskell vim now terminal log for issue
user@GenericUbuntuMachine:~$ sudo apt-get install vim git ghc ghc-mod libzmq3-dev libghc-zlib-dev haskell-stack rlwrap tmux
[sudo] password for user:
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
cabal-helper emacs24 emacs24-bin-common emacs24-common emacs24-common-non-dfsg emacs24-el ghc-mod-el
git-man haskell-mode libbsd-dev liberror-perl libffi-dev libgif7 libgmp-dev libgmpxx4ldbl
liblockfile-bin liblockfile1 libm17n-0 libncurses5-dev libotf0 libsodium18 libtinfo-dev libzmq5
m17n-db vim-common vim-runtime vim-tiny zlib1g-dev
@begriffs
begriffs / url-picker-hack.html
Created June 1, 2016 21:22
<label for="ospicker">Choose OS</label>
<select id="ospicker">
<option value="https://download.postgresql.org/pub/repos/yum/9.5/fedora/fedora-23-x86_64/pgdg-fedora95-9.5-3.noarch.rpm">Fedora 23 - x86_64</option>
<option value="https://download.postgresql.org/pub/repos/yum/9.5/fedora/fedora-22-x86_64/pgdg-fedora95-9.5-3.noarch.rpm">Fedora 22 - x86_64</option>
<option value="https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-redhat95-9.5-2.noarch.rpm">Red Hat Enterprise Linux 7 - x86_64</option>
<option value="https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-sl95-9.5-2.noarch.rpm">Scientific Linux 7 - x86_64</option>
<option value="https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-2.noarch.rpm">CentOS 7 - x86_64</option>
<option value="https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-oraclelinux95-9.5-2.noarch.rpm">Oracle Enterprise Linux 7 - x86_64</option>
<option value="https://