Database huge after migration to 2.0 #7414

Closed
opened 2026-02-05 13:01:31 +03:00 by OVERLORD · 0 comments
Owner

Originally created by @henfri on GitHub (Oct 3, 2025).

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

  • Yes

The bug

Hello,

I upgraded to 2.0.0 yesterday and the DB size has grown from 5G to >50G.
This is what I tried for finding the reason/fixing:

docker exec -it immich_postgres psql -U postgres

psql (14.19 (Debian 14.19-1.pgdg12+1))

Type "help" for help.



postgres=# \c immich
You are now connected to database "immich" as user "postgres".
immich=# VACUUM FULL;

VACUUM
immich=#
immich=# \l+
                                                                   List of databases

   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------
 immich    | postgres | UTF8     | en_US.utf8 | en_US.utf8 |                       | 32 GB   | pg_default |
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |                       | 8569 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +| 8417 kB | pg_default | unmodifiable empty database
           |          |          |            |            | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +| 8417 kB | pg_default | default template for new databases
           |          |          |            |            | postgres=CTc/postgres |         |            |

(4 rows)



immich=# SELECT
    table_name,
    pg_size_pretty(pg_total_relation_size(table_name::text)) AS total_size
FROM information_schema.tables
WHERE table_schema='public'
ORDER BY pg_total_relation_size(table_name::text) DESC;

        table_name         | total_size

---------------------------+------------
 smart_search              | 1383 MB
 face_search               | 779 MB
 assets                    | 299 MB
 asset_files               | 216 MB
 geodata_places            | 109 MB
 exif                      | 93 MB
 asset_faces               | 65 MB
 asset_job_status          | 35 MB
 tag_asset                 | 15 MB
 naturalearth_countries    | 8856 kB
 person                    | 2824 kB
 albums_assets_assets      | 2400 kB
 memories_assets_assets    | 1528 kB
 tags                      | 448 kB
 tags_closure              | 360 kB
 memories                  | 264 kB
 assets_audit              | 184 kB
 users                     | 96 kB
 albums                    | 88 kB
 sessions                  | 80 kB
 albums_audit              | 72 kB
 libraries                 | 64 kB
 api_keys                  | 64 kB
 move_history              | 64 kB
 activity                  | 56 kB
 shared_links              | 48 kB
 albums_shared_users_users | 40 kB
 partners_audit            | 32 kB
 kysely_migrations         | 32 kB
 session_sync_checkpoints  | 32 kB
 notifications             | 32 kB
 version_history           | 32 kB
 album_users_audit         | 32 kB
 system_metadata           | 32 kB
 asset_stack               | 32 kB
 kysely_migrations_lock    | 24 kB
 audit                     | 24 kB
 partners                  | 24 kB
 shared_link__asset        | 24 kB
 user_metadata             | 16 kB
 users_audit               | 16 kB
(41 rows)



immich=#
immich=# SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS indexes_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid) - pg_indexes_size(relid)) AS toast_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

        table_name         | total_size | table_size | indexes_size | toast_size

---------------------------+------------+------------+--------------+------------
 smart_search              | 1383 MB    | 17 MB      | 7952 kB      | 1358 MB
 face_search               | 779 MB     | 19 MB      | 8776 kB      | 752 MB
 assets                    | 299 MB     | 125 MB     | 174 MB       | 8192 bytes
 asset_files               | 216 MB     | 133 MB     | 82 MB        | 8192 bytes
 geodata_places            | 109 MB     | 35 MB      | 73 MB        | 24 kB
 exif                      | 93 MB      | 66 MB      | 26 MB        | 72 kB
 asset_faces               | 65 MB      | 31 MB      | 34 MB        | 0 bytes
 asset_job_status          | 35 MB      | 25 MB      | 10112 kB     | 0 bytes
 tag_asset                 | 15 MB      | 5072 kB    | 10 MB        | 0 bytes
 naturalearth_countries    | 8856 kB    | 2080 kB    | 104 kB       | 6672 kB
 person                    | 2824 kB    | 1872 kB    | 944 kB       | 8192 bytes
 albums_assets_assets      | 2400 kB    | 1024 kB    | 1376 kB      | 0 bytes
 memories_assets_assets    | 1528 kB    | 592 kB     | 936 kB       | 0 bytes
 tags                      | 448 kB     | 176 kB     | 264 kB       | 8192 bytes
 tags_closure              | 360 kB     | 112 kB     | 248 kB       | 0 bytes
 memories                  | 264 kB     | 144 kB     | 112 kB       | 8192 bytes
 assets_audit              | 184 kB     | 56 kB      | 128 kB       | 0 bytes
 users                     | 96 kB      | 8192 bytes | 80 kB        | 8192 bytes
 albums                    | 88 kB      | 16 kB      | 64 kB        | 8192 bytes
 sessions                  | 80 kB      | 8192 bytes | 64 kB        | 8192 bytes
 albums_audit              | 72 kB      | 8192 bytes | 64 kB        | 0 bytes
 api_keys                  | 64 kB      | 8192 bytes | 48 kB        | 8192 bytes
 move_history              | 64 kB      | 8192 bytes | 48 kB        | 8192 bytes
 libraries                 | 64 kB      | 8192 bytes | 48 kB        | 8192 bytes
 activity                  | 56 kB      | 0 bytes    | 48 kB        | 8192 bytes
 shared_links              | 48 kB      | 0 bytes    | 40 kB        | 8192 bytes
 albums_shared_users_users | 40 kB      | 0 bytes    | 32 kB        | 8192 bytes
 partners_audit            | 32 kB      | 0 bytes    | 32 kB        | 0 bytes
 version_history           | 32 kB      | 8192 bytes | 16 kB        | 8192 bytes
 asset_stack               | 32 kB      | 0 bytes    | 32 kB        | 0 bytes
 session_sync_checkpoints  | 32 kB      | 0 bytes    | 24 kB        | 8192 bytes
 notifications             | 32 kB      | 0 bytes    | 24 kB        | 8192 bytes
 kysely_migrations         | 32 kB      | 8192 bytes | 16 kB        | 8192 bytes
 album_users_audit         | 32 kB      | 0 bytes    | 32 kB        | 0 bytes
 system_metadata           | 32 kB      | 8192 bytes | 16 kB        | 8192 bytes
 partners                  | 24 kB      | 0 bytes    | 24 kB        | 0 bytes
 kysely_migrations_lock    | 24 kB      | 8192 bytes | 16 kB        | 0 bytes
 shared_link__asset        | 24 kB      | 0 bytes    | 24 kB        | 0 bytes
 audit                     | 24 kB      | 0 bytes    | 16 kB        | 8192 bytes
 users_audit               | 16 kB      | 0 bytes    | 16 kB        | 0 bytes
 user_metadata             | 16 kB      | 0 bytes    | 8192 bytes   | 8192 bytes
(41 rows)

I have mostly pictures ~1.5TB, with some Videos included.

What can be the reason for this and how can I fix it?

Greetings,
Hendrik

The OS that Immich Server is running on

Debian

Version of Immich Server

v2.0.0

Version of Immich Mobile App

2.0.0

Platform with the issue

  • Server
  • Web
  • Mobile

Device make and model

Google Pixel 9

Your docker-compose.yml content

(1:1 the one delivered from website)

#
# WARNING: To install Immich, follow our guide: https://immich.app/docs/install/docker-compose
#
# Make sure to use the docker-compose.yml of the current release:
#
# https://github.com/immich-app/immich/releases/latest/download/docker-compose.yml
#
# The compose file on main may not be compatible with the latest release.

name: immich

services:
  immich-server:
    container_name: immich_server
    image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release}
    # extends:
    #   file: hwaccel.transcoding.yml
    #   service: cpu # set to one of [nvenc, quicksync, rkmpp, vaapi, vaapi-wsl] for accelerated transcoding
    volumes:
      # Do not edit the next line. If you want to change the media storage location on your system, edit the value of UPLOAD_LOCATION in the .env file
      - ${UPLOAD_LOCATION}:/data
      - /etc/localtime:/etc/localtime:ro
    env_file:
      - .env
    ports:
      - '2283:2283'
    depends_on:
      - redis
      - database
    restart: always
    healthcheck:
      disable: false

  immich-machine-learning:
    container_name: immich_machine_learning
    # For hardware acceleration, add one of -[armnn, cuda, rocm, openvino, rknn] to the image tag.
    # Example tag: ${IMMICH_VERSION:-release}-cuda
    image: ghcr.io/immich-app/immich-machine-learning:${IMMICH_VERSION:-release}
    # extends: # uncomment this section for hardware acceleration - see https://immich.app/docs/features/ml-hardware-acceleration
    #   file: hwaccel.ml.yml
    #   service: cpu # set to one of [armnn, cuda, rocm, openvino, openvino-wsl, rknn] for accelerated inference - use the `-wsl` version for WSL2 where applicable
    volumes:
      - model-cache:/cache
    env_file:
      - .env
    restart: always
    healthcheck:
      disable: false

  redis:
    container_name: immich_redis
    image: docker.io/valkey/valkey:8-bookworm@sha256:fea8b3e67b15729d4bb70589eb03367bab9ad1ee89c876f54327fc7c6e618571
    healthcheck:
      test: redis-cli ping || exit 1
    restart: always

  database:
    container_name: immich_postgres
    image: ghcr.io/immich-app/postgres:14-vectorchord0.4.3-pgvectors0.2.0@sha256:41eacbe83eca995561fe43814fd4891e16e39632806253848efaf04d3c8a8b84
    environment:
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      POSTGRES_USER: ${DB_USERNAME}
      POSTGRES_DB: ${DB_DATABASE_NAME}
      POSTGRES_INITDB_ARGS: '--data-checksums'
      # Uncomment the DB_STORAGE_TYPE: 'HDD' var if your database isn't stored on SSDs
      # DB_STORAGE_TYPE: 'HDD'
    volumes:
      # Do not edit the next line. If you want to change the database storage location on your system, edit the value of DB_DATA_LOCATION in the .env file
      - ${DB_DATA_LOCATION}:/var/lib/postgresql/data
    shm_size: 128mb
    restart: always

volumes:
  model-cache:

Your .env content

1:1 the one from the website, only paths adopted

# You can find documentation for all the supported env variables at https://immich.app/docs/install/environment-variables

# The location where your uploaded files are stored
UPLOAD_LOCATION=/srv/dev-disk-by-uuid-c4a6a2c9-5cf0-49b8-812a-0784953f9ba3/Fotos/Fotos_Immich

# The location where your database files are stored. Network shares are not supported for the database
#DB_DATA_LOCATION=/srv/dev-disk-by-uuid-b4c59765-5685-4439-a886-399209a31637/dockerconfig/immich
DB_DATA_LOCATION=/srv/dev-disk-by-uuid-e5807607-f73e-48e4-8432-615b9cc5436c/immich


# To set a timezone, uncomment the next line and change Etc/UTC to a TZ identifier from this list: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones#List
TZ=Europe/Berlin

# The Immich version to use. You can pin this to a specific version like "v1.71.0"
IMMICH_VERSION=release

# Connection secret for postgres. You should change it to a random password
# Please use only the characters `A-Za-z0-9`, without special characters or spaces
DB_PASSWORD=x

# The values below this line do not need to be changed
###################################################################################
DB_USERNAME=postgres
DB_DATABASE_NAME=immich

Reproduction steps

  1. docker compose down
  2. update of docker-compose.yaml
  3. docker compose up
    ...

Relevant log output

none

