Skip to main content

​
Overview

PriceSignal uses TimescaleDB, a PostgreSQL extension optimized for time-series data. This provides powerful querying capabilities for cryptocurrency price history while maintaining PostgreSQL compatibility.

​
TimescaleDB Version

PriceSignal uses:
  • TimescaleDB HA: timescale/timescaledb-ha:pg15
  • PostgreSQL Version: 15
  • Database Name: price_signal

​
Quick Start with Docker

1

Start TimescaleDB Container

The easiest way to get started is using Docker Compose:
docker compose up -d db
This starts TimescaleDB with:
  • Port: 5432
  • Database: price_signal
  • User: postgres
  • Password: example (change for production!)
2

Verify Database is Running

# Check container status
docker compose ps db

# Test connection
docker compose exec db pg_isready

# Connect to database
docker compose exec db psql -U postgres -d price_signal
3

Run Migrations

The application automatically runs database migrations on startup:
docker compose up server
In development mode, the database is automatically seeded with initial data via DbSeeder.Initialize().

​
Manual Installation

​
Install TimescaleDB on Ubuntu/Debian

1

Add TimescaleDB Repository

# Add PostgreSQL repository
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Add TimescaleDB repository
sudo sh -c "echo 'deb https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -c -s) main' > /etc/apt/sources.list.d/timescaledb.list"
wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo apt-key add -

sudo apt update
2

Install PostgreSQL and TimescaleDB

sudo apt install postgresql-15 timescaledb-2-postgresql-15
3

Configure TimescaleDB

# Tune PostgreSQL for TimescaleDB
sudo timescaledb-tune --quiet --yes

# Restart PostgreSQL
sudo systemctl restart postgresql
4

Create Database

sudo -u postgres psql

CREATE DATABASE price_signal;
\c price_signal
CREATE EXTENSION IF NOT EXISTS timescaledb;

​
Database Configuration

​
Connection String Format

PriceSignal supports two connection string formats: 
ConnectionStrings:PriceSignalDB=Host=localhost;Port=5432;Database=price_signal;Username=postgres;Password=yourpassword
In production, the application reads the connection URI from /app/secrets/uri and automatically converts it to Npgsql format. See DependencyInjection.cs:96-107.

​
Environment-Specific Configuration

The application determines the connection method based on the environment: 
if (isDevelopment)
{
    connectionString = configuration.GetConnectionString("PriceSignalDB") ??
                      throw new InvalidOperationException("Connection string not found");
}
else
{
    connectionString = ConvertToNpgsqlConnectionString(File.ReadAllText("/app/secrets/uri"));
}

​
Docker Compose Configuration

​
Development Setup

services:
  db:
    image: timescale/timescaledb-ha:pg15
    restart: always
    user: postgres
    volumes:
      - db-data:/var/lib/postgresql/data
    environment:
      - POSTGRES_DB=price_signal
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=example
    ports:
      - 5432:5432
    healthcheck:
      test: ["CMD", "pg_isready"]
      interval: 10s
      timeout: 5s
      retries: 5

volumes:
  db-data:

​
Production Setup

Never use default credentials in production. Always use strong passwords and restrict network access.
services:
  db:
    image: timescale/timescaledb-ha:pg15
    restart: unless-stopped
    user: postgres
    volumes:
      - db-data:/var/lib/postgresql/data
      - ./backups:/backups
    environment:
      - POSTGRES_DB=price_signal
      - POSTGRES_USER=${DB_USER}
      - POSTGRES_PASSWORD=${DB_PASSWORD}
    # Don't expose port publicly in production
    # Only allow internal network access
    healthcheck:
      test: ["CMD", "pg_isready", "-U", "${DB_USER}"]
      interval: 10s
      timeout: 5s
      retries: 5

volumes:
  db-data:
    driver: local

​
Kubernetes Setup

For production Kubernetes deployments, PriceSignal uses the TimescaleDB operator which provides managed TimescaleDB instances.

