Database index `fs_storage_path_prefix` is not created with Postgresql

[RegisPerdreau]

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration/webserver/proxy issue.
• This issue is not already reported on Github (I've searched it).
• Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
• Nextcloud Server is running on 64bit capable CPU, PHP and OS.
• I agree to follow Nextcloud's Code of Conduct.

Bug description

About PostgreSQL support.

Considering this line of code : https://github.com/nextcloud/server/blob/master/core/Command/Db/AddMissingIndices.php#L175

When installing Nextcloud, database index is not created with postgresql database to avoid any later issues (according this commit: 6953265

According to our research, the missing index is in the oc_filecache table.

With MySQL/Mariadb in database, the index is indeed created by Nextcloud and is called fs_storage_path_prefix. But he is
therefore not created with PostgreSQL

We think that performance issue could occur specially when Cronjob is running with huge database.

Could Nextcloud consider solving this annoying issue ?

The commit talks about extra parameters in dbal driver, could we have any explanations about ?

Expected behavior

Expect to have database index created.

Nextcloud Server version

25

Database engine version

PostgreSQL

[szaimen]

Hi, Nextcloud 24 is not supported anymore.

[RegisPerdreau]

Sorry, i have updated, same problem with 25 and higher

[szaimen]

Cc @icewind1991 can we drop the check for postgres in the mentioned line or will it bug out?

[pielonet]

Hi,

PostgreSQL does not support field length limitations in Indexes, thus Doctrine/DBAL won't use it for this platform.

The "lengths" options in the following command would not be taken into account which would have a disastrous effect on Postgresql since indexes on paths would not have any limit and could exceed database capability.

$table->addIndex(['storage', 'path'], 'fs_storage_path_prefix', [], ['lengths' => [null, 64]]);

This index is able to optimize requests in this form (used for example by Nextcloud's cron job) :

select * from oc_filecache where storage=2 and path like 'files_versions%';

Nevertheless PostgreSQL supports Indexes that can optimize requests with "LIKE" expressions (See : https://www.postgresql.org/docs/12/indexes-opclass.html):

CREATE INDEX fs_storage_path_prefix ON oc_filecache (storage, path varchar_pattern_ops);

Note that "path" field is of type VARCHAR(4000) and that the B-tree version 4 limitation is 2704 bytes for the column row (https://dba.stackexchange.com/questions/69161/character-varying-index-overhead-length-limit/69164)

Thus I recommend running the following request prior to creating the Index :

select max(octet_length(path)) from oc_filecache;

The result has to be way less than 2704 in order to assure that the index can be created properly and that future INSERT/UPDATE commands in oc_filecache table won't reach the 2704 limit for "path" field length. (The "path" field is that of files in Nextcloud's files tree: e.g :"files/subdirectory1/subdirectory2/my_file.pdf"). Beware that users use several depths and very long subdirectory names !

Kind regards,
Thierry

[pielonet]

I updated my first comment as It was incorrect : in order to optimize requests with "Like" expressions one can not use an index with a "left(path, 64)" limitation. This index will simply be ignored by the optimizer if the request uses a "LIKE" comparison (See : https://www.postgresql.org/docs/current/indexes-expressional.html)