Using a postgres database instead of local storage

[nitanmarcel]

I want to run trillium, and the main issue is that I have a small vps that has only 2GB of storage left, and I wish to use a cockroach 10gb postgres storage for free. Is there a way to connect trillium to it?

[rodrigoalexandrecosta]

I don't think so, Trilium uses a sqlite db by default and I would say that would be extremely difficult to change that, you would need to go hard in the code and deal with things like:

• Migrations
• Queries or other functionalities that are distinct between sqlite and postgres
• Possibly distinct/unrelated/inexistent column types
• Credentials and stuff like that

I didn't dive into the code yet but these seems only the beginning, since AFAIK sqlite was chosen not only for being lightweight but also for performance reasons.

But perhaps you could achieve what you want (somehow) using the Sync Server, anything that provides Docker and internet connection would do I think.

[nitanmarcel]

Pf. Ok thanks. I'll keep looking for self hosted alternatives (for milanote) :)

[nitanmarcel]

Btw, What if I use something like rsync and create a drive that connects to google drive for example. Is that a good idea?

[rodrigoalexandrecosta]

I also don't think that will work. Trilium has it's own sync solution and you may risk database corruption using alternative sync. For the sync server you would need at least a very small instance running Docker.

But I also should say, you mentioned having only 2GB of free space, this is probably more than enough for using Trilium for a good time, probably enough time to come up with a solution in the future, but I don't actually know what the size of the db for people who use it for months or years.

[zadam]

This is correct. Trilium has no DB abstraction, just queries SQLite directly and is thus dependent on it.

[nitanmarcel]

I went away and tried it anyway, it didn't go we'll at all 😅

Besides being slow, some data wouldn't persist properly or I would get disconnected.

Instead of a drive I used an S3 like object storage and mounted it where the trillium_data should be

[sottey]

I take probably 5-7 meeting notes a day, plus store all of my habit tracking, user manuals and software development notes and artifacts in Trilium and have for a year. My db is about 80megs.

So I think that 2gb should be more than enough.

My .02

[nitanmarcel]

I'm so awful at storage management that I might hit 0 very fast, actually did it 4 times in the last 2 days ^^

[werererer]

I think DB abstraction can be implemented gradually. They will become increasingly relevant as multiple users are considered ¹. More users will generate databases of larger size effecting performance and other metrics. This makes the freedom of choice of databases more relevant.

We could start by first including a micro abstraction layer, and then start rewriting microscopic small parts of the application. Later expanding the rewrite step by step. This would be a very long but stable process. But since every single step in between would result in stable software, the longevity of the process wouldn't even matter.

I can imagine that something like Kysely could be used:

https://kysely.dev/

Implementing a database abstraction will make Trilium a more scalable and flexible solution for teams - at least over the very long time.

Footnotes

1. https://github.com/TriliumNext/Trilium/issues/4956 ↩

[eliandoran]

There are a few hundred SQL queries scattered across the code base, so I don't think this will happen without some kind of corporate sponsorship for the effort.

Personally I don't think the effort justifies the value gained, but I'm not against it.