DuckDB on a VPS: Analytical SQL Engine for CSV, Parquet, and JSON Without a Database Server

DuckDB is an embedded OLAP (analytical) database that runs queries on CSV, Parquet, and JSON files directly — no server to install, no data loading, no schema migration. It’s like SQLite but for analytical queries. A single Python import gives you SQL queries that run 10–100× faster than pandas on the same data, using all CPU cores for parallel column-oriented processing. On a VPS, DuckDB powers data APIs, ETL pipelines, and analytics notebooks with minimal infrastructure.

DuckDB vs PostgreSQL vs ClickHouse for Analytics

Step 1: Install DuckDB

<code"># Python (most common usage)
pip install duckdb pandas pyarrow

# Or CLI binary:
wget https://github.com/duckdb/duckdb/releases/latest/download/duckdb_cli-linux-amd64.zip
unzip duckdb_cli-linux-amd64.zip
chmod +x duckdb && sudo mv duckdb /usr/local/bin/

# Verify:
duckdb --version
python3 -c "import duckdb; print(duckdb.__version__)"

Step 2: Query CSV Files Directly

<code">import duckdb

# Connect (in-memory — fastest for one-off analysis)
con = duckdb.connect()

# Query CSV directly — no import needed!
# Works on local files, S3, or HTTPS URLs
result = con.execute("""
    SELECT
        DATE_TRUNC('month', date::DATE) AS month,
        category,
        SUM(revenue)  AS total_revenue,
        COUNT(*)      AS orders,
        AVG(revenue)  AS avg_order_value
    FROM read_csv_auto('/data/sales-2024.csv')
    WHERE status = 'completed'
    GROUP BY 1, 2
    ORDER BY 1, 3 DESC
""").fetchdf()

print(result.head(20))

# Read multiple CSVs with glob:
con.execute("""
    SELECT * FROM read_csv_auto('/data/sales-*.csv')
    WHERE year = 2024
""")

# Query CSV from S3 (install httpfs extension):
con.execute("INSTALL httpfs; LOAD httpfs;")
con.execute("""
    SELECT COUNT(*) FROM read_csv_auto('s3://my-bucket/data/sales.csv')
""")

# Query CSV from HTTPS:
con.execute("""
    SELECT * FROM read_csv_auto('https://data.yourdomain.com/export.csv')
""").fetchdf()

Step 3: Query Parquet Files

<code"># Parquet is the preferred format for DuckDB — column-oriented, compressed
# 10 GB CSV → 1-2 GB Parquet → much faster queries

# Create Parquet from CSV:
con.execute("""
    COPY (SELECT * FROM read_csv_auto('/data/large-dataset.csv'))
    TO '/data/large-dataset.parquet'
    (FORMAT PARQUET, COMPRESSION ZSTD)
""")

# Query Parquet (much faster than CSV for large datasets):
result = con.execute("""
    SELECT
        user_id,
        SUM(amount) AS lifetime_value,
        COUNT(*) AS total_orders,
        MAX(created_at) AS last_order
    FROM read_parquet('/data/orders.parquet')
    GROUP BY user_id
    ORDER BY lifetime_value DESC
    LIMIT 100
""").fetchdf()

# Scan entire directory of Parquet files:
con.execute("""
    SELECT * FROM read_parquet('/data/logs/**/*.parquet')
    WHERE timestamp >= CURRENT_DATE - INTERVAL '7 days'
""")

Step 4: Persistent Database with DuckDB

<code"># For persistent storage (not just in-memory):
con = duckdb.connect('/opt/analytics/analytics.duckdb')

# Create tables (stored in .duckdb file):
con.execute("""
    CREATE TABLE IF NOT EXISTS daily_metrics AS
    SELECT
        DATE_TRUNC('day', timestamp) AS date,
        service,
        COUNT(*) AS requests,
        AVG(response_time) AS avg_ms,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_time) AS p95_ms
    FROM read_csv_auto('/var/log/nginx/access.log.csv')
    GROUP BY 1, 2
""")

# Query the persistent table:
con.execute("SELECT * FROM daily_metrics ORDER BY date DESC LIMIT 30")

Step 5: REST API Serving Analytics

<code">pip install fastapi uvicorn duckdb

<code">from fastapi import FastAPI, Query
import duckdb

app = FastAPI()

# Shared DuckDB connection (thread-safe)
con = duckdb.connect('/opt/analytics/analytics.duckdb')

@app.get("/api/revenue")
def get_revenue(
    period: str = Query("month", enum=["day", "week", "month"]),
    limit: int = Query(12, le=100),
):
    result = con.execute(f"""
        SELECT
            DATE_TRUNC('{period}', order_date) AS period,
            SUM(amount) AS revenue,
            COUNT(*) AS orders
        FROM read_parquet('/data/orders.parquet')
        WHERE order_date >= CURRENT_DATE - INTERVAL '1 year'
        GROUP BY 1
        ORDER BY 1 DESC
        LIMIT {limit}
    """).fetchdf()
    return result.to_dict('records')

@app.get("/api/top-products")
def get_top_products(limit: int = 10):
    result = con.execute(f"""
        SELECT product_name, SUM(revenue) AS total
        FROM read_parquet('/data/orders.parquet')
        GROUP BY product_name
        ORDER BY total DESC
        LIMIT {limit}
    """).fetchdf()
    return result.to_dict('records')

<code">uvicorn analytics_api:app --host 127.0.0.1 --port 8001

Step 6: ETL Pipeline with DuckDB

<code"># DuckDB as ETL: extract from CSV, transform with SQL, load to PostgreSQL
import duckdb
import psycopg2

con = duckdb.connect()

# Install PostgreSQL extension:
con.execute("INSTALL postgres; LOAD postgres;")

# ETL pipeline: CSV → transform → PostgreSQL
con.execute("""
    ATTACH 'postgresql://user:pass@localhost/prod' AS pg (TYPE POSTGRES);

    INSERT INTO pg.analytics.daily_summary
    SELECT
        order_date::DATE                    AS date,
        customer_segment,
        product_category,
        SUM(revenue)                        AS total_revenue,
        COUNT(DISTINCT customer_id)         AS unique_customers,
        AVG(order_value)                    AS avg_order_value
    FROM read_csv_auto('/data/raw/*.csv')
    WHERE order_date >= CURRENT_DATE - INTERVAL '1 day'
    GROUP BY 1, 2, 3
""")

print("ETL complete!")

Getting Started

DuckDB requires no server installation — pip install duckdb and start querying files immediately. On a 4 vCPU Ubuntu VPS at VPS.DO, DuckDB uses all 4 cores for analytical queries, processing gigabytes per second. For teams running Python data pipelines or building analytics APIs, DuckDB eliminates the need for a separate analytics database while outperforming pandas for SQL-expressible operations.

Conclusion

DuckDB brings OLAP database performance to file-based analytics — query CSV, Parquet, and JSON files with SQL using all CPU cores, without loading data into a database server. The zero-setup embedded model makes it perfect for VPS-hosted ETL pipelines, analytics APIs, and data exploration notebooks. For teams working with files exported from databases, logs, or cloud storage, DuckDB provides the fastest SQL analytics available without database infrastructure overhead.