Bunjs SQLite has poor write performance

sqlite_write_performance.md

The bun:sqlite has a write performance of only 957/s on SSD, while the node better-sqlite3 has 51080/s.

Test results:

bun:sqlite, ssd: 957/s
bun:sqlite, memory: 66906/s
node better-sqlite3, ssd: 51080/s
node better-sqlite3, memory: 56863/s
go-sqlite3, ssd: 49453/s
go-sqlite3, memory: 54632/s

Environment:

• CPU: AMD Ryzen 9 5900HX with Radeon Graphics
• Disk: Kingston Technology Company KINGSTON SNV2S1000G
• Linux 6.7.4-2-MANJARO x86_64
• bun 1.0.29
• node v21.6.1
• better-sqlite3 9.4.3
• go 1.22.0
• go-sqlite3 v1.14.22

bun:sqlite codes:

import { Database } from 'bun:sqlite'

const createSchema = 'CREATE TABLE IF NOT EXISTS logs (id integer primary key, username text not null, created_at text default current_timestamp)'

const times = 0xffff

function testSsd () {
  const db = new Database('data.db', { create: true, readwrite: true })
  db.exec('PRAGMA journal_mode = WAL')
  db.exec(createSchema)

  const t = performance.now()

  for (let i = 0; i < times; i++) {
    const q = db.prepare('INSERT INTO logs (username) VALUES (?) RETURNING id')
    const { id } = q.get('Shaun')
  }

  const qps = Math.floor(times / ((performance.now() - t) / 1000.))

  console.log(`ssd: ${qps}/s`)
}

function testMemory () {
  const db = new Database('/dev/shm/data.db', { create: true, readwrite: true })
  db.exec('PRAGMA journal_mode = WAL')
  db.exec(createSchema)

  const t = performance.now()

  for (let i = 0; i < times; i++) {
    const q = db.prepare('INSERT INTO logs (username) VALUES (?) RETURNING id')
    const { id } = q.get('Shaun')
  }

  const qps = Math.floor(times / ((performance.now() - t) / 1000.))

  console.log(`memory: ${qps}/s`)
}

testSsd()
testMemory()

node better-sqlite3 codes:

const Database = require('better-sqlite3')

const createSchema = 'CREATE TABLE IF NOT EXISTS logs (id integer primary key, username text not null, created_at text default current_timestamp)'

const times = 0xffff

function testSsd () {
  const db = new Database('data.db', { create: true, readwrite: true })
  db.exec('PRAGMA journal_mode = WAL')
  db.exec(createSchema)

  const t = performance.now()

  for (let i = 0; i < times; i++) {
    const q = db.prepare('INSERT INTO logs (username) VALUES (?) RETURNING id')
    const { id } = q.get('Shaun')
  }

  const qps = Math.floor(times / ((performance.now() - t) / 1000.))

  console.log(`ssd: ${qps}/s`)
}

function testMemory () {
  const db = new Database('/dev/shm/data.db', { create: true, readwrite: true })
  db.exec('PRAGMA journal_mode = WAL')
  db.exec(createSchema)

  const t = performance.now()

  for (let i = 0; i < times; i++) {
    const q = db.prepare('INSERT INTO logs (username) VALUES (?) RETURNING id')
    const { id } = q.get('Shaun')
  }

  const qps = Math.floor(times / ((performance.now() - t) / 1000.))

  console.log(`memory: ${qps}/s`)
}

testSsd()
testMemory()

go-sqlite3 codes:

package main

import (
	"fmt"
	"github.com/jmoiron/sqlx"
	_ "github.com/mattn/go-sqlite3"
	"log"
	"time"
)

const createSchema = "CREATE TABLE IF NOT EXISTS logs (id integer primary key, username text not null, created_at text default current_timestamp)"

const times = 0xffff

type LastInsertedId struct {
	Id int64 `db:"id"`
}

func testSsd() {
	db, err := sqlx.Connect("sqlite3", "file:data.db")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	db.MustExec("PRAGMA journal_mode = WAL")
	db.MustExec(createSchema)

	t := time.Now()

	var id LastInsertedId
	for i := 0; i < times; i++ {
		err := db.QueryRowx("INSERT INTO logs (username) VALUES (?) RETURNING id", "Shaun").StructScan(&id)
		if err != nil {
			log.Fatal(err)
		}
	}

	qps := int(times / (float64(time.Now().Sub(t).Nanoseconds()) / 1000000000.))

	fmt.Printf("ssd: %d/s\n", qps)
}

func testMemory() {
	db, err := sqlx.Connect("sqlite3", "file:/dev/shm/data.db")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	db.MustExec("PRAGMA journal_mode = WAL")
	db.MustExec(createSchema)

	t := time.Now()

	var id LastInsertedId
	for i := 0; i < times; i++ {
		err := db.QueryRowx("INSERT INTO logs (username) VALUES (?) RETURNING id", "Shaun").StructScan(&id)
		if err != nil {
			log.Fatal(err)
		}
	}

	qps := int(times / (float64(time.Now().Sub(t).Nanoseconds()) / 1000000000.))

	fmt.Printf("memory: %d/s\n", qps)
}

func main() {
	testSsd()
	testMemory()
}