Vertica database support

[cookielevanna5]

Hi, I’m evaluating golang-migrate for managing database migrations in a Vertica environment. I noticed that Vertica is not explicitly listed among the supported databases.

Could you clarify whether golang-migrate can work with Vertica via generic PostgreSQL compatibility or drivers, and what limitations exist in such a setup (e.g., migrations, rollbacks, schema introspection)?

Any guidance or best practices for using golang-migratewith Vertica would be appreciated.

[ziqq]

There is no Vertica driver in golang-migrate, and using the postgres driver against Vertica won't work reliably even though Vertica's client protocol is derived from PostgreSQL's. The postgres driver relies on PostgreSQL-specific features, most notably pg_advisory_lock() for locking and PostgreSQL catalog queries, which Vertica doesn't have, so it fails on Lock() or when creating schema_migrations.

The practical option is a small custom driver. golang-migrate drivers are just implementations of the database.Driver interface (Open, Close, Lock, Unlock, Run, SetVersion, Version, Drop), registered with database.Register("vertica", ...). You can use vertica-sql-go as the database/sql driver and copy the structure of one of the simpler existing drivers in database/ (e.g. the ClickHouse one, which also lacks advisory locks).

Limitations to plan for:

• Locking: there are no advisory locks, so either implement Lock() with a row in a lock table, or make sure only one migrator runs at a time (e.g. a single CI job).
• Transactions/rollbacks: in Vertica most DDL statements commit implicitly, so a migration with several DDL statements is not atomic. If one fails halfway you will end up with a dirty version and need migrate force. Keep each migration to one DDL statement where possible.
• Down migrations work like everywhere else (they are just SQL files), but given the point above, write them so they can be re-run safely (DROP ... IF EXISTS).