DuckDB for TradeEntry

What is DuckDB?

DuckDB is a modern analytical SQL database designed for high-performance data analytics.

The simplest way to understand it:

"DuckDB is to analytics what SQLite is to storage — embedded, zero-setup, and extremely fast."

Unlike PostgreSQL (optimized for web apps and transactions), DuckDB is built for the kind of work TradeEntry does: scanning thousands of symbols, running backtests, computing market profiles, and querying years of 1-minute bar data in seconds.

Property	DuckDB
Embedded	Yes — runs inside your Python process, no separate server
Server required	No — just a single `.db` file on disk
Query language	Standard SQL (very similar to PostgreSQL)
Storage model	Columnar (groups same-type data together for fast scans)
Sweet spot	Large aggregations, historical analysis, Parquet queries
Official site	duckdb.org

Why TradeEntry Needs DuckDB

TradeEntry manages and analyses:

Intraday 1-minute OHLCV bars for thousands of NSE instruments
Daily EOD bhavcopy data
Options chains with strikes and expiries
Relative strength rankings across sectors
Market Profile (VPOC, VAH, VAL, TPO)
Backtesting across multi-year datasets
Custom scanners (trend + volume + RS combinations)

These are analytical workloads — queries that read millions of rows, aggregate, rank, and filter, but rarely write or update individual rows.

PostgreSQL is not designed for this. DuckDB is purpose-built for exactly this type of work.

PostgreSQL vs DuckDB — Choosing the Right Tool

This is the most important concept in the architecture. Both databases co-exist in TradeEntry; each does what it is best at.

Dimension	PostgreSQL	DuckDB
Architecture	Client-server (separate process)	Embedded (in-process)
Optimised for	Transactions (OLTP)	Analytics (OLAP)
Storage model	Row-oriented	Column-oriented
Multi-user writes	Excellent	Not designed for this
Concurrent reads	Good	Excellent
Query style	CRUD — INSERT, UPDATE, DELETE	Large SELECT with GROUP BY, WINDOW
Parquet support	Needs extension	First-class, built-in
Setup	Install + configure server	`pip install duckdb`

PostgreSQL stays for (never replace):

User authentication
Orders and trade entries
Alerts and notifications
scr_master — the single source of truth for all instrument metadata
API backend state
Any data that is written live by the application

DuckDB is used for (never put in PostgreSQL):

Historical candle analytics
Backtesting engine queries
Relative strength calculations across all symbols
Market Profile (VPOC, VAH, VAL) computations
Scanner engine (multi-symbol multi-timeframe)
Quantitative research notebooks

TradeEntry Hybrid Architecture

The key rule:
scr_master lives only in PostgreSQL. DuckDB references masid as a foreign key — it never stores symbol names, expiry dates, or strike prices. This avoids any duplication or sync problems.

Why DuckDB is Fast

Row-Oriented vs Columnar Storage

PostgreSQL (row-oriented): Stores each row together on disk.

Row 1: masid=1, sdate=09:15, sopen=22000, shigh=22050, slow=21990, sclose=22030
Row 2: masid=1, sdate=09:16, sopen=22030, shigh=22080, slow=22010, sclose=22060
...

To compute MAX(shigh), PostgreSQL reads every field of every row, even the ones it doesn't need.

DuckDB (columnar): Stores each column together on disk.

shigh column: [22050, 22080, 22100, 22090, ...]   ← only this is read for MAX(shigh)

For analytics queries (AVG, MAX, GROUP BY, WINDOW FUNCTIONS), DuckDB reads only the columns needed — often 10–50× less data. This is why it feels instant on multi-year datasets.

Vectorized Execution

DuckDB processes 1,024 values at once (a "vector") using CPU SIMD instructions, instead of processing row by row. Combined with columnar storage, this gives analytics performance that is often 10–100× faster than PostgreSQL for the same query.

Key Features

1. Embedded — No Server Needed

import duckdb

# In-memory (scratch work, notebooks)
con = duckdb.connect()

# Persistent (catalog, production use)
con = duckdb.connect("/path/to/catalog.db")

No daemon. No service. No config files. The .db file is the entire database.

2. Direct Parquet Queries — No Import Required

DuckDB can query Parquet files on disk without copying data into a table first.

import duckdb

# Query a single file
df = duckdb.sql("""
    SELECT masid, COUNT(*) AS bars, MIN(sdate) AS first, MAX(sdate) AS last
    FROM read_parquet('/ieod/data/N/NIFTY/2024/1_JANUARY')
    GROUP BY masid
""").df()

# Query multiple files with glob
df = duckdb.sql("""
    SELECT masid, AVG(sclose) AS avg_close
    FROM read_parquet('/ieod/data/N/NIFTY/2024/*')
    WHERE sclose > sopen
    GROUP BY masid
""").df()

