postgres bulk uptate batch - ghdrako/doc_snipets GitHub Wiki

https://www.crunchydata.com/blog/simulating-update-or-delete-with-limit-in-postgres-ctes-to-the-rescue

https://medium.com/@siddarthpatil/strategies-for-efficiently-updating-millions-of-records-in-postgresql-46afe980cf6b https://stackoverflow.com/questions/67091815/how-to-update-a-single-column-in-postgres-in-a-batch-for-55-million-records

Using ctid

DO $$ 
DECLARE
    ctid_min TEXT;
    ctid_max TEXT;
    batch_size INTEGER := 1000; -- Rozmiar partii (ilość wierszy na raz)
BEGIN
    LOOP
        -- Pobierz minimalny i maksymalny ctid dla danej partii
        SELECT min(ctid), max(ctid) 
        INTO ctid_min, ctid_max
        FROM (SELECT ctid FROM example_table ORDER BY ctid LIMIT batch_size) AS subquery;

        -- Jeśli brak więcej rekordów, zakończ pętlę
        EXIT WHEN ctid_min IS NULL;

        -- Wykonaj aktualizację dla bieżącej partii
        UPDATE example_table
        SET column_name = new_value
        WHERE ctid >= ctid_min AND ctid <= ctid_max;

        -- Usuń już zaktualizowane rekordy z kolejki
        DELETE FROM example_table WHERE ctid >= ctid_min AND ctid <= ctid_max;
    END LOOP;
END $$;