postgresql

README

PostgreSQL Cache Backend

This package provides a PostgreSQL implementation of the httpcache.Cache interface using the pgx/v5 driver.

Features

• ✅ ACID Compliance - Uses PostgreSQL transactions for reliability
• ✅ Thread-Safe - Safe for concurrent access from multiple goroutines
• ✅ Configurable - Customizable table name, key prefix, and timeout
• ✅ Connection Pooling - Efficient resource usage with pgxpool
• ✅ Atomic Updates - UPSERT operations using ON CONFLICT
• ✅ Auto-initialization - Automatic table creation
• ✅ Error Logging - Centralized error logging via httpcache logger

Installation

go get github.com/sandrolain/httpcache/postgresql

Quick Start

import (
    "context"
    "github.com/sandrolain/httpcache"
    "github.com/sandrolain/httpcache/postgresql"
)

ctx := context.Background()

// Create cache with default configuration
cache, err := postgresql.New(ctx, "postgres://user:pass@localhost/mydb", nil)
if err != nil {
    log.Fatal(err)
}
defer cache.Close()

// Use with HTTP transport
transport := httpcache.NewTransport(cache)
client := transport.Client()

Configuration

Default Configuration

config := postgresql.DefaultConfig()
// TableName: "httpcache"
// KeyPrefix: "cache:"
// Timeout:   5 * time.Second

Custom Configuration

config := &postgresql.Config{
    TableName: "my_http_cache",
    KeyPrefix: "api:",
    Timeout:   10 * time.Second,
}

cache, err := postgresql.New(ctx, connString, config)

Usage Examples

With Automatic Connection Pool

ctx := context.Background()
connString := "postgres://user:pass@localhost:5432/mydb?sslmode=disable"

cache, err := postgresql.New(ctx, connString, nil)
if err != nil {
    log.Fatal(err)
}
defer cache.Close()

With Existing Connection Pool

import "github.com/jackc/pgx/v5/pgxpool"

pool, err := pgxpool.New(ctx, connString)
if err != nil {
    log.Fatal(err)
}

config := postgresql.DefaultConfig()
cache, err := postgresql.NewWithPool(pool, config)
if err != nil {
    log.Fatal(err)
}

With Single Connection

import "github.com/jackc/pgx/v5"

conn, err := pgx.Connect(ctx, connString)
if err != nil {
    log.Fatal(err)
}

cache, err := postgresql.NewWithConn(conn, nil)
if err != nil {
    log.Fatal(err)
}

Database Schema

The cache automatically creates the following table:

CREATE TABLE IF NOT EXISTS httpcache (
    key TEXT PRIMARY KEY,
    data BYTEA NOT NULL,
    created_at TIMESTAMP NOT NULL
)

You can customize the table name via configuration.

Connection String Format

postgres://username:password@host:port/database?options

Common options:

• sslmode=disable - Disable SSL (for local development)
• sslmode=require - Require SSL (for production)
• pool_max_conns=10 - Maximum pool connections
• pool_min_conns=2 - Minimum pool connections

Examples:

// Local development
"postgres://postgres:postgres@localhost:5432/httpcache?sslmode=disable"

// Production with SSL
"postgres://user:pass@db.example.com:5432/mydb?sslmode=require"

// With connection pool settings
"postgres://user:pass@localhost/db?pool_max_conns=20&pool_min_conns=5"

Performance Considerations

Connection Pooling

For production use, the connection pool is automatically configured. You can tune it via the connection string:

connString := "postgres://user:pass@host/db?pool_max_conns=20&pool_min_conns=5"

Timeouts

Operations have a default timeout of 5 seconds. Adjust via configuration:

config := &postgresql.Config{
    Timeout: 30 * time.Second, // For slow networks
}

Indexes

For better performance with large caches, consider adding indexes:

-- If you query by created_at
CREATE INDEX idx_httpcache_created_at ON httpcache(created_at);

Error Handling

All errors are logged using the centralized httpcache logger:

import "log/slog"

// Set custom logger
logger := slog.New(slog.NewJSONHandler(os.Stderr, nil))
httpcache.SetLogger(logger)

Thread Safety

The implementation is fully thread-safe and can be used concurrently from multiple goroutines.

Testing

Tests require a running PostgreSQL server:

# Set connection string (optional)
export POSTGRESQL_TEST_URL="postgres://postgres:postgres@localhost:5432/httpcache_test?sslmode=disable"

# Run tests
go test -v

# Run benchmarks
go test -bench=.

