Updating the smart search model causes Postgres syntax error

[briantjkuo]

The bug

I updated the smart search model to immich-app/ViT-H-14-378-quickgelu__dfn5b, which caused a CLIP dimension size mismatch with the database. The server threw a syntax error as a result of changing the dimension size to 'vector(1024)'.

The OS that Immich Server is running on

Ubuntu 24.04.1 LTS

Version of Immich Server

v1.125.1

Version of Immich Mobile App

N/A

Platform with the issue

• Server
• Web
• Mobile

Your docker-compose.yml content

#
# WARNING: 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}:/usr/src/app/upload
      - /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, openvino] 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, openvino, openvino-wsl] 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/redis:6.2-alpine@sha256:eaba718fecd1196d88533de7ba49bf903ad33664a92debb24660a922ecd9cac8
    healthcheck:
      test: redis-cli ping || exit 1
    restart: always

  database:
    container_name: immich_postgres
    image: docker.io/tensorchord/pgvecto-rs:pg14-v0.2.0@sha256:90724186f0a3517cf6914295b5ab410db9ce23190a2d9d0b9dd6463e3fa298f0
    environment:
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      POSTGRES_USER: ${DB_USERNAME}
      POSTGRES_DB: ${DB_DATABASE_NAME}
      POSTGRES_INITDB_ARGS: '--data-checksums'
    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
    healthcheck:
      test: >-
        pg_isready --dbname="$${POSTGRES_DB}" --username="$${POSTGRES_USER}" || exit 1;
        Chksum="$$(psql --dbname="$${POSTGRES_DB}" --username="$${POSTGRES_USER}" --tuples-only --no-align
        --command='SELECT COALESCE(SUM(checksum_failures), 0) FROM pg_stat_database')";
        echo "checksum failure count is $$Chksum";
        [ "$$Chksum" = '0' ] || exit 1
      interval: 5m
      start_interval: 30s
      start_period: 5m
    command: >-
      postgres
      -c shared_preload_libraries=vectors.so
      -c 'search_path="$$user", public, vectors'
      -c logging_collector=on
      -c max_wal_size=2GB
      -c shared_buffers=512MB
      -c wal_compression=on
    restart: always

volumes:
  model-cache:

Your .env content

# 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=/mnt/immich/library
# The location where your database files are stored
DB_DATA_LOCATION=./postgres

# 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=Etc/UTC

# 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=redacted_password

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

Reproduction steps

1. In Immich GUI, navigate to admin settings and change smart search model to immich-app/ViT-H-14-378-quickgelu__dfn5b.
2. Upon saving the new model, the syntax error is displayed in immich_server logs, but the service did not immediately exit.
3. When docker container is restarted at a later time, immich_server throws the same error and fails to start.

Relevant log output

[Nest] 7  - 01/24/2025, 8:47:56 AM     LOG [Microservices:SmartInfoService] Dimension size of model immich-app/ViT-H-14-378-quickgelu__dfn5b is 1024, but database expects 512.
[Nest] 7  - 01/24/2025, 8:47:56 AM     LOG [Microservices:SmartInfoService] Updating database CLIP dimension size to 1024.
Query failed : {
  durationMs: 1.010298999999577,
  error: PostgresError: syntax error at or near "'vector(1024)'"
      at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:788:26)
      at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:474:6)
      at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
      at Socket.emit (node:events:524:28)
      at addChunk (node:internal/streams/readable:561:12)
      at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
      at Readable.push (node:internal/streams/readable:392:5)
      at TCP.onStreamRead (node:internal/stream_base_commons:189:23) {
    severity_local: 'ERROR',
    severity: 'ERROR',
    code: '42601',
    position: '58',
    file: 'scan.l',
    line: '1176',
    routine: 'scanner_yyerror'
  },
  sql: `alter table "smart_search" alter column "embedding" type 'vector(1024)'`,
  params: []
}
PostgresError: syntax error at or near "'vector(1024)'"
    at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:788:26)
    at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:474:6)
    at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
    at Socket.emit (node:events:524:28)
    at addChunk (node:internal/streams/readable:561:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
    at Readable.push (node:internal/streams/readable:392:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:189:23) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42601',
  position: '58',
  file: 'scan.l',
  line: '1176',
  routine: 'scanner_yyerror'
}
microservices worker error: PostgresError: syntax error at or near "'vector(1024)'"
microservices worker exited with code 1
Killing api process

Additional information

No response

[dionorgua]

Same here.

[dionorgua]

Just extra quotas. As a workaround it's possible to execute query manually:
https://immich.app/docs/guides/database-queries/

#docker exec -it immich_postgres psql --dbname=immich --username=postgres
immich=# alter table "smart_search" alter column "embedding" type vector(768);
ALTER TABLE

Note: change 768 to desired value from log

[he2121]

same question

[paulthvt]

Just extra quotas. As a workaround it's possible to execute query manually: https://immich.app/docs/guides/database-queries/

#docker exec -it immich_postgres psql --dbname=immich --username=postgres
immich=# alter table "smart_search" alter column "embedding" type vector(768);
ALTER TABLE

Note: change 768 to desired value from log

This fixed the issue on my side. Thanks @dionorgua! 🙏

[Vinay1a1]

Thanks for the fix. I also got the same error.

[Vinay1a1]

Okay, it is still not working and giving syntax error.

[mertalev]

This was fixed in #15630. You can either a) wait for the next release, b) run main temporarily to apply the fix, or c) run the below SQL commands:

truncate smart_search;
-- change 768 to the right size for your model
alter table smart_search alter column embedding set data type vector(768);
reindex index clip_index;

If running these commands, refer to this section to find the right dimension size for your model.