This is extremely powerful for TradeEntry — years of Parquet files become directly queryable SQL.

3. Native Parquet Support

DuckDB and Parquet are designed for each other:

Parquet Feature	Benefit for TradeEntry
Columnar compression	5–10× smaller files vs CSV
Row groups with statistics	Skip files/groups that don't match WHERE clause
Predicate pushdown	`WHERE sdate BETWEEN ...` reads only matching row groups
Parallel reads	Multiple cores scan different row groups simultaneously

Our existing Parquet layout {masid}_{MONTH_NAME} (e.g. 1_JANUARY) is already well-structured for DuckDB's file-skipping optimisations.

4. Direct Pandas Integration

DuckDB works directly with in-memory Pandas DataFrames — no file I/O needed:

import duckdb
import pandas as pd

# df is already in memory from a previous step
df = pd.read_parquet("/ieod/data/N/NIFTY/2024/1_JANUARY")

# Query it with SQL
result = duckdb.sql("""
    SELECT
        sdate::DATE AS trading_day,
        FIRST(sopen  ORDER BY sdate) AS open,
        MAX(shigh)                   AS high,
        MIN(slow)                    AS low,
        LAST(sclose  ORDER BY sdate) AS close,
        SUM(svolume)                 AS volume
    FROM df
    GROUP BY trading_day
    ORDER BY trading_day
""").df()

DuckDB can reference the variable df directly in the SQL string — it scans the DataFrame in-memory without copying it.

5. PostgreSQL Live Join (`postgres_scanner`)

DuckDB can connect to PostgreSQL and JOIN tables across both databases:

import duckdb

con = duckdb.connect()
con.execute("INSTALL postgres_scanner; LOAD postgres_scanner;")
con.execute("""
    ATTACH 'host=localhost dbname=tedb user=postgres password=xxx'
    AS pg (TYPE POSTGRES, READ_ONLY)
""")

# Join Parquet bars with scr_master names — one SQL statement
result = con.execute("""
    SELECT sm.mname, bars.sdate::DATE AS date, bars.sclose, bars.svolume
    FROM pg.public.scr_master sm
    JOIN read_parquet('/ieod/data/N/NIFTY/2024/1_JANUARY') bars
      ON bars.masid = sm.masid
    WHERE sm.underly = 0
    ORDER BY date
""").df()

This means scanner queries can pull symbol names from PostgreSQL without duplicating scr_master.

TradeEntry Use Cases

Use Case 1: Historical Candle Analytics

import duckdb

con = duckdb.connect("/ieod/_catalog/catalog.db")

# Average daily range for NIFTY in 2024
df = con.execute("""
    SELECT
        sdate::DATE AS day,
        MAX(shigh) - MIN(slow) AS daily_range,
        LAST(sclose ORDER BY sdate) AS close
    FROM read_parquet('/ieod/data/N/NIFTY/2024/*')
    WHERE masid = 1
    GROUP BY day
    ORDER BY day
""").df()

Use Case 2: Relative Strength Scan

Compare every Nifty 50 stock against the index over the last 20 days:

def rs_scan(masid_list, benchmark_masid, parquet_root, days=20):
    con = duckdb.connect()
    results = []

    for masid in masid_list:
        sql = f"""
        WITH daily AS (
            SELECT sdate::DATE AS d,
                   LAST(sclose ORDER BY sdate) AS close
            FROM read_parquet('{parquet_root}/*/{masid}_*')
            GROUP BY d
            ORDER BY d DESC
            LIMIT {days}
        )
        SELECT
            {masid}           AS masid,
            FIRST(close)      AS close_now,
            LAST(close)       AS close_then,
            (FIRST(close) / LAST(close) - 1) * 100 AS return_pct
        FROM daily
        """
        results.append(con.execute(sql).df())

    return pd.concat(results).sort_values('return_pct', ascending=False)

Use Case 3: Market Profile (VPOC / VAH / VAL)

def market_profile(masid, date, parquet_root):
    con = duckdb.connect()
    return con.execute(f"""
    WITH tpos AS (
        -- Divide each bar into 0.25-point price buckets
        SELECT
            ROUND(price / 0.25) * 0.25 AS price_level,
            COUNT(*) AS tpo_count
        FROM read_parquet('{parquet_root}/*/{masid}_*'),
             UNNEST(GENERATE_SERIES(slow, shigh, 0.25)) AS t(price)
        WHERE sdate::DATE = '{date}'
          AND masid = {masid}
        GROUP BY price_level
    ),
    total AS (SELECT SUM(tpo_count) AS total FROM tpos),
    cumulative AS (
        SELECT price_level, tpo_count,
               SUM(tpo_count) OVER (ORDER BY tpo_count DESC) / total.total AS cum_vol_pct
        FROM tpos, total
    )
    SELECT
        (SELECT price_level FROM tpos ORDER BY tpo_count DESC LIMIT 1) AS vpoc,
        MIN(CASE WHEN cum_vol_pct <= 0.70 THEN price_level END) AS val,
        MAX(CASE WHEN cum_vol_pct <= 0.70 THEN price_level END) AS vah
    FROM cumulative
    """).df()

