Borkware Photoblog

February 27, 2010

Reducing the size of your Lightroom 2 Catalog

Filed under: hacks, Lightroom, Random, tips — Mark Dalrymple @ 10:33 pm

My Lightroom library has 12000 images. My catalog (Lightroom 2 Catalog.lrcat) was half a gig (556 megs). That meant that every time I played with Lightroom I’d eventually have to ship 500 megs to the Time Capsule during the next backup. 48K of overhead per photo seems excessive.

By deleting history for all photos, I got it down to 161 megs (or about 13K per image). I rarely revisit the history of the edits I do in Lightroom, so I don’t see a compelling reason to keep them around.

How to do it

  • Make sure you have a backup of your catalog, just in case.
  • In the Library, go to “All Photos” in the Catalog
  • Command-A to select all
  • “D” to go into the develop module
  • Develop -> Clear History , choose “Selected Photos”
    Picture 6.png
  • Catalog Settings -> Relaunch and Optimize
  • Bask in the glory of having less catalog to back up

What happened

Danger. Nerdity approaching

To help optimize my backups I started using this über-nifty utility called Backup Loupe which shows you what was actually backed up by time machine. I use a Time Capsule, and get annoyed when backups take a long time. I worked to trim down my backed-up directories and got (usually) pretty small backups.

Except when I used LightRoom. If I opened up LR and did anything, I’d have to ship the 550 meg catalog over to the Time Capsule. Half a gig isn’t that much in today’s scheme of things, but it’s still annoying. It still has to move across the network. It still has to get archived to an external disk for off-site storage (which is a glacial process to start out with). And it’s a fair amount of space. Worst-case is that you change the catalog once an hour, you’d end up with 390 gigs consumed on the Time Capsule (24 hourly backups, 31 daily backups). That’s a third of the space I paid for.

The catalog is stored as Lightroom 2 Catalog.lrcat in your Lightroom user directory. Luckily this file is a sqlite3 database:

% file '/Users/markd/Pictures/Lightroom/Lightroom 2 Catalog.lrcat'
/Users/markd/Pictures/Lightroom/Lightroom 2 Catalog.lrcat: SQLite database (Version 3)

That’s something I can deal with.

There’s a spiffy utility called sqlite3_analyzer, available from the SQLite download page.

Run it and it’ll spew a load of stuff at you.

In particular are the page counts for the tables:

ADOBE_ADDITIONALMETADATA.............. 17135       12.0%
ADOBE_IMAGEDEVELOPSETTINGS............ 6118         4.3%

“Library Image Develop History Step” takes the most space, distantly followed by “Additional Metadata”.

Paging down the analyzer output, you find the detailed information about the table and its indices:


Percentage of total database..........  70.9%
Number of entries..................... 131181
Bytes of storage consumed............. 414224384
Bytes of payload...................... 404684584   97.7%
Average payload per entry............. 3084.93

So 131,181 individual history steps, taking up 395 megs of space. An average of 3K per entry. Or about eleven steps per photo.

Just for fun, I wanted to see what the most-used steps were.

sqlite> select count(name), name from ADOBE_LIBRARYIMAGEDEVELOPHISTORYSTEP group by name order by 1 desc limit 20;
6423 | Crop Rectangle
4353 | Add Brush Stroke
2623 | Paste Settings
2485 | Exposure
1851 | Update Exposure Adjustment
1700 | Clarity
1555 | Vibrance
1534 | Fill Light
892 | Brightness
850 | Black Clipping
766 | Highlight Recovery
454 | Add Graduated Filter
402 | Update Brightness Adjustment

Looks like I do a lot of cropping, a lot of adjustment brushing, a lot of settings pasting, and a lot of exposure adjusting. I know I need to compose in-camera better and hold the damn thing straight. Looks like I have concrete evidence for that now.

In a copy of the Lightroom catalog, I picked the top 30 of history step types and created tables of their contents, just to see who the biggest physical consumers were. I figure all editing history steps are not created equal.

Using the power of emacs, I quickly created and executed a lot of commands like:

create table blah_crop as select * from ADOBE_LIBRARYIMAGEDEVELOPHISTORYSTEP where name = 'Crop Rectangle';
create table blah_addbrush as select * from ADOBE_LIBRARYIMAGEDEVELOPHISTORYSTEP where name = 'Add Brush Stroke';
create table blah_pastesettings as select * from ADOBE_LIBRARYIMAGEDEVELOPHISTORYSTEP where name = 'Paste Settings';

And then re-ran the sqlite3_analyzer.

BLAH_ADDBRUSH......................... 60780       25.3%
ADOBE_ADDITIONALMETADATA.............. 17135        7.1%
BLAH_UPDATEEXPOSUREADJ................ 10898        4.5%
BLAH_ENABLEBRUSH...................... 6417         2.7%
ADOBE_IMAGEDEVELOPSETTINGS............ 6118         2.5%
BLAH_UPDATEBRIGHTNESS................. 4147         1.7%

The percentage of space that “Develop History Step” consumes has dropped because of the duplicate data I inserted. But you can see that the steps take up 101129 database blocks, and the “Add Brush Stroke” history step takes up well over half of them. 60,780 blocks consumes 237 megs. With 4353 “Add Brush Strokes”, that averages out to 56K per brush stroke.

Looking at one brush step, you can see why:

sqlite3> select * from ADOBE_LIBRARYIMAGEDEVELOPHISTORYSTEP where name = 'Add Brush Stroke' limit 1;
Dabs = { "M 0.319562 0.913815",
"M 0.349541 0.931545",
"M 0.361839 0.944725",
"M 0.329643 0.946453",
"M 0.297456 0.948412",
"M 0.265375 0.952863",
"M 0.233249 0.955063",
"M 0.201048 0.953529",
"M 0.168847 0.951995",

That’s a lot of dabbing.

After some milling around, I discovered “Clear History”, verified that it wouldn’t reset my image’s edits, just wipe out the history. After clearing out all the history, and vacuuming the database, it got a lot smaller.

Now, the big spenders in the file are

ADOBE_ADDITIONALMETADATA.............. 17135       41.3%
ADOBE_IMAGEDEVELOPSETTINGS............ 6120        14.8%
AGLIBRARYFILE......................... 2180         5.3%
AGPHOTOPROPERTY....................... 1741         4.2%
ADOBE_IMAGES.......................... 1665         4.0%

Which works out to about 5K of additional metadata per image, which I can live with.


1 Comment »

  1. WOW, I just cleared history for all my files (after backing up my catalog of course) and it dropped the size from > 4GB to < 1GB. Amazing! I was wondering why my catalog had grown so large over time. I'm a heavy user of the adjustment brush and had never cleared my history, that's probably why. Very happy right now. This will make my backups a lot faster and smaller and the catalog is already snappier in launch and quit. Very happy right now.

    Comment by — December 5, 2012 @ 12:34 am

RSS feed for comments on this post.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Create a free website or blog at

%d bloggers like this: