Shared albums are slower when browsed by shared users #6392

Open
opened 2026-02-05 12:15:21 +03:00 by OVERLORD · 3 comments
Owner

Originally created by @millallo on GitHub (Jun 29, 2025).

I have searched the existing issues, both open and closed, to make sure this is not a duplicate report.

  • Yes

The bug

I have an external library with 1 million photos in 1500 folders and I would like to share it with other users. Since it is almost impossible to display these photos in the user's timeline, the best solution I found was to create albums for each folder and share them with users with the immich-folder-album-creator script, and it worked perfectly.
Now I have 1500 albums shared with users, some folders contain 6000 photos, some 100...
The problem is performance: when I open an album as a shared user, the photos load extremely slowly, while when I open albums as owner user, the loading is extremely fast.
What I noticed when I browse as a shared user is that the PostgreSQL processes are at 100% CPU, and this makes me think that it is not using the indexes of the tables.
Immich 1.135.3 and Postgres are in docker container with 8 cpus @ 2.00GHz , 8Gram and SSD disks.

This is when I scroll photos in album as a shared user:

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
1107153 systemd+  20   0 1869052 691572 619384 R  93.4   8.5  22:08.28 postgres
1355467 systemd+  20   0 1895148 713948 617320 R  93.4   8.8  11:00.19 postgres
1355468 systemd+  20   0 1868528 689296 617564 R  93.4   8.5  10:45.97 postgres
1355469 systemd+  20   0 1867172 688520 617464 R  93.4   8.5  10:11.26 postgres
1355470 systemd+  20   0 1867520 688684 617328 R  93.0   8.5  10:18.53 postgres
1360073 systemd+  20   0 1855040 657564 598580 R  93.0   8.1   1:02.30 postgres

I ran this query during the slow behavior:

SELECT pid, now() - query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC;

 pid |    duration     |                                                                                                                                                                                                                                                                                                             query