​
Using TimescaleDB Secret

The Pulumi infrastructure expects a Kubernetes secret named timescale-cluster-app: 
apiVersion: v1
kind: Secret
metadata:
  name: timescale-cluster-app
  namespace: default
type: Opaque
stringData:
  uri: postgresql://username:password@timescale-host:5432/price_signal
The application mounts this secret and reads the URI: 
volumeMounts:
  - mountPath: /app/secrets
    name: price-signal-secret-volume
    readOnly: true

volumes:
  - name: price-signal-secret-volume
    secret:
      secretName: timescale-cluster-app

​
Schema and Migrations

​
Entity Framework Core

PriceSignal uses Entity Framework Core with PostgreSQL and follows these conventions:
  • Naming Convention: Snake case (e.g., user_id, price_rules)
  • Enum Mapping: PostgreSQL enums (e.g., NotificationChannelType)
  • Interceptors: Auditable entity tracking with timestamps

​
Database Seeding

In development mode, the database is automatically seeded: 
using (var scope = app.Services.CreateScope())
{
    var services = scope.ServiceProvider;
    var context = services.GetRequiredService<AppDbContext>();
    if (!builder.Environment.IsDevelopment())
    {
        DbSeeder.Initialize(context);
    }
}

​
Optimization for Time-Series Data

​
TimescaleDB Hypertables

TimescaleDB automatically optimizes tables containing time-series data through hypertables, which:
  • Partition data by time automatically
  • Improve query performance for time-range queries
  • Enable efficient data retention policies
  • Support continuous aggregations
For optimal cryptocurrency price queries: 
-- Index on instrument and timestamp for price lookups
CREATE INDEX idx_prices_instrument_time ON prices (instrument_id, timestamp DESC);

-- Index on user rules for alert processing
CREATE INDEX idx_price_rules_user_enabled ON price_rules (user_id, is_enabled);

-- Index on notification channels
CREATE INDEX idx_notification_channels_user ON user_notification_channels (user_id);

​
Backup and Restore

​
Backup Database

# Using Docker Compose
docker compose exec db pg_dump -U postgres price_signal > backup.sql

# With compression
docker compose exec db pg_dump -U postgres price_signal | gzip > backup.sql.gz

# Backup to container volume
docker compose exec db pg_dump -U postgres price_signal > /backups/backup-$(date +%Y%m%d).sql

​
Restore Database

# From SQL file
docker compose exec -T db psql -U postgres price_signal < backup.sql

# From compressed backup
gunzip -c backup.sql.gz | docker compose exec -T db psql -U postgres price_signal

​
Automated Backups

Create a backup script: 
#!/bin/bash
BACKUP_DIR="/backups"
DATE=$(date +%Y%m%d_%H%M%S)
FILENAME="price_signal_${DATE}.sql.gz"

docker compose exec db pg_dump -U postgres price_signal | gzip > "${BACKUP_DIR}/${FILENAME}"

# Keep only last 7 days of backups
find ${BACKUP_DIR} -name "price_signal_*.sql.gz" -mtime +7 -delete

​
Monitoring

​
Check Database Size

SELECT pg_size_pretty(pg_database_size('price_signal'));

​
Monitor Active Connections

SELECT count(*) FROM pg_stat_activity WHERE datname = 'price_signal';

​
Check Table Sizes

SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

​
Troubleshooting

​
Connection Refused

# Check if PostgreSQL is running
docker compose ps db

# Check logs
docker compose logs db

# Verify port is listening
docker compose exec db netstat -tulpn | grep 5432

​
Permission Denied

# Check volume permissions
docker compose exec db ls -la /var/lib/postgresql/data

# Recreate with correct permissions
docker compose down -v
docker compose up -d db

​
TimescaleDB Extension Not Available

-- Check available extensions
SELECT * FROM pg_available_extensions WHERE name LIKE 'timescale%';

-- Create extension
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

​
Next Steps

Build docs developers (and LLMs) love

Get started for freeTalk to us