ololobus

\set aid random(1, 100000 * :scale)
\set bid random(1, 1 * :scale)
\set tid random(1, 10 * :scale)
\set delta random(-5000, 5000)
BEGIN;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

ololobus

CREATE TABLE large_test (num1 bigint, num2 double precision, num3 double precision);

INSERT INTO large_test (num1, num2, num3)
  SELECT round(random()*10), random(), random()*142
  FROM generate_series(1, 20000000) s(i);

EXPLAIN (analyse, buffers) 
SELECT num1, avg(num3) as num3_avg, sum(num2) as num2_sum 
FROM large_test
GROUP BY num1;

ololobus

Apache Spark installation + ipython/jupyter notebook integration guide for macOS

Tested with Apache Spark 2.1.0, Python 2.7.13 and Java 1.8.0_112

For older versions of Spark and ipython, please, see also previous version of text.

Install Java Development Kit

ololobus

#!/bin/sh -ex
#export PATH=/usr/pgsql-11/bin:$PATH

pg_ctl stop -D /tmp/master || echo "ok"
pg_ctl stop -D /tmp/slave || echo "ok"
rm -rf /tmp/master
rm -rf /tmp/slave

# setup master
initdb -D /tmp/master

ololobus

#!/usr/bin/env python3

import requests
import time
import contextlib

from pprint import pprint

import psycopg2

ololobus

Results

Caching with ruby hash is about 1000 times faster than per iteration request to DB without Rails ActiveRecord SQL caching and about 200 times faster than per iteration request with SQL caching.

ololobus

 // @@ -569,6 +569,21 @@ PushPage(StringInfo input_message)
	/*
	 * Every backend on the compute side will verify the page checksum
	 * after reading it from pageserver using GetPage@LSN. Here in the
	 * WAL redo process we are reading pages directly from stdin, so we
	 * would better verify checksum too before applying any WAL records
	 * on top of it.
	 */
	if (!PageIsVerifiedExtended((Page) content, blknum,
								PIV_LOG_WARNING | PIV_REPORT_STAT))

ololobus

Parallel COPY with errors handling

Resources

• https://www.postgresql.org/message-id/flat/603c8f070909141218i291bc983t501507ebc996a531%40mail.gmail.com#603c8f070909141218i291bc983t501507ebc996a531@mail.gmail.com
• https://github.com/ossc-db/pg_bulkload
• http://paradigm4.com/HTMLmanual/13.3/scidb_ug/ch05s02s02.html
• https://wiki.postgresql.org/wiki/Error_logging_in_COPY

ololobus

PostgreSQL Multi-Tenant Sharding Test

Setup

1. Create two Postgres clusters, one on 5432 port (node1) and one on 5433 (node2). Compile and install postgres_fdw extension on both.

2. Set enable_partitionwise_join and enable_partitionwise_aggregate to on.

3. Set postgres_fdw.use_remote_estimate to true.

ololobus

#!/usr/bin/env sh

export PATH=$(pwd)/target/debug/:$(pwd)/tmp_install/bin:$PATH

# Remove all traces of the previous run
killall pageserver
killall wal_acceptor
killall postgres

rm -rf zenith_tmp