anomalyco/opencode
1
0
Fork 0
mirror of https://github.com/anomalyco/opencode.git synced 2026-07-22 02:05:26 -04:00
Code Issues 5.9k Packages Projects Releases 19 Wiki Activity

sqlite vs embedded postgres #4764

New Issue
Closed
opened 2026-02-16 17:45:20 -05:00 by yindo · 11 comments
yindo commented 2026-02-16 17:45:20 -05:00
Owner
Copy Link
Copy Source

Originally created by @adsharma on GitHub (Jan 11, 2026).

Originally assigned to: @thdxr on GitHub.

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).

Originally created by @adsharma on GitHub (Jan 11, 2026). Originally assigned to: @thdxr on GitHub. ### 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).
yindo closed this issue 2026-02-16 17:45:20 -05:00
yindo commented 2026-02-16 17:45:20 -05:00
Author
Owner
Copy Link
Copy Source

@github-actions[bot] commented on GitHub (Jan 11, 2026):

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.

@github-actions[bot] commented on GitHub (Jan 11, 2026): 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.
yindo commented 2026-02-16 17:45:20 -05:00
Author
Owner
Copy Link
Copy Source

@rekram1-node commented on GitHub (Jan 11, 2026):

we will prolly start using sqlite instead soon

@rekram1-node commented on GitHub (Jan 11, 2026): we will prolly start using sqlite instead soon
yindo commented 2026-02-16 17:45:20 -05:00
Author
Owner
Copy Link
Copy Source

@adsharma commented on GitHub (Jan 11, 2026):

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.

@adsharma commented on GitHub (Jan 11, 2026): 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.
yindo commented 2026-02-16 17:45:20 -05:00
Author
Owner
Copy Link
Copy Source

@pschiel commented on GitHub (Jan 16, 2026):

proposal to introduce storage interface, supporting json/sqlite: https://github.com/anomalyco/opencode/pull/9012

postgres could be just another provider, selectable in your config

@pschiel commented on GitHub (Jan 16, 2026): proposal to introduce storage interface, supporting json/sqlite: https://github.com/anomalyco/opencode/pull/9012 postgres could be just another provider, selectable in your config
yindo commented 2026-02-16 17:45:20 -05:00
Author
Owner
Copy Link
Copy Source

@adsharma commented on GitHub (Jan 16, 2026):

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!

@adsharma commented on GitHub (Jan 16, 2026): 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!
yindo commented 2026-02-16 17:45:20 -05:00
Author
Owner
Copy Link
Copy Source

@pschiel commented on GitHub (Jan 20, 2026):

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.

@pschiel commented on GitHub (Jan 20, 2026): > 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.
yindo commented 2026-02-16 17:45:20 -05:00
Author
Owner
Copy Link
Copy Source

@adsharma commented on GitHub (Jan 21, 2026):

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

@adsharma commented on GitHub (Jan 21, 2026): https://github.com/Ladybug-Memory/pgembed - if you want to experiment with persisting session state with this backend.
yindo commented 2026-02-16 17:45:20 -05:00
Author
Owner
Copy Link
Copy Source

@Rezrazi commented on GitHub (Feb 11, 2026):

@pschiel what was the reason your approach was dismissed? I do share the opinion of having a an interface for storage can benefit the project as it can allow for more distributed setups that can access and share the same history.

@Rezrazi commented on GitHub (Feb 11, 2026): @pschiel what was the reason your approach was dismissed? I do share the opinion of having a an interface for storage can benefit the project as it can allow for more distributed setups that can access and share the same history.
yindo commented 2026-02-16 17:45:20 -05:00
Author
Owner
Copy Link
Copy Source

@adsharma commented on GitHub (Feb 11, 2026):

From an end user benefit point of view, the access to postgres extensions is a much bigger deal. But there is a perception that for embedded use sqlite works and postgres doesn't. Don't know where it comes from (other than familiarity with embedded sqlite).

@adsharma commented on GitHub (Feb 11, 2026): From an end user benefit point of view, the access to postgres extensions is a much bigger deal. But there is a perception that for embedded use sqlite works and postgres doesn't. Don't know where it comes from (other than familiarity with embedded sqlite).
yindo commented 2026-02-16 17:45:20 -05:00
Author
Owner
Copy Link
Copy Source

@thdxr commented on GitHub (Feb 14, 2026):

sqlite is a better fit for the direction we're going, also maintaining agnostic storage backends is way too much overhead. you can replicate sqlite db into whatever you need

@thdxr commented on GitHub (Feb 14, 2026): sqlite is a better fit for the direction we're going, also maintaining agnostic storage backends is way too much overhead. you can replicate sqlite db into whatever you need
yindo commented 2026-02-16 17:45:21 -05:00
Author
Owner
Copy Link
Copy Source

@adsharma commented on GitHub (Feb 14, 2026):

Similar discussion and decision here: https://github.com/roborev-dev/roborev/issues/213

My guess is that over time opencode will support hosted postgres (shared coding agents). And similar to roborev-dev, will pursue a dual database strategy. This is likely based on the maintainer belief that:

  • SQLite is a good enough SQL implementation (which I agree)
  • SQLite has good enough vector search (I'm not sure if this is true)
  • SQLite has lower overhead in an embedded environment (resource usage, startup time)
  • SQLite provides a path to knowledge graphs and deterministic retrieval (As a maintainer of a graphdb, I'm biased)

Pgembed is built around the belief that:

  • over time, the richer postgres extension ecosystem will provide customer benefit (pgvector, pgvectorscale)
  • the resource usage gap can be closed (added support for alpine containers recently)
  • gentler upgrade path when you outgrow local storage and want to explore hosted postgres for multi-agent state

Please show up on our discord to discuss this topic further. Nanobot on pgembed is a proof of concept.

@adsharma commented on GitHub (Feb 14, 2026): Similar discussion and decision here: https://github.com/roborev-dev/roborev/issues/213 My guess is that over time opencode will support hosted postgres (shared coding agents). And similar to roborev-dev, will pursue a dual database strategy. This is likely based on the maintainer belief that: - SQLite is a good enough SQL implementation (which I agree) - SQLite has good enough vector search (I'm not sure if this is true) - SQLite has lower overhead in an embedded environment (resource usage, startup time) - SQLite provides a path to knowledge graphs and deterministic retrieval (As a maintainer of a graphdb, I'm biased) Pgembed is built around the belief that: - over time, the richer postgres extension ecosystem will provide customer benefit (pgvector, pgvectorscale) - the resource usage gap can be closed (added support for alpine containers recently) - gentler upgrade path when you outgrow local storage and want to explore hosted postgres for multi-agent state Please show up on our discord to discuss this topic further. [Nanobot on pgembed](https://github.com/adsharma/nanobot/tree/pgembed) is a proof of concept.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
acp
bad
bug
discussion
docs
documentation
enhancement
extension
good first issue
growth
help-wanted
mcp
model-problem
needs:compliance
nix
opentui
perf
pull-request
Mirrored from GitHub Pull Request
 tui
web
windows
zen
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
yindo
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: anomalyco/opencode#4764
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?