IBM Security MaaS360

 View Only

Optimizing storage space used in Oracle database

By Vinayaka Hegde posted Tue January 30, 2024 04:15 AM


Authored by @Vinayaka Hegde,

We had a large json data of about 3053 bytes which we wanted to store in database, Upon reviewing multiple options we can either use varchar2 type column or blob type column.

If we see oracle uses more CPU to store and retrieve data from blob column type but performance of database is faster if we use varchar2 compared to blob.

Again if we store large data in varchar2 it will affect performance again so we decided to gZip it and do base64 encode and save it in oracle database.

It saved a lot of memory and improved performance as well.

Below is the picture of how much space we can save if we use gZip + base64

63% less DB space used while saving Data into oracle database

Performance further improved on Read operation in java by 12X by caching database data in Memcached. Data retried in 100ms vs database call 1200ms

Code snippet :

For compressing data and doing base64 encoding

public static String compressDataAndencodeBase64(String data){
   ByteArrayOutputStream byteAOS =
new ByteArrayOutputStream();
GZIPOutputStream zos = null;
String result = StringUtils.EMPTY;
[] bytes;
      zos =
new GZIPOutputStream(byteAOS);
} catch (Exception e) {
LOGGER.error("Could not compress Data:{}", data, e);
} finally {
   bytes = byteAOS.toByteArray()
result = Base64.encodeBase64String(bytes);

For retrieving data from oracle database:

public static String decompressData(String compressedData) {
byte[] bytes;
String result = StringUtils.EMPTY;
GZIPInputStream zi = null;
      bytes = Base64.decodeBase64(compressedData)
zi = new GZIPInputStream(new ByteArrayInputStream(bytes));
result = IOUtils.toString(zi);
} catch (Exception e) {
LOGGER.error("Could not decompress Data:{}", compressedData, e);    } finally {
   return result;