Question: How to support concurrent operations?

[brian-penguin]

Since all the migrations are applied within a transaction by default, is there a way to apply a migration without the transaction? I would like to be able to add indexes to large frequently accessed tables without locking the table.

[Billuc]

Hi !
I am no Postgres expert, so correct me if I am wrong, please.
At first glance, I dont think transactions are the culprits here, especially since Postgres runs everything in transactions under the hood either way.
By looking though the docs, I came to think that lock are what is blocking you:

SHARE (ShareLock)

Conflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent data changes.

Acquired by CREATE INDEX (without CONCURRENTLY).

Have you tried using CREATE INDEX CONCURRENTLY ? It seems to match your use case and it doesn't lock data access, only schema changes.

Source: https://www.postgresql.org/docs/current/explicit-locking.html

[brian-penguin]

I've got some time on Friday, let me see if I can mock up an example app then

[brian-penguin]

Got a little more info:

I'm running cigogne 5.0.4 and using a postgres 18 instance running in docker from pgvector/pgvector:0.8.1-pg18-trixie

A migration like this:

--- migration:up
CREATE INDEX CONCURRENTLY idx_todo_items_created_at ON todo_items (created_at);

--- migration:down
DROP INDEX IF EXISTS idx_todo_items_created_at;

--- migration:end

Will give me an error when I run gleam run -m cigogne up

Applying migration 20251031154116-index_todo_created_at
Database error: Transaction rolled back : Postgresql error: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

I'm not sure but I'm looking at https://github.com/Billuc/cigogne/blob/main/src/cigogne.gleam#L498 and it looks like we are calling database.apply_migration_no_transaction(db_data, migration) with a db_data where the connection is a database.transaction and I'm wondering if that's applying as a transaction despite executing using the connection

I think as default behavior this is exactly what I would want. The whole of the migrate:up or migrate:down should always be wrapped in a transaction unless I'm confident that I want the operation to run without it.

I'm wondering if there's room to support something like

--- migration:up:disable_ddl_transaction
CREATE INDEX CONCURRENTLY idx_todo_items_created_at ON todo_items (created_at);

--- migration:down
DROP INDEX IF EXISTS idx_todo_items_created_at;

--- migration:end

[Billuc]

I think your idea of adding a flag like disable_transaction is the best way of solving this issue. Whenever this flag is set, we should indeed use apply_migration_no_transaction. Maybe we could have a check before applying the migration to make sure there is only one statement to execute.

This will need quite some changes, as the apply_migrations you mentionned will have to change.
Also, I should give some thought to how this would interact with dependency migrations. Since we merge all dependency migrations into one big migration, how do we handle the fact that there can be migrations with transaction enabled and disabled ?

Just thinking out loud here. I will try to take some time to do this, but I doubt I can do it this week.

[Billuc]

@brian-penguin I got time to develop something to add the "disable_transaction" flag. Do you want / have time to review the PR ?