Unlogged tables in PostgreSQL are a specialized feature designed for maximum write performance by completely bypassing the Write-Ahead Log (WAL). It trade durability for speed: if the database crashes or undergoes an unclean shutdown, the table is automatically truncated (emptied) upon recovery.

Feature Regular (Logged) Unlogged Temporary (TEMP)
Storage Location Disk Disk Disk / Local Buffers
Lifetime Persistent schema object Persistent schema object Session / transaction
WAL Logging Yes No No
Survival: Conn Close Yes Yes No (Dropped)
Survival: Clean Reboot Yes Yes No (Dropped)
Survival: System Crash Yes No (Truncated) No (Dropped)

Scenario: Real-time Analytics Tracker

The server receives millions of clickstream events per minute via an HTTP API. Instead of writing each individual click to a heavy permanent database (which would cause a bottleneck), the server streams the events into an unlogged table using pgx.CopyFrom. A background worker then aggregates the clicks hourly into a permanent dashboard table.

Database Schema

-- Permanent table for the dashboard
CREATE TABLE hourly_page_clicks (
    page_path TEXT NOT NULL,
    hour_timestamp TIMESTAMP NOT NULL,
    click_count INT NOT NULL,
    PRIMARY KEY (page_path, hour_timestamp)
);

-- Unlogged high-speed landing pad for raw clicks
CREATE UNLOGGED TABLE staging_clicks (
    page_path TEXT NOT NULL,
    tracked_at TIMESTAMP NOT NULL
);

Go Server Code

This server uses the high-performance jackc/pgx/v5 driver. It leverages a Go channel as a memory buffer and uses PostgreSQL bulk-copy (COPY) to flush batches into the unlogged table instantly.

package main

import (
	"context"
	"fmt"
	"log"
	"net/http"
	"time"

	"://github.com"
	"://github.com/pgxpool"
)

// ClickEvent represents the raw incoming payload
type ClickEvent struct {
	PagePath  string
	TrackedAt time.Time
}

// Global channel acting as an in-memory queue
var clickQueue = make(chan ClickEvent, 50000)

func main() {
	ctx := context.Background()
	connStr := "postgres://postgres:password@localhost:5432/analytics_db"

	// 1. Initialize Connection Pool
	pool, err := pgxpool.New(ctx, connStr)
	if err != nil {
		log.Fatalf("Unable to connect to database: %v\n", err)
	}
	defer pool.Close()

	// 2. Start the Background Batch Flusher (Writes to UNLOGGED table)
	go startBatchFlusher(ctx, pool)

	// 3. Start the Hourly Aggregator Worker
	go startHourlyAggregator(ctx, pool)

	// 4. HTTP API Endpoint to receive telemetry
	http.HandleFunc("/track", handleTrackClick)

	fmt.Println("Telemetry Go server running on :8080...")
	log.Fatal(http.ListenAndServe(":8080", nil))
}

// handleTrackClick handles incoming HTTP POST requests and puts them in the queue
func handleTrackClick(w http.ResponseWriter, r *http.Request) {
	if r.Method != http.MethodPost {
		http.Error(w, "Method not allowed", http.StatusMethodNotAllowed)
		return
	}

	path := r.URL.Query().Get("path")
	if path == "" {
		http.Error(w, "Missing path parameter", http.StatusBadRequest)
		return
	}

	// Push non-blocking event to the queue
	select {
	case clickQueue <- ClickEvent{PagePath: path, TrackedAt: time.Now()}:
		w.WriteHeader(http.StatusAccepted)
	default:
		// Queue full, reject early to save server memory
		http.Error(w, "Server busy", http.StatusServiceUnavailable)
	}
}

// startBatchFlusher collects chunks from the channel and pushes to the UNLOGGED table
func startBatchFlusher(ctx context.Context, pool *pgxpool.Pool) {
	ticker := time.NewTicker(2 * time.Second) // Flush every 2 seconds
	defer ticker.Stop()

	var batch []ClickEvent

	for {
		select {
		case event := <-clickQueue:
			batch = append(batch, event)
			if len(batch) >= 5000 { // Or flush when batch hits 5,000 rows
				flushToUnloggedTable(ctx, pool, batch)
				batch = nil
			}

		case <-ticker.C:
			if len(batch) > 0 {
				flushToUnloggedTable(ctx, pool, batch)
				batch = nil
			}
		}
	}
}

// flushToUnloggedTable utilizes PostgreSQL's COPY protocol for maximum throughput
func flushToUnloggedTable(ctx context.Context, pool *pgxpool.Pool, events []ClickEvent) {
	// Transform slice into pgx CopyFrom Source format
	rows := [][]interface{}{}
	for _, e := range events {
		rows = append(rows, []interface{}{e.PagePath, e.TrackedAt})
	}

	// COPY INTO staging_clicks (Extremely fast because it's UNLOGGED)
	_, err := pool.CopyFrom(
		ctx,
		pgx.Identifier{"staging_clicks"},
		[]string{"page_path", "tracked_at"},
		pgx.CopyFromRows(rows),
	)
	if err != nil {
		log.Printf("Error bulk inserting to unlogged table: %v\n", err)
	}
}

// startHourlyAggregator periodically flattens data into the permanent table
func startHourlyAggregator(ctx context.Context, pool *pgxpool.Pool) {
	ticker := time.NewTicker(1 * time.Hour)
	defer ticker.Stop()

	for range ticker.C {
		log.Println("Starting hourly aggregation...")

		// Run inside a transaction to ensure atomic clean up
		tx, err := pool.Begin(ctx)
		if err != nil {
			log.Printf("Failed to start agg tx: %v\n", err)
			continue
		}

		// 1. Move and aggregate data into the permanent, logged table
		_, err = tx.Exec(ctx, `
			INSERT INTO hourly_page_clicks (page_path, hour_timestamp, click_count)
			SELECT 
				page_path, 
				date_trunc('hour', tracked_at) as hr, 
				COUNT(*) 
			FROM staging_clicks
			GROUP BY page_path, hr
			ON CONFLICT (page_path, hour_timestamp) 
			DO UPDATE SET click_count = hourly_page_clicks.click_count + EXCLUDED.click_count;
		`)
		if err != nil {
			tx.Rollback(ctx)
			log.Printf("Aggregation failed: %v\n", err)
			continue
		}

		// 2. Wipe the unlogged table clean for the next hour
		_, err = tx.Exec(ctx, "TRUNCATE TABLE staging_clicks;")
		if err != nil {
			tx.Rollback(ctx)
			log.Printf("Truncate unlogged table failed: %v\n", err)
			continue
		}

		tx.Commit(ctx)
		log.Println("Aggregation successfully completed and unlogged table cleared.")
	}
}

Why this architecture?

  1. Zero WAL Bottleneck: The background flusher hits staging_clicks every 2 seconds. Because the table is UNLOGGED, Postgres executes the CopyFrom operation purely in shared memory and direct table writes. Your server avoids disk I/O bottlenecks.
  2. Channel-backed Safety: If the Go app suddenly crashes, uncommitted clicks in the channel are lost, but anything already flushed to the database survives until the next crash/reboot.

VS Memory → Redis → Postgres (Logged) ⭐️

Crash Recovery & Data Loss

Memory vs. Disk Mechanics