ETL - sql orchestrator is stuck when there is not sleep() between queries

Hello guys, I'm facing a problem. Currently I'm working on a Data
transformation Pipeline on Postgres. The strategy is,

We select every tables in a given schema ( 50 tables ), we apply some case
when, translation, enum and load it into a different new schema with a
CREATE TABLE SCHEMA_2.table_1 AS SELECT * FROM SCHEMA_1.TABLE_1, then we do
it again about 3 more times and everytime it’s a new schema, new table. We
only keep and don’t drop the schema1.

To orchestrate the whole, we've got a bunch of .sql files that we run by
using psql directly. That's our "strategy".

So we're copying a lot of data, but it allows us to debug, and investigate
business bugs, because we can plug us into schema 2,3 and search why it's
an issue.

All is fine, and can work great.
But sometimes, some queries that used to take about 20 secs to complete can
suddenly end in 5mins.
Important all queries have the same shape -> CREATE TABLE SELECT AS *(a bit
of transform) FROM TABLE). No update, nothing, it’s dead simple.

We are just trying to copy a table from schema1, to schema2, to schema3 and
finally schema3. That’s it.
The thing to understand here is schema2, schema3 are dropped at every
pipeline transformation, so everytime we run the script, it drops
everything from schema2 to the final stage.

We tuned the config a little bit, and we tried kind of everything (
synchronous_commit, wal, vacuum )
Nothing works, it’s very random, some query won’t simply work ( even after
hours ).

We use different machines, different config, and different datasets.

The only thing that makes it work every time, in 100% cases, is to put a
sleep(10sec) between each schema.
So we select 50 tables, we create a new schema with it, then we sleep 10
sec then we do again the same query but with the freshly created schema and
we create a third schema, sleep 10s and again..

And that makes the whole pipeline successful each time.

So, It seems it's a background process inside postgres, that should ingest
a lot of data, and we have to give him time to take a rest, like a
bg_writers or something else ?
I disabled autovacuum=off . Same.
Why does the query never end even after hours ? Why there is no log about
where the query is stuck.
To be clear, if I kill the stuck query and run again it will work.

I don't know much about what's going on inside Postgres, which randomly
takes a lot of time, with the same code, same data.

PostgreSQL 12.4 (Debian 12.4-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

Thank you so much for your time..