Config: Add support for PostgreSQL about photoprism HOT 61 OPEN

I would love to see postgres support! I'll contribute time, talent, and/or treasure.

from photoprism.

Don't see Postgresql in the Roadmap

That's because we plan to support PostgreSQL anyway, ideally when there is less pressure to release new features than right now. We can't perform a major backend/database refactoring while pushing huge new features like multi-user support.

from photoprism.

• Wish we already had PostgreSQL support as it is the technologically better choice
• There is (still) too little funding and too much work for our team
• As soon as funding improves (which we really hope for), PostgreSQL support will be one of our highest priorities
• There should already be tools for migrating from MariaDB to PostgreSQL:
  • You are invited to do research on this! ✨

from photoprism.

Would you consider leaving this issue open so people can 👍 it? I almost didn't find it.

from photoprism.

Fair enough. Most of my friends use Postgres (as do I), and I don't think I know anyone who prefers MariaDB, but SQLite is a good second choice so I just went with that.

from photoprism.

Is there a place where people can put money towards a particular feature? I think that would help to see how much existing / future users value a particular feature. Also, for many people it may be more appealing towards a specific feature than just to make a donation and hope that eventually the feature they need will make it.

from photoprism.

I had a quick look and it looks like the queries at least are trivial to add. The biggest problem is the models. The varbinary and datetime types are hard-coded into the models but don't exist in PostgreSQL, so the migration fails.

I'm not sure what the solution is here. I'd guess that the solution is to use the types Gorm expects (e.g. []byte instead of string when you want a column filled with bytes) but there's probably a good reason why it wasn't done that way to start with.

I'll play with it some more and see. It'd be nice to put everything in my PostgreSQL DB instead of SQLite.

from photoprism.

I'm aware of Unicode encodings and some of the important differences between them. I still don't see anything in the docs indicating that using a varchar containing ASCII will consume 4 bytes in an index but I'll take your word for it.

To be clear, in case there's some miscommunication going on, my assumption is that even if the column is switched to varchar, plain ASCII (i.e. the first 128 unicode code points, which are all encoded with 8 bits) will still be stored in it. That being the case, 1 character = 1 byte and comparisons are bog-standard string comparisons.

In other news, here's a PoC of PostgreSQL mostly working. It's intended as an overview of the work that needs to be done, not as a serious proposal.

from photoprism.

I can also contribute Postgres knowledge & time.

from photoprism.

I can help you with PostgreSQL support.

from photoprism.

Are there any contributors working on this atm? My team is interested in this feature, and I might be able to contribute some time

from photoprism.

But it uses 4 BYTES per ASCII character

As far as I can tell looking at the SQLite docs, the MySQL docs and PostgreSQL docs, that isn't the case at all. A varchar uses a 1-4 byte prefix depending on the size of the field but each byte of payload consumes one byte of storage.

Also when you compare strings, it's somewhat more complex with unicode than just to compare bytes.

But we're not storing unicode, we're storing ASCII in a field that could contain unicode. I don't think any of those edge-cases apply here.

I'm aware you can PROBABLY do the same with VARCHAR with the right settings and enough time to test, but it was hard to see business value in such experiments.

Fair enough.

Also, queries aren't so straightforward after all. The queries extensively use 0 and 1 instead of false and true, which isn't supported by PostgreSQL (and as a side note, makes the query more difficult to read, since you don't know if it's meant to be a boolean comparison or an integer comparison).

I managed to do a little bit of cleanup of that and managed to get something working at least.

from photoprism.

Ah, looks like the string vs. []byte is mostly solved by gorm V2 anyhow. You'll just be able to put type:bytes in the tag and it'll handle it for you.

from photoprism.

Haha, don't worry, my server is plenty slow already so that's what I blame :P It's just too bad that using an ORM compatible with all three RDBMSes is hard to do at this point, but I agree, features are more important.

from photoprism.

@bobobo1618 datetime has existed in postgresql for ~10 years and varbinary = bit datatype

...sqlite has no locking for reads, only writes. Scales very well for multi-user, many core, read heavy systems. Just not writing. Also all datatypes in sqlite are treated the same. How it is inserted is how the specific "cell" treats the data, this is all over the docs.

from photoprism.

which are not understood by PostgreSQL.

create domain datetime as timestamp;

?

from photoprism.

@pashagolub My apologies for not getting back to you sooner! We had to focus all our resources on the release and then needed a break. Any help with adding PostgreSQL is, of course, much appreciated. There are two basic strategies:

1. Keep the current ORM (which doesn't support dynamic columns for the auto-migrations) and work around this by using only manual migrations for PostgreSQL. This seems doable to me with a few code changes, but needs to be tested before you invest a lot of time.
2. Upgrading the ORM, which requires rewriting large chunks of code and re-testing every single detail. This approach seems cleaner, but it could also result in much more work and prevent us from releasing new features for some time, which might not be popular with some users (except those who are just waiting for PostgreSQL support, of course).

Should you decide to tackle this, I'm happy to help and give advice to the best of my ability. Also, if you have any personal questions, feel free to contact me directly via email so as to avoid notifying all issue subscribers on GitHub about a new comment.

from photoprism.

The strategy should be doing 1 and then 2. ;)
But after 1 there is often no reason for 2.

from photoprism.

We currently use GORM v1, I was assuming this is mentioned/discussed in the comments above: https://v1.gorm.io/docs/

from photoprism.

Sorry. Missed that

from photoprism.

Not right now, but in general: anything to store a few tables will do... as simple and stable as possible... many developers are familiar with mysql, so that's my default when I start a new project. Tooling is also good.

sqlite is a very lean option, but obviously - if you run multiple processes or want to directly access / backup your data - it doesn't scale well or at all.

from photoprism.

It became clear that we have to build a single binary for distribution to reach broad adoption. Differences between SQL dialects are too large to have them abstracted away by our current ORM library, for example when doing date range queries. They are already different between MySQL and sqlite.

For those reasons we will not implement Postgres support for our MVP / first release. If you have time & energy, you are welcome to help us. I will close this issue for now, we can revisit it later when there is time and enough people want this 👍

from photoprism.

ok fair point - I was raising this because cost of maintenance and troubleshooting at scale is much lower with postgres, and lots of succesfull projects have this support. so, from what you wrote about differences, it seems that you don't have pluggable orm-like generic read/write storage methods just yet, right ?

from photoprism.

@sokoow We do use GORM, but it doesn't help with search queries that use database specific SQL.

If you like to dive into the subject, DATEDIFF is a great example: MySQL and SQL Server use DATEDIFF(), Postgres seems to prefer DATE_PART() whereas sqlite only has julianday().

It goes even deeper when you look into how tables are organized. You can't abstract and optimize at the same time. We want to provide the best performance to our users.

See

• http://www.sqlines.com/postgresql/how-to/datediff
• https://www.sqlite.org/lang_datefunc.html

from photoprism.

No that's a fair point, you're not the first project that has this challenge - something to think about on higher abstraction level.

from photoprism.

If you have time & energy, you are welcome to help us.

I guess it won't be so hard, so I would try

from photoprism.

Getting it to work somehow at a single point in time is not hard, getting it to work with decent performance, finding developers who are comfortable with it and constantly maintaining the code is incredibly hard.

Keep in mind: You also need to maintain continuous integration infrastructure and effectively run all tests with every database.

from photoprism.

Ofcourse, tests might be same for every supported database and this might be solved within #60.
Also, sqlite support will probably entail some architectural changes (like search using Bleve and db driver dependent sql queries). It won't be hard to add postgresql support after that. And may be you'll find "developers who are comfortable with it" by this time

from photoprism.

@LeKovr Did you see how we renamed the label from "rejected" to "descoped"? 😉 Yes indeed, later it might be great to add support for additional databases, if users actually need it in practice. Maybe everyone will be happy with an embedded database if we do it well. It is hard to predict.

What I meant was that if you change some code that involves SQL you might feel uncomfortable because you only have experience with one database, so you end up doing nothing. And that can be very dangerous for a project.

from photoprism.

@lastzero, You are right. May be later. There are more important things to do by now

from photoprism.

The varbinary and datetime types are hard-coded into the models

may be create domain varbinary... may helps

from photoprism.

All of the varbinary have different lengths and seem to have different purposes, so I don't think that'll help unfortunately.

from photoprism.

Yes, we use binary for plain ASCII, especially when strings need to be sorted, indexed or compared and should not be normalized in any way.

from photoprism.

Shouldn't that be the case by default for string fields? I know MySQL does some stupid stuff with character encodings but it shouldn't modify plain ASCII, right?

from photoprism.

But it uses 4 BYTES per ASCII character, so the index becomes very big. Also when you compare strings, it's somewhat more complex with unicode than just to compare bytes. I'm aware you can PROBABLY do the same with VARCHAR with the right settings and enough time to test, but it was hard to see business value in such experiments.

from photoprism.

Not in the index, check again. Maybe also not in memory when comparing.

from photoprism.

I couldn't find documentation so I just ran a quick test to see.

import sqlite3
c = sqlite3.connect('test.db')
c.execute('CREATE TABLE things (integer PRIMARY KEY, testcolumn varchar(32))')
c.execute('CREATE INDEX test_idx ON things(testcolumn)')
for x in range(0, 10000):
    c.execute('INSERT INTO things(testcolumn) VALUES (?)', (hex(x * 472882049 % 15485867),))
c.commit()

Which resulted in 79.3k of actual data:

SELECT SUM(length(testcolumn)) FROM things;
79288