-----+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 100 | 00:00:03.584706 | select "assets"."id", "assets"."livePhotoVideoId" from "albums" inner join "albums_assets_assets" as "albumAssets" on "albums"."id" = "albumAssets"."albumsId" inner join "assets" on "assets"."id" = "albumAssets"."assetsId" and "assets"."deletedAt" is null left join "albums_shared_users_users" as "albumUsers" on "albumUsers"."albumsId" = "albums"."id" left join "users" on "users"."id" = "albumUsers"."usersId" and "users"."deletedAt" is null where array["assets"."id", "assets"."livePhotoVideoId"] && array[$1]::uuid[]  and ("albums"."ownerId" = $2 or "users"."id" = $3) and "albums"."deletedAt" is null
 102 | 00:00:03.584163 | select "assets"."id", "assets"."livePhotoVideoId" from "albums" inner join "albums_assets_assets" as "albumAssets" on "albums"."id" = "albumAssets"."albumsId" inner join "assets" on "assets"."id" = "albumAssets"."assetsId" and "assets"."deletedAt" is null left join "albums_shared_users_users" as "albumUsers" on "albumUsers"."albumsId" = "albums"."id" left join "users" on "users"."id" = "albumUsers"."usersId" and "users"."deletedAt" is null where array["assets"."id", "assets"."livePhotoVideoId"] && array[$1]::uuid[]  and ("albums"."ownerId" = $2 or "users"."id" = $3) and "albums"."deletedAt" is null
  93 | 00:00:03.560361 | select "assets"."id", "assets"."livePhotoVideoId" from "albums" inner join "albums_assets_assets" as "albumAssets" on "albums"."id" = "albumAssets"."albumsId" inner join "assets" on "assets"."id" = "albumAssets"."assetsId" and "assets"."deletedAt" is null left join "albums_shared_users_users" as "albumUsers" on "albumUsers"."albumsId" = "albums"."id" left join "users" on "users"."id" = "albumUsers"."usersId" and "users"."deletedAt" is null where array["assets"."id", "assets"."livePhotoVideoId"] && array[$1]::uuid[]  and ("albums"."ownerId" = $2 or "users"."id" = $3) and "albums"."deletedAt" is null
  92 | 00:00:03.558339 | select "assets"."id", "assets"."livePhotoVideoId" from "albums" inner join "albums_assets_assets" as "albumAssets" on "albums"."id" = "albumAssets"."albumsId" inner join "assets" on "assets"."id" = "albumAssets"."assetsId" and "assets"."deletedAt" is null left join "albums_shared_users_users" as "albumUsers" on "albumUsers"."albumsId" = "albums"."id" left join "users" on "users"."id" = "albumUsers"."usersId" and "users"."deletedAt" is null where array["assets"."id", "assets"."livePhotoVideoId"] && array[$1]::uuid[]  and ("albums"."ownerId" = $2 or "users"."id" = $3) and "albums"."deletedAt" is null
  99 | 00:00:03.55813  | select "assets"."id", "assets"."livePhotoVideoId" from "albums" inner join "albums_assets_assets" as "albumAssets" on "albums"."id" = "albumAssets"."albumsId" inner join "assets" on "assets"."id" = "albumAssets"."assetsId" and "assets"."deletedAt" is null left join "albums_shared_users_users" as "albumUsers" on "albumUsers"."albumsId" = "albums"."id" left join "users" on "users"."id" = "albumUsers"."usersId" and "users"."deletedAt" is null where array["assets"."id", "assets"."livePhotoVideoId"] && array[$1]::uuid[]  and ("albums"."ownerId" = $2 or "users"."id" = $3) and "albums"."deletedAt" is null
 103 | 00:00:03.558122 | select "assets"."id", "assets"."livePhotoVideoId" from "albums" inner join "albums_assets_assets" as "albumAssets" on "albums"."id" = "albumAssets"."albumsId" inner join "assets" on "assets"."id" = "albumAssets"."assetsId" and "assets"."deletedAt" is null left join "albums_shared_users_users" as "albumUsers" on "albumUsers"."albumsId" = "albums"."id" left join "users" on "users"."id" = "albumUsers"."usersId" and "users"."deletedAt" is null where array["assets"."id", "assets"."livePhotoVideoId"] && array[$1]::uuid[]  and ("albums"."ownerId" = $2 or "users"."id" = $3) and "albums"."deletedAt" is null

However, no query seems to exceed 4 seconds

The OS that Immich Server is running on

Virtual machine Debian 12

Version of Immich Server

v1.135.3

Version of Immich Mobile App

not used

Platform with the issue

  • Server
  • Web
  • Mobile

Your docker-compose.yml content

default installation

Your .env content

default installation

Reproduction steps

...

Relevant log output


Additional information

No response

