Incremental cleaning of table (PostgreSQL)

db_clean_readonly.sh

#!/bin/bash

START_ID=300000000
GLOBAL_END_ID=478367875
CHUNK_SIZE=${BATCH_SIZE:-500000}
JSON_CONDITION='{"read": true}'
PAUSE=30

DRY_RUN=false
if [[ "$1" == "--dry-run" ]]; then
    DRY_RUN=true
fi

CURRENT_ID=$START_ID

if [ "$DRY_RUN" = true ]; then
    echo "==> STARTING DRY RUN (No records will be deleted)"
else
    echo "==> STARTING ACTUAL DELETION"
fi

echo "==> Range: ID $START_ID to $GLOBAL_END_ID"
echo "==> Chunk Size: $CHUNK_SIZE"
echo "==> Condition: changes = '$JSON_CONDITION'"

while [ "$CURRENT_ID" -lt "$GLOBAL_END_ID" ]; do

    NEXT_ID=$((CURRENT_ID + CHUNK_SIZE))

    # Cap the batch end if it exceeds the global limit
    if [ "$NEXT_ID" -gt "$GLOBAL_END_ID" ]; then
        NEXT_ID=$GLOBAL_END_ID
    fi

    echo "Processing range: [$CURRENT_ID - $NEXT_ID)..."

    if [ "$DRY_RUN" = true ]; then
        OUTPUT=$(psql "$IDENTITY_MIGRATIONS_DB_URL" -t -A -c "
            SELECT count(*) FROM auditlog_logentry
            WHERE id >= $CURRENT_ID
            AND id < $NEXT_ID
            AND changes = '$JSON_CONDITION';
        ")
        COUNT=${OUTPUT:-0}
        RESULT="Would delete: $COUNT records"
    else
        OUTPUT=$(psql "$IDENTITY_MIGRATIONS_DB_URL" -c "
            DELETE FROM auditlog_logentry
            WHERE id >= $CURRENT_ID
            AND id < $NEXT_ID
            AND changes = '$JSON_CONDITION';
        ")
        RESULT=$(echo "$OUTPUT" | grep "DELETE" | xargs)
    fi

    echo "   -> Result: $RESULT"

    CURRENT_ID=$NEXT_ID

    sleep $PAUSE

done

ccho "==> Done."