Load the data in postgresql

Read the article the crackdown on the performance of inserts in Oracle using transactions in Java. As at the moment I am working with postgresql, I decided to check how much will give this tuning in conjunction with galanga.
Used stack: golang-1.1.2 + postgresql-9.3.1.
To run the program will need golanowski driver for postgresql. I use the driver github.com/lib/pq that installed with the command go get-u github.com/lib/pq.

testing framework.

Home desktop with archlinux-x86_64 and kernel-3.11.5, 4GB RAM (at the time of the test was freely about 1.2 GB), with a regular old spindle HDD. Postgres more or less configured (up to 768МБ shared_buffers and max_connections to 1000, + some tuning), but no hardcore.
For purity of experiment did not make any goroutine — all in 1 thread.

Text

/* sql_insertion_test01.go 
* Synthetic test for simple insert operations into postgresql database
* Call: ./sql_insertion_test01
* Pls do not use the name "insert_test", because this name is always used by go-pkg-system
*/
package main

import (
_ "github.com/lib/pq"
"database/sql"
"fmt"
"time"
)

const DB_CONNECT_STRING = 
"host=localhost port=5432 user=your_role password=your_password dbname=your_database sslmode=disable"

func main() {

db, err := sql.Open("postgres", DB_CONNECT_STRING)
defer db.Close()

if err != nil {
fmt.Printf("Database opening error -- > %v\n", err)
panic("Database error")
}

init_database(&db)
make_insertion(&db)

}

/*-----------------------------------------------------------------------------*/
func init_database(pdb **sql.DB) {

db := *pdb

init_db_strings := []string{
"DROP SCHEMA IF EXISTS sb CASCADE;",
"CREATE SCHEMA sb;",
//be careful - next multiline string is quoted by backquote symbol
`CREATE TABLE sb.test_data(
id serial,
device_id integer not null,
parameter_id integer not null,
value varchar(100),
event_ctime timestamp default current_timestamp,
constraint id_pk primary key (id));`}

for _, qstr := range init_db_strings {
_, err := db.Exec(qstr)

if err != nil {
fmt.Printf("Database init error -- > %v\n", err)
panic("Query error")
}
}
fmt.Println("Database rebuilded successfully")
}

/*-----------------------------------------------------------------------------*/
func make_insertion(pdb **sql.DB) {

db := *pdb
const TEST_NUMBER = 400000

// backquotes for next multiline string
const INSERT_QUERY = `insert into sb.test_data(device_id, parameter_id, value)
values ($1, $2, $3);`

insert_query, err := db.Prepare(INSERT_QUERY)
insert_query defer.Close()

if err != nil {
fmt.Printf("Query preparation error -- > %v\n", err)
panic("Test query error")
}

t1 := time.Now()

for i := 0; i < TEST_NUMBER; i++ {

_, err = insert_query.Exec(i, i, "0")

if err != nil {
fmt.Printf("Query execution error -- > %v\n", err)
panic("Error")
}
}

t2 := time.Since(t1)

fmt.Printf("%v queries are executed for %d seconds (%d per second)\n",
TEST_NUMBER, t2.Seconds(), TEST_NUMBER/t2.Seconds())

// do not  forget  clean up after work )
//_, err = db.Query("TRUNCATE sb.test_data;")
}

As can be seen from the text — no special allocations in the transaction, Korotin, temporary tables and other tricks — direct input.
Well, of course a bunch of prepare-exec.

Results

Results — 16000-17000 inserts per second on my machine.
The results do not depend on the constants TEST_NUMBER (set of 1000, 2000, 4000, 40000 and 400000 stopped). It is possible for large values and plugging will occur, but I think that for current purposes such a performance is enough.
Expected to get the worst numbers and planned to Tinker with the transaction, but not needed.

Conclusions

I think that the test is still somewhat synthetic — data fit in the cache. Not feeling that at some parameters had to rest against the performance of the disk subsystem. And desktop the kernel allocates memory with a stronger server.
It is possible that the authors of the driver used some kind of a trick. Nevertheless, I believe that this bunch of applications is quite usable.

Article based on information from habrahabr.ru