apache / apache/gravitino

[Improvement] Introduce a schema_version table to track database schema version for reliable upgrade execution

Open
#12,519 0 comments 0 reactions 0 assignees View on GitHub
improvement
Dominant language
Java
Stars
3.2k
Forks
935
Avg merge
1d 17h
Merged PRs (30d)
339

Description

## What would you like to be improved?

Currently, the Gravitino database schema has no mechanism to record which schema version is currently applied. The upgrade scripts (e.g. `upgrade-1.1.0-to-1.2.0-postgresql.sql`) are executed by detecting whether the `metalake_meta` table exists to distinguish a fresh install from an upgrade, then running **all** upgrade scripts whose target version is `<= current app version` — regardless of whether they have already been applied.

This leads to two concrete problems:

1. **Non-idempotent upgrade scripts fail silently.** Scripts like `upgrade-0.8.0-to-0.9.0` contain statements such as `ALTER TABLE ... ADD COLUMN` and `DROP CONSTRAINT` / `ADD CONSTRAINT` without `IF NOT EXISTS` / `IF EXISTS` guards. Re-running them on an already-upgraded database causes errors. To avoid blocking the upgrade, the Helm currently swallows these errors with a `WARNING` and continues — meaning a genuine failure (wrong permissions, disk full, etc.) is indistinguishable from an expected "already applied" skip.

2. **Multi-hop upgrade is fragile.** A database at version 1.2.0 upgrading to 2.0.0 will re-execute `upgrade-0.x.0-to-*.sql` scripts that were applied long ago, relying entirely on SQL-level idempotency that is not consistently guaranteed across all scripts.

## How should we improve?

Introduce a `schema_version` table to persist the current schema version in the database itself:

```sql
CREATE TABLE IF NOT EXISTS schema_version (
version VARCHAR(32) NOT NULL,
upgraded_at BIGINT NOT NULL,
PRIMARY KEY (version)
);
```

Each schema file and upgrade script should `INSERT` the applicable version into this table on completion. The upgrade runner (Helm Job / init-container) should:

1. Read the current version from `schema_version` (if the table exists).
2. Only execute upgrade scripts whose source version `>` current DB version and target version `<=` app version.
3. `INSERT` the new version after each script succeeds.
4. Fail fast (`exit 1`) on any upgrade script error rather than silently continuing, since with precise version tracking there is no ambiguity about whether an error is "expected".

This approach eliminates the reliance on SQL idempotency as a safety net, makes multi-hop upgrades explicit and auditable, and allows operators to immediately see the applied schema version by querying the database.

Contributor guide

Open the contributing guide

Research direction

Start by tracing the Helm Job/init-container upgrade runner and the existing files such as upgrade-1.1.0-to-1.2.0-postgresql.sql and upgrade-0.8.0-to-0.9.0. Verify how fresh installs and multi-hop upgrades are detected, then define completion around recording each applied version, selecting only applicable scripts, and failing on script errors instead of continuing.

Written by the indexing model from the issue text.

Assessment

Tech stack
helm, kubernetes, sql
Domain
databases, devops, infrastructure
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.