13215d7ddb
Issue #46 (Phase A only — Phase B human rating still pending, issue stays open). Adds the data-collection half of the calibration milestone: - scripts/calibration_runner.sh — runs 20 fixed balanced-depth queries across 4 categories (factual, comparative, contradiction-prone, scope-edge), 5 each, capturing per-run logs to docs/stress-tests/M3.3-runs/. - scripts/calibration_collect.py — loads every persisted ResearchResult under ~/.marchwarden/traces/*.result.json and emits a markdown rating worksheet with one row per run. Recovers question text from each trace's start event and category from the run-log filename. - docs/stress-tests/M3.3-rating-worksheet.md — 22 runs (20 calibration + caffeine smoke + M3.2 multi-axis), with empty actual_rating columns for the human-in-the-loop scoring step. - docs/stress-tests/M3.3-runs/*.log — runtime logs from the calibration runner, kept as provenance. Gitignore updated with an exception carving stress-test logs out of the global *.log ignore. Note: M3.1's 4 runs predate #54 (full result persistence) and so are unrecoverable to the worksheet — only post-#54 runs have a result.json sibling. 22 rateable runs is still within the milestone target of 20–30. Phases B (human rating) and C (analysis + rubric + wiki update) follow in a later session. This issue stays open until both are done. Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
350 lines
39 KiB
Text
350 lines
39 KiB
Text
Researching: Compare PostgreSQL and SQLite for embedded analytics workloads.
|
||
|
||
{"question": "Compare PostgreSQL and SQLite for embedded analytics workloads.", "depth": "balanced", "max_iterations": null, "token_budget": null, "event": "ask_started", "logger": "marchwarden.cli", "level": "info", "timestamp": "2026-04-09T01:55:28.499294Z"}
|
||
{"transport": "stdio", "server": "marchwarden-web-researcher", "event": "mcp_server_starting", "logger": "marchwarden.mcp", "level": "info", "timestamp": "2026-04-09T01:55:29.256154Z"}
|
||
{"event": "Processing request of type CallToolRequest", "logger": "mcp.server.lowlevel.server", "level": "info", "timestamp": "2026-04-09T01:55:29.264747Z"}
|
||
{"question": "Compare PostgreSQL and SQLite for embedded analytics workloads.", "depth": "balanced", "max_iterations": 5, "token_budget": 20000, "model_id": "claude-sonnet-4-6", "event": "research_started", "trace_id": "01881015-61a9-4894-a723-4e1d8b7a7755", "researcher": "web", "logger": "marchwarden.researcher.web", "level": "info", "timestamp": "2026-04-09T01:55:29.297908Z"}
|
||
{"step": 1, "decision": "Beginning research: depth=balanced", "question": "Compare PostgreSQL and SQLite for embedded analytics workloads.", "context": "", "max_iterations": 5, "token_budget": 20000, "event": "start", "trace_id": "01881015-61a9-4894-a723-4e1d8b7a7755", "researcher": "web", "logger": "marchwarden.researcher.trace", "level": "info", "timestamp": "2026-04-09T01:55:29.298261Z"}
|
||
{"step": 2, "decision": "Starting iteration 1/5", "tokens_so_far": 0, "event": "iteration_start", "trace_id": "01881015-61a9-4894-a723-4e1d8b7a7755", "researcher": "web", "logger": "marchwarden.researcher.trace", "level": "info", "timestamp": "2026-04-09T01:55:29.298356Z"}
|
||
{"step": 9, "decision": "Starting iteration 2/5", "tokens_so_far": 1147, "event": "iteration_start", "trace_id": "01881015-61a9-4894-a723-4e1d8b7a7755", "researcher": "web", "logger": "marchwarden.researcher.trace", "level": "info", "timestamp": "2026-04-09T01:55:38.957520Z"}
|
||
{"step": 16, "decision": "Starting iteration 3/5", "tokens_so_far": 8781, "event": "iteration_start", "trace_id": "01881015-61a9-4894-a723-4e1d8b7a7755", "researcher": "web", "logger": "marchwarden.researcher.trace", "level": "info", "timestamp": "2026-04-09T01:55:45.812510Z"}
|
||
{"step": 23, "decision": "Starting iteration 4/5", "tokens_so_far": 18324, "event": "iteration_start", "trace_id": "01881015-61a9-4894-a723-4e1d8b7a7755", "researcher": "web", "logger": "marchwarden.researcher.trace", "level": "info", "timestamp": "2026-04-09T01:56:00.757335Z"}
|
||
{"step": 28, "decision": "Token budget reached before iteration 5: 34877/20000", "event": "budget_exhausted", "trace_id": "01881015-61a9-4894-a723-4e1d8b7a7755", "researcher": "web", "logger": "marchwarden.researcher.trace", "level": "info", "timestamp": "2026-04-09T01:56:03.990690Z"}
|
||
{"step": 29, "decision": "Beginning synthesis of gathered evidence", "evidence_count": 35, "iterations_run": 4, "tokens_used": 34877, "event": "synthesis_start", "trace_id": "01881015-61a9-4894-a723-4e1d8b7a7755", "researcher": "web", "logger": "marchwarden.researcher.trace", "level": "info", "timestamp": "2026-04-09T01:56:03.990849Z"}
|
||
{"step": 30, "decision": "Parsed synthesis JSON successfully", "duration_ms": 78663, "event": "synthesis_complete", "trace_id": "01881015-61a9-4894-a723-4e1d8b7a7755", "researcher": "web", "logger": "marchwarden.researcher.trace", "level": "info", "timestamp": "2026-04-09T01:57:20.513065Z"}
|
||
{"step": 48, "decision": "Research complete", "confidence": 0.88, "citation_count": 10, "gap_count": 3, "discovery_count": 4, "total_duration_sec": 114.441, "event": "complete", "trace_id": "01881015-61a9-4894-a723-4e1d8b7a7755", "researcher": "web", "logger": "marchwarden.researcher.trace", "level": "info", "timestamp": "2026-04-09T01:57:20.536570Z"}
|
||
{"confidence": 0.88, "citations": 10, "gaps": 3, "discovery_events": 4, "tokens_used": 61699, "iterations_run": 4, "wall_time_sec": 111.20896744728088, "budget_exhausted": true, "event": "research_completed", "trace_id": "01881015-61a9-4894-a723-4e1d8b7a7755", "researcher": "web", "logger": "marchwarden.researcher.web", "level": "info", "timestamp": "2026-04-09T01:57:20.538075Z"}
|
||
{"error": "[Errno 13] Permission denied: '/home/micro/.marchwarden/costs.jsonl'", "event": "cost_ledger_write_failed", "trace_id": "01881015-61a9-4894-a723-4e1d8b7a7755", "researcher": "web", "logger": "marchwarden.researcher.web", "level": "warning", "timestamp": "2026-04-09T01:57:20.546420Z"}
|
||
{"event": "Processing request of type ListToolsRequest", "logger": "mcp.server.lowlevel.server", "level": "info", "timestamp": "2026-04-09T01:57:20.668474Z"}
|
||
{"trace_id": "01881015-61a9-4894-a723-4e1d8b7a7755", "confidence": 0.88, "citations": 10, "tokens_used": 61699, "wall_time_sec": 111.20896744728088, "event": "ask_completed", "logger": "marchwarden.cli", "level": "info", "timestamp": "2026-04-09T01:57:21.511598Z"}
|
||
╭─────────────────────────────────── Answer ───────────────────────────────────╮
|
||
│ PostgreSQL and SQLite differ substantially for embedded analytics workloads │
|
||
│ across architecture, concurrency, feature set, and performance │
|
||
│ characteristics. │
|
||
│ │
|
||
│ **Architecture:** SQLite is a serverless, embedded database engine that │
|
||
│ reads/writes a single file on disk, making it highly portable and │
|
||
│ zero-configuration. PostgreSQL uses a client-server architecture requiring a │
|
||
│ separate server process, which enables it to scale and handle multiple │
|
||
│ concurrent clients efficiently via Multi-Version Concurrency Control (MVCC) │
|
||
│ [Source 5]. For embedded analytics specifically, SQLite's in-process nature │
|
||
│ eliminates network overhead, which can yield significant read performance │
|
||
│ advantages in local scenarios [Source 31]. │
|
||
│ │
|
||
│ **Concurrency:** SQLite allows multiple concurrent readers but only one │
|
||
│ writer at a time, using file-level locking. This single-writer model is a │
|
||
│ significant bottleneck for write-heavy or high-concurrency analytical │
|
||
│ ingestion workloads [Source 24, Source 25]. PostgreSQL's MVCC ensures │
|
||
│ readers and writers do not block each other, making it far superior for │
|
||
│ multi-user or mixed OLTP/OLAP environments [Source 5]. Turso's work on │
|
||
│ concurrent writes for SQLite demonstrates the community recognizes this │
|
||
│ limitation, achieving up to 4x write throughput improvements over vanilla │
|
||
│ SQLite [Source 24]. │
|
||
│ │
|
||
│ **OLAP/Analytical Performance:** SQLite is row-oriented and was designed │
|
||
│ primarily as a world-class OLTP engine. For analytical workloads—complex │
|
||
│ aggregations, percentile calculations, large scans—SQLite struggles │
|
||
│ significantly. A cited benchmark shows a single percentile query over 13M │
|
||
│ rows taking ~4 seconds in SQLite [Source 6]. PostgreSQL, while also │
|
||
│ row-oriented, supports more advanced SQL features (window functions, complex │
|
||
│ joins, partitioning) and can be tuned for analytics [Source 22]. However, │
|
||
│ PostgreSQL itself hits a 'Postgres Wall' for heavy analytical workloads when │
|
||
│ row-scanning large datasets exceeds available RAM [Source 13]. Neither │
|
||
│ SQLite nor PostgreSQL is natively columnar; PostgreSQL can be extended with │
|
||
│ columnar storage extensions for better OLAP performance [Source 23]. │
|
||
│ │
|
||
│ **Feature Set:** PostgreSQL offers a richer feature set including more data │
|
||
│ types, advanced indexing, role-based access control, JSON/array support, │
|
||
│ geospatial extensions (PostGIS), and time-series extensions. SQLite uses │
|
||
│ dynamic typing and has a simpler, more limited feature set—easier to use but │
|
||
│ potentially limiting for complex analytical applications [Source 5, Source │
|
||
│ 1]. │
|
||
│ │
|
||
│ **Recommended Alternatives for Embedded Analytics:** DuckDB is widely cited │
|
||
│ as the superior embedded engine for analytical workloads, outperforming both │
|
||
│ SQLite and PostgreSQL on OLAP queries by a large margin [Source 6, Source │
|
||
│ 2]. For embedded analytics use cases requiring columnar processing, DuckDB │
|
||
│ or Stoolap (a Rust-based embedded OLAP engine) are more purpose-built │
|
||
│ options. Stoolap benchmarks show up to 138x faster analytical query │
|
||
│ performance versus SQLite [Source 9]. │
|
||
│ │
|
||
│ **Summary:** SQLite wins for lightweight, read-heavy, single-writer, │
|
||
│ local/embedded OLTP workloads where portability and zero configuration │
|
||
│ matter. PostgreSQL wins for multi-user, concurrent, complex-query │
|
||
│ environments. For true embedded analytics workloads (large-scale │
|
||
│ aggregations, complex OLAP queries), neither is optimal—DuckDB or a hybrid │
|
||
│ architecture (PostgreSQL as system-of-record + DuckDB as analytical engine) │
|
||
│ is the modern recommended approach. │
|
||
╰──────────────────────────────────────────────────────────────────────────────╯
|
||
Citations
|
||
┏━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━┓
|
||
┃ # ┃ Title / Locator ┃ Excerpt ┃ Conf ┃
|
||
┡━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━┩
|
||
│ 1 │ SQLite vs. PostgreSQL: The │ PostgreSQL is a client-server │ 0.97 │
|
||
│ │ key differences and │ database system... This │ │
|
||
│ │ advantages of each │ architecture enables │ │
|
||
│ │ https://databaseschool.com/ar │ PostgreSQL to scale and handle │ │
|
||
│ │ ticles/sqlite-vs-postgresql-t │ multiple concurrent clients │ │
|
||
│ │ he-key-differences-and-advant │ efficiently... SQLite is a │ │
|
||
│ │ ages-of-each │ serverless database engine. It │ │
|
||
│ │ │ functions as a lightweight │ │
|
||
│ │ │ library embedded directly into │ │
|
||
│ │ │ applications... SQLite's │ │
|
||
│ │ │ concurrency model is more │ │
|
||
│ │ │ restrictive: while it allows │ │
|
||
│ │ │ multiple readers, only one │ │
|
||
│ │ │ process can write to the │ │
|
||
│ │ │ database at a time. │ │
|
||
├─────┼───────────────────────────────┼────────────────────────────────┼───────┤
|
||
│ 2 │ Making -SQLite- Analytics │ In some analytical queries │ 0.95 │
|
||
│ │ Great Again! – Oldmoe's blog │ SQLite will struggle to │ │
|
||
│ │ https://oldmoe.blog/2025/03/1 │ perform compared to other OLAP │ │
|
||
│ │ 2/making-sqlite-analytics-gre │ oriented engines like DuckDB. │ │
|
||
│ │ at-again/ │ Consider the following │ │
|
||
│ │ │ scenario: You have a table │ │
|
||
│ │ │ with 13M entries of latency │ │
|
||
│ │ │ data, and you want to │ │
|
||
│ │ │ determine the following │ │
|
||
│ │ │ percentiles: p50, p95, p99... │ │
|
||
│ │ │ After around 4 seconds you │ │
|
||
│ │ │ will see the result. │ │
|
||
├─────┼───────────────────────────────┼────────────────────────────────┼───────┤
|
||
│ 3 │ DuckDB vs. Postgres for │ That 'quick' analytical query │ 0.95 │
|
||
│ │ embedded analytics: How to │ powering a customer-facing │ │
|
||
│ │ choose (and when to use a │ dashboard now takes 5 seconds, │ │
|
||
│ │ hybrid architecture) │ up from 50 milliseconds. Then │ │
|
||
│ │ https://motherduck.com/learn- │ thirty seconds. Then it times │ │
|
||
│ │ more/duckdb-vs-postgres-embed │ out. You've hit the 'Postgres │ │
|
||
│ │ ded-analytics/ │ Wall.' This isn't a Postgres │ │
|
||
│ │ │ failure. It's an architectural │ │
|
||
│ │ │ mismatch. Postgres processes │ │
|
||
│ │ │ analytics using the same │ │
|
||
│ │ │ row-oriented logic designed │ │
|
||
│ │ │ for transaction safety. │ │
|
||
├─────┼───────────────────────────────┼────────────────────────────────┼───────┤
|
||
│ 4 │ Beyond the Single-Writer │ SQLite has a single-writer │ 0.93 │
|
||
│ │ Limitation with Turso's │ transaction model, which means │ │
|
||
│ │ Concurrent Writes │ whenever a transaction writes │ │
|
||
│ │ https://turso.tech/blog/beyon │ to the database, no other │ │
|
||
│ │ d-the-single-writer-limitatio │ write transactions can make │ │
|
||
│ │ n-with-tursos-concurrent-writ │ progress until that │ │
|
||
│ │ es │ transaction is complete... │ │
|
||
│ │ │ When concurrent writes are │ │
|
||
│ │ │ used, we achieve up to 4x the │ │
|
||
│ │ │ write throughput of SQLite, │ │
|
||
│ │ │ while also removing the │ │
|
||
│ │ │ dreaded SQLITE_BUSY error. │ │
|
||
├─────┼───────────────────────────────┼────────────────────────────────┼───────┤
|
||
│ 5 │ Stoolap vs. SQLite: Comparing │ OLAP (Online Analytical │ 0.92 │
|
||
│ │ Rust OLAP and Traditional │ Processing) systems are │ │
|
||
│ │ OLTP Databases | Better Stack │ designed for a completely │ │
|
||
│ │ Community │ different purpose. OLAP │ │
|
||
│ │ https://betterstack.com/commu │ databases are optimized for │ │
|
||
│ │ nity/guides/ai/stoolap-vs-sql │ complex queries and data │ │
|
||
│ │ ite/ │ analysis... Most standard │ │
|
||
│ │ │ application databases, │ │
|
||
│ │ │ including SQLite, PostgreSQL, │ │
|
||
│ │ │ and MySQL, are classified as │ │
|
||
│ │ │ OLTP (Online Transaction │ │
|
||
│ │ │ Processing) systems. │ │
|
||
├─────┼───────────────────────────────┼────────────────────────────────┼───────┤
|
||
│ 6 │ Postgres Tuning & Performance │ Analytics or OLAP activity │ 0.91 │
|
||
│ │ for Analytics Data | Crunchy │ typically involves much │ │
|
||
│ │ Data Blog │ longer, more complex queries │ │
|
||
│ │ https://www.crunchydata.com/b │ than OLTP activity, joining │ │
|
||
│ │ log/postgres-tuning-and-perfo │ data from multiple tables, and │ │
|
||
│ │ rmance-for-analytics-data │ working on large data sets. │ │
|
||
│ │ │ This means it's very resource │ │
|
||
│ │ │ intensive. Without careful │ │
|
||
│ │ │ planning and tuning, you can │ │
|
||
│ │ │ find yourself with analytics │ │
|
||
│ │ │ queries that not only take far │ │
|
||
│ │ │ too long to run, but also slow │ │
|
||
│ │ │ down your existing │ │
|
||
│ │ │ application. │ │
|
||
├─────┼───────────────────────────────┼────────────────────────────────┼───────┤
|
||
│ 7 │ Postgres Columnar Storage: 4 │ PostgreSQL is a row-oriented │ 0.90 │
|
||
│ │ Popular Extensions and a │ database by design, meaning it │ │
|
||
│ │ Quick Tutorial │ stores data tuple-by-tuple... │ │
|
||
│ │ https://www.epsio.io/blog/pos │ This structure is suitable for │ │
|
||
│ │ tgres-columnar-storage-4-popu │ transactional workloads but │ │
|
||
│ │ lar-extensions-and-a-quick-tu │ not optimized for analytical │ │
|
||
│ │ torial │ queries that typically scan │ │
|
||
│ │ │ large volumes of data across a │ │
|
||
│ │ │ few columns... While │ │
|
||
│ │ │ PostgreSQL does not natively │ │
|
||
│ │ │ support columnar storage, │ │
|
||
│ │ │ several extensions and │ │
|
||
│ │ │ external tools introduce │ │
|
||
│ │ │ columnar capabilities. │ │
|
||
├─────┼───────────────────────────────┼────────────────────────────────┼───────┤
|
||
│ 8 │ SQLite vs PostgreSQL │ SQLite was faster. Of course │ 0.88 │
|
||
│ │ Performance & Comparison | │ it was. Writing to a local │ │
|
||
│ │ Pythonic AF │ file inside the same process │ │
|
||
│ │ https://medium.com/pythonic-a │ will almost always be faster │ │
|
||
│ │ f/sqlite-vs-postgresql-perfor │ than sending queries to a │ │
|
||
│ │ mance-comparison-46ba1d39c9c8 │ server. │ │
|
||
├─────┼───────────────────────────────┼────────────────────────────────┼───────┤
|
||
│ 9 │ Everyone Is Wrong About │ why SQLite is often the │ 0.80 │
|
||
│ │ SQLite (Here's When It Beats │ superior production choice for │ │
|
||
│ │ Postgres) │ read-heavy, single-server, and │ │
|
||
│ │ https://www.youtube.com/watch │ edge workloads ... SQLite vs │ │
|
||
│ │ ?v=t20KyfjtUs4 │ PostgreSQL Performance. │ │
|
||
├─────┼───────────────────────────────┼────────────────────────────────┼───────┤
|
||
│ 10 │ SQLite SO MUCH FASTER than │ Of course, with the advent of │ 0.82 │
|
||
│ │ Postgres - Reddit │ DuckDB, you use DuckDB for │ │
|
||
│ │ https://www.reddit.com/r/sqli │ data analysis tasks since it │ │
|
||
│ │ te/comments/1gu219r/sqlite_so │ can be faster than either │ │
|
||
│ │ _much_faster_than_postgres/ │ SQLite or PostgreSQL in those │ │
|
||
└─────┴───────────────────────────────┴────────────────────────────────┴───────┘
|
||
Gaps
|
||
┏━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
|
||
┃ Category ┃ Topic ┃ Detail ┃
|
||
┡━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
|
||
│ source_not_found │ Quantitative head-to-head │ Most benchmarks found │
|
||
│ │ benchmark of SQLite vs │ compare SQLite vs │
|
||
│ │ PostgreSQL specifically on │ PostgreSQL on OLTP │
|
||
│ │ analytical queries (not │ (reads/writes of individual │
|
||
│ │ just OLTP) │ rows) or compare each │
|
||
│ │ │ individually to │
|
||
│ │ │ DuckDB/Stoolap on OLAP. A │
|
||
│ │ │ direct, rigorous benchmark │
|
||
│ │ │ of SQLite vs PostgreSQL on │
|
||
│ │ │ complex analytical queries │
|
||
│ │ │ (GROUP BY, window │
|
||
│ │ │ functions, aggregations │
|
||
│ │ │ over millions of rows) was │
|
||
│ │ │ not surfaced in the │
|
||
│ │ │ evidence. │
|
||
├──────────────────┼─────────────────────────────┼─────────────────────────────┤
|
||
│ source_not_found │ PostgreSQL columnar │ While columnar extensions │
|
||
│ │ extension performance vs │ for PostgreSQL (e.g., Citus │
|
||
│ │ SQLite for embedded │ columnar, hydra) are │
|
||
│ │ analytics │ mentioned, no direct │
|
||
│ │ │ benchmark comparing │
|
||
│ │ │ PostgreSQL-with-columnar-ex │
|
||
│ │ │ tension vs SQLite for │
|
||
│ │ │ embedded analytical │
|
||
│ │ │ workloads was found. │
|
||
├──────────────────┼─────────────────────────────┼─────────────────────────────┤
|
||
│ source_not_found │ SQLite WAL mode impact on │ WAL mode is mentioned as │
|
||
│ │ analytical query │ improving concurrent │
|
||
│ │ performance │ read/write behavior in │
|
||
│ │ │ SQLite, but its specific │
|
||
│ │ │ impact on analytical query │
|
||
│ │ │ throughput in embedded │
|
||
│ │ │ scenarios was not │
|
||
│ │ │ quantified in the evidence. │
|
||
└──────────────────┴─────────────────────────────┴─────────────────────────────┘
|
||
Discovery Events
|
||
┏━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┓
|
||
┃ ┃ Suggested ┃ ┃ ┃
|
||
┃ Type ┃ Researcher ┃ Query ┃ Reason ┃
|
||
┡━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━┩
|
||
│ related_research │ database │ DuckDB vs SQLite │ DuckDB is │
|
||
│ │ │ vs PostgreSQL │ consistently │
|
||
│ │ │ analytical │ cited as │
|
||
│ │ │ benchmark OLAP │ outperforming │
|
||
│ │ │ embedded 2024 │ both for │
|
||
│ │ │ 2025 │ analytics; a │
|
||
│ │ │ │ rigorous │
|
||
│ │ │ │ three-way │
|
||
│ │ │ │ comparison would │
|
||
│ │ │ │ better answer the │
|
||
│ │ │ │ embedded │
|
||
│ │ │ │ analytics │
|
||
│ │ │ │ question. │
|
||
├──────────────────┼───────────────────┼───────────────────┼───────────────────┤
|
||
│ related_research │ database │ SQLite past │ The VLDB paper on │
|
||
│ │ │ present future │ SQLite's │
|
||
│ │ │ VLDB paper bloom │ past/present/futu │
|
||
│ │ │ filter analytical │ re is cited │
|
||
│ │ │ performance 2022 │ multiple times as │
|
||
│ │ │ │ authoritative on │
|
||
│ │ │ │ SQLite's │
|
||
│ │ │ │ analytical │
|
||
│ │ │ │ limitations; │
|
||
│ │ │ │ accessing it │
|
||
│ │ │ │ directly would │
|
||
│ │ │ │ strengthen │
|
||
│ │ │ │ claims. │
|
||
├──────────────────┼───────────────────┼───────────────────┼───────────────────┤
|
||
│ related_research │ database │ pg_duckdb │ The motherduck │
|
||
│ │ │ extension │ article │
|
||
│ │ │ PostgreSQL │ references │
|
||
│ │ │ embedded │ pg_duckdb as a │
|
||
│ │ │ analytics │ key tool for │
|
||
│ │ │ performance │ hybrid │
|
||
│ │ │ hybrid │ Postgres+DuckDB │
|
||
│ │ │ architecture │ analytics; │
|
||
│ │ │ │ benchmarks for │
|
||
│ │ │ │ this approach │
|
||
│ │ │ │ were not found. │
|
||
├──────────────────┼───────────────────┼───────────────────┼───────────────────┤
|
||
│ new_source │ null │ Stoolap embedded │ Stoolap is an │
|
||
│ │ │ OLAP Rust │ emerging embedded │
|
||
│ │ │ database │ OLAP engine │
|
||
│ │ │ benchmark SQLite │ (Rust) claiming │
|
||
│ │ │ PostgreSQL │ 138x speedup over │
|
||
│ │ │ │ SQLite; it's a │
|
||
│ │ │ │ relevant new │
|
||
│ │ │ │ entrant to the │
|
||
│ │ │ │ embedded │
|
||
│ │ │ │ analytics space. │
|
||
└──────────────────┴───────────────────┴───────────────────┴───────────────────┘
|
||
Open Questions
|
||
┏━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
|
||
┃ Priority ┃ Question ┃ Context ┃
|
||
┡━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
|
||
│ high │ At what data volume does │ The evidence shows SQLite │
|
||
│ │ SQLite's analytical performance │ struggles at 13M rows for │
|
||
│ │ become unacceptably slow │ percentile queries (~4s), but │
|
||
│ │ compared to PostgreSQL for │ no clear threshold or scaling │
|
||
│ │ typical embedded analytics │ curve vs PostgreSQL was found. │
|
||
│ │ workloads? │ │
|
||
├──────────┼─────────────────────────────────┼─────────────────────────────────┤
|
||
│ high │ Does enabling WAL mode and │ Hacker News discussion mentions │
|
||
│ │ tuning SQLite │ WAL + synchronous=NORMAL as │
|
||
│ │ (synchronous=NORMAL, page size, │ approaching 'line speed with IO │
|
||
│ │ etc.) meaningfully close the │ subsystem' for writes, but │
|
||
│ │ analytical performance gap with │ analytical query impact is │
|
||
│ │ PostgreSQL? │ unclear. │
|
||
├──────────┼─────────────────────────────────┼─────────────────────────────────┤
|
||
│ medium │ Is a hybrid architecture │ The Postgres+DuckDB hybrid is │
|
||
│ │ (SQLite for OLTP + DuckDB for │ well-documented, but an │
|
||
│ │ OLAP, sharing the same data) │ SQLite+DuckDB embedded hybrid │
|
||
│ │ practical for embedded │ (for truly serverless apps) is │
|
||
│ │ applications, and how does it │ less explored in the evidence. │
|
||
│ │ compare to using PostgreSQL │ │
|
||
│ │ alone? │ │
|
||
├──────────┼─────────────────────────────────┼─────────────────────────────────┤
|
||
│ medium │ How do PostgreSQL columnar │ PostgreSQL columnar extensions │
|
||
│ │ storage extensions (e.g., │ are mentioned as improving OLAP │
|
||
│ │ Hydra, Citus columnar) perform │ performance, but no direct │
|
||
│ │ for embedded analytics compared │ comparison to SQLite in │
|
||
│ │ to native SQLite? │ embedded scenarios was found. │
|
||
├──────────┼─────────────────────────────────┼─────────────────────────────────┤
|
||
│ medium │ What is the operational │ SQLite's binary is ~500KB vs │
|
||
│ │ overhead (memory, disk, setup │ PostgreSQL requiring a server │
|
||
│ │ complexity) of running │ process; for edge/IoT embedded │
|
||
│ │ PostgreSQL vs SQLite in a truly │ analytics, resource constraints │
|
||
│ │ embedded edge or mobile │ may be the deciding factor. │
|
||
│ │ environment? │ │
|
||
└──────────┴─────────────────────────────────┴─────────────────────────────────┘
|
||
╭───────────────────────────────── Confidence ─────────────────────────────────╮
|
||
│ Overall: 0.88 │
|
||
│ Corroborating sources: 10 │
|
||
│ Source authority: medium │
|
||
│ Contradiction detected: False │
|
||
│ Query specificity match: 0.82 │
|
||
│ Budget status: spent │
|
||
│ Recency: current │
|
||
╰──────────────────────────────────────────────────────────────────────────────╯
|
||
╭──────────────────────────────────── Cost ────────────────────────────────────╮
|
||
│ Tokens: 61699 │
|
||
│ Iterations: 4 │
|
||
│ Wall time: 111.21s │
|
||
│ Model: claude-sonnet-4-6 │
|
||
╰──────────────────────────────────────────────────────────────────────────────╯
|
||
|
||
trace_id: 01881015-61a9-4894-a723-4e1d8b7a7755
|