This page documents the PostgreSQL database schema used by the Immich server: the key tables and their purposes, how schema definitions are organized in TypeScript, the vector extension setup for ML embeddings, migration management via Kysely, advisory lock usage, and schema drift detection.
For information about how environment variables configure the database connection, see 7.2. For information about how data entities map to filesystem storage, see 2.6. For information on the background job system that writes to many of these tables, see 3.2.
Immich uses PostgreSQL (minimum version range enforced at startup, defined as POSTGRES_VERSION_RANGE in server/src/constants.ts) with Kysely as the type-safe SQL query builder. The database interface is accessed through the DB TypeScript type exported from server/src/schema/index.ts, which aggregates all table definitions.
All repository classes receive a Kysely<DB> instance injected via the @InjectKysely() decorator from nestjs-kysely.
| Layer | Technology |
|---|---|
| Database engine | PostgreSQL ≥ 14 |
| Query builder | Kysely |
| Schema definition | TypeScript table classes (src/schema/tables/) |
| Migration runner | Kysely Migrator (FileMigrationProvider) |
| Schema comparison | @immich/sql-tools (schemaDiff, schemaFromCode, schemaFromDatabase) |
| Vector indexing | pgvector, pgvecto.rs, or vectorchord (one required) |
Sources: server/src/repositories/database.repository.ts1-28 server/src/app.module.ts56-58
Schema definitions live under server/src/schema/. Each database table has a corresponding TypeScript class in server/src/schema/tables/. These are not ORM entities — they are pure Kysely column type definitions used to build the DB type.
Schema table definition files:
| File | Table Name | Description |
|---|---|---|
asset.table.ts | asset | Core asset records (photos, videos) |
asset-exif.table.ts | asset_exif | EXIF metadata extracted per asset |
asset-file.table.ts | asset_file | Generated files (thumbnails, previews, fullsize) |
asset-job-status.table.ts | asset_job_status | Per-asset job completion tracking |
asset-metadata.table.ts | asset_metadata | Additional key-value metadata per asset |
album.table.ts | album | Album records |
album-user.table.ts | album_user | Album–user membership with role |
person.table.ts | person | Recognized people |
face-search.table.ts | face_search | Face embedding vectors |
smart-search.table.ts | smart_search | CLIP embedding vectors |
memory.table.ts | memory | Memory records (e.g., "On this day") |
session.table.ts | session | User sessions |
stack.table.ts | stack | Asset stacks |
tag.table.ts | tag | Tags |
tag-asset.table.ts | tag_asset | Tag–asset associations |
user.table.ts | user | User accounts |
The DB type in src/schema/index.ts is the union of all table definitions, consumed by every Kysely<DB> instance in the codebase.
Sources: server/test/medium.factory.ts57-70 server/src/repositories/database.repository.ts24
Entity Relationship Diagram (core tables)
Sources: server/test/medium.factory.ts57-70 server/src/repositories/database.repository.ts240-275
Beyond the core domain entities, several tables manage system state:
| Table | Purpose |
|---|---|
system_metadata | Key-value store for system configuration (see SystemMetadataKey enum). Stores SystemConfig, maintenance mode state, geocoding state, etc. |
user_metadata | Key-value store for per-user preferences (see UserMetadataKey enum). Stores UserPreferences, license info, onboarding state. |
api_key | User API key hashes and permissions |
shared_link | Shared link tokens with expiry and password options |
partner | Partner sharing relationships between users |
library | External library definitions with import paths |
move | Tracks in-progress file path migrations |
audit | Audit log of entity create/update/delete events |
version_history | History of Immich server versions seen |
notification | User notification records |
activity | Album activity / comment records |
duplicate | Detected duplicate asset pairs |
sync_checkpoint | Mobile sync checkpoint and ack state per session |
ocr_text | OCR text extracted from assets |
workflow | Plugin workflow definitions |
kysely_migrations | Migration history (managed by Kysely) |
kysely_migrations_lock | Distributed migration lock (managed by Kysely) |
The system_metadata table is accessed via SystemMetadataRepository and its key type is SystemMetadataKey (defined in server/src/enum.ts). The full set of keys and their value shapes is declared in the SystemMetadata interface in server/src/types.ts483-494
Sources: server/src/types.ts483-494 server/src/types.ts541-550
Immich requires one of three PostgreSQL vector extensions for ML embedding storage and similarity search. The extension is selected automatically or forced via the DB_VECTOR_EXTENSION environment variable.
DatabaseExtension enum value | Extension name | DB_VECTOR_EXTENSION value |
|---|---|---|
DatabaseExtension.VectorChord | vectorchord | vectorchord |
DatabaseExtension.Vectors | vectors (pgvecto.rs) | pgvecto.rs |
DatabaseExtension.Vector | vector (pgvector) | pgvector |
Auto-detection priority (if DB_VECTOR_EXTENSION is not set) checks available extensions in the order: vectorchord → vectors → vector.
Note:
pgvecto.rs(DatabaseExtension.Vectors) is deprecated and will be removed. A deprecation warning is logged at startup. Seemessages.deprecatedExtensionin server/src/services/database.service.ts56-59
Two tables hold vector embeddings:
VectorIndex enum | Index name | Table | Column | Purpose |
|---|---|---|---|---|
VectorIndex.Clip | clip_index | smart_search | embedding | CLIP semantic search embeddings |
VectorIndex.Face | face_index | face_search | embedding | Face recognition embeddings |
| Extension | Index algorithm | Detection string in indexdef |
|---|---|---|
pgvector | HNSW | using hnsw |
pgvecto.rs | vectors | using vectors |
vectorchord | vchordrq | using vchordrq |
DatabaseRepository.reindexVectorsIfNeeded() dynamically adjusts the lists parameter for VectorChord indexes based on row count. The targetListCount() method implements:
count < 128,000 → 1 listcount < 2,048,000 → next power-of-2 of count / 1000count ≥ 2,048,000 → power-of-2 based on √countA VECTORCHORD_LIST_SLACK_FACTOR prevents reindexing when the count is only borderline over a threshold.
The probes object in DatabaseRepository (server/src/repositories/database.repository.ts50-53) tracks the current probe count per index at runtime.
Vector Extension Lifecycle
Sources: server/src/services/database.service.ts62-145 server/src/repositories/database.repository.ts50-275 server/src/repositories/config.repository.ts217-231
Migrations are plain TypeScript or SQL files stored in server/src/schema/migrations/. They are run by Kysely's Migrator class, which tracks applied migrations in the kysely_migrations table and uses kysely_migrations_lock for distributed safety.
DatabaseRepository.createMigrator() constructs the migrator with:
migrationTableName: 'kysely_migrations'migrationLockTableName: 'kysely_migrations_lock'migrationFolder: join(__dirname, '..', 'schema/migrations')allowUnorderedMigrations: true in development mode onlyKey methods on DatabaseRepository:
| Method | Purpose |
|---|---|
runMigrations() | Runs migrateToLatest(), logs each result |
revertLastMigration() | Runs migrateDown() one step, returns reverted migration name |
getMigrations() | Queries kysely_migrations for applied migration list |
CLI migration tool (server/src/bin/migrations.ts) supports:
| Command | Action |
|---|---|
generate <name> | Computes schemaDiff between code and live DB, writes new migration file |
create <name> | Creates empty migration file |
run | Applies all pending migrations |
revert | Reverts last migration |
debug | Prints the current schema diff |
Migration flow at startup:
Sources: server/src/repositories/database.repository.ts384-537 server/src/bin/migrations.ts14-59 server/src/services/database.service.ts62-145
PostgreSQL advisory locks (pg_advisory_lock / pg_advisory_unlock) are used to coordinate concurrent processes. The DatabaseLock enum (in server/src/enum.ts) assigns integer IDs to each lock type.
DatabaseRepository.withLock(lock, callback) (server/src/repositories/database.repository.ts450-464) acquires a session-level advisory lock, executes callback, then releases. An AsyncLock wrapper prevents concurrent in-process acquisition of the same lock name.
DatabaseRepository.tryLock(lock) uses pg_try_advisory_lock for non-blocking acquisition, returning a boolean.
DatabaseLock value | Where used | Purpose |
|---|---|---|
DatabaseLock.Migrations | DatabaseService.onBootstrap() | Serializes extension creation + migration runs across multiple server instances |
DatabaseLock.GetSystemConfig | getConfig() in src/utils/config.ts | Prevents concurrent system config cache rebuilds |
DatabaseLock.MaintenanceOperation | main.ts bootstrap | Blocks normal worker startup while a maintenance operation is in progress |
Sources: server/src/repositories/database.repository.ts450-491 server/src/main.ts67-91 server/src/utils/config.ts35-43
At each server startup, after migrations complete, DatabaseService.onBootstrap() calls DatabaseRepository.getSchemaDrift() to compare the TypeScript-defined schema against the live database.
getSchemaDrift() (server/src/repositories/database.repository.ts290-306) uses @immich/sql-tools:
schemaFromCode({ overrides: true, namingStrategy: 'default' }) — reads all imported TypeScript table definitionsschemaFromDatabase({ connection: database.config }) — introspects the live PostgreSQL instanceschemaDiff(source, target, options) — computes the differenceDrift detection configuration:
| Schema element | ignoreExtra | Effect |
|---|---|---|
tables | true | Extra tables in DB do not cause warnings |
constraints | false | Missing constraints are reported as drift |
indexes | true | Extra indexes in DB are ignored |
triggers | true | Extra triggers in DB are ignored |
columns | true | Extra columns in DB are ignored |
functions | false | Missing functions are reported as drift |
parameters | true | Extra parameters are ignored |
Drift is logged as a warning; it does not block startup.
Schema Drift and Migration Tooling
Sources: server/src/repositories/database.repository.ts286-306 server/src/bin/migrations.ts84-140
smart_search Table: Dimension ManagementThe smart_search table requires special handling because the CLIP model's embedding dimension can change when switching models.
DatabaseRepository.setDimensionSize(dimSize) (server/src/repositories/database.repository.ts327-356) performs:
smart_searchdim_size_constraint check: array_length(embedding::real[], 1) = dimSizeclip_indexembedding column type to vector(dimSize)VACUUM ANALYZE smart_searchdeleteAllSearchEmbeddings() truncates smart_search when re-embedding is needed (e.g., after a model change).
Sources: server/src/repositories/database.repository.ts327-360
migrateFilePaths UtilityDatabaseRepository.migrateFilePaths(sourceFolder, targetFolder) (server/src/repositories/database.repository.ts409-448) bulk-updates file paths in a single transaction using REGEXP_REPLACE. It touches:
asset.originalPath, asset.encodedVideoPathasset_file.pathperson.thumbnailPathuser.profileImagePathThis is used by the CLI migrate-file-paths command when the storage location changes.
Sources: server/src/repositories/database.repository.ts409-448
Refresh this wiki
This wiki was recently refreshed. Please wait 2 days to refresh again.