Originally created by @millallo on GitHub (Jun 29, 2025). ### I have searched the existing issues, both open and closed, to make sure this is not a duplicate report. - [x] Yes ### The bug I have an external library with 1 million photos in 1500 folders and I would like to share it with other users. Since it is almost impossible to display these photos in the user's timeline, the best solution I found was to create albums for each folder and share them with users with the immich-folder-album-creator script, and it worked perfectly. Now I have 1500 albums shared with users, some folders contain 6000 photos, some 100... The problem is performance: when I open an album as a shared user, the photos load extremely slowly, while when I open albums as owner user, the loading is extremely fast. What I noticed when I browse as a shared user is that the PostgreSQL processes are at 100% CPU, and this makes me think that it is not using the indexes of the tables. Immich 1.135.3 and Postgres are in docker container with 8 cpus @ 2.00GHz , 8Gram and SSD disks. This is when I scroll photos in album as a shared user: ``` PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 1107153 systemd+ 20 0 1869052 691572 619384 R 93.4 8.5 22:08.28 postgres 1355467 systemd+ 20 0 1895148 713948 617320 R 93.4 8.8 11:00.19 postgres 1355468 systemd+ 20 0 1868528 689296 617564 R 93.4 8.5 10:45.97 postgres 1355469 systemd+ 20 0 1867172 688520 617464 R 93.4 8.5 10:11.26 postgres 1355470 systemd+ 20 0 1867520 688684 617328 R 93.0 8.5 10:18.53 postgres 1360073 systemd+ 20 0 1855040 657564 598580 R 93.0 8.1 1:02.30 postgres ``` I ran this query during the slow behavior: ``` SELECT pid, now() - query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' ORDER BY duration DESC; pid | duration | query -----+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 100 | 00:00:03.584706 | select "assets"."id", "assets"."livePhotoVideoId" from "albums" inner join "albums_assets_assets" as "albumAssets" on "albums"."id" = "albumAssets"."albumsId" inner join "assets" on "assets"."id" = "albumAssets"."assetsId" and "assets"."deletedAt" is null left join "albums_shared_users_users" as "albumUsers" on "albumUsers"."albumsId" = "albums"."id" left join "users" on "users"."id" = "albumUsers"."usersId" and "users"."deletedAt" is null where array["assets"."id", "assets"."livePhotoVideoId"] && array[$1]::uuid[] and ("albums"."ownerId" = $2 or "users"."id" = $3) and "albums"."deletedAt" is null 102 | 00:00:03.584163 | select "assets"."id", "assets"."livePhotoVideoId" from "albums" inner join "albums_assets_assets" as "albumAssets" on "albums"."id" = "albumAssets"."albumsId" inner join "assets" on "assets"."id" = "albumAssets"."assetsId" and "assets"."deletedAt" is null left join "albums_shared_users_users" as "albumUsers" on "albumUsers"."albumsId" = "albums"."id" left join "users" on "users"."id" = "albumUsers"."usersId" and "users"."deletedAt" is null where array["assets"."id", "assets"."livePhotoVideoId"] && array[$1]::uuid[] and ("albums"."ownerId" = $2 or "users"."id" = $3) and "albums"."deletedAt" is null 93 | 00:00:03.560361 | select "assets"."id", "assets"."livePhotoVideoId" from "albums" inner join "albums_assets_assets" as "albumAssets" on "albums"."id" = "albumAssets"."albumsId" inner join "assets" on "assets"."id" = "albumAssets"."assetsId" and "assets"."deletedAt" is null left join "albums_shared_users_users" as "albumUsers" on "albumUsers"."albumsId" = "albums"."id" left join "users" on "users"."id" = "albumUsers"."usersId" and "users"."deletedAt" is null where array["assets"."id", "assets"."livePhotoVideoId"] && array[$1]::uuid[] and ("albums"."ownerId" = $2 or "users"."id" = $3) and "albums"."deletedAt" is null 92 | 00:00:03.558339 | select "assets"."id", "assets"."livePhotoVideoId" from "albums" inner join "albums_assets_assets" as "albumAssets" on "albums"."id" = "albumAssets"."albumsId" inner join "assets" on "assets"."id" = "albumAssets"."assetsId" and "assets"."deletedAt" is null left join "albums_shared_users_users" as "albumUsers" on "albumUsers"."albumsId" = "albums"."id" left join "users" on "users"."id" = "albumUsers"."usersId" and "users"."deletedAt" is null where array["assets"."id", "assets"."livePhotoVideoId"] && array[$1]::uuid[] and ("albums"."ownerId" = $2 or "users"."id" = $3) and "albums"."deletedAt" is null 99 | 00:00:03.55813 | select "assets"."id", "assets"."livePhotoVideoId" from "albums" inner join "albums_assets_assets" as "albumAssets" on "albums"."id" = "albumAssets"."albumsId" inner join "assets" on "assets"."id" = "albumAssets"."assetsId" and "assets"."deletedAt" is null left join "albums_shared_users_users" as "albumUsers" on "albumUsers"."albumsId" = "albums"."id" left join "users" on "users"."id" = "albumUsers"."usersId" and "users"."deletedAt" is null where array["assets"."id", "assets"."livePhotoVideoId"] && array[$1]::uuid[] and ("albums"."ownerId" = $2 or "users"."id" = $3) and "albums"."deletedAt" is null 103 | 00:00:03.558122 | select "assets"."id", "assets"."livePhotoVideoId" from "albums" inner join "albums_assets_assets" as "albumAssets" on "albums"."id" = "albumAssets"."albumsId" inner join "assets" on "assets"."id" = "albumAssets"."assetsId" and "assets"."deletedAt" is null left join "albums_shared_users_users" as "albumUsers" on "albumUsers"."albumsId" = "albums"."id" left join "users" on "users"."id" = "albumUsers"."usersId" and "users"."deletedAt" is null where array["assets"."id", "assets"."livePhotoVideoId"] && array[$1]::uuid[] and ("albums"."ownerId" = $2 or "users"."id" = $3) and "albums"."deletedAt" is null ```` However, no query seems to exceed 4 seconds ### The OS that Immich Server is running on Virtual machine Debian 12 ### Version of Immich Server v1.135.3 ### Version of Immich Mobile App not used ### Platform with the issue - [x] Server - [ ] Web - [ ] Mobile ### Your docker-compose.yml content ```YAML default installation ``` ### Your .env content ```Shell default installation ``` ### Reproduction steps 1. 2. 3. ... ### Relevant log output ```shell ``` ### Additional information _No response_
Author
Owner

