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]

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

• [Feature] Flexible Vector Dimension Infrastructure for Future Experiments #6090: [Feature] Flexible Vector Dimension Infrastructure for Future Experiments - discusses vector database infrastructure and pgvector capabilities
• Vector Database #5909: Vector Database - asks about vector database integration for improved codebase searching
• [FEATURE]: a plugin feature for people to port their database / sql / customized rag / llm cache ? #3376: [FEATURE] a plugin feature for people to port their database / sql / customized rag / llm cache - requests plugin infrastructure for custom database implementations
• [FEATURE] Sliding window context management for long-running sessions #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.

[pschiel]

proposal to introduce storage interface, supporting json/sqlite: #9012

postgres could be just another provider, selectable in your config

[adsharma]

Excellent. I'm looking forward to pushing my work to pypi as a new package (didn't hear back from the maintainer of pgserver after a week, no activity on github).

Will ping here once its on pypi and then we can look into writing an embedded postgres provider. Thank you for making the interface generic!

[pschiel]

Excellent. I'm looking forward to pushing my work to pypi as a new package (didn't hear back from the maintainer of pgserver after a week, no activity on github).

Will ping here once its on pypi and then we can look into writing an embedded postgres provider. Thank you for making the interface generic!

unfortunately it wasn't received well, their own branch prefers hardcoding sqlite with no interfance and an unconfigurable schema.

[adsharma]

https://github.com/Ladybug-Memory/pgembed - if you want to experiment with persisting session state with this backend.