Database setup

Watchdog uses PostgreSQL with the TimescaleDB extension to store URL metadata and time-series monitoring metrics. This guide covers database setup, configuration, migrations, and connection pooling.

Overview

The database architecture consists of:

PostgreSQL - Primary relational database for URL metadata
TimescaleDB - Time-series extension for storing monitoring metrics
pgx - High-performance PostgreSQL driver and connection pool
Goose - Database migration tool for schema management

Prerequisites

Install PostgreSQL

Install PostgreSQL 12 or higher:

# Ubuntu/Debian
sudo apt-get install postgresql postgresql-contrib

# macOS
brew install postgresql

# Docker
docker run -d --name postgres -e POSTGRES_PASSWORD=mypassword -p 5432:5432 postgres:15

Install TimescaleDB

Install the TimescaleDB extension:

# Ubuntu/Debian
sudo add-apt-repository ppa:timescale/timescaledb-ppa
sudo apt-get update
sudo apt-get install timescaledb-postgresql-15

# macOS
brew tap timescale/tap
brew install timescaledb

# Docker (recommended)
docker run -d --name timescaledb -e POSTGRES_PASSWORD=mypassword -p 5432:5432 timescale/timescaledb:latest-pg15

Using the official TimescaleDB Docker image is the easiest way to get started with both PostgreSQL and TimescaleDB pre-configured.

Install Goose CLI

Install the Goose migration tool:

go install github.com/pressly/goose/v3/cmd/goose@latest

Database configuration

Configure database connection settings in your .env file:

.env

DB_USER=tsdbadmin
DB_PASSWORD=your_secure_password
DB_HOST=localhost
DB_PORT=5432
DB_DATABASE=tsdb

GOOSE_DRIVER=postgres
GOOSE_DBSTRING="postgresql://${DB_USER}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_DATABASE}?sslmode=disable"
GOOSE_MIGRATION_DIR=migrations

Production security: Replace sslmode=disable with sslmode=require in production to enforce encrypted connections.

Environment variables

DB_USER

string

required

PostgreSQL username with permissions to create tables and extensions.

DB_PASSWORD

string

required

PostgreSQL password for authentication.

DB_HOST

string

required

Database server hostname or IP address (localhost for local development).

DB_PORT

string

required

Database server port (default PostgreSQL port is 5432).

DB_DATABASE

string

required

Name of the database to use for Watchdog.

Initial setup

Create database

Create the Watchdog database:

# Using psql
psql -U postgres -c "CREATE DATABASE tsdb;"

# Or connect and create
psql -U postgres
CREATE DATABASE tsdb;
\q

Enable TimescaleDB extension

Connect to the database and enable TimescaleDB:

psql -U postgres -d tsdb
CREATE EXTENSION IF NOT EXISTS timescaledb;
\dx  -- Verify extension is installed
\q

The TimescaleDB extension must be enabled before running migrations, as migrations create hypertables that depend on it.

Run migrations

Apply database migrations using Goose:

# From project root
goose -dir migrations postgres "postgresql://tsdbadmin:password@localhost:5432/tsdb?sslmode=disable" up

# Check migration status
goose -dir migrations postgres "postgresql://tsdbadmin:password@localhost:5432/tsdb?sslmode=disable" status

Database schema

Watchdog uses three main tables:

URLs table

Stores metadata for monitored URLs:

migrations/20251115112101_create_urls_table.sql