@jrasm91 commented on GitHub (Sep 11, 2025):

Can you run this sql and let me know the results?

EXPLAIN ANALYZE
select
  "asset"."id",
  "asset"."livePhotoVideoId"
from
  "album"
  inner join "album_asset" as "albumAssets" on "album"."id" = "albumAssets"."albumsId"
  inner join "asset" on "asset"."id" = "albumAssets"."assetsId"
  and "asset"."deletedAt" is null
  left join "album_user" as "albumUsers" on "albumUsers"."albumsId" = "album"."id"
  left join "user" on "user"."id" = "albumUsers"."usersId"
  and "user"."deletedAt" is null
where
  array["asset"."id", "asset"."livePhotoVideoId"] && array['ASSET_ID']::uuid[]
  and (
    "album"."ownerId" = 'ALBUM_OWNER_ID'
    or "user"."id" = 'USER_ID'
  )
  and "album"."deletedAt" is null

There are a few UUIDs that need to be replaced with valid values from your system.

@jrasm91 commented on GitHub (Sep 11, 2025): Can you run this sql and let me know the results? ```sql EXPLAIN ANALYZE select "asset"."id", "asset"."livePhotoVideoId" from "album" inner join "album_asset" as "albumAssets" on "album"."id" = "albumAssets"."albumsId" inner join "asset" on "asset"."id" = "albumAssets"."assetsId" and "asset"."deletedAt" is null left join "album_user" as "albumUsers" on "albumUsers"."albumsId" = "album"."id" left join "user" on "user"."id" = "albumUsers"."usersId" and "user"."deletedAt" is null where array["asset"."id", "asset"."livePhotoVideoId"] && array['ASSET_ID']::uuid[] and ( "album"."ownerId" = 'ALBUM_OWNER_ID' or "user"."id" = 'USER_ID' ) and "album"."deletedAt" is null ``` There are a few UUIDs that need to be replaced with valid values from your system.
Author
Owner

@millallo commented on GitHub (Sep 12, 2025):

