batcher

README

batcher

A utility to perform large updates or deletes in batches to improve performance. TRUNCATE TABLE is obviously a faster way to purge an entire table, but in many cases, you have an enormous table, of which you need to remove or update a chunk. Copying the rows you want to keep (for a mass delete) can have all sorts of referential constraint issues.

After testing many different approaches, I've created this, which generates singleton updates or deletes of the rows in question. If you want to, you can output the generated SQL to a flat file and process it in some other way. If you use the -execute option, batcher will use Go's internal concurrency to perform your mass update without having to worry about long transactions or the excruciating slowness of some databases when doing set operations.

No names, no packdrill.

Installation

Binaries for Mac, Linux and Windows, as well as the source code in zip and tar.gz can be found here.

If you want to build the source, I used Go 1.15.6.

On Mac, you can also:

brew tap SpokeyWheeler/tap
brew install batcher

If you're into Docker, you can:

docker run -it spokey/batcher:latest

Usage

$ ./batcher
'update', 'delete', 'version' or 'help' subcommand is required
flags:
  -concurrency int
    	concurrency (default 20)
  -database string
    	database name
  -dbtype string
    	database type, e.g. postgres, informix, oracle, mysql (default "postgres")
  -execute
    	execute the operation ('dry-run' only by default)
  -host string
    	host name or IP (default "localhost")
  -opts string
    	JDBC URL options (e.g. sslmode=disable) (default "sslmode=require")
  -password string
    	password
  -portnum string
    	port number (default "26257")
  -set string
    	e.g. 'column_name=value, column_name=value ...' (ignored if provided with delete subcommand)
  -table string
    	table name
  -user string
    	user name
  -verbose
    	provide detailed output (will output all statements to the screen)
  -where string
    	e.g. 'column=value AND column IS NOT NULL ...'

CAUTION

This can seriously mess up your day if you get it wrong. Please dry run first to make sure the statement that will run is the one you want!