[QUESTION] Why storing PDF labels in database?

[i6media]

Question We noticed that the entire PDF of a label is stored in the tig_postnl_shipment_label SQL table. Storing binary or base64 in databases is usually considered bad practice.

Our database is growing rapidly in size due to this matter. Wouldn't it be better to store the labels in the var folder of Magento 2 instead of in the DB itself?

[tig-jeffreybranderhorst]

Hi @i6media ,

Thank you for submitting this issue, I will place this on our backlog so PostNL together with our developers can have a look at this and change it.

Have a great day, Jeffrey

[tig-jeffreybranderhorst]

Hi @i6media ,

Just a little sidenote, I just heard in the past we decided to place it in the database because one of the reason was access issues on servers and this way the size of database should not grow rapidly because it's a few kb for each label. But nonetheless, we are going to discuss the (scrum) story furthermore, perhaps introduce a pruning system for labels say older than a year. I’ll keep you posted.

Have a great day, Jeffrey

[i6media]

Hi Jeffrey,

Thank you for the reply. I agree that labels are only a few kb, yet they are being loaded in the server's memory instead of on its harddrive. Memory is much more expensive than disk-size. If the DB grows over time (e.g. a few years), the few KB's are going to add up which makes it much better to store the files on the servers HDD/SSD, instead of loading it into the memory.

[tig-jeffreybranderhorst]

Hi @i6media ,

You are completely right that memory is more important than disk space. I'm making sure your messages are also placed on our backlog in the same issue, so when we are going to discuss it, your points will be taking along with it.

Have a great day and thank you once again! Jeffrey

[mlaurense]

This is way more than 'a few KB'... This table contains 104,077 rows with mainly old label data...

[sedero]

We have the same issue, 5,2GB of label entries in the database... for 104k shipment orders. Might be better to clean up old labels or store them somewhere else.

[Jeroen-Buro210]

Hi,

Any news on when this issue is looked at? We have the same problem, with a database table over 10GB. If it's not looked at soon, is there something we can do to temporarily fix this? Can I purge rows safely or is there going to be issues after that?

[JorisPostNL]

Hi Jeroen-Buro21,

Version 1.12.9 includes a change that ensures the label database will not continue to grow infinitely, but will overwrite earlier entries in the table at a certain point. If you would like to further decrease the size of the database we advise running an Optimize Table, which will essentially defragment the table clearing up space that is not used at that point.

Kind regards Team Channel Integrations PostNL

[i6media]

Still think that it's bad practice to store binary data in MySQL, which in this case is completely unnecessary. Why aren't labels stored in the pub/media/postnl directory for example?

[JorisPostNL]

Hi i6media,

Thank you for your feedback. We will discuss the matter of storing labels and if we come up with another solution we will place that on the backlog for future developing.

Kind regards Team Channel Integrations PostNL

[i6media]

On the backlog again? To be fair, I created this issue almost a year ago...

Wouldn't it be better to store the labels in the var folder of Magento 2 instead of in the DB itself?

There are many developers experiencing the same issue here.

[JorisPostNL]

Hi i6media,

The initial reporting of this issue resulted in the implementation of an automatic cleanup of the labels in the database, ensuring that it will not grow beyond a certain point, which was released in July this year. Based on the feedback we receive in this thread we will re-assess this fix and explore other options which might better suit the wishes of our customers.

Kind regards, Team Channel Integrations PostNL