Choosing a Database for a New Go Project Without Regret

"Which database should we use?" is asked at the start of every project and answered, too often, by whatever the last project used or by a benchmark chart. Neither is a good reason. The databases that Go developers pick from are all fast enough for almost every starting workload, and they differ in what they let you express, what they guarantee and what it costs to run. This article measures one workload on five of them to show how little speed separates them, then gives a way to choose that you can defend a year later.

It belongs to the database cornerstone. The follow-up question, whether to write SQL or use an ORM, is the next article, and each store has a guide: PostgreSQL, MySQL, SQLite, Redis and MongoDB.

The same work on five stores

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"os"
	"path/filepath"
	"time"

	_ "github.com/go-sql-driver/mysql"
	_ "github.com/jackc/pgx/v5/stdlib"
	"github.com/redis/go-redis/v9"
	"go.mongodb.org/mongo-driver/v2/bson"
	"go.mongodb.org/mongo-driver/v2/mongo"
	"go.mongodb.org/mongo-driver/v2/mongo/options"
	_ "modernc.org/sqlite"
)

const n = 5000

func must(err error) {
	if err != nil {
		log.Fatal(err)
	}
}

type result struct {
	name   string
	insert time.Duration
	read   time.Duration
}

func sqlStore(name, driver, dsn string, create []string, ph func(int) string) result {
	db, err := sql.Open(driver, dsn)
	must(err)
	defer db.Close()
	db.SetMaxOpenConns(1)
	for _, q := range create {
		_, err := db.Exec(q)
		must(err)
	}
	ins := fmt.Sprintf("insert into kv648 (id, v) values (%s, %s)", ph(1), ph(2))
	start := time.Now()
	for i := 0; i < n; i++ {
		_, err := db.Exec(ins, i, fmt.Sprintf("value-%d", i))
		must(err)
	}
	insert := time.Since(start)
	sel := fmt.Sprintf("select v from kv648 where id = %s", ph(1))
	start = time.Now()
	for i := 0; i < n; i++ {
		var v string
		must(db.QueryRow(sel, (i*7919)%n).Scan(&v))
	}
	return result{name, insert, time.Since(start)}
}

func main() {
	ctx := context.Background()
	dir, err := os.MkdirTemp("", "choose648")
	must(err)
	defer os.RemoveAll(dir)
	dollar := func(i int) string { return fmt.Sprintf("$%d", i) }
	qmark := func(int) string { return "?" }

	var results []result
	results = append(results, sqlStore("PostgreSQL 18", "pgx", "postgres://postgres:[email protected]:55432/lab?sslmode=disable",
		[]string{"drop table if exists kv648", "create table kv648 (id int primary key, v text not null)"}, dollar))
	results = append(results, sqlStore("MySQL 8.4", "mysql", "root:secret@tcp(127.0.0.1:33099)/lab",
		[]string{"drop table if exists kv648", "create table kv648 (id int primary key, v varchar(100) not null)"}, qmark))
	results = append(results, sqlStore("SQLite (file, WAL)", "sqlite", "file:"+filepath.Join(dir, "kv.db")+"?_pragma=journal_mode(WAL)&_pragma=synchronous(NORMAL)",
		[]string{"create table kv648 (id integer primary key, v text not null)"}, qmark))
	results = append(results, sqlStore("SQLite (file, default sync)", "sqlite", "file:"+filepath.Join(dir, "kv2.db"),
		[]string{"create table kv648 (id integer primary key, v text not null)"}, qmark))

	rdb := redis.NewClient(&redis.Options{Addr: "127.0.0.1:56379"})
	defer rdb.Close()
	must(rdb.FlushDB(ctx).Err())
	start := time.Now()
	for i := 0; i < n; i++ {
		must(rdb.Set(ctx, fmt.Sprint("kv:", i), fmt.Sprintf("value-%d", i), 0).Err())
	}
	ins := time.Since(start)
	start = time.Now()
	for i := 0; i < n; i++ {
		_, err := rdb.Get(ctx, fmt.Sprint("kv:", (i*7919)%n)).Result()
		must(err)
	}
	results = append(results, result{"Redis 7 (in memory)", ins, time.Since(start)})

	client, err := mongo.Connect(options.Client().ApplyURI("mongodb://127.0.0.1:57017"))
	must(err)
	defer client.Disconnect(ctx)
	coll := client.Database("lab").Collection("kv648")
	_ = coll.Drop(ctx)
	start = time.Now()
	for i := 0; i < n; i++ {
		_, err := coll.InsertOne(ctx, bson.D{{Key: "_id", Value: i}, {Key: "v", Value: fmt.Sprintf("value-%d", i)}})
		must(err)
	}
	ins = time.Since(start)
	start = time.Now()
	for i := 0; i < n; i++ {
		var doc bson.M
		must(coll.FindOne(ctx, bson.D{{Key: "_id", Value: (i * 7919) % n}}).Decode(&doc))
	}
	results = append(results, result{"MongoDB 8", ins, time.Since(start)})

	fmt.Printf("%d single row inserts (one statement each), then %d point reads by key, one connection, on one machine\n", n, n)
	fmt.Printf("%-28s %12s %14s %12s %14s\n", "store", "inserts", "per insert", "reads", "per read")
	for _, r := range results {
		fmt.Printf("%-28s %12v %14v %12v %14v\n", r.name, r.insert.Round(time.Millisecond), (r.insert / n).Round(time.Microsecond), r.read.Round(time.Millisecond), (r.read / n).Round(time.Microsecond))
	}
}

