SQL vs NoSQL vs Columnar: Choosing the Right Database for Your Go Service

The Dataset That Started Everything

100 million user events. 2TB of data. Real-time analytics requirements. Our client wanted sub-second queries on any time range.

We tried everything. Here's what actually worked.

Round 1: PostgreSQL (The SQL Veteran)

Started with PostgreSQL 15. It's reliable, battle-tested, ACID compliant. Perfect for transactions, right?

The Go Implementation

type PostgresRepo struct {
    db *sql.DB
}
func (r *PostgresRepo) InsertEvent(event Event) error {
    query := `
        INSERT INTO events (user_id, event_type, payload, created_at)
        VALUES ($1, $2, $3, $4)
        ON CONFLICT (id) DO UPDATE SET updated_at = NOW()
    `
    _, err := r.db.Exec(query, event.UserID, event.Type, event.Payload, event.CreatedAt)
    return err
}
// The analytics query that killed us
func (r *PostgresRepo) GetEventStats(start, end time.Time) ([]Stats, error) {
    query := `
        SELECT
            date_trunc('hour', created_at) as hour,
            event_type,
            COUNT(*) as count,
            COUNT(DISTINCT user_id) as unique_users
        FROM events
        WHERE created_at BETWEEN $1 AND $2
        GROUP BY hour, event_type
        ORDER BY hour DESC
    `
    // This query took 45 seconds on 100M rows
    rows, err := r.db.Query(query, start, end)
    // ...
}

PostgreSQL Performance Reality

• Insert speed: 15,000 rows/sec (with indexes)
• Query time (1 day range): 2.3 seconds
• Query time (1 month range): 45 seconds
• Storage: 2.1TB with indexes
• Monthly cost: $3,200 (RDS db.r6g.4xlarge)

We tried everything: partitioning by month, BRIN indexes, materialized views. Still too slow for real-time analytics.

Round 2: MongoDB (The NoSQL Promise)

"NoSQL will scale!" they said. "Flexible schema!" they promised.

The MongoDB Approach

type MongoRepo struct {
    collection *mongo.Collection
}
func (r *MongoRepo) InsertEvents(events []Event) error {
    // Batch insert for performance
    docs := make([]interface{}, len(events))
    for i, e := range events {
        docs[i] = bson.M{
            "user_id":    e.UserID,
            "event_type": e.Type,
            "payload":    e.Payload,
            "created_at": e.CreatedAt,
        }
    }
    _, err := r.collection.InsertMany(context.TODO(), docs)
    return err
}
func (r *MongoRepo) GetEventStats(start, end time.Time) ([]Stats, error) {
    pipeline := mongo.Pipeline{
        {{"$match", bson.D{
            {"created_at", bson.D{
                {"$gte", start},
                {"$lte", end},
            }},
        }}},
        {{"$group", bson.D{
            {"_id", bson.D{
                {"hour", bson.D{{"$dateTrunc", bson.D{
                    {"date", "$created_at"},
                    {"unit", "hour"},
                }}}},
                {"type", "$event_type"},
            }},
            {"count", bson.D{{"$sum", 1}}},
            {"unique_users", bson.D{{"$addToSet", "$user_id"}}},
        }}},
    }
    // Better than PostgreSQL, but still not great
    cursor, err := r.collection.Aggregate(context.TODO(), pipeline)
    // ...
}

MongoDB Performance Metrics

• Insert speed: 45,000 docs/sec (batch insert)
• Query time (1 day range): 1.8 seconds
• Query time (1 month range): 28 seconds
• Storage: 1.8TB (with compression)
• Monthly cost: $2,800 (Atlas M60)

Better for writes, but aggregations on 100M documents still painful. And don't get me started on the $addToSet memory usage.

Round 3: Cassandra (The Scale Champion)

Cassandra promised linear scalability. Facebook uses it! Netflix uses it! Surely it would work for us.

Cassandra Implementation