Use Case 4: Daily OHLCV Resample (1-min → 1-Day)

No separate daily Parquet needed — compute on the fly:

def get_daily_bars(masid, from_date, to_date, parquet_root):
    con = duckdb.connect()
    return con.execute(f"""
    SELECT
        sdate::DATE                          AS date,
        FIRST(sopen  ORDER BY sdate)         AS open,
        MAX(shigh)                           AS high,
        MIN(slow)                            AS low,
        LAST(sclose  ORDER BY sdate)         AS close,
        SUM(svolume)                         AS volume,
        LAST(oi      ORDER BY sdate)         AS oi
    FROM read_parquet('{parquet_root}/*/{masid}_*')
    WHERE masid = {masid}
      AND sdate::DATE BETWEEN '{from_date}' AND '{to_date}'
    GROUP BY date
    ORDER BY date
    """).df()

Use Case 5: Scanner Engine

Monthly uptrend + weekly pullback scanner:

def scanner_monthly_uptrend_weekly_pullback(masid_list, parquet_root):
    results = []
    con = duckdb.connect()

    for masid in masid_list:
        # Get last 6 months of daily bars
        bars = con.execute(f"""
        SELECT sdate::DATE AS d, LAST(sclose ORDER BY sdate) AS close
        FROM read_parquet('{parquet_root}/*/{masid}_*')
        WHERE sdate >= current_date - INTERVAL 6 MONTHS
        GROUP BY d ORDER BY d
        """).df()

        if len(bars) < 20:
            continue

        monthly_trend = bars['close'].iloc[-1] > bars['close'].iloc[-22]
        weekly_pullback = bars['close'].iloc[-1] < bars['close'].iloc[-5]

        if monthly_trend and weekly_pullback:
            results.append({'masid': masid, 'close': bars['close'].iloc[-1]})

    return pd.DataFrame(results)

Installation

# Add to requirements.txt
duckdb==1.2.2

# Install in venv
pip install duckdb==1.2.2

Pin the Version

Always pin DuckDB to a specific version. The .db file format changes between major versions are not backward-compatible. If you upgrade DuckDB, you must rebuild the catalog .db file.

Limitations

DuckDB is not suitable for:

Scenario	Why DuckDB is Wrong	Use Instead
High-concurrency writes	Single writer at a time	PostgreSQL
Live web API backend	Not designed for concurrent connections	PostgreSQL
User authentication	No row-level security	PostgreSQL
Real-time streaming inserts	Optimised for batch reads	PostgreSQL / Redis
ACID transactions	Limited compared to PostgreSQL	PostgreSQL

Bottom line: DuckDB reads historical data fast. PostgreSQL writes application data safely. They are complementary, not competing.

Summary

Use PostgreSQL for:                    Use DuckDB for:
─────────────────────────────          ─────────────────────────────
✓ scr_master (symbol registry)        ✓ Historical bar queries
✓ Trades and orders                   ✓ Parquet catalog (catalog.db)
✓ User accounts                       ✓ Backtesting engine
✓ Alerts and notifications            ✓ Scanner engine
✓ Staging (scr_nsefo/scr_nseeq)       ✓ Market Profile calculations
✓ Live application state              ✓ Relative strength analysis
                                       ✓ Research notebooks

This hybrid architecture is the pattern used by modern quantitative trading platforms. PostgreSQL handles "now." DuckDB handles "history."

What is DuckDB?​

Why TradeEntry Needs DuckDB​

PostgreSQL vs DuckDB — Choosing the Right Tool​

PostgreSQL stays for (never replace):​

DuckDB is used for (never put in PostgreSQL):​

TradeEntry Hybrid Architecture​

Why DuckDB is Fast​

Row-Oriented vs Columnar Storage​

Vectorized Execution​

Key Features​

1. Embedded — No Server Needed​

2. Direct Parquet Queries — No Import Required​

3. Native Parquet Support​

4. Direct Pandas Integration​

5. PostgreSQL Live Join (postgres_scanner)​

TradeEntry Use Cases​

Use Case 1: Historical Candle Analytics​

Use Case 2: Relative Strength Scan​

Use Case 3: Market Profile (VPOC / VAH / VAL)​

Use Case 4: Daily OHLCV Resample (1-min → 1-Day)​

Use Case 5: Scanner Engine​

Installation​

Limitations​

Summary​