Support other backend databases (Postgres?)

[ccutrer]

Support other backend databases (Postgres?)

[blakeblackshear]

Not really. I still believe we are a very long way away from the limits of what sqlite can manage in terms of performance. Even modest hardware can handle far more than what 99.999% of frigate users will ever need. Newer versions of sqlite will resolve many of the other pain points too.

A different database engine isn't going to magically make the disks faster by introducing a networking layer for communication, nor is it going to resolve any inefficient database patterns that exist. Performance is not the reason to introduce a different database option. Making the architecture distributed across multiple servers or high availability would be reasons, but I'm not sure that will ever make sense for Frigate.

Before introducing another database option for performance reasons, I want to exhaust every attempt at improving performance with sqlite.

[NickM-27]

See #3644 (comment)

[ccutrer]

According to that comment, @blakeblackshear is not interested in maintaining any other database, but might be open to others handling such maintenance. Is that still true @blakeblackshear? I've looked through the code a bit, and it looks like you're already using peewee ORM, which already has support for multiple backends. I know there are a few hand-written queries, but that shouldn't be too difficult to translate any idiosyncrasies. Maintaining database access from a web application is one of my primary job duties, and I have abundant experience in that area. In general SQLite is considered a relatively simple database, lacking many features that other databases have, while having only a few features that are unique to it, so using SQLite as the baseline is usually pretty easy. The one thing that SQLite does that can be difficult to translate is that SQLite's datatypes are "advisory" only - it's perfectly possible to stuff an integer into a string column, for example. If I were to run in to any uses like that in Frigate, it may require more extensive changes. What do you say, @blakeblackshear - would you accept PRs by others to add support for PostgreSQL?