type CassandraRepo struct {
    session *gocql.Session
}
func (r *CassandraRepo) CreateSchema() {
    // The partition key is EVERYTHING in Cassandra
    query := `
        CREATE TABLE IF NOT EXISTS events (
            date_bucket text,      -- YYYYMMDD for partitioning
            created_at timestamp,
            event_id uuid,
            user_id bigint,
            event_type text,
            payload text,
            PRIMARY KEY ((date_bucket), created_at, event_id)
        ) WITH CLUSTERING ORDER BY (created_at DESC)
    `
    r.session.Query(query).Exec()
}
func (r *CassandraRepo) InsertEvent(event Event) error {
    bucket := event.CreatedAt.Format("20060102")
    query := `
        INSERT INTO events (date_bucket, created_at, event_id, user_id, event_type, payload)
        VALUES (?, ?, ?, ?, ?, ?)
    `
    return r.session.Query(query,
        bucket,
        event.CreatedAt,
        gocql.TimeUUID(),
        event.UserID,
        event.Type,
        event.Payload,
    ).Exec()
}
// The query that made us cry
func (r *CassandraRepo) GetEventStats(start, end time.Time) ([]Stats, error) {
    // You have to query EACH partition separately in Cassandra
    var allStats []Stats
    for d := start; d.Before(end); d = d.AddDate(0, 0, 1) {
        bucket := d.Format("20060102")
        query := `
            SELECT created_at, event_type, user_id
            FROM events
            WHERE date_bucket = ?
            AND created_at >= ?
            AND created_at <= ?
        `
        iter := r.session.Query(query, bucket, start, end).Iter()
        // Now aggregate in Go... this is painful
        // ...
    }
    // Manual aggregation of 100M rows in Go = OOM
    return allStats, nil
}

Cassandra Reality Check

• Insert speed: 120,000 rows/sec (insane!)
• Query time (1 day range): 0.8 seconds
• Query time (1 month range): Crashed (OOM in Go)
• Storage: 2.5TB (no compression by default)
• Monthly cost: $4,100 (3x i3.2xlarge)

Cassandra is amazing for writes and simple queries. But aggregations? Forget it. You're doing that in your application layer.

Round 4: ClickHouse (The Analytics Beast)

Then we discovered ClickHouse. A columnar database designed for analytics. Originally built at Yandex for Metrica — processing billions of events per day.

ClickHouse Magic

type ClickHouseRepo struct {
    conn driver.Conn
}
func (r *ClickHouseRepo) CreateTable() {
    query := `
        CREATE TABLE IF NOT EXISTS events (
            created_at DateTime,
            user_id UInt64,
            event_type LowCardinality(String),  -- Automatic dictionary encoding
            payload String
        ) ENGINE = MergeTree()
        PARTITION BY toYYYYMM(created_at)
        ORDER BY (event_type, created_at, user_id)
        SETTINGS index_granularity = 8192
    `
    r.conn.Exec(context.Background(), query)
}
func (r *ClickHouseRepo) BatchInsert(events []Event) error {
    batch, err := r.conn.PrepareBatch(context.Background(),
        "INSERT INTO events")
    if err != nil {
        return err
    }
    for _, event := range events {
        err := batch.Append(
            event.CreatedAt,
            event.UserID,
            event.Type,
            event.Payload,
        )
        if err != nil {
            return err
        }
    }
    return batch.Send()
}
// The query that made us believers
func (r *ClickHouseRepo) GetEventStats(start, end time.Time) ([]Stats, error) {
    query := `
        SELECT
            toStartOfHour(created_at) as hour,
            event_type,
            count() as cnt,
            uniqExact(user_id) as unique_users
        FROM events
        WHERE created_at BETWEEN ? AND ?
        GROUP BY hour, event_type
        ORDER BY hour DESC
    `
    rows, err := r.conn.Query(context.Background(), query, start, end)
    // This returns in 0.3 seconds for ANY date range
    // ...
}

ClickHouse Performance Shock

• Insert speed: 200,000 rows/sec (batch)
• Query time (1 day range): 0.08 seconds
• Query time (1 month range): 0.3 seconds
• Query time (1 year range): 1.2 seconds
• Storage: 180GB (12x compression!)
• Monthly cost: $800 (single node!)

180GB. The same data that took 2.1TB in PostgreSQL compressed to 180GB. We couldn't believe it either.

The Real-World Comparison Table

When to Use Each Database

PostgreSQL - The Safe Choice

• ✅ Need ACID transactions
• ✅ Complex relationships (foreign keys)
• ✅ Team knows SQL
• ✅ Data fits on single node (< 10TB)
• ❌ Analytics on billions of rows
• ❌ Time-series data (consider TimescaleDB — a PostgreSQL extension that adds hypertables, automatic partitioning, and columnar compression specifically for time-series workloads)