5000 single row inserts (one statement each), then 5000 point reads by key, one connection, on one machine
store                             inserts     per insert        reads       per read
PostgreSQL 18                      2.169s          434µs       1.153s          231µs
MySQL 8.4                          7.868s        1.574ms       2.164s          433µs
SQLite (file, WAL)                   73ms           15µs         26ms            5µs
SQLite (file, default sync)        1.753s          351µs         49ms           10µs
Redis 7 (in memory)                1.696s          339µs       1.763s          353µs
MongoDB 8                          2.003s          401µs       2.434s          487µs

The program does the simplest thing a service does, in the simplest way: 5,000 inserts of a small row, one statement and one commit each, then 5,000 point reads by key, from one connection. It runs against containers on the same laptop, so the network is a local bridge, and the code is deliberately plain, with no batching or tuning. The result:

Four things stand out, and none of them is a ranking.

The server databases are within a small factor of each other. PostgreSQL, Redis and MongoDB sit between 230 and 490 microseconds per operation, and MySQL is slower on inserts. Almost all of that is a round trip over a socket plus, for writes, a flush to disk. The databases are doing very little work; the lab is measuring the plumbing. MySQL's insert cost is its default of flushing the log on every commit in this container setup, a setting that can be relaxed and then trades durability.

SQLite is faster because there is no network. At 15 microseconds per insert and 5 per read it is an order of magnitude ahead, since the database is a library call in the same process. That is a real advantage for the right workload and says nothing about scale: it has one writer and one machine. Its default settings, on the other hand, took 351 microseconds per insert because every commit syncs the file, which is what WAL mode and synchronous(NORMAL) change.

Redis is not faster for a single call than PostgreSQL here, which surprises people. A trip over a socket costs about the same for both. Redis wins on very high concurrency and on structures like counters and sorted sets, not on the latency of one command.

Single row speed is the wrong criterion. The difference between 230 and 490 microseconds does not show in a web request that also renders a page and calls three services. What shows is whether the database can express your queries, keep your data correct, and be operated by the people you have.

What should decide it

Five questions, in order, settle most projects.

1. What shape is the data? Rows with relationships (customers, orders, products) belong in a relational database. Nested documents read whole by key can live in MongoDB. Counters, sessions, caches and rate limits are Redis's job. Large append only event streams that you aggregate belong in a columnar store. Pick for the shape you have, not the shape you imagine.

2. What must never be wrong? Money, inventory, permissions and anything with invariants want transactions, constraints and foreign keys, which PostgreSQL and MySQL provide and enforce. A store that lets the application enforce invariants will, eventually, have a bug that breaks one. See transaction isolation levels for how much "transaction" is worth.

3. What queries will you want next year? A relational database answers new questions with a new query. A key value store or a key designed store such as DynamoDB answers them with a new table or a migration, and sometimes not at all. If you cannot list the questions, choose the flexible one.

4. Who will run it? A managed PostgreSQL costs money and saves nights. A self hosted cluster of anything costs people. SQLite costs nothing to run and has limits you must respect. Choose what your team can operate at three in the morning, including backups and restores.

5. How big will it be, honestly? Most projects never outgrow a single PostgreSQL server, which handles terabytes and thousands of queries a second. If you expect more, you will know the specific bottleneck and can address it then, with replicas (read replicas), caching (Redis caching patterns) or partitioning (sharding basics). Choosing a distributed database for scale you do not have buys complexity now for a problem you may never meet.

The default, and its limits

Choose PostgreSQL unless you have a reason not to. It does the relational work well, and it also has JSON columns, full text search (search in Postgres from Go), queues with SKIP LOCKED (a Postgres job queue), advisory locks, notifications, row level security, extensions for geospatial data and vectors, and a thoughtful community. One system covers needs that otherwise become three or four, which is the strongest argument for it: every extra database is an extra thing to run, back up, secure and keep consistent.

Reasons to choose something else are real:

• SQLite when the program is one process on one machine: a CLI, a desktop or mobile app, a small service with modest writes, or a per tenant store. Nothing to run, trivial backups, very fast. Limits: one writer, no network access.
• MySQL or MariaDB when your organisation already runs and understands it, or a platform you depend on requires it. It is a capable database; do not switch from it without a reason.
• Redis alongside another database, for caching, rate limits, locks and queues where losing data on a crash is acceptable. Not as the system of record by default.
• MongoDB when the data is truly documents with varying shapes, read whole, with few relationships, and the team knows how to model for it.
• DynamoDB, Cassandra, ClickHouse, Elasticsearch when you have the specific workload they exist for: massive key access, huge write rates, analytics scans and search. They are specialists, and a specialist as your only database is a risk.

Avoiding regret

Regret comes from the same few mistakes. Choosing for scale you do not have. Choosing a database that cannot express the query you add in month six. Using a cache as a database. Treating "schemaless" as "no design needed", when the schema moves into code. And mixing several databases without a clear owner of each fact, so the same data lives in three places that disagree. Write down which system is the source of truth for each kind of data and treat the others as copies you can rebuild.

Keep the door open cheaply. Access the database through a thin layer of your own functions, as in small store interfaces, so a migration is a project and not a rewrite. Use plain SQL features and keep the dialect specifics in one place. Keep migrations in version control. And measure your own workload before you believe anyone's chart, including this one: the lab above is an illustration of how close the numbers are, not a benchmark of your system. The PostgreSQL project's own page on what it is good for is a fair statement of the case for the default, and the Go team's guide to accessing databases is the neutral starting point for the code.