CREATE TABLE urls
(
    id            SERIAL PRIMARY KEY,
    url           TEXT         NOT NULL,
    http_method   VARCHAR(255)       NOT NULL,
    status        VARCHAR(255)  NOT NULL,
    monitoring_frequency  VARCHAR(255)  NOT NULL,
    contact_email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Columns:

id - Unique identifier for the URL
url - The URL being monitored
http_method - HTTP method to use (get, post, patch, put, delete)
status - Current health status (healthy, unhealthy, pending)
monitoring_frequency - Check interval (ten_seconds, one_minute, five_minutes, etc.)
contact_email - Email address for status change notifications
created_at / updated_at - Timestamps

URL statuses table (hypertable)

Stores time-series monitoring metrics:

migrations/20251208081023_create_url_status_table.sql

CREATE TABLE url_statuses
(
    time     TIMESTAMPTZ NOT NULL,
    url_id   BIGINT      NOT NULL REFERENCES urls(id) ON DELETE CASCADE,
    status   BOOLEAN     NOT NULL
);

SELECT create_hypertable('url_statuses', 'time');

Columns:

time - Timestamp of the check (hypertable partition key)
url_id - Foreign key to urls table
status - Check result (true for success, false for failure)

create_hypertable() converts this table into a TimescaleDB hypertable, enabling efficient time-series queries and automatic partitioning.

Incidents table (hypertable)

Tracks downtime incidents:

migrations/20251213095702_create_incidents_table.sql

CREATE TABLE incidents
(
    time     TIMESTAMPTZ NOT NULL,
    url_id   BIGINT      NOT NULL REFERENCES urls(id) ON DELETE CASCADE,
    resolved_at TIMESTAMPTZ DEFAULT NULL
);

SELECT create_hypertable('incidents', 'time');

Columns:

time - Incident start time (hypertable partition key)
url_id - Foreign key to monitored URL
resolved_at - Timestamp when incident was resolved (NULL for ongoing incidents)

Connection pooling

Watchdog uses pgxpool for efficient connection management:

func InitiateDB(ctx context.Context, logger *slog.Logger) *pgxpool.Pool {
	pool, err := pgxpool.New(ctx, fmt.Sprintf(
		"postgres://%v:%v@%v:%v/%v",
		env.FetchString("DB_USER"),
		env.FetchString("DB_PASSWORD"),
		env.FetchString("DB_HOST"),
		env.FetchString("DB_PORT"),
		env.FetchString("DB_DATABASE"),
	))
	if err != nil {
		panic(fmt.Sprintf("pgxpool connection failed: %v", err))
	}
	if err := pool.Ping(ctx); err != nil {
		logger.Error("pgxpool connection failed: ", err)
		os.Exit(0)
	}
	fmt.Println("Connected to PostgreSQL database!")
	return pool
}

Connection pool configuration

The pgxpool library automatically manages connection pooling with sensible defaults. You can customize pool behavior by modifying the connection string:

// Custom pool configuration
config, err := pgxpool.ParseConfig(fmt.Sprintf(
	"postgres://%s:%s@%s:%s/%s?pool_max_conns=20&pool_min_conns=5",
	dbUser, dbPassword, dbHost, dbPort, dbName,
))
if err != nil {
	panic(err)
}

// Customize pool settings
config.MaxConns = 20                      // Maximum number of connections
config.MinConns = 5                       // Minimum idle connections
config.MaxConnLifetime = time.Hour        // Maximum connection lifetime
config.MaxConnIdleTime = 30 * time.Minute // Maximum idle time

pool, err := pgxpool.NewWithConfig(ctx, config)

Default pool settings:

Max connections: Determined by server resources (typically 4-10)
Min connections: 0 (connections created on demand)
Connection lifetime: Unlimited
Idle timeout: Unlimited

For most deployments, the default settings are sufficient. Tune pool size based on your concurrency requirements and database server capacity.

Repository pattern

Watchdog uses the repository pattern to abstract database operations:

type UrlRepository interface {
	FetchAll(ctx context.Context, limit int, offset int, filter UrlQueryFilter) ([]Url, error)
	Add(ctx context.Context, url string, httpMethod enums.HttpMethod, frequency enums.MonitoringFrequency, contactEmail string) (int, error)
	Delete(ctx context.Context, Id int) error
	FindById(ctx context.Context, Id int) (Url, error)
	UpdateStatus(ctx context.Context, Id int, status enums.SiteHealth) error
}

func NewUrlRepository(pool *pgxpool.Pool) UrlRepository {
	return urlRepository{
		pool: pool,
	}
}

Benefits:

Decouples business logic from SQL
Enables easy testing with mock repositories
Provides type-safe database operations
Centralizes query logic

Migrations

Watchdog uses Goose for schema versioning and migrations.

Running migrations

goose -dir migrations postgres "$GOOSE_DBSTRING" up

Migration file structure

Migrations follow the Goose format with -- +goose Up and -- +goose Down directives:

-- +goose Up
-- +goose StatementBegin
CREATE TABLE example (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
DROP TABLE example;
-- +goose StatementEnd

Always include a Down migration to allow rollbacks. Test both directions before deploying to production.

TimescaleDB features

TimescaleDB provides powerful time-series capabilities:

Automatic partitioning

Hypertables are automatically partitioned by time for optimal query performance:

-- Query recent data (uses recent chunks only)
SELECT * FROM url_statuses
WHERE time > NOW() - INTERVAL '1 hour'
AND url_id = 42;

-- Aggregate historical data
SELECT time_bucket('1 hour', time) AS hour,
       COUNT(*) as checks,
       COUNT(*) FILTER (WHERE status = true) as successful
FROM url_statuses
WHERE url_id = 42
AND time > NOW() - INTERVAL '7 days'
GROUP BY hour
ORDER BY hour DESC;

Data retention policies

Automatically drop old data to manage storage:

-- Drop data older than 90 days
SELECT add_retention_policy('url_statuses', INTERVAL '90 days');

-- Drop old incidents after 1 year
SELECT add_retention_policy('incidents', INTERVAL '1 year');

Continuous aggregates

Pre-compute aggregations for fast queries:

-- Create hourly aggregates
CREATE MATERIALIZED VIEW url_statuses_hourly
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS hour,
       url_id,
       COUNT(*) as total_checks,
       COUNT(*) FILTER (WHERE status = true) as successful_checks,
       AVG(CASE WHEN status = true THEN 1 ELSE 0 END) as uptime_percentage
FROM url_statuses
GROUP BY hour, url_id;

-- Refresh policy
SELECT add_continuous_aggregate_policy('url_statuses_hourly',
    start_offset => INTERVAL '3 hours',
    end_offset => INTERVAL '1 hour',
    schedule_interval => INTERVAL '1 hour');

Continuous aggregates are optional but highly recommended for large-scale deployments with many monitored URLs.

Troubleshooting

Connection refused errors

Symptoms: pgxpool connection failed: connection refusedSolutions:

Verify PostgreSQL is running: sudo systemctl status postgresql
Check PostgreSQL is listening on the correct port: sudo netstat -tlnp | grep 5432
Verify pg_hba.conf allows connections from your host
Check firewall rules allow port 5432

TimescaleDB extension not found

Symptoms: ERROR: extension "timescaledb" is not availableSolutions:

Install TimescaleDB extension package
Run timescaledb-tune to configure PostgreSQL
Add shared_preload_libraries = 'timescaledb' to postgresql.conf
Restart PostgreSQL: sudo systemctl restart postgresql

Migration failures

Symptoms: Goose migrations fail or leave database in inconsistent stateSolutions:

Check migration status: goose status
Manually fix the database schema
Update goose_db_version table to reflect actual state
Re-run migrations: goose up

For complete reset (development only):

goose down-to 0  # Rollback all
goose up         # Re-apply all

Connection pool exhaustion

Symptoms: all pool connections are in use errors under high loadSolutions:

Increase pool size in connection configuration
Reduce MAXIMUM_WORK_POOL_SIZE to limit concurrency
Check for connection leaks (connections not properly closed)
Increase PostgreSQL max_connections setting

Performance optimization

Create indexes

Add indexes for common query patterns:

-- Index for URL lookups
CREATE INDEX idx_urls_status ON urls(status);
CREATE INDEX idx_urls_frequency ON urls(monitoring_frequency);

-- Index for time-series queries
CREATE INDEX idx_url_statuses_url_id_time ON url_statuses(url_id, time DESC);

Configure autovacuum

Ensure autovacuum is enabled for table maintenance:

ALTER TABLE urls SET (autovacuum_vacuum_scale_factor = 0.1);

Monitor query performance

Use EXPLAIN ANALYZE to identify slow queries:

EXPLAIN ANALYZE
SELECT * FROM url_statuses
WHERE url_id = 42
AND time > NOW() - INTERVAL '1 day';

Get Started

Core Concepts

Configuration

Guides

Overview

Prerequisites

Database configuration

Environment variables

Initial setup

Database schema

URLs table

URL statuses table (hypertable)

Incidents table (hypertable)

Connection pooling

Connection pool configuration

Repository pattern

Migrations

Running migrations

Migration file structure

TimescaleDB features

Automatic partitioning

Data retention policies

Continuous aggregates

Troubleshooting

Performance optimization

Build docs developers (and LLMs) love

Get Started

Core Concepts

Configuration

Guides

​Overview

​Prerequisites

​Database configuration

​Environment variables

​Initial setup

​Database schema

​URLs table

​URL statuses table (hypertable)

​Incidents table (hypertable)

​Connection pooling

​Connection pool configuration

​Repository pattern

​Migrations

​Running migrations

​Migration file structure

​TimescaleDB features

​Automatic partitioning

​Data retention policies

​Continuous aggregates

​Troubleshooting

​Performance optimization

Build docs developers (and LLMs) love

Overview

Prerequisites

Database configuration

Environment variables

Initial setup

Database schema

URLs table

URL statuses table (hypertable)

Incidents table (hypertable)

Connection pooling

Connection pool configuration

Repository pattern

Migrations

Running migrations

Migration file structure

TimescaleDB features

Automatic partitioning

Data retention policies

Continuous aggregates

Troubleshooting

Performance optimization