Advanced Database Setup¶
While we believe this contributed content may be helpful to advanced users, we have not yet thoroughly reviewed it. If you have suggestions for improvement, please let us know by clicking to send a pull request.
PhotoPrism is compatible with SQLite 3 and MariaDB 10.5.12+. Official support for MySQL 8 is discontinued as Oracle seems to have stopped shipping new features and enhancements. As a result, the testing effort required before each release is no longer feasible.
Our configuration examples are generally based on the current stable version to take advantage of performance improvements. This does not mean that older versions are no longer supported and you must upgrade immediately. We recommend not using the
:latest tag for the MariaDB Docker image and to upgrade manually by changing the tag once we had a chance to test a new major version.
Local Solid-State Drives (SSDs) are best for databases of any kind. Never store database files on an unreliable device such as a USB flash drive, SD card, or shared network folder. These may also have unexpected file size limitations, which is especially problematic for databases that do not split data into smaller files.
When creating a new database, make sure to set the charset and collation as follows:
CREATE DATABASE photoprism
CHARACTER SET = 'utf8mb4'
COLLATE = 'utf8mb4_unicode_ci';
Now create a user and grant privileges for this new database:
CREATE USER 'photoprism'@'%' IDENTIFIED BY 'insecure';
GRANT ALL PRIVILEGES ON photoprism.* to 'photoprism'@'%';
Set the database environment variables for PhotoPrism and MariaDB as follows:
Set strong passwords if the database is exposed to an external network. Never expose your database to the public Internet in this way, for example, if it is running on a cloud server.
An index schema migration is performed automatically every time PhotoPrism is (re)started. The following instructions may be helpful in special cases, such as when a temporary problem has prevented a successful migration:
Many users reporting poor performance and high CPU usage have migrated from SQLite to MariaDB, so their database schema is no longer optimized for performance. For example, MariaDB cannot handle rows with
text columns in memory and always uses temporary tables on disk if there are any.
If this is the case, please make sure that your migrated database schema matches that of a fresh, non-migrated installation. It may help to run the migrations manually in a terminal using the migrations subcommands. However, this does not guarantee that all issues such as missing indexes are resolved.