I analyzed it with sqlite3_analyzer.

Table:

Bytes of storage consumed......................... 167936
Bytes of payload.................................. 109288      65.1%
Bytes of metadata................................. 50517       30.1%

Index:

Bytes of storage consumed......................... 163840
Bytes of payload.................................. 129160      78.8%
Bytes of metadata................................. 30476       18.6%

So for 79288 bytes of actual data sitting in the column, we have 109288 bytes total for the data itself (1.38 bytes per byte) and 129160 for the index (1.63 bytes per byte).

I repeated the test with varbinary(32) instead of varchar(32) and got precisely the same result, down to the exact number of bytes.

So I don't see any evidence that a varchar consumes more space in an index than a varbinary.

from photoprism.

You'll find some information on this page: https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-conversion.html

You might also want to read this and related RFCs: https://en.wikipedia.org/wiki/Comparison_of_Unicode_encodings

Note that Microsoft, as far as I know, still uses UCS-2 instead of UTC-8 in Windows, for all the reasons I mentioned. Maybe they switched to UTF-16. Their Linux database driver for SQL Server used null terminated strings, guess how well this works with UCS-2. Not at all.

For MySQL, we use 4 byte UTF8, which needs 4 bytes in indexes unless somebody completely refactored InnoDB in the meantime. Note that the MySQL manual was wrong on InnoDB for a long time, insisting that MySQL doesn't know or support indexed organized tables while InnoDB ONLY uses index organized tables.

When you're done with this, enjoy learning about the four Unicode normalization forms: https://en.wikipedia.org/wiki/Unicode_equivalence#Normalization

Did you know there's a difference between Linux und OS X? Apple uses decomposed, so you need to convert all strings when copying files. Their bundled command line tools were not compiled with iconv support, so you had to compile it yourself. Some of this still not fixed until today.

from photoprism.

Note that Sqlite ignores VARBINARY and probably also VARCHAR to some degree. It uses dynamic typing. That's why all string keys are prefixed with at least once non-numeric character. It would convert the value to INT otherwise and comparisons with binary data or strings would fail:

SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statements that work on statically typed databases should work the same way in SQLite. However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases.

See https://www.sqlite.org/datatype3.html

from photoprism.

Actually on string vs. []byte it occurred to me, if you only want to store ASCII here and don't want to treat this like a thing that's semantically like a string, is it a bad thing to use a []byte type? Is it the hassle of converting to/from strings when dealing with other APIs that's offputting?

With []byte, gorm will choose an appropriate type for each DB by default.

from photoprism.

See https://mathiasbynens.be/notes/mysql-utf8mb4

The InnoDB storage engine has a maximum index length of 767 bytes, so for utf8 or utf8mb4 columns, you can index a maximum of 255 or 191 characters, respectively. If you currently have utf8 columns with indexes longer than 191 characters, you will need to index a smaller number of characters when using utf8mb4. (Because of this, I had to change some indexed VARCHAR(255) columns to VARCHAR(191).)

Maybe we can switch to []byte in Go. Let's revisit this later, there are a ton of items on our todo with higher priority and that's by far not the only change we need to support other databases.

Edit: As you can see in the code, I already implemented basic support for multiple dialects when we added Sqlite. For Postgres there's more to consider, especially data types. Sqlite pretty much doesn't care. Bool columns and date functions might also need attention. I'm fully aware Postgres is very popular in the GIS community, so it will be worth adding when we have the resources needed to implement and maintain it (see next comment).

from photoprism.

We also need to consider the impact on testing and continuous integration when adding support for additional storage engines and APIs. That's often underestimated and causes permanent overhead. From a contributor's perspective, it might just be a one time pull request. Anyhow, we value your efforts and feedback! Just so that you see why we're cautious.

from photoprism.

Note that only Gorm 2 aka 1.2 supports compatible general types. I already tried upgrading, but it turned out to be extremely time consuming and tedious due to many changes. We decided to rather release earlier and without bugs, than to go for Postgres support in our first release.

If you look at our public roadmap, you'll notice that there are a ton of important feature requests that deliver value to regular users. So don't expect us to completely refactor our storage layer in the next few months 👍

If we get it done earlier, that's good... but no commitment at this point. As this is the core of our app, we can also not "just" merge a pull request. There are too many edge cases to keep in mind.

from photoprism.

We initially started with a built-in TiDB server. When that caused issues, we simply sticked with MySQL-compatible databases. SQLite is not that much different, and ignores most data types anyway. MariaDB works well for what we do. Migrating from a more to a less powerful DBMS is much more difficult when your app depends on all the features. Note that SQLite is much slower than MariaDB in every regard, especially on powerful servers with many logical cores due to file based locking. We don't want to get any bad performance reviews! 😉

from photoprism.

