What you should check before, during and after your operation:

EXPLAIN ANALYZE the SELECT alone first. Does it use efficient indexes or sequential scans?

Monitor the disk I/O and WAL generation with tools like:-

pg_stat_bgwriter (checkpoint and buffer usage stats)

pg_stat_io (in PG16+, detailed I/O statistics)

OS-level tools: iostat, vmstat, or atop.

Watch transaction duration. Long transactions can block vacuum.

Use pg_stat_activity to see if your query is causing waits or blocking others.

Look for lock contention in pg_locks.

If you want to reduce impact on other users:

Instead of a single huge INSERT INTO ... SELECT, break it into chunks, like this:

INSERT INTO target_table (col1, col2, ...)

SELECT col1, col2, ...

FROM source_table

WHERE some_condition

ORDER BY some_column

LIMIT 10000

OFFSET N;

Then loop your client or script to step N += batch size.

This shortens each transaction and avoids holding locks for too long.

Use pg_background or parallel job frameworks

Run batches asynchronously or schedule during low-traffic times.

Consider CREATE UNLOGGED TABLE for temp use

If you just need intermediate storage and can afford data loss on crash.

Adjust maintenance_work_mem & work_mem

Increase these parameters before the operation to improve performance of sorts or index creation (but only if you have enough RAM).

Run during maintenance windows

Especially on OLTP systems, to avoid impacting peak hours.

Monitor system resources

Before you start, check CPU, memory, and disk throughput headroom. On production, run on a staging system first if possible.