Simple JSONB partial update bench. Requires PostgreSQL 9.5

jsonb_partial_update_bench.php

<?php
const NUM_DATA=10000;
const DUMMY_BYTES=36000;
$db = pg_connect('host=localhost dbname=yohgaki') || die('Failed to connect db');


$dummy = base64_encode(file_get_contents('/dev/urandom', false, null, -1, DUMMY_BYTES));
$dummy = substr($dummy, 0, DUMMY_BYTES);
//$dummy = str_repeat('a', DUMMY_BYTES);

// Clean up
@pg_query('DROP TABLE jsonb_test;');
$ddl = <<< EOD
	CREATE TABLE IF NOT EXISTS jsonb_test (
		id INT8 PRIMARY KEY,
		data JSONB NOT NULL
	);
EOD;
pg_query($ddl);

// Prepare test data
pg_prepare('JSONB_TEST1', 'INSERT INTO jsonb_test (id, data) VALUES ($1, $2);');
$json = '{"dummy":"'.$dummy.'", "array": ["a","b","c"]}';
for ($i = 0; $i < NUM_DATA; $i++) {
	pg_execute('JSONB_TEST1', array($i, $json));
}


// 従来通り全体を更新
pg_prepare('UPDATE1', 'UPDATE jsonb_test SET data = $2 WHERE id = $1;');
$json = '{"dummy":"'.$dummy.'", "new": "abc", "array": ["a","b","c"]}';
$start = microtime(true);
for ($i = 0; $i < NUM_DATA; $i++) {
	pg_execute('UPDATE1', array($i, $json));
}
echo 'TIME: '. (microtime(true)-$start) .PHP_EOL;


// Clean up
@pg_query('DROP TABLE jsonb_test;');
$ddl = <<< EOD
	CREATE TABLE IF NOT EXISTS jsonb_test (
		id INT8 PRIMARY KEY,
		data JSONB NOT NULL
	);
EOD;
pg_query($ddl);

// Prepare test data
pg_prepare('JSONB_TEST2', 'INSERT INTO jsonb_test (id, data) VALUES ($1, $2);');
$json = '{"dummy":"'.$dummy.'", "array": ["a","b","c"]}';
for ($i = 0; $i < NUM_DATA; $i++) {
	pg_execute('JSONB_TEST2', array($i, $json));
}


// 部分更新
pg_prepare('UPDATE2', 'UPDATE jsonb_test SET data = data || $2 WHERE id = $1;');
$json = '{"new":"xyz"}';
$start = microtime(true);
for ($i = 0; $i < NUM_DATA; $i++) {
	pg_execute('UPDATE2', array($i, $json));
}
echo 'TIME: '. (microtime(true)-$start) .PHP_EOL;