@bobobo1618 datetime has existed in postgresql for ~10 years and varbinary = bit datatype

I think you misunderstood. Of course PostgreSQL has equivalent types (timestamp for datetime, bit varying(n) for varbinary), the problem is that PhotoPrism hard-codes the specific names datetime and varbinary, which are not understood by PostgreSQL.

from photoprism.

Are there any news to PostgreSQL support?

from photoprism.

No, we are currently working on multi-user support, which is really an epic.
You can find a list of upcoming features on our roadmap: https://github.com/photoprism/photoprism/projects/5

from photoprism.

Not sure if there's a place to mention it - or if it's really a new issue - but how to migrate between databases would also be nice in addition to "just" supporting postgresql.

from photoprism.

Don't see Postgresql in the Roadmap, or is it under generic name for other DBs support?

from photoprism.

Here you find an overview of our current funding options. Sponsors in higher tiers can give golden sponsor labels to features.

Is there a place where people can put money towards a particular feature?

Not anymore. While we like IssueHunt and are grateful for the donations we've received so far, it hasn't proven to be a sustainable funding option for us as we spend much of our time maintaining existing features and providing support.
If we don't have enough resources to provide support and bugfixes, we can't start working on new features.

from photoprism.

I often wish we had a more compatible, intuitive database abstraction layer. But compared to important core features that are still missing, like batch editing, this is not a big pain point at the moment and therefore not a top priority.

from photoprism.

1. Keep the current ORM

Would you please name it? :) It's hard to find the name in the .mod file without actually knowing it :-)

Speaking about go.mod... I was surprised to see lib/pq dependency. :-D

from photoprism.

Any update on when we can expect PostgreSQL support?

from photoprism.

We had several contributors who wanted to work on this. However, there is no pull request for it yet and so I can't tell you anything about the progress.

from photoprism.

any news for postgres support?

from photoprism.

@ezra-varady We appreciate any help we can get! To reiterate what I wrote above, there are two possible strategies:

1. Keep the current GORM version (which does not support abstract/dynamic column types if you use auto-migrations to create/update the database schema) and work around this by using our manual migration package to maintain the PostgreSQL schema. This seems doable to me with a few changes in the internal/config package, though it should be tested as a proof-of-concept before you invest a lot of time.
2. Upgrade GORM from v1 to v2, which requires rewriting large parts of the code and retesting every single detail. This approach may be beneficial in the long run, although it will probably also cause a lot more work and might prevent us from releasing new features for some time, which our users would not be happy about. For this reason, the entire work could of course also be done in a long-lived feature branch until everything is ready. However, you must then be prepared to resolve merge conflicts with our main branch (develop) from time to time until it can finally be merged.

Due to the higher chances of success (and because it doesn't block us from upgrading later), I would personally recommend going for (1), i.e. adding (a) manual migrations (for the initial setup of the database schema in the first step) and (b) hand-written SQL for custom queries for which the ORM is not used, for example:

Should you decide to tackle this, we will be happy to help and provide advice to the best of our ability. You are also welcome to contact us via email or chat if you have general questions that don't need to be documented as a public issue comment on GitHub.

from photoprism.

@lastzero did you considered the option to remove GORM at all and replace it with something else? Perhaps a lightweight lib for db access? something similar to github.com/jmoiron/sqlx ?

from photoprism.

Maybe https://entgo.io/ or https://bun.uptrace.dev/

from photoprism.

I think pgx is enough for most of the functionality. But again if we want to be able to talk to different databases, we should come with some kind of database engine. And ORM is not the best choice, because the problem is not in the relation-object mapping but in the logic behind

from photoprism.

@vnnv @stavros-k @pashagolub Yes, of course we have also considered switching to a completely different library... There are many more choices now than when we started the project.

That said, some kind of abstraction seems necessary if we want to support multiple dialects with the resources we have. Also, I think it's a good idea to cover simple standard use cases instead of creating every single SQL query manually.

Either way, the amount of work required to switch to a different library would be even greater than what I described in my comment above as 2.: #47 (comment)

Even for 1. and 2. it seems extremely difficult to find contributors with the time and experience required, and my personal time is very limited due to the amount of support and feature requests we receive.

from photoprism.

That said, some kind of abstraction seems necessary if we want to support multiple dialects with the resources we have. Also, I think it's a good idea to cover simple standard use cases instead of creating every single SQL query manually.

What kind of abstraction are you looking for? I saw that is regarding column types.
Do you have columns that you dont know the type before hand?

If you do know it before hand but its "changing" frequently, Ent might be a better option, as you can extend the generated code with some gotemplates. As for migrations I would look into Atlas.

That being said, I was just subscribed into this issue for a long time, and thought I'd share what I found from my recent search for a db lib. As I was looking to start a mini side project.

I wish I had the experience to help with it.

from photoprism.