Hi Jasm91,
this is the output

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=1002.07..364501.21 rows=9268 width=32) (actual time=283.793..682.717 rows=1 loops=1)
   Hash Cond: ("albumUsers"."usersId" = "user".id)
   Filter: ((album."ownerId" = '6f5c7756-2e21-44fa-8e1c-75940b288439'::uuid) OR ("user".id = '6c5b860b-a89a-47d2-85b5-4ad5d3f618d1'::uuid))
   ->  Nested Loop Left Join  (cost=1001.01..364426.01 rows=9268 width=64) (actual time=283.751..682.670 rows=1 loops=1)
         ->  Nested Loop  (cost=1000.72..363125.10 rows=8858 width=64) (actual time=283.735..682.651 rows=1 loops=1)
               ->  Gather  (cost=1000.43..362114.93 rows=8858 width=48) (actual time=283.711..682.624 rows=1 loops=1)
                     Workers Planned: 2
                     Workers Launched: 2
                     ->  Nested Loop  (cost=0.43..360229.13 rows=3691 width=48) (actual time=523.762..652.182 rows=0 loops=3)
                           ->  Parallel Seq Scan on asset  (cost=0.00..351482.16 rows=4142 width=32) (actual time=523.750..652.168 rows=0 loops=3)
                                 Filter: (("deletedAt" IS NULL) AND (ARRAY[id, "livePhotoVideoId"] && '{65b2249f-52c0-4009-8ff2-8ddc859bd9e8}'::uuid[]))
                                 Rows Removed by Filter: 662627
                           ->  Index Scan using "album_asset_assetsId_idx" on album_asset "albumAssets"  (cost=0.43..2.10 rows=1 width=32) (actual time=0.023..0.023 rows=1 loops=1)
                                 Index Cond: ("assetsId" = asset.id)
               ->  Memoize  (cost=0.29..0.31 rows=1 width=32) (actual time=0.019..0.020 rows=1 loops=1)
                     Cache Key: "albumAssets"."albumsId"
                     Cache Mode: logical
                     Hits: 0  Misses: 1  Evictions: 0  Overflows: 0  Memory Usage: 1kB
                     ->  Index Scan using album_pkey on album  (cost=0.28..0.30 rows=1 width=32) (actual time=0.011..0.011 rows=1 loops=1)
                           Index Cond: (id = "albumAssets"."albumsId")
                           Filter: ("deletedAt" IS NULL)
         ->  Memoize  (cost=0.29..0.33 rows=1 width=32) (actual time=0.012..0.014 rows=1 loops=1)
               Cache Key: album.id
               Cache Mode: logical
               Hits: 0  Misses: 1  Evictions: 0  Overflows: 0  Memory Usage: 1kB
               ->  Index Only Scan using album_user_pkey on album_user "albumUsers"  (cost=0.28..0.32 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=1)
                     Index Cond: ("albumsId" = album.id)
                     Heap Fetches: 0
   ->  Hash  (cost=1.03..1.03 rows=3 width=16) (actual time=0.017..0.019 rows=3 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on "user"  (cost=0.00..1.03 rows=3 width=16) (actual time=0.012..0.013 rows=3 loops=1)
               Filter: ("deletedAt" IS NULL)
 Planning Time: 0.853 ms
 JIT:
   Functions: 57
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 2.796 ms, Inlining 0.000 ms, Optimization 1.857 ms, Emission 27.208 ms, Total 31.861 ms
 Execution Time: 684.215 ms

Please consider the issue seems to be auto-resolved, may be after a dababase restart...

@millallo commented on GitHub (Sep 12, 2025): Hi Jasm91, this is the output ``` ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Hash Left Join (cost=1002.07..364501.21 rows=9268 width=32) (actual time=283.793..682.717 rows=1 loops=1) Hash Cond: ("albumUsers"."usersId" = "user".id) Filter: ((album."ownerId" = '6f5c7756-2e21-44fa-8e1c-75940b288439'::uuid) OR ("user".id = '6c5b860b-a89a-47d2-85b5-4ad5d3f618d1'::uuid)) -> Nested Loop Left Join (cost=1001.01..364426.01 rows=9268 width=64) (actual time=283.751..682.670 rows=1 loops=1) -> Nested Loop (cost=1000.72..363125.10 rows=8858 width=64) (actual time=283.735..682.651 rows=1 loops=1) -> Gather (cost=1000.43..362114.93 rows=8858 width=48) (actual time=283.711..682.624 rows=1 loops=1) Workers Planned: 2 Workers Launched: 2 -> Nested Loop (cost=0.43..360229.13 rows=3691 width=48) (actual time=523.762..652.182 rows=0 loops=3) -> Parallel Seq Scan on asset (cost=0.00..351482.16 rows=4142 width=32) (actual time=523.750..652.168 rows=0 loops=3) Filter: (("deletedAt" IS NULL) AND (ARRAY[id, "livePhotoVideoId"] && '{65b2249f-52c0-4009-8ff2-8ddc859bd9e8}'::uuid[])) Rows Removed by Filter: 662627 -> Index Scan using "album_asset_assetsId_idx" on album_asset "albumAssets" (cost=0.43..2.10 rows=1 width=32) (actual time=0.023..0.023 rows=1 loops=1) Index Cond: ("assetsId" = asset.id) -> Memoize (cost=0.29..0.31 rows=1 width=32) (actual time=0.019..0.020 rows=1 loops=1) Cache Key: "albumAssets"."albumsId" Cache Mode: logical Hits: 0 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB -> Index Scan using album_pkey on album (cost=0.28..0.30 rows=1 width=32) (actual time=0.011..0.011 rows=1 loops=1) Index Cond: (id = "albumAssets"."albumsId") Filter: ("deletedAt" IS NULL) -> Memoize (cost=0.29..0.33 rows=1 width=32) (actual time=0.012..0.014 rows=1 loops=1) Cache Key: album.id Cache Mode: logical Hits: 0 Misses: 1 Evictions: 0 Overflows: 0 Memory Usage: 1kB -> Index Only Scan using album_user_pkey on album_user "albumUsers" (cost=0.28..0.32 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=1) Index Cond: ("albumsId" = album.id) Heap Fetches: 0 -> Hash (cost=1.03..1.03 rows=3 width=16) (actual time=0.017..0.019 rows=3 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on "user" (cost=0.00..1.03 rows=3 width=16) (actual time=0.012..0.013 rows=3 loops=1) Filter: ("deletedAt" IS NULL) Planning Time: 0.853 ms JIT: Functions: 57 Options: Inlining false, Optimization false, Expressions true, Deforming true Timing: Generation 2.796 ms, Inlining 0.000 ms, Optimization 1.857 ms, Emission 27.208 ms, Total 31.861 ms Execution Time: 684.215 ms ``` Please consider the issue seems to be auto-resolved, may be after a dababase restart...
Author
Owner

@jrasm91 commented on GitHub (Sep 12, 2025):

Perfect, thanks!

It looks like the bulk of the time is from a full table scan on assets that takes 500 of the 600 milliseconds:

->  Parallel Seq Scan on asset  (cost=0.00..351482.16 rows=4142 width=32) (actual time=523.750..652.168 rows=0 loops=3)
      Filter: (("deletedAt" IS NULL) AND (ARRAY[id, "livePhotoVideoId"] && '{65b2249f-52c0-4009-8ff2-8ddc859bd9e8}'::uuid[]))
      Rows Removed by Filter: 662627

@mertalev when you have some time, do you want to take a look at improving this query? Or at least verifying we have the correct indexes to make it fast? This is the access check to verify access to an asset via a shared album.

@jrasm91 commented on GitHub (Sep 12, 2025): Perfect, thanks! It looks like the bulk of the time is from a full table scan on assets that takes 500 of the 600 milliseconds: ``` -> Parallel Seq Scan on asset (cost=0.00..351482.16 rows=4142 width=32) (actual time=523.750..652.168 rows=0 loops=3) Filter: (("deletedAt" IS NULL) AND (ARRAY[id, "livePhotoVideoId"] && '{65b2249f-52c0-4009-8ff2-8ddc859bd9e8}'::uuid[])) Rows Removed by Filter: 662627 ``` @mertalev when you have some time, do you want to take a look at improving this query? Or at least verifying we have the correct indexes to make it fast? This is the access check to verify access to an asset via a shared album.
Sign in to join this conversation.
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: immich-app/immich#6392