Table of Contents

Overview PostgreSQL TimescaleDB InfluxDB Schema Design Indexing Partitioning Optimization

Integrations

Database Storage Patterns for API Data

Efficient database design for crypto trading data. Store whale signals, OHLCV data, and trading history with optimal performance. PostgreSQL, TimescaleDB, and InfluxDB patterns.

Published March 21, 2026 • 21 min read • Advanced

Database Selection for Crypto Trading

You'll store three data types: signals (occasional, small), time-series data (frequent, medium), and trading history (append-only). Different databases excel at different patterns. The right choice depends on your query patterns and volume.

Data Types

Signals — Whale accumulation, exchange flows (events, occasional)
Time-Series — OHLCV candles, funding rates, exchange flow (high-frequency, regular intervals)
Trading History — Executions, P&L, positions (append-only, immutable)

Pro setup: Use PostgreSQL for signals and trading history, TimescaleDB extension for time-series data (same database), and InfluxDB as optional time-series backup for metrics.

Comparison Matrix

Database	Best For	Scalability	Cost
PostgreSQL	Signals, trades	Medium	Low
TimescaleDB	Time-series	High	Low-Medium
InfluxDB	Metrics, streams	Very High	Medium-High

PostgreSQL for Signals and History

Table Schema for Signals

PostgreSQL Schema
CREATE TABLE whale_signals (
  id BIGSERIAL PRIMARY KEY,
  symbol VARCHAR(20),
  signal_type VARCHAR(50),
  confidence DECIMAL(3,2),
  amount_tokens DECIMAL(20,8),
  amount_usd DECIMAL(20,2),
  whale_count INT,
  exchange_flow_24h DECIMAL(20,2),
  funding_rate DECIMAL(8,6),
  created_at TIMESTAMPTZ DEFAULT now(),
  INDEX idx_symbol_time (symbol, created_at DESC)
);

Trading History Table

Trades Table
CREATE TABLE trades (
  id BIGSERIAL PRIMARY KEY,
  signal_id BIGINT REFERENCES whale_signals(id),
  entry_price DECIMAL(20,8),
  exit_price DECIMAL(20,8),
  quantity DECIMAL(20,8),
  pnl DECIMAL(20,8),
  status VARCHAR(20),  -- open, closed
  created_at TIMESTAMPTZ,
  closed_at TIMESTAMPTZ
);

Querying Efficiently

Always filter by symbol and time range first
Use EXPLAIN ANALYZE to verify indexes are used
Aggregate historical data into summary tables
Archive old data to separate tables by month/year

Get your API key in 30 seconds

Wire this integration to live data in minutes. Free API key, 200 calls/day, no card required.

Get your API key →

TimescaleDB for Time-Series Data

What is TimescaleDB?

PostgreSQL extension for time-series data. Automatically partitions data by time, compresses old data, and optimizes queries for time-range patterns.

Hypertable Setup

TimescaleDB Hypertable
-- Create regular table first
CREATE TABLE ohlcv (
  time TIMESTAMPTZ,
  symbol VARCHAR(20),
  open DECIMAL(20,8),
  high DECIMAL(20,8),
  low DECIMAL(20,8),
  close DECIMAL(20,8),
  volume DECIMAL(30,8)
);
-- Convert to hypertable
SELECT create_hypertable('ohlcv', 'time');

Continuous Aggregates

Pre-compute aggregates (1h, 1d) for faster queries:

Continuous Aggregate
CREATE MATERIALIZED VIEW ohlcv_1h WITH (timescaledb.continuous) AS
SELECT time_bucket('1h', time) AS time,
  symbol,
  first(open, time) AS open,
  max(high) AS high,
  min(low) AS low,
  last(close, time) AS close,
  sum(volume) AS volume
FROM ohlcv
GROUP BY time, symbol;

Queries against ohlcv_1h are instant because aggregates are pre-computed.

InfluxDB for Metrics

When to Use InfluxDB

Best for monitoring metrics (CPU, memory, request latency) rather than trading data. Excellent compression, retention policies, and downsampling.

Data Ingestion

InfluxDB Write
from influxdb_client import InfluxDBClient, Point
client = InfluxDBClient(url="http://localhost:8086")
write_api = client.write_api()
point = Point("whale_signals")
  .tag("symbol", "BTC")
  .field("confidence", 0.87)
  .field("amount", 2150)
write_api.write(bucket="crypto", record=point)

Retention and Downsampling

Keep raw data: 7 days
Hourly aggregates: 90 days
Daily aggregates: 2 years

InfluxDB automatically downsamples old data, compressing 7 days of 1-minute data into 90 days of 1-hour data.

Schema Design Best Practices

1. Use Appropriate Data Types

DECIMAL for prices and amounts (not FLOAT)
BIGINT for volumes and counts
TIMESTAMPTZ for all timestamps (always timezone-aware)
VARCHAR for symbols and enums

2. Normalize Early, Denormalize Wisely

Start normalized (reduce duplication). Denormalize for performance only when measured.

3. Partition by Time

Split data by month or year. Queries on recent data are faster. Archiving is easy (drop old partition).

4. Add Audit Columns

Audit Columns
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now(),
created_by VARCHAR(100),
updated_by VARCHAR(100)

Indexing Strategies

Primary Indexes

Index Examples
-- Filter by symbol and recent data
CREATE INDEX idx_symbol_time
ON whale_signals (symbol, created_at DESC);
-- Filter by confidence range
CREATE INDEX idx_confidence
ON whale_signals (confidence DESC);

Monitoring Index Effectiveness

Check Index Usage
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;  -- Unused indexes

Remove indexes that are never used.

Partitioning and Archival

Time-Based Partitioning

Partition by Range
CREATE TABLE whale_signals_2026_q1 PARTITION OF whale_signals
FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');

Archive Old Data

Quarterly, copy data >90 days old to archive table
Compress with gzip
Store in S3 or backup storage
Drop from main table

Keeps main table lean and queries fast.

Query Optimization

Common Slow Queries

Scanning without filtering by symbol
Selecting entire rows when only certain columns needed
Full-table joins on large tables
Aggregating without GROUP BY optimization

EXPLAIN ANALYZE

Query Analysis
EXPLAIN ANALYZE SELECT confidence FROM whale_signals
WHERE symbol = 'BTC' AND created_at > now() - interval '7 days';

Shows actual execution time and whether indexes are used.

Database wisdom: A well-designed database is worth 1000 lines of application code. Spend time on schema design, indexing, and partitioning. Slow queries compound into system-wide issues.

Design Efficient Data Storage

Professional database design for high-volume trading data. PostgreSQL + TimescaleDB patterns for optimal performance.

View Pricing Plans

All plans support direct database integrations.