[PW-5393] sales_order_payment table size keeps growing

[jonathanribas]

Is your feature request related to a problem? Please describe. As far as our e-commerce grows, we have noticed that sales_order_payment table is the largest one on our e-commerce platform. After taking a look deeper in which fields contains a large amount of data, we have noticed that Adyen module is storing a lot of data on additional_information column.

Describe the solution you'd like After placing payment, we may don't need anymore whole data which is stored on additional_information column. Most important data is PSP Reference and it is stored in a separate column.

[acampos1916]

Hello @jonathanribas, thank you for opening this issue. This indeed could be a good improvement, we'll do an investigation about how to approach the sales_order_payment table cleanup after the data has been used.

[jonathanribas]

Hi @tnaber, Have you guys planned to develop a script in order to move old data to new table?

[jonathanribas]

Hi @tnaber, Have you guys planned to develop a script in order to move old data to new table?

[tnaber]

Hi @jonathanribas, currently we were thinking about adding a function which would check the old location when there is no data available in the new location. This means we were not planning on moving the old data.

[jonathanribas]

Hi @tnaber, can't you write a basic PHP script to move data? It will save merrchants some precious gigabytes :)

[Morerice]

Hi @jonathanribas

Since v8.1.0 we have stopped saving the stateData field in the sales_order_payment table. We believe this field was the biggest contributor to the growth of this table.

Do you still see this issue happening when v8.1.0+ is being used?

Thanks, Jean Adyen

[jonathanribas]

Hi @Morerice, yes it's better but there are still data like url that are not needed anymore after an order is placed. This takes place inside this table... For a merchant with a lot of transactions it's really an huge amount of data. Hope you understand my point.

[Morerice]

Hi @jonathanribas,

Yes I completely get your point. However at this point we don't really want to create a php script just for this specific purpose. Would an SQL command which would remove this specific data of orders that were created 3+ months before be something helpful?

Thanks, Jean Adyen

[jonathanribas]

Hi @Morerice, yes it could be great!

[Morerice]

Hi @jonathanribas,

Please find an example of the SQL script that can be used below. Ultimately it's not something we recommend, but if table size is such an issue, this script should provide some respite. Adyen will not be responsible for any data deleted that may still be relevant to the merchant, for any reason.

# Replace x with the number of months to go back and y with the character size from which to start deleting
# Example is shown below

UPDATE sales_order_payment sop
INNER JOIN sales_order so ON so.entity_id = sop.parent_id
SET sop.additional_information = NULL
WHERE so.updated_at < NOW() - INTERVAL x MONTH AND method LIKE 'adyen_%' AND LENGTH(sop.additional_information) > y;


UPDATE sales_order_payment sop
INNER JOIN sales_order so ON so.entity_id = sop.parent_id
SET sop.additional_information = NULL
WHERE so.updated_at < NOW() - INTERVAL 3 MONTH AND method LIKE 'adyen_%' AND LENGTH(sop.additional_information) > 300;

Regards, Jean Adyen

[jonathanribas]

Hi @Morerice, this way it will break additional_information field as it will cut it in the middle.

[Morerice]

Hi @jonathanribas,

Could you elaborate about what you mean by 'cutting it in the middle?'. This sql query will set the whole additional_information field to NULL, to alleviate the table size issue.

Thanks, Jean Adyen

[jonathanribas]

Hi @Morerice, sorry I read your query too fast! Should be safe to run it if not too much data inside the table. For huge amount data, doing batches is safer. I've checked for additional_information usage in the module and it doesn't seem to be used to show data on Sales Order View.

[Morerice]

Hi @jonathanribas,

No worries! Yea ultimately for large amounts I would recommend maybe even making the date selection a bit more specific.

Regards, Jean Adyen