Skip to content

cianfhoghlaim/cianchosaint / .agents/skills/duckdb

duckdb

Verified

FileKindSize
SKILL.mdskill md12.9 KB
references/duckdb-comprehensive-research.mdmarkdown28.7 KB
references/duckdb.mdmarkdown13.1 KB

references/duckdb-comprehensive-research.md

View on GitHub
# DuckDB Comprehensive Research Report

**Report Generated:** 2025-11-17
**Latest DuckDB Version:** 1.4.2 LTS (Released November 12, 2025)

---

## Table of Contents

1. [Core Features & Capabilities](#1-core-features--capabilities)
2. [Architecture & Patterns](#2-architecture--patterns)
3. [APIs & Integration](#3-apis--integration)
4. [Ontologies & Data Models](#4-ontologies--data-models)
5. [Best Practices & Common Patterns](#5-best-practices--common-patterns)
6. [Real-World Use Cases](#6-real-world-use-cases)
7. [Extensions & Ecosystem](#7-extensions--ecosystem)

---

## 1. Core Features & Capabilities

### What is DuckDB?

**DuckDB** is an in-process SQL OLAP (Online Analytical Processing) database management system. It's often described as "SQLite for Analytics" due to its:
- Embedded, in-process nature (no separate server process)
- PostgreSQL-compatible SQL dialect
- Optimization for analytical workloads rather than transactional ones

### Key Features

#### Columnar Storage
- Stores data by columns rather than by rows
- Far more efficient for analytical queries that scan large portions of datasets
- Only relevant columns are read from disk into memory
- Uses PAX (Partition Attributes Across) columnar storage format
- Enables superior CPU cache utilization and vectorized operations
- Supports efficient compression on a per-column basis

#### Vectorized Query Execution
- Processes large batches of values (vectors) in single operations
- Takes advantage of modern CPU architectures and SIMD (Single Instruction, Multiple Data) instructions
- Greatly reduces overhead compared to row-by-row processing in traditional systems
- Processes data in dense, contiguous blocks for maximum CPU efficiency
- Leads to significantly better performance for OLAP queries

#### ACID Compliance
- Fully supports ACID transactions (Atomicity, Consistency, Isolation, Durability)
- Uses Multi-Version Concurrency Control (MVCC) based on HyPer's serializable variant
- Optimistic concurrency control approach
- Always uses Snapshot Isolation (similar to SERIALIZABLE)
- Transactions don't hold locks - conflicts result in transaction abort and retry
- Lock-free MVCC provides multiple consistent views on the same dataset

#### Performance Characteristics
- Can be 10-50 times faster than SQLite for analytical queries
- Up to 600 times faster when reading Parquet vs CSV files
- Automatic parallelization across all available CPU cores
- Morsel-driven parallelism for NUMA-aware execution
- Zone maps for selective scanning

#### File Format Support
- **Parquet** (highly optimized, recommended format)
- **CSV** (with auto-detection of settings)
- **JSON** (newline-delimited and regular JSON)
- **Apache Arrow** (zero-copy integration)
- **Data Lake Formats:**
  - Apache Iceberg (native support via extension)
  - Delta Lake (native support via delta extension)
  - DuckLake (DuckDB's own lakehouse format, released May 2025)

#### Cloud and Network Storage
- AWS S3
- Azure Blob Storage
- Google Cloud Storage
- HTTP/HTTPS URLs
- Can query remote files directly without downloading

#### SQL Dialect Features
- **PostgreSQL-compatible** SQL dialect with ANSI SQL compliance
- **Window Functions:** 14+ window-specific functions plus all aggregation functions
- **Common Table Expressions (CTEs):** Full support including recursive CTEs
- **Advanced Features:**
  - User-defined aggregates
  - MERGE statement (added in v1.4.0)
  - TRY expression for error handling
  - UUID v7 support
  - Complex subqueries and joins

#### Supported Data Types

**Numeric Types:**
- INTEGER, BIGINT, SMALLINT, TINYINT
- DOUBLE, FLOAT, REAL
- DECIMAL/NUMERIC (arbitrary precision)
- HUGEINT (128-bit integers)

**String Types:**
- VARCHAR, TEXT
- BLOB (binary data)

**Temporal Types:**
- DATE
- TIMESTAMP (with/without timezone)
- TIME
- INTERVAL

**Boolean:**
- BOOLEAN

**Complex Types:**
- ARRAY
- STRUCT
- MAP
- LIST
- JSON

**Geospatial:**
- GEOMETRY (via spatial extension)

#### Database Encryption
- Available starting with v1.4.0
- Industry-standard AES-256 encryption
- GCM mode by default
- Encrypts database files at rest

#### Local UI
- Full-featured local web UI available out-of-the-box (since v1.2.1)
- All queries run locally - data never leaves your computer
- No backend infrastructure required

---

## 2. Architecture & Patterns

### Embedded Architecture

**In-Process Design:**
- DuckDB does not run as a separate process
- Completely embedded within a host process
- No client-server architecture overhead
- No network latency
- Direct memory access to data

**Shared-Everything Architecture:**
- Compute and storage are not separated
- All components share the same memory space
- Optimal for single-machine analytical workloads

### Query Execution Model

#### Push-Based Vectorized Query Processing
- Operators are "parallelism-aware"
- Parallelism is managed dynamically within the query plan
- Not baked into the plan statically

#### Morsel-Driven Parallelism
- Pioneered in academic research
- NUMA-aware execution
- Work is divided into small chunks ("morsels")
- Enables efficient utilization of multiple CPU cores

#### Streaming Execution Engine
- Allows small chunks of data to flow through the system
- Entire datasets don't need to be materialized in memory
- Enables processing of larger-than-memory datasets

### Storage and Indexing Patterns

#### Persistent Storage
- Data stored on fixed-size pages
- Pages managed by buffer manager
- Supports durable persistence to disk
- Can also operate in pure in-memory mode

#### Indexing
- Zone maps for efficient data skipping
- Statistics-based query optimization
- Automatic index selection
- Support for explicit index creation

### Memory Management

#### Non-Traditional Buffer Management
- **Key Innovation:** DuckDB doesn't reserve a fixed portion of memory for a buffer pool
- All available memory can be used flexibly:
  - For persistent data when needed
  - For large hash tables during aggregations
  - For intermediate results

**Default Memory Limit:**
- Uses up to 80% of available system memory by default
- Configurable via pragmas

**Memory Allocation Strategy:**
- Buffer manager handles most data processed
- Some aggregate functions (list, mode, quantile, string_agg, approx functions) use memory outside buffer manager
- Actual memory consumption can exceed specified limit for complex aggregates

**Out-of-Core Processing:**
- Intermediate results can be spilled to disk
- Enables computation of complex queries exceeding available memory
- External aggregation support

#### Statistics Propagation
- Creates new filters by inspecting column statistics
- Enables filter pushdown optimizations
- Reduces unnecessary I/O

### Extension System

**Architecture:**
- Flexible extension mechanism for dynamic loading
- Extensions enhance functionality with:
  - Additional file formats
  - New data types
  - Domain-specific functionality

**Two-Phase Process:**
1. **Installation:** Downloads extension binary and verifies metadata
2. **Loading:** Dynamically loads binary into DuckDB instance

**Extension Types:**
- **Core Extensions:** Built and signed by DuckDB team
- **Community Extensions:** Third-party developed extensions

**Platform Support:**
- macOS, Windows, Linux
- Available across all clients (Python, R, Node.js, etc.)

**Extension Storage:**
- Downloaded to `~/.duckdb` directory
- Binaries matched to OS and processor architecture

---

## 3. APIs & Integration

### Client Libraries Overview

All DuckDB clients:
- Support the same SQL syntax
- Use the same on-disk database format
- Can share databases across different language clients
- Example: Create database in Python, query from Node.js

### Python API

**Installation:**
```bash
pip install duckdb
```

**Requirements:**
- Python 3.9 or newer

**API Styles:**
- **DB API:** Standard Python database interface (PEP 249)
- **Relational API:** DuckDB-specific fluent interface
- **Function API:** Direct function calls

**Example Usage:**
```python
import duckdb

# In-memory database
con = duckdb.connect()

# Persistent database
con = duckdb.connect('my_database.db')

# Query directly
result = con.execute("SELECT * FROM 'data.parquet'").fetchall()

# Relational API
result = con.table('my_table').filter('column > 10').aggregate('count(*)')
```

**Data Ingestion:**
- Direct Pandas DataFrame integration
- Apache Arrow integration (zero-copy)
- Read from Parquet, CSV, JSON files
- Query files directly without loading

### Node.js API

**Two Packages Available:**

1. **@duckdb/node-api (Node Neo - Recommended)**
   - High-level API for applications
   - Native Promise support
   - Lossless support for all DuckDB data types
   - Low-level bindings available as @duckdb/node-bindings

2. **duckdb (Deprecated)**
   - Legacy package
   - Use Node Neo instead

**Installation:**
```bash
npm install @duckdb/node-api
```

**Example Usage:**
```javascript
const duckdb = require('@duckdb/node-api');

const db = new duckdb.Database(':memory:');
const conn = db.connect();

const result = await conn.run('SELECT * FROM read_parquet("data.parquet")');
```

### R Client

**Two Integration Approaches:**

1. **duckplyr Package (Drop-in Replacement)**
   - Translates dplyr API to DuckDB's execution engine
   - Drop-in replacement for dplyr
   - Uses DuckDB's relational API (not SQL interface)
   - Bypasses SQL parser for better performance
   - Can be 20x faster than standard dplyr

   ```r
   install.packages("duckplyr")
   library(duckplyr)

   # Your existing dplyr code runs automatically on DuckDB
   df %>%
     filter(value > 100) %>%
     summarise(total = sum(amount))
   ```

2. **Standard DuckDB Client with dbplyr**
   - SQL backend for dbplyr
   - Programmatic query construction
   - Based on PostgreSQL backend with additional mapped functions

   ```r
   install.packages("duckdb")
   library(duckdb)
   library(dplyr)

   con <- dbConnect(duckdb::duckdb(), "my_database.db")
   ```

### Other Language Clients

- **Java/JDBC:** Full JDBC driver support
- **C/C++:** Native API
- **Go:** Go bindings
- **Rust:** Rust bindings
- **Julia:** Julia package

### WebAssembly (WASM)

**Browser Support:**
- Runs entirely in the browser
- No backend infrastructure needed
- Tested with Chrome, Firefox, Safari, Node.js

**Features:**
- Speaks Arrow fluently
- Reads Parquet, CSV, JSON
- Filesystem APIs or HTTP requests
- Extension support (WebAssembly modules)

**Installation:**
```bash
npm install @duckdb/duckdb-wasm
```

**Current Version:** 1.30.0

**Limitations:**
- Single-threaded by default (multithreading experimental)
- Sandboxed environment
- Limited out-of-core operations

**Use Cases:**
- In-browser analytics
- Client-side data exploration
- Offline-capable applications
- Data visualization tools

### Apache Arrow Integration

**Zero-Copy Integration:**
- Rapid analysis of larger-than-memory datasets
- Works in Python and R
- Supports SQL and relational APIs

**Supported Arrow Objects:**
- Tables
- Datasets
- RecordBatchReaders
- Scanners

**Optimization:**
- Pushdown of filters and projections
- Only relevant columns/partitions read
- Partition elimination in Parquet files
- No data copying between Arrow and DuckDB

---

## 4. Ontologies & Data Models

### Internal Data Modeling

#### Table Storage
- **Columnar Layout:** Data physically stored by column
- **Page-Based:** Fixed-size pages managed by buffer manager
- **Compression:** Per-column compression strategies
- **Statistics:** Maintained at column and segment level

#### Schema Management

**System Catalogs:**
- `information_schema.tables` - All tables and views
- `information_schema.columns` - Column details
- `information_schema.schemata` - Schema information
- `duckdb_tables()` - DuckDB-specific table info
- `duckdb_views()` - View information
- `duckdb_indexes()` - Index information
- `duckdb_schemas()` - Schema details

**Common Commands:**
```sql
-- List tables
SHOW TABLES;
SHOW ALL TABLES;  -- across all schemas

-- Table structure
DESCRIBE table_name;
PRAGMA table_info('table_name');

-- Indexes
SHOW INDEXES;
```

#### Views and Materialization
- Standard SQL views (virtual)
- Materialized views support
- CTEs for query-scoped temporary results
- Recursive CTEs for hierarchical data

### Relationship to Other Databases

#### vs SQLite

**Similarities:**
- Embedded, in-process architecture
- No separate server process
- Zero configuration
- Single-file database (optional)
- Cross-platform compatibility

**Differences:**

| Feature | DuckDB | SQLite |
|---------|--------|--------|
| **Storage Model** | Columnar | Row-based |
| **Workload Optimization** | OLAP (Analytics) | OLTP (Transactions) |
| **Best For** | Aggregations, scans, analytics | Point queries, transactional writes |
| **Performance** | 10-50x faster for analytics | Better for single-row operations |
| **Memory Usage** | Higher for better performance | Extremely lightweight |

**When to Use Each:**
- **SQLite:** Mobile apps, IoT devices, browser caching, transactional workloads
- **DuckDB:** Data analysis, aggregations, reporting, data science workflows

**Complementary Use:**
- Can use both in the same application
- SQLite for transactional data, DuckDB for analytics

#### vs PostgreSQL

**Similarities:**
- SQL dialect (DuckDB is PostgreSQL-compatible)
- ACID compliance
- Rich feature set
- Window functions, CTEs, advanced SQL

**Differences:**

| Feature | DuckDB | PostgreSQL |
|---------|--------|-----------|
| **Architecture** | Embedded, in-process | Client-server |
| **Deployment** | No installation needed | Server installation required |
| **Concurrency** | Single-writer, optimistic | Multi-user, pessimistic locking |
| **Network** | None | Network overhead present |
| **Analytics** | Optimized (columnar) | Good but row-based |

**Performance:**
- DuckDB outperforms PostgreSQL on analytical benchmarks
- Particularly with large, wide datasets
- Columnar storage allows skipping irrelevant data
- Faster for aggregation-heavy queries

**When to Use Each:**
- **PostgreSQL:** Multi-user applications, B2B SaaS, distributed systems
- **DuckDB:** Local-first workflows, data exploration, single-user analytics

#### DuckDB as "PostgreSQL Dialect + Columnar"
- Familiar PostgreSQL syntax
- Analytical performance of columnar storage
- Best of both worlds for single-machine analytics

---

## 5. Best Practices & Common Patterns

### File Format Selection

**Parquet (Strongly Recommended):**
- Up to 600x faster than CSV
- Smaller disk footprint
- Built-in compression
- Columnar format matches DuckDB's architecture
- Preserves data types and schema

**CSV:**
- Use only for initial data ingestion
- Auto-detection available but slower
- Convert to Parquet for repeated querying

**JSON:**
- Good for semi-structured data
- Auto-detection of newline-delimited vs regular JSON
- Consider converting to Parquet for performance

**Best Practice:**
```sql
-- Convert CSV to Parquet
COPY (SELECT * FROM 'data.csv') TO 'data.parquet' (FORMAT parquet);

-- Then query Parquet
SELECT * FROM 'data.parquet' WHERE ...;
```

### Query Optimization

#### Use EXPLAIN for Analysis
```sql
-- View query plan without execution
EXPLAIN SELECT ...;

-- Profile query execution
EXPLAIN ANALYZE SELECT ...;
```

**Look For:**
- Hash joins (good) vs nested loop joins (bad)
- Filter pushdown applied
- Parallelism utilized
- Statistics used

#### Avoid SELECT *
```sql
-- Bad - scans all columns
SELECT * FROM large_table;

-- Good - only needed columns
SELECT column1, column2 FROM large_table;
```

#### Join Order Optimization
- Let DuckDB's optimizer handle join ordering
- Optimizer provides enormous performance benefits
- Avoids intermediate cardinality explosions
- Statistics propagation creates efficient filters

#### Prepared Statements
- Cache parsing and planning output
- Most beneficial for queries with runtime < 100ms
- Reduce overhead for repeated queries

```python
# Python example
stmt = con.prepare("SELECT * FROM tbl WHERE id = ?")
for id in ids:
    result = stmt.execute([id])
```

### Connection Management

**Reuse Connections:**
- DuckDB performs best with connection reuse
- Disconnecting/reconnecting incurs overhead
- Keep connection alive for multiple queries

**Example Anti-Pattern:**
```python
# Bad - creates new connection each time
for file in files:
    con = duckdb.connect('db.db')
    con.execute(f"SELECT * FROM '{file}'")
    con.close()

# Good - reuse connection
con = duckdb.connect('db.db')
for file in files:
    con.execute(f"SELECT * FROM '{file}'")
con.close()
```

### Memory Configuration

**Adjust Memory Limit:**
```sql
SET memory_limit = '4GB';
SET memory_limit = '80%';  -- 80% of available RAM (default)
```

**Monitor Memory:**
```sql
-- Check current settings
SELECT * FROM duckdb_settings() WHERE name = 'memory_limit';
```

### Data Loading Patterns

#### Direct File Querying
```sql
-- Query without loading
SELECT * FROM 'data.parquet' WHERE value > 100;

-- Join multiple files
SELECT * FROM 'sales_*.parquet' WHERE year = 2024;
```

#### Bulk Loading
```sql
-- CSV with auto-detection
CREATE TABLE my_table AS SELECT * FROM read_csv('data.csv', AUTO_DETECT=TRUE);

-- Parquet
CREATE TABLE my_table AS SELECT * FROM 'data.parquet';

-- Multiple files
CREATE TABLE combined AS SELECT * FROM 'data/*.parquet';
```

#### Incremental Loading
```sql
-- Append to existing table
INSERT INTO my_table SELECT * FROM 'new_data.parquet';

-- Upsert pattern (v1.4.0+)
MERGE INTO target USING source ON target.id = source.id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;
```

### Performance Optimization Patterns

#### Filter Early
```sql
-- Good - filter before join
SELECT * FROM
    (SELECT * FROM large_table WHERE date >= '2024-01-01') filtered
JOIN dimension ON filtered.key = dimension.key;
```

#### Use Appropriate Data Types
- Smaller types use less memory and are faster
- Use INTEGER instead of BIGINT when range allows
- DATE vs TIMESTAMP for date-only data

#### Partition Awareness
```sql
-- Take advantage of Parquet partitioning
SELECT * FROM 'data/year=*/month=*/*.parquet'
WHERE year = 2024 AND month = 11;
```

### When to Use DuckDB

**Ideal Use Cases:**
- Local-first workflows
- Jupyter notebooks and data exploration
- Ad hoc data analysis
- ETL and data transformation
- Processing large CSV/JSON files
- Machine learning feature engineering
- Log analysis and diagnostics
- Data quality validation in CI/CD
- Embedded analytics in applications
- Cost-effective analytics (vs cloud services)

**Not Ideal For:**
- Multi-user concurrent writes
- High-frequency transactional workloads
- Distributed systems requiring sharding
- When you need a database server
- Real-time operational databases

---

## 6. Real-World Use Cases

### Data Science Workflows

**Exploratory Data Analysis (EDA):**
```python
import duckdb
import pandas as pd

# Query large datasets efficiently
result = duckdb.query("""
    SELECT
        category,
        AVG(sales) as avg_sales,
        COUNT(*) as transaction_count
    FROM 'large_sales_data.parquet'
    WHERE year = 2024
    GROUP BY category
    ORDER BY avg_sales DESC
""").to_df()
```

**Benefits:**
- Works in Jupyter notebooks
- Handles larger-than-memory datasets
- SQL interface familiar to data scientists
- Direct integration with Pandas and Arrow

### ETL and Data Transformation

**Pipeline Example:**
```sql
-- Extract, transform, and load
COPY (
    SELECT
        customer_id,
        DATE_TRUNC('month', order_date) as month,
        SUM(amount) as monthly_total,
        COUNT(*) as order_count
    FROM read_csv('raw_orders/*.csv', AUTO_DETECT=TRUE)
    WHERE status = 'completed'
    GROUP BY customer_id, month
) TO 'processed/monthly_summary.parquet' (FORMAT parquet);
```

**Use Cases:**
- Pre-filtering before loading to data warehouse
- Data cleaning and validation
- Format conversion (CSV to Parquet)
- Aggregation and summarization

### Embedded Analytics

**Application Integration:**
```javascript
// Node.js embedded analytics
const duckdb = require('@duckdb/node-api');

class AnalyticsEngine {
    constructor(dbPath) {
        this.db = new duckdb.Database(dbPath);
        this.conn = this.db.connect();
    }

    async getDashboardMetrics(userId) {
        return await this.conn.run(`
            SELECT
                DATE_TRUNC('day', timestamp) as date,
                COUNT(*) as events,
                COUNT(DISTINCT session_id) as sessions
            FROM user_events
            WHERE user_id = $1
                AND timestamp >= CURRENT_DATE - INTERVAL 30 DAYS
            GROUP BY date
            ORDER BY date
        `, [userId]);
    }
}
```

### Log Analysis

**Processing Log Files:**
```sql
-- Analyze JSON logs
SELECT
    level,
    COUNT(*) as count,
    ARRAY_AGG(DISTINCT error_code) as error_codes
FROM read_json('logs/**/*.json', format='newline_delimited')
WHERE timestamp >= CURRENT_TIMESTAMP - INTERVAL 1 HOUR
    AND level IN ('ERROR', 'CRITICAL')
GROUP BY level;
```

**Benefits:**
- No need for Elasticsearch for basic analysis
- Pre-filter before loading to expensive services
- Local diagnostics and debugging

### Machine Learning Feature Engineering

**Feature Generation:**
```python
import duckdb

# Create training features
features = duckdb.query("""
    SELECT
        customer_id,
        -- Aggregated features
        COUNT(*) as total_orders,
        SUM(amount) as total_spent,
        AVG(amount) as avg_order_value,
        MAX(order_date) as last_order_date,
        -- Time-based features
        DATE_DIFF('day', MAX(order_date), CURRENT_DATE) as days_since_last_order,
        -- Window functions
        PERCENT_RANK() OVER (ORDER BY SUM(amount)) as spending_percentile
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL 365 DAYS
    GROUP BY customer_id
""").to_df()

# Use directly in scikit-learn, PyTorch, etc.
```

### Data Quality Validation

**CI/CD Integration:**
```sql
-- Validation queries
-- Check for nulls in required fields
SELECT COUNT(*) as null_count
FROM 'incoming_data.parquet'
WHERE customer_id IS NULL;

-- Validate data ranges
SELECT COUNT(*) as invalid_dates
FROM 'incoming_data.parquet'
WHERE order_date > CURRENT_DATE;

-- Schema validation
SELECT column_name, data_type
FROM (DESCRIBE SELECT * FROM 'incoming_data.parquet')
WHERE column_name IN ('customer_id', 'order_date', 'amount');
```

### Processing Large Files

**Efficient Large File Handling:**
```python
# Process 100GB CSV without loading into memory
duckdb.execute("""
    COPY (
        SELECT *
        FROM read_csv('huge_file.csv', AUTO_DETECT=TRUE)
        WHERE condition = 'something'
    ) TO 'filtered_output.parquet' (FORMAT parquet)
""")
```

---

## 7. Extensions & Ecosystem

### Core Extensions

**Official Extensions (Built and Signed by DuckDB Team):**

- **httpfs:** HTTP/HTTPS and S3 file system support
- **parquet:** Enhanced Parquet reading and writing
- **json:** JSON file support
- **icu:** International Components for Unicode
- **tpch:** TPC-H benchmark data generator
- **tpcds:** TPC-DS benchmark data generator
- **fts:** Full-text search
- **excel:** Excel file reading
- **spatial:** Geospatial/GIS functionality
- **delta:** Delta Lake format support
- **iceberg:** Apache Iceberg format support
- **azure:** Azure Blob Storage support
- **postgres_scanner:** Read from PostgreSQL databases
- **mysql_scanner:** Read from MySQL databases
- **sqlite_scanner:** Read from SQLite databases
- **substrait:** Query plan serialization

### Community Extensions

**Third-Party Extensions:**
- Maintained by community
- Available through DuckDB's extension repository
- Varied functionality and domain-specific features

**Installation:**
```sql
INSTALL extension_name;
LOAD extension_name;
```

**Example:**
```sql
-- Install and load spatial extension
INSTALL spatial;
LOAD spatial;

-- Use spatial functions
SELECT ST_Area(geom) as area
FROM read_parquet('geo_data.parquet');
```

### Spatial Extension

**Geospatial Capabilities:**

**Core Technology:**
- Based on GEOS (same as PostGIS)
- GDAL/OGR for format support
- PROJ for coordinate transformations

**Features:**
- GEOMETRY data type (Simple Features model)
- 100+ ST_ functions (PostGIS-compatible)
- Support for 50+ geospatial file formats
- Spatial operations: ST_Area, ST_Intersects, ST_Buffer, etc.

**Example Usage:**
```sql
INSTALL spatial;
LOAD spatial;

-- Read geospatial file
CREATE TABLE buildings AS
SELECT * FROM ST_Read('buildings.geojson');

-- Spatial query
SELECT name, ST_Area(geom) as area
FROM buildings
WHERE ST_Intersects(geom,
    ST_GeomFromText('POLYGON((...))')
);

-- Export to different format
COPY (SELECT * FROM buildings)
TO 'output.gpkg'
WITH (FORMAT GDAL, DRIVER 'GPKG');
```

### Data Lake Extensions

#### Delta Lake Extension

**Features:**
- Native Delta Lake support
- Developed with Databricks
- Uses delta-kernel-rs project

```sql
INSTALL delta;
LOAD delta;

SELECT * FROM delta_scan('s3://bucket/delta-table');
```

#### Apache Iceberg Extension

**Features:**
- Read Iceberg tables
- SQL interfaces
- Versioning support
- ACID transactions

```sql
INSTALL iceberg;
LOAD iceberg;

SELECT * FROM iceberg_scan('s3://bucket/iceberg-table');
```

#### DuckLake

**DuckDB's Lakehouse Format (Released May 2025):**
- Open-source lakehouse format
- Standard SQL databases for metadata
- Parquet for data storage
- Simplified lakehouse management

### Cloud Storage Extensions

**AWS S3 (httpfs extension):**
```sql
INSTALL httpfs;
LOAD httpfs;

SET s3_region='us-east-1';
SET s3_access_key_id='...';
SET s3_secret_access_key='...';

SELECT * FROM 's3://bucket/data.parquet';
```

**Azure (azure extension):**
```sql
INSTALL azure;
LOAD azure;

SELECT * FROM 'az://container/data.parquet';
```

### Database Scanner Extensions

**Query Other Databases:**

```sql
-- PostgreSQL
INSTALL postgres_scanner;
LOAD postgres_scanner;

SELECT * FROM postgres_scan('host=localhost dbname=mydb', 'table_name');

-- MySQL
INSTALL mysql_scanner;
LOAD mysql_scanner;

-- SQLite
INSTALL sqlite_scanner;
LOAD sqlite_scanner;

SELECT * FROM sqlite_scan('database.db', 'table_name');
```

**Use Cases:**
- Data migration
- Cross-database analytics
- ETL from operational databases
- Data validation

---

## Key Takeaways for LLM Training & Developer Tools

### 1. Primary Mental Model
- **DuckDB = SQLite for Analytics**
- Embedded, no server, but optimized for OLAP not OLTP
- PostgreSQL-compatible SQL with columnar storage

### 2. Core Architectural Insight
- Columnar storage + vectorized execution = analytical performance
- In-process = zero network overhead
- Flexible memory management = handles larger-than-memory datasets

### 3. Common Patterns

**File Processing:**
```sql
-- Query files directly
SELECT * FROM 'data.parquet' WHERE ...;
SELECT * FROM read_csv('data.csv');
SELECT * FROM read_json('data.json');

-- Multiple files
SELECT * FROM 'data/*.parquet';
```

**Data Transformation:**
```sql
-- Convert formats
COPY (SELECT * FROM 'input.csv') TO 'output.parquet' (FORMAT parquet);

-- Filter and export
COPY (SELECT * FROM ... WHERE ...) TO 'filtered.parquet';
```

**Integration with Data Science:**
```python
import duckdb
result = duckdb.query("SELECT ...").to_df()  # Returns Pandas DataFrame
```

### 4. Decision Framework

**Use DuckDB When:**
- Single-machine analytics
- Local-first workflows
- Processing files (Parquet, CSV, JSON)
- Embedded in applications
- Data science/ML pipelines
- ETL transformations
- Cost-sensitive workloads

**Don't Use DuckDB When:**
- Need multi-user concurrent writes
- Distributed system architecture
- High-frequency transactions
- Network-based database server required

### 5. Performance Tips
1. Always use Parquet when possible (up to 600x faster than CSV)
2. Reuse database connections
3. Use EXPLAIN ANALYZE to profile queries
4. Let optimizer handle join ordering
5. Select only needed columns (avoid SELECT *)
6. Configure memory appropriately (default 80% of RAM)

### 6. Modern Features (2025)
- Database encryption (v1.4.0)
- MERGE statement (v1.4.0)
- DuckLake format (v1.3.0)
- Local web UI (v1.2.1)
- UUID v7 support
- Native Delta Lake and Iceberg support

### 7. Ecosystem Integration
- Python: Native integration with Pandas, Arrow
- R: duckplyr (drop-in dplyr replacement)
- Node.js: @duckdb/node-api
- Browser: DuckDB-Wasm
- Cloud: S3, Azure, GCS support
- Databases: PostgreSQL, MySQL, SQLite scanners

---

## Additional Resources

### Official Documentation
- **Main Site:** https://duckdb.org/
- **Documentation:** https://duckdb.org/docs/
- **Blog:** https://duckdb.org/news
- **GitHub:** https://github.com/duckdb/duckdb

### Key Papers and Presentations
- "DuckDB-Wasm: Fast Analytical Processing for the Web" (CMU)
- "Memory Management in DuckDB" (2024)
- "Analytics-Optimized Concurrent Transactions" (2024)
- "DuckDB Spatial" (GeoPython 2024)

### Community Resources
- Discord: Active community support
- GitHub Discussions: Technical questions
- MotherDuck Blog: Use cases and tutorials
- Community Extensions Repository

---

**Report End**

This research document provides a comprehensive overview of DuckDB suitable for:
- Training large language models on database systems
- Creating developer tools and documentation
- Understanding modern analytical database design
- Making architectural decisions for data workloads
slack-gif-creatoranthropics/skillsKnowledge and utilities for creating animated GIFs optimized for Slack. Provides constraints, validation tools, and animation concepts. Use when users request animated GIFs for Slack like "make me a GIF of X doing Y for Slack."Stars: 198kUpdated Claude CodeCodexVerifiedInstall
ponytailDietrichGebert/ponytailLazy senior dev mode for any coding task: the smallest change that fully solves it, and a reply a busy human understands in one read.Stars: 194kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailHarvest every shortcut: comment into one debt ledger, so deferrals get tracked instead of forgotten. One-shot report.Stars: 194kUpdated Claude CodeCodexVerifiedInstall
ponytail-debtDietrichGebert/ponytailList every `shortcut:` comment (and older `ponytail:` ones) as a debt ledger. One-shot report, changes nothing. Use for "ponytail debt", "what did ponytail defer", "list the shortcuts", /ponytail-debt.Stars: 194kUpdated Claude CodeCodexVerifiedInstall