sqlite vs embedded postgres

[adsharma]

Question

Have you considered using an embedded postgres server such as:

https://github.com/adsharma/pgserver/tree/pg18_2_39

over sqlite for sessions, messsages and file history?

This would give you access to pgvector and Tigerdata's pgvectorscale (DiskANN like indexing).

[github-actions[bot]]

This issue might be a duplicate of existing issues. Please check:

• #6090: [Feature] Flexible Vector Dimension Infrastructure for Future Experiments - discusses vector database infrastructure and pgvector capabilities
• #5909: Vector Database - asks about vector database integration for improved codebase searching
• #3376: [FEATURE] a plugin feature for people to port their database / sql / customized rag / llm cache - requests plugin infrastructure for custom database implementations
• #4659: [FEATURE] Sliding window context management for long-running sessions - addresses session/message storage improvements and scalability

Feel free to ignore if none of these address your specific case.

[rekram1-node]

we will prolly start using sqlite instead soon

[adsharma]

If opencode evolves into a general purpose platform beyond coding, people may have a lot of state sitting around.

Scalability: pgvector > sqlite-vec

The main reason people prefer sqlite is the embedded nature of the solution. That advantage goes away with pgserver and opens up the door to other postgres extensions like pgvectorscale and beyond.

My motivation is to implement something like https://github.com/yichuan-w/LEANN like solution on top of general purpose session storage.