Postgres (OLTP) vs. Clickhouse (OLAP)

main.go

package main

import (
	"database/sql"
	"log"
	"os"
	"strconv"
	"time"

	_ "github.com/ClickHouse/clickhouse-go/v2"
	_ "github.com/lib/pq"
)

/* setup:
docker run -e POSTGRES_PASSWORD=password -p 5432:5432 postgres
docker run -p 9000:9000 clickhouse/clickhouse-server
go run main.go 100000
2024/04/14 21:01:46 PostgreSQL
2024/04/14 21:01:46 Creating table
2024/04/14 21:01:46 Inserting data
2024/04/14 21:01:56 Querying data
2024/04/14 21:01:56 Count: 100000
2024/04/14 21:01:56 Query took: 3.675208ms
2024/04/14 21:01:56 Deleting data
2024/04/14 21:01:56 ClickHouse
2024/04/14 21:01:56 Creating table
2024/04/14 21:01:56 Inserting 100,000 rows
2024/04/14 21:01:56 Querying data
2024/04/14 21:01:56 Count: 100000
2024/04/14 21:01:56 Query took: 1.541708ms
2024/04/14 21:01:56 Deleting data

go run main.go 1000000
2024/04/14 21:07:14 PostgreSQL
2024/04/14 21:07:14 Creating table
2024/04/14 21:07:14 Inserting data
2024/04/14 21:08:56 Querying data
2024/04/14 21:08:56 Count: 1000000
2024/04/14 21:08:56 Query took: 19.076875ms
2024/04/14 21:08:56 Deleting data
2024/04/14 21:08:56 ClickHouse
2024/04/14 21:08:56 Creating table
2024/04/14 21:08:56 Inserting 100,000 rows
2024/04/14 21:08:56 Querying data
2024/04/14 21:08:56 Count: 1000000
2024/04/14 21:08:56 Query took: 1.778667ms
2024/04/14 21:08:56 Deleting data
*/

func main() {
	// get count of rows from arguements
	if len(os.Args) < 2 {
		log.Fatalf("Please provide the number of rows to insert and delete")
	}
	rowCnt, err := strconv.Atoi(os.Args[1])
	if err != nil {
		log.Fatalf("Error converting arguement to integer: %v", err)
	}

	// insert and delete data in both databases
	insertAndDeleteInPostgres(rowCnt)
	insertAndDeleteInClickHouse(rowCnt)
}

func insertAndDeleteInPostgres(rowCnt int) {
	log.Default().Println("PostgreSQL")
	connStr := "host=localhost port=5432 user=postgres password=password dbname=postgres sslmode=disable"
	db, err := sql.Open("postgres", connStr)
	if err != nil {
		log.Fatalf("Error connecting to PostgreSQL: %v", err)
	}
	defer db.Close()

	// Create table
	log.Default().Println("Creating table")
	_, err = db.Exec("CREATE TABLE IF NOT EXISTS your_table (column_name TEXT)")
	if err != nil {
		log.Fatalf("Error creating table in PostgreSQL: %v", err)
	}

	log.Default().Println("Inserting data")
	t := time.Now()
	tx, err := db.Begin()
	if err != nil {
		log.Fatalf("Error beginning transaction in PostgreSQL: %v", err)
	}
	stmt, err := tx.Prepare("INSERT INTO your_table (column_name) VALUES ($1)")
	if err != nil {
		log.Fatalf("Error preparing statement in PostgreSQL: %v", err)
	}
	defer stmt.Close()
	for i := 0; i < rowCnt; i++ {
		_, err = stmt.Exec("value")
		if err != nil {
			log.Fatalf("Error executing statement in PostgreSQL: %v", err)
		}
	}
	err = tx.Commit()
	if err != nil {
		log.Fatalf("Error committing transaction in PostgreSQL: %v", err)
	}

	// Time how long an aggregate query takes
	log.Default().Println("Querying data")
	t = time.Now()
	rows, err := db.Query("SELECT COUNT(*) FROM your_table")
	if err != nil {
		log.Fatalf("Error querying data from PostgreSQL: %v", err)
	}
	defer rows.Close()
	for rows.Next() {
		var count int
		err = rows.Scan(&count)
		if err != nil {
			log.Fatalf("Error scanning data from PostgreSQL: %v", err)
		}
		log.Printf("Count: %d", count)
	}
	log.Printf("Query took: %v", time.Since(t))

	// Delete all rows
	log.Default().Println("Deleting data")
	_, err = db.Exec("DELETE FROM your_table")
	if err != nil {
		log.Fatalf("Error deleting data from PostgreSQL: %v", err)
	}

	// Drop table
	_, err = db.Exec("DROP TABLE IF EXISTS your_table")
	if err != nil {
		log.Fatalf("Error dropping table in PostgreSQL: %v", err)
	}
}

func insertAndDeleteInClickHouse(rowCnt int) {
	log.Default().Println("ClickHouse")
	connStr := "tcp://localhost:9000?username=default"
	db, err := sql.Open("clickhouse", connStr)
	if err != nil {
		log.Fatalf("Error connecting to ClickHouse: %v", err)
	}
	defer db.Close()

	// Create table
	log.Default().Println("Creating table")
	_, err = db.Exec("CREATE TABLE IF NOT EXISTS your_table (column_name String) ENGINE = MergeTree() ORDER BY column_name")
	if err != nil {
		log.Fatalf("Error creating table in ClickHouse: %v", err)
	}

	// Batch insert 100,000 rows
	log.Default().Println("Inserting 100,000 rows")
	tx, err := db.Begin()
	if err != nil {
		log.Fatalf("Error beginning transaction in ClickHouse: %v", err)
	}
	stmt, err := tx.Prepare("INSERT INTO your_table (column_name) VALUES (?)")
	if err != nil {
		log.Fatalf("Error preparing statement in ClickHouse: %v", err)
	}
	for i := 0; i < rowCnt; i++ {
		_, err := stmt.Exec("Some value")
		if err != nil {
			log.Fatalf("Error inserting data into ClickHouse: %v", err)
		}
	}
	err = tx.Commit()
	if err != nil {
		log.Fatalf("Error committing transaction in ClickHouse: %v", err)
	}

	// Time how long an aggregate query takes
	log.Default().Println("Querying data")
	t := time.Now()
	rows, err := db.Query("SELECT COUNT(*) FROM your_table")
	if err != nil {
		log.Fatalf("Error querying data from ClickHouse: %v", err)
	}
	defer rows.Close()
	for rows.Next() {
		var count int
		err = rows.Scan(&count)
		if err != nil {
			log.Fatalf("Error scanning data from ClickHouse: %v", err)
		}
		log.Printf("Count: %d", count)
	}
	log.Printf("Query took: %v", time.Since(t))

	// Delete all rows
	log.Default().Println("Deleting data")
	_, err = db.Exec("TRUNCATE TABLE your_table")
	if err != nil {
		log.Fatalf("Error deleting data from ClickHouse: %v", err)
	}

	// Drop table
	_, err = db.Exec("DROP TABLE IF EXISTS your_table")
	if err != nil {
		log.Fatalf("Error dropping table in ClickHouse: %v", err)
	}
}