Tests will be skipped if PostgreSQL is not available.

Integration Tests

Integration tests use Testcontainers to automatically spin up PostgreSQL and CockroachDB instances:

# Run integration tests (requires Docker)
go test -v -tags=integration

# Run specific integration test
go test -v -tags=integration -run TestPostgreSQLCacheIntegration

See INTEGRATION_TESTS.md for more details.

Cleanup

Manual Table Cleanup

// Delete all cache entries
_, err := pool.Exec(ctx, "DELETE FROM httpcache")

// Drop the table
_, err := pool.Exec(ctx, "DROP TABLE IF EXISTS httpcache")

TTL Support (Future)

Currently, the cache does not automatically expire old entries. Consider implementing a cleanup job:

-- Delete entries older than 7 days
DELETE FROM httpcache WHERE created_at < NOW() - INTERVAL '7 days';

Production Recommendations

1. Use SSL: Always use sslmode=require in production
2. Connection Pooling: Let the driver manage the pool automatically
3. Monitoring: Monitor cache hit rates and database performance
4. Cleanup: Implement periodic cleanup of old cache entries
5. Backups: Include cache table in backup strategy (or exclude if ephemeral)
6. Indexing: Add indexes based on your access patterns

Limitations

• No automatic TTL/expiration (implement via cleanup job)
• No built-in cache eviction policy
• Network latency affects performance
• Requires PostgreSQL 9.5+ (for ON CONFLICT support)

See Also

• Complete Example
• Main Documentation
• pgx Documentation

License

MIT License - See LICENSE.txt

Documentation

Overview

Package postgresql provides a PostgreSQL interface for HTTP caching.

Index

• Constants
• Variables
• type Cache
  • func New(ctx context.Context, connString string, config *Config) (*Cache, error)
  • func NewWithConn(conn *pgx.Conn, config *Config) (*Cache, error)
  • func NewWithPool(pool *pgxpool.Pool, config *Config) (*Cache, error)
  • func (c *Cache) Close()
  • func (c *Cache) CreateTable(ctx context.Context) error
  • func (c *Cache) Delete(key string)
  • func (c *Cache) Get(key string) (resp []byte, ok bool)
  • func (c *Cache) Set(key string, resp []byte)
• type Config
  • func DefaultConfig() *Config

Constants

const (
	// DefaultTableName is the default table name for cache storage
	DefaultTableName = "httpcache"
	// DefaultKeyPrefix is the default prefix for cache keys
	DefaultKeyPrefix = "cache:"
)

Variables

var (
	// ErrNilPool is returned when a nil pool is provided
	ErrNilPool = errors.New("postgresql: pool cannot be nil")
	// ErrNilConn is returned when a nil connection is provided
	ErrNilConn = errors.New("postgresql: connection cannot be nil")
)

Types

type Cache

type Cache struct {
	// contains filtered or unexported fields
}

Cache is an implementation of httpcache.Cache that stores responses in PostgreSQL.

func New

func New(ctx context.Context, connString string, config *Config) (*Cache, error)

New creates a new Cache with a connection pool from the given connection string.

func NewWithConn

func NewWithConn(conn *pgx.Conn, config *Config) (*Cache, error)

NewWithConn returns a new Cache using the provided connection.

func NewWithPool

func NewWithPool(pool *pgxpool.Pool, config *Config) (*Cache, error)

NewWithPool returns a new Cache using the provided connection pool.

func (*Cache) Close

func (c *Cache) Close()

Close closes the connection pool or connection.

func (*Cache) CreateTable

func (c *Cache) CreateTable(ctx context.Context) error

CreateTable creates the cache table if it doesn't exist.

func (*Cache) Delete

func (c *Cache) Delete(key string)

Delete removes the response with key from the cache.

func (*Cache) Get

func (c *Cache) Get(key string) (resp []byte, ok bool)

Get returns the response corresponding to key if present.

func (*Cache) Set

func (c *Cache) Set(key string, resp []byte)

Set saves a response to the cache as key.

type Config

type Config struct {
	// TableName is the name of the table to store cache entries (default: "httpcache")
	TableName string
	// KeyPrefix is the prefix to add to all cache keys (default: "cache:")
	KeyPrefix string
	// Timeout is the maximum time to wait for database operations (default: 5s)
	Timeout time.Duration
}

Config holds the configuration for the PostgreSQL cache.

func DefaultConfig

func DefaultConfig() *Config

DefaultConfig returns a Config with default values.