{"id":249,"date":"2017-11-23T04:44:38","date_gmt":"2017-11-23T04:44:38","guid":{"rendered":"http:\/\/www.yohz.com\/blogs\/?p=249"},"modified":"2019-09-05T10:26:18","modified_gmt":"2019-09-05T02:26:18","slug":"compressing-pdfs-in-your-database","status":"publish","type":"post","link":"https:\/\/www.yohz.com\/blogs\/2017\/11\/23\/compressing-pdfs-in-your-database\/","title":{"rendered":"Compressing PDFs in your database"},"content":{"rendered":"

\u2192 This article refers to SQL Blob Viewer, which has now been renamed to SQL Image Viewer<\/a>.\u00a0 The techniques described in this blog is still applicable, as the functionality of the product remains the same.\u00a0 Only the name has changed.<\/em><\/p>\n

Recently, a user wanted to compress PDFs stored in his database, in order to reduce the overall size of the database.\u00a0 He asked if we had any application that could do this.\u00a0 Unfortunately, we don’t, but it got me to exploring the available options.<\/p>\n

Turns out that PDF software development kits aren’t cheap at all.\u00a0 Licensing can run into thousands of dollars, which isn’t feasible for us.\u00a0 Open-source software is another option, which is what I finally went with.\u00a0 In this case, I used Ghostscript, an all-purpose PDF toolkit, available at\u00a0https:\/\/www.ghostscript.com\/<\/a>.<\/p>\n

There are 3 steps to compressing PDFs in your database – extracting the PDFs, compressing or optimizing them, and finally uploading them back into the database.\u00a0 We will use SQL Blob Viewer<\/a> to first extract the PDFs, then Ghostscript to reduce the PDF size, and finally SQL File Import<\/a> to upload the PDFs back into the database.\u00a0 For reference purposes, these PDFs were created from document scans, so they have a 600 dpi resolution and are not optimized for PDF storage.\u00a0 We’re running this example on Windows, but there is also a Linux version of Ghostscript, and both SQL Blob Viewer and SQL File Import will run on Linux using Wine.<\/p>\n

 <\/p>\n

Extracting the PDFs<\/strong><\/p>\n

Extracting PDFs from your database using SQL Blob Viewer<\/a> is very simple – first write the SQL command to\u00a0retrieve the PDFs.<\/p>\n

\"\"<\/p>\n

We then export the PDF files to disk, using the primary key value in the ID<\/em> field to name the exported files.\u00a0 We do this so that when we upload the compressed files, we can use the ID<\/em> value to update the correct rows.<\/p>\n

\"\"<\/p>\n

If you have a lot of PDFs to export, you should choose to retrieve only the first few rows, to avoid loading the entire data set into memory.\u00a0 After that, when you export the result set, the entire result set will be exported.\u00a0 See this page <\/a>for details on how to export large result sets with SQL Blob Viewer.<\/p>\n

\"\"<\/p>\n

 <\/p>\n

Compressing\u00a0the PDFs<\/strong><\/p>\n

Now that we’ve exported the files, it’s time to use Ghostscript <\/a>to compress the images found in those PDFs.<\/p>\n

The easiest way to do this is to reduce the resolution of the images.\u00a0 You can do this using the PDFSETTINGS<\/strong> option.\u00a0 The possible values are:<\/p>\n