MongoDB - The Flexible Friend

• ✅ Rapidly changing schema
• ✅ Document-oriented data
• ✅ Geographic queries (2dsphere indexes)
• ✅ Medium-scale analytics
• ❌ Multi-document transactions (slow)
• ❌ Aggregations on huge datasets

Cassandra - The Write Monster

• ✅ Insane write throughput needed
• ✅ Multi-datacenter replication
• ✅ Simple key-value access patterns
• ✅ Can't afford any downtime
• ❌ Ad-hoc queries
• ❌ Aggregations/Analytics

ClickHouse - The Analytics King

• ✅ Analytics on billions of rows
• ✅ Time-series data
• ✅ Append-only workloads
• ✅ Cost-sensitive (10x cheaper)
• ❌ Frequent updates/deletes
• ❌ ACID transactions
• ❌ Deduplication requires workarounds (ReplacingMergeTree, FINAL queries)

The Hybrid Architecture We Actually Built

Plot twist: We use THREE databases now:

// PostgreSQL for user data and transactions
type UserService struct {
    postgres *sql.DB
}
// MongoDB for flexible product catalog
type CatalogService struct {
    mongo *mongo.Collection
}
// ClickHouse for all analytics
type AnalyticsService struct {
    clickhouse driver.Conn
}
// Data flows through Kafka
type EventProcessor struct {
    kafka      *kafka.Writer
    postgres   *sql.DB
    clickhouse driver.Conn
}
func (p *EventProcessor) ProcessEvent(event Event) error {
    // Write to PostgreSQL for real-time features
    if err := p.writeToPostgres(event); err != nil {
        return err
    }
    // Batch write to ClickHouse for analytics
    p.eventBuffer = append(p.eventBuffer, event)
    if len(p.eventBuffer) >= 10000 {
        if err := p.flushToClickHouse(); err != nil {
            return err
        }
    }
    return nil
}

The Hard-Learned Lessons

1. One database rarely fits all - We fought this truth for months
2. Columnar databases are magic for analytics - 10x compression, 100x query speed
3. Cassandra needs perfect data modeling - Get your partition key wrong and you're screwed
4. MongoDB aggregations have limits - $addToSet will OOM on large datasets
5. PostgreSQL can handle more than you think - Until it can't
6. ClickHouse changes everything for analytics - But learn its quirks

How to Actually Choose

Don't default to PostgreSQL and hope for the best. Start from your data:

• Structured data with relationships and transactions? → PostgreSQL. Orders, users, payments — anything where consistency matters more than speed.
• Documents up to 16MB, nested JSON, varying schema per record? → MongoDB. Product catalogs with 200+ attributes, user profiles with dynamic fields, content that you need to query inside JSON without pain.
• Append-only events, logs, metrics, time-series? → ClickHouse. If you're writing once and aggregating later, columnar storage wins by orders of magnitude.
• Massive write throughput with simple reads by key? → Cassandra. IoT sensors, message queues, activity feeds — when you need 100K+ writes/sec across data centers.

If you're already in pain:

1. Identify what kind of data is causing problems — not just "reads are slow"
2. Consider whether it's a data model issue, not a database issue
3. Add a specialized database for the specific workload
4. Use CDC (Debezium) or Kafka to keep systems in sync

The Bottom Line

The main takeaway: the right database depends on the shape of your data and how you access it, not on what's popular.

ClickHouse turned our 45-second analytics queries into 300ms. MongoDB eliminated our painful JSON parsing in PostgreSQL. And PostgreSQL remained rock solid for everything transactional.

Don't pick a database and force your data into it. Look at your data first, then pick the tool that fits.

P.S. We still use PostgreSQL for 80% of our data. It's boring, reliable, and it works. ClickHouse handles the analytics that would make PostgreSQL cry. MongoDB stores our product catalog with its 200+ attributes per item. And Cassandra? We migrated off it. Turns out we didn't need that kind of write scale after all.

The metrics in this article come from production infrastructure. If you want to reproduce the comparison yourself, there's a simplified benchmark suite on GitHub — it runs on a single machine with generated data, so the numbers will differ, but it's a good starting point to get a feel for the differences.