Skip to content

Instantly share code, notes, and snippets.

@breadchris
Last active April 15, 2024 04:09
Show Gist options
  • Save breadchris/16f16581cfeed9faa85b14252ce74802 to your computer and use it in GitHub Desktop.
Save breadchris/16f16581cfeed9faa85b14252ce74802 to your computer and use it in GitHub Desktop.
Postgres (OLTP) vs. Clickhouse (OLAP)
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)
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment