Brad Folkens bfolkens

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:

This brief guide is written from my own experience with migrating a large (~5GB) MySQL database to PostgreSQL for a Rails project.

No warranties, guarantees, support etc. Use at your own risk and, as always, ENSURE YOU MAKE BACKUPS FIRST!

I chose [pgloader][1] because it's extremely fast. YMMV.

Replace mysql2 gem with pg in Gemfile.
Update config/database.yml for PostgreSQL. I used [Rails' template][2] as a starting point.

brew install fish  
curl -L https://get.oh-my.fish | fish

	%% -------------------------------------------------------------------
	%%
	%% Copyright (c) 2016 Hippware Inc. All Rights Reserved.
	%%
	%% This Source Code Form is subject to the terms of the Mozilla Public
	%% License, v. 2.0. If a copy of the MPL was not distributed with this
	%% file, You can obtain one at http://mozilla.org/MPL/2.0/.
	%%
	%% Required Parameters:
	%%

	// Fix iOS Audio Context by Blake Kus https://gist.github.com/kus/3f01d60569eeadefe3a1
	// MIT license
	(function() {
	window.AudioContext = window.AudioContext \|\| window.webkitAudioContext;
	if (window.AudioContext) {
	window.audioContext = new window.AudioContext();
	}
	var fixAudioContext = function (e) {
	if (window.audioContext) {
	// Create empty buffer

	# The multipart parser wants to push data to the output stream, and the fog
	# uploader wants to pull data from the input stream, so we run the fog upload
	# on a separate fiber and use this class to act as the intermediary stream
	# between the two processes.
	#
	# This stream is not a complete implementation, it only handles the behavior we
	# need right now.

	require 'fiber'

	WITH table_scans as (
	SELECT relid,
	tables.idx_scan + tables.seq_scan as all_scans,
	( tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del ) as writes,
	pg_relation_size(relid) as table_size
	FROM pg_stat_user_tables as tables
	),
	all_writes as (
	SELECT sum(writes) as total_writes
	FROM table_scans

	// see: http://en.wikipedia.org/wiki/Hashcash

	var crypto = require("crypto");

	var table = "0123456789/:" + "abcdefghijklmnopqrstuvwxyz" +
	"ABCDEFGHIJKLMNOPQRSTUVWXYZ";
	var next = function (array) {
	for (i = array.length - 1; i >= 0; i--) {
	if (array[i] < table.length - 1) {
	array[i] += 1;

	1c1
	< require 'rack/utils'
	---
	> require 'streaming_upload'
	3,4c3,18
	< module Rack
	< module Multipart
	---
	> class StreamingMultipartRequest < ActionDispatch::Request
	> def parse_multipart(env)

	height: 780
	license: bsd-2-clause