I did see some messages on DB-Reindexing

Additional information

No response

Originally created by @henfri on GitHub (Oct 3, 2025). ### I have searched the existing issues, both open and closed, to make sure this is not a duplicate report. - [x] Yes ### The bug Hello, I upgraded to 2.0.0 yesterday and the DB size has grown from 5G to >50G. This is what I tried for finding the reason/fixing: ``` docker exec -it immich_postgres psql -U postgres psql (14.19 (Debian 14.19-1.pgdg12+1)) Type "help" for help. postgres=# \c immich You are now connected to database "immich" as user "postgres". immich=# VACUUM FULL; VACUUM immich=# immich=# \l+                                                                    List of databases    Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   |  Size   | Tablespace |                Description -----------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------  immich    | postgres | UTF8     | en_US.utf8 | en_US.utf8 |                       | 32 GB   | pg_default |  postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |                       | 8569 kB | pg_default | default administrative connection database  template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +| 8417 kB | pg_default | unmodifiable empty database            |          |          |            |            | postgres=CTc/postgres |         |            |  template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +| 8417 kB | pg_default | default template for new databases            |          |          |            |            | postgres=CTc/postgres |         |            | (4 rows) immich=# SELECT     table_name,     pg_size_pretty(pg_total_relation_size(table_name::text)) AS total_size FROM information_schema.tables WHERE table_schema='public' ORDER BY pg_total_relation_size(table_name::text) DESC;         table_name         | total_size ---------------------------+------------  smart_search              | 1383 MB  face_search               | 779 MB  assets                    | 299 MB  asset_files               | 216 MB  geodata_places            | 109 MB  exif                      | 93 MB  asset_faces               | 65 MB  asset_job_status          | 35 MB  tag_asset                 | 15 MB  naturalearth_countries    | 8856 kB  person                    | 2824 kB  albums_assets_assets      | 2400 kB  memories_assets_assets    | 1528 kB  tags                      | 448 kB  tags_closure              | 360 kB  memories                  | 264 kB  assets_audit              | 184 kB  users                     | 96 kB  albums                    | 88 kB  sessions                  | 80 kB  albums_audit              | 72 kB  libraries                 | 64 kB  api_keys                  | 64 kB  move_history              | 64 kB  activity                  | 56 kB  shared_links              | 48 kB  albums_shared_users_users | 40 kB  partners_audit            | 32 kB  kysely_migrations         | 32 kB  session_sync_checkpoints  | 32 kB  notifications             | 32 kB  version_history           | 32 kB  album_users_audit         | 32 kB  system_metadata           | 32 kB  asset_stack               | 32 kB  kysely_migrations_lock    | 24 kB  audit                     | 24 kB  partners                  | 24 kB  shared_link__asset        | 24 kB  user_metadata             | 16 kB  users_audit               | 16 kB (41 rows) immich=# immich=# SELECT     relname AS table_name,     pg_size_pretty(pg_total_relation_size(relid)) AS total_size,     pg_size_pretty(pg_relation_size(relid)) AS table_size,     pg_size_pretty(pg_indexes_size(relid)) AS indexes_size,     pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid) - pg_indexes_size(relid)) AS toast_size FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;         table_name         | total_size | table_size | indexes_size | toast_size ---------------------------+------------+------------+--------------+------------  smart_search              | 1383 MB    | 17 MB      | 7952 kB      | 1358 MB  face_search               | 779 MB     | 19 MB      | 8776 kB      | 752 MB  assets                    | 299 MB     | 125 MB     | 174 MB       | 8192 bytes  asset_files               | 216 MB     | 133 MB     | 82 MB        | 8192 bytes  geodata_places            | 109 MB     | 35 MB      | 73 MB        | 24 kB  exif                      | 93 MB      | 66 MB      | 26 MB        | 72 kB  asset_faces               | 65 MB      | 31 MB      | 34 MB        | 0 bytes  asset_job_status          | 35 MB      | 25 MB      | 10112 kB     | 0 bytes  tag_asset                 | 15 MB      | 5072 kB    | 10 MB        | 0 bytes  naturalearth_countries    | 8856 kB    | 2080 kB    | 104 kB       | 6672 kB  person                    | 2824 kB    | 1872 kB    | 944 kB       | 8192 bytes  albums_assets_assets      | 2400 kB    | 1024 kB    | 1376 kB      | 0 bytes  memories_assets_assets    | 1528 kB    | 592 kB     | 936 kB       | 0 bytes  tags                      | 448 kB     | 176 kB     | 264 kB       | 8192 bytes  tags_closure              | 360 kB     | 112 kB     | 248 kB       | 0 bytes  memories                  | 264 kB     | 144 kB     | 112 kB       | 8192 bytes  assets_audit              | 184 kB     | 56 kB      | 128 kB       | 0 bytes  users                     | 96 kB      | 8192 bytes | 80 kB        | 8192 bytes  albums                    | 88 kB      | 16 kB      | 64 kB        | 8192 bytes  sessions                  | 80 kB      | 8192 bytes | 64 kB        | 8192 bytes  albums_audit              | 72 kB      | 8192 bytes | 64 kB        | 0 bytes  api_keys                  | 64 kB      | 8192 bytes | 48 kB        | 8192 bytes  move_history              | 64 kB      | 8192 bytes | 48 kB        | 8192 bytes  libraries                 | 64 kB      | 8192 bytes | 48 kB        | 8192 bytes  activity                  | 56 kB      | 0 bytes    | 48 kB        | 8192 bytes  shared_links              | 48 kB      | 0 bytes    | 40 kB        | 8192 bytes  albums_shared_users_users | 40 kB      | 0 bytes    | 32 kB        | 8192 bytes  partners_audit            | 32 kB      | 0 bytes    | 32 kB        | 0 bytes  version_history           | 32 kB      | 8192 bytes | 16 kB        | 8192 bytes  asset_stack               | 32 kB      | 0 bytes    | 32 kB        | 0 bytes  session_sync_checkpoints  | 32 kB      | 0 bytes    | 24 kB        | 8192 bytes  notifications             | 32 kB      | 0 bytes    | 24 kB        | 8192 bytes  kysely_migrations         | 32 kB      | 8192 bytes | 16 kB        | 8192 bytes  album_users_audit         | 32 kB      | 0 bytes    | 32 kB        | 0 bytes  system_metadata           | 32 kB      | 8192 bytes | 16 kB        | 8192 bytes  partners                  | 24 kB      | 0 bytes    | 24 kB        | 0 bytes  kysely_migrations_lock    | 24 kB      | 8192 bytes | 16 kB        | 0 bytes  shared_link__asset        | 24 kB      | 0 bytes    | 24 kB        | 0 bytes audit                     | 24 kB      | 0 bytes    | 16 kB        | 8192 bytes  users_audit               | 16 kB      | 0 bytes    | 16 kB        | 0 bytes  user_metadata             | 16 kB      | 0 bytes    | 8192 bytes   | 8192 bytes (41 rows) ``` I have mostly pictures ~1.5TB, with some Videos included. What can be the reason for this and how can I fix it? Greetings, Hendrik ### The OS that Immich Server is running on Debian ### Version of Immich Server v2.0.0 ### Version of Immich Mobile App 2.0.0 ### Platform with the issue - [x] Server - [ ] Web - [ ] Mobile ### Device make and model Google Pixel 9 ### Your docker-compose.yml content ```YAML (1:1 the one delivered from website) # # WARNING: To install Immich, follow our guide: https://immich.app/docs/install/docker-compose # # Make sure to use the docker-compose.yml of the current release: # # https://github.com/immich-app/immich/releases/latest/download/docker-compose.yml # # The compose file on main may not be compatible with the latest release. name: immich services: immich-server: container_name: immich_server image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release} # extends: # file: hwaccel.transcoding.yml # service: cpu # set to one of [nvenc, quicksync, rkmpp, vaapi, vaapi-wsl] for accelerated transcoding volumes: # Do not edit the next line. If you want to change the media storage location on your system, edit the value of UPLOAD_LOCATION in the .env file - ${UPLOAD_LOCATION}:/data - /etc/localtime:/etc/localtime:ro env_file: - .env ports: - '2283:2283' depends_on: - redis - database restart: always healthcheck: disable: false immich-machine-learning: container_name: immich_machine_learning # For hardware acceleration, add one of -[armnn, cuda, rocm, openvino, rknn] to the image tag. # Example tag: ${IMMICH_VERSION:-release}-cuda image: ghcr.io/immich-app/immich-machine-learning:${IMMICH_VERSION:-release} # extends: # uncomment this section for hardware acceleration - see https://immich.app/docs/features/ml-hardware-acceleration # file: hwaccel.ml.yml # service: cpu # set to one of [armnn, cuda, rocm, openvino, openvino-wsl, rknn] for accelerated inference - use the `-wsl` version for WSL2 where applicable volumes: - model-cache:/cache env_file: - .env restart: always healthcheck: disable: false redis: container_name: immich_redis image: docker.io/valkey/valkey:8-bookworm@sha256:fea8b3e67b15729d4bb70589eb03367bab9ad1ee89c876f54327fc7c6e618571 healthcheck: test: redis-cli ping || exit 1 restart: always database: container_name: immich_postgres image: ghcr.io/immich-app/postgres:14-vectorchord0.4.3-pgvectors0.2.0@sha256:41eacbe83eca995561fe43814fd4891e16e39632806253848efaf04d3c8a8b84 environment: POSTGRES_PASSWORD: ${DB_PASSWORD} POSTGRES_USER: ${DB_USERNAME} POSTGRES_DB: ${DB_DATABASE_NAME} POSTGRES_INITDB_ARGS: '--data-checksums' # Uncomment the DB_STORAGE_TYPE: 'HDD' var if your database isn't stored on SSDs # DB_STORAGE_TYPE: 'HDD' volumes: # Do not edit the next line. If you want to change the database storage location on your system, edit the value of DB_DATA_LOCATION in the .env file - ${DB_DATA_LOCATION}:/var/lib/postgresql/data shm_size: 128mb restart: always volumes: model-cache: ``` ### Your .env content ```Shell 1:1 the one from the website, only paths adopted # You can find documentation for all the supported env variables at https://immich.app/docs/install/environment-variables # The location where your uploaded files are stored UPLOAD_LOCATION=/srv/dev-disk-by-uuid-c4a6a2c9-5cf0-49b8-812a-0784953f9ba3/Fotos/Fotos_Immich # The location where your database files are stored. Network shares are not supported for the database #DB_DATA_LOCATION=/srv/dev-disk-by-uuid-b4c59765-5685-4439-a886-399209a31637/dockerconfig/immich DB_DATA_LOCATION=/srv/dev-disk-by-uuid-e5807607-f73e-48e4-8432-615b9cc5436c/immich # To set a timezone, uncomment the next line and change Etc/UTC to a TZ identifier from this list: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones#List TZ=Europe/Berlin # The Immich version to use. You can pin this to a specific version like "v1.71.0" IMMICH_VERSION=release # Connection secret for postgres. You should change it to a random password # Please use only the characters `A-Za-z0-9`, without special characters or spaces DB_PASSWORD=x # The values below this line do not need to be changed ################################################################################### DB_USERNAME=postgres DB_DATABASE_NAME=immich ``` ### Reproduction steps 1. docker compose down 2. update of docker-compose.yaml 3. docker compose up ... ### Relevant log output ```shell none I did see some messages on DB-Reindexing ``` ### Additional information _No response_
Sign in to join this conversation.
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: immich-app/immich#7414