Why does databasechangelog not have a primary key?

I think (Nathan can add to this if I am incorrect) that there just isn’t much need for one. Nothing else references it, the table probably rarely grows beyond 100,000 rows (and most times far fewer than that), and creating a unique constraint on it in a database-independent way would make the code far more complex with little benefit. I think one of the design goals was for the Liquibase table to have as little impact on the database as possible.

Steve Donie
Principal Software Engineer
Datical, Inc. http://www.datical.com/

I noticed that the table databasechangelog does not have a primary key.

What is the reason behind that?

Wouldn’t it make sense to create it with a primary key on (id, author, filename) - or at least a unique index on it?

Thanks for the answer.

As I assume Liquibase is doing lookups on the table, a unique index would make sense especially if the table does grow that much. Without an index the database always needs to scan the entire table if Liquibase checks if a changeset has already been applied. So the impact without an index is actually bigger than with the index.

And I don’t see why this would make the code more complex? Liquibase can already create a unique index in database independent way ().

The main answer is because we try to keep things consistent across database types and there are some databases that have max key sizes larger than the author + id + filename values can be.

Originally there was a PK but we were running into problems where people needed a longer path and/or author field and there was no way to increase that due to the PK constraints.

Since we really only do a full table scan anyway, it was better to drop the PK and increase the column sizes than to keep the PK that is rarely to never used and be either stuck with smaller columns or have different table definitions per database type.

You can always manually add a primary key if you like, Liquibase will not care.

Nathan

1 Like

Yeah, seems like a limit we shouldn’t be hitting, but we did.

I don’t remember what databases ran into problems but I may look at re-enabling it for supported databases in 4.0.

Nathan

and there are some databases that have max key sizes larger than the author + id + filename values can be.

Wow. Really? Those only add up to a maximum of 765 characters.

I didn’t think there were any modern DBMS that can’t index that.

Originally there was a PK

Ah! I thought I saw PKs with earlier versions. But I wasn't sure.

Thanks for the insight.

I’ve run into a problem because of the lack of a primary key on databasechangelog - on MySQL running with safe update mode the updates to that table fail. I don’t know if it would be easy to make it database specific, but on MySQL I have to create my own primary key because Liquibase is unusable without one.

Oh, stupidity. Why did you create design when the only why to search table is the sequence scan? Correct index will improve the search a lot. And in case of PostgreSQL the primary key must be for logical replication, for instance for upgrade or move a database under a load by logical replication. So I must for every database add primary key for your table manually.

Btw what is the select you do on this table and what is the primary key must be?

The decision to not have a primary key on the databasechangelog table hit us many times.
MariaDB / Galera Cluster: Every table needs a primary key for master/master replication
PostgreSQL: Postgres needs a primary key on every table for replication (as Yahont already stated). Also, you cannot edit table contents which do not have a primary key in PgAdmin4.

Why not setting id column as primary key?

According to DATABASECHANGELOG table | Liquibase Docs

“There is no primary key on the table. This is to avoid any database-specific restrictions on key lengths. The composite of ‘id’, ‘author’, and ‘filename’ is unique across all rows of the table.”

@NathanVoxland has said in a Liquibase monthly meetup that you can put the primary key on the table yourself.