automatic prune on history #365

New Issue

2026-10-04T23:54:34+02:00

adam commented

2026-10-04 23:54:34 +02:00

By default, Piwigo fills table piwigo_history without worrying about the number of rows and how heavy the database dump will become.

Plugin Prune History already offers interesting features but I find it a bit "complex" for something that should remain invisible to users.

I think Piwigo core could have a:

$conf['history_auto_prune_threshold'] = 1000000; // 1 million

which would mean "maintain the number of history rows under 1 million". 0 to disable.

I think of this issue because I just made some statistics on Piwigo.com and the biggest history table has 20M lines. It's huge. It works fine on Piwigo.com because we have strong servers, but on a "standard" shared hosting, it could quickly become a problem with slowliness on database.

By default, Piwigo fills table piwigo_history without worrying about the number of rows and how heavy the database dump will become. Plugin Prune History already offers interesting features but I find it a bit "complex" for something that should remain invisible to users. I think Piwigo core could have a: ``` $conf['history_auto_prune_threshold'] = 1000000; // 1 million ``` which would mean "maintain the number of history rows under 1 million". 0 to disable. I think of this issue because I just made some statistics on Piwigo.com and the biggest history table has 20M lines. It's huge. It works fine on Piwigo.com because we have strong servers, but on a "standard" shared hosting, it could quickly become a problem with slowliness on database.

adam closed this issue

2026-10-04 23:54:34 +02:00

adam commented

2026-10-04 23:54:35 +02:00

@Eric-Piwigo what do you think of this "basic" feature for pruning history? I know you feel concerned about this subject, as you implemented the plugin Prune History :-)

@Eric-Piwigo what do you think of this "basic" feature for pruning history? I know you feel concerned about this subject, as you implemented the plugin Prune History :-)

adam commented

2026-10-04 23:54:35 +02:00

It's a good idea to save database performance. But I wonder about the meaning of the number of rows to maintain. 1 million rows (for example) could be huge for galleries with a (relative) small rate of visitors but not enough for galleries with a high rate of visitors. Setting the correct value according the different cases could be hard for galleries webmasters.

I designed Prune History plugin to allow to maintain a "understandable" range of history data like months / days to maintain. Could we not have something like this:
$conf['history_auto_prune_threshold'] = 90; // number of days (90 days = 3 months)

It's a good idea to save database performance. But I wonder about the meaning of the number of rows to maintain. 1 million rows (for example) could be huge for galleries with a (relative) small rate of visitors but not enough for galleries with a high rate of visitors. Setting the correct value according the different cases could be hard for galleries webmasters. I designed Prune History plugin to allow to maintain a "understandable" range of history data like months / days to maintain. Could we not have something like this: $conf['history_auto_prune_threshold'] = 90; // number of days (90 days = 3 months)

adam commented

2026-10-04 23:54:36 +02:00

Actually, on a technical point of view, this is exactly what I think is wrong in "Prune History". On a technical point of view, we don't care about the "how long do we keep history?" we care about "how small must the history table stay to be fast?".

On a very popular Piwigo, you will get 1M lines in 1 month. On a private & family-oriented Piwigo you will need 10 years to reach 1M lines.

I think the Prune History is interesting because it has "user logical setting" with a duration but it may easily lead to a 20M lines table. This is what I want to avoid.

Actually, on a technical point of view, this is exactly what I think is wrong in "Prune History". On a technical point of view, we don't care about the "how long do we keep history?" we care about "how small must the history table stay to be fast?". On a very popular Piwigo, you will get 1M lines in 1 month. On a private & family-oriented Piwigo you will need 10 years to reach 1M lines. I think the Prune History is interesting because it has "user logical setting" with a duration but it may easily lead to a 20M lines table. This is what I want to avoid.

adam commented

2026-10-04 23:54:37 +02:00

Well, I agree with your technical view. The mapping between lines number in table and the estimated duration keeped should be documented otherwise we'll having a lot of issues from the user-side experience.

I can plan to modify Prune History to detect the value of $conf['history_auto_prune_threshold'] and give advices. And review these functionnalities according to this new option. But that will be (very) later. ;-)

Go on your idea. Anyway it's a good idea. My recent personal experience with history table shows me that its growth is a real problem.

Well, I agree with your technical view. The mapping between lines number in table and the estimated duration keeped should be documented otherwise we'll having a lot of issues from the user-side experience. I can plan to modify Prune History to detect the value of $conf['history_auto_prune_threshold'] and give advices. And review these functionnalities according to this new option. But that will be (very) later. ;-) Go on your idea. Anyway it's a good idea. My recent personal experience with history table shows me that its growth is a real problem.

adam commented

2026-10-04 23:54:37 +02:00

Once this automatic prune is done, avoiding "too big" history tables, we can inrease the size of column piwigo_history.IP to make it compatible with IPv6, see https://github.com/Piwigo/Piwigo/issues/290

Once this automatic prune is done, avoiding "too big" history tables, we can inrease the size of column piwigo_history.IP to make it compatible with IPv6, see https://github.com/Piwigo/Piwigo/issues/290

adam commented

2026-10-04 23:54:38 +02:00

Note (because I'm manually pruning Piwigo.org demo history) : when you delete lines from history, we should find a way to delete by "small" blocks, ie no more than100k lines at once.

Note (because I'm manually pruning Piwigo.org demo history) : when you delete lines from history, we should find a way to delete by "small" blocks, ie no more than100k lines at once.

adam commented

2026-10-04 23:54:39 +02:00

Just an example:

mysql> select count(*) from history;
+----------+
| count(*) |
+----------+
| 45240489 |
+----------+
1 row in set (0.00 sec)

mysql> select max(id) from history;
+----------+
| max(id)  |
+----------+
| 47666906 |
+----------+
1 row in set (0.00 sec)

mysql> delete from history where id < 46666906;
Query OK, 44240488 rows affected (5 min 22.93 sec)

Just an example: ``` mysql> select count(*) from history; +----------+ | count(*) | +----------+ | 45240489 | +----------+ 1 row in set (0.00 sec) mysql> select max(id) from history; +----------+ | max(id) | +----------+ | 47666906 | +----------+ 1 row in set (0.00 sec) mysql> delete from history where id < 46666906; Query OK, 44240488 rows affected (5 min 22.93 sec) ```

Sign in to join this conversation.

master

issue-2560-people

translation

16.x

feat/embedding-based-contextual-tagging

issue-2576-search-include-ai-in-search-results

feat/search-ocr-ai

issue-2391-update-menu-design

15.x

detect-container

issue-2386-update-filters-users-activity

issue-2362-add-filters-options-in-admin

issue-2367-imagemagick7

issue-2225-quicksearch-help

14.x

issue-2164-BatchManagerUnitMode-Redesign

13.x

12.x

issue-1657-SVG_Support

issue-1703-historySearchMethod

11.x

2.10

2.9

2.8

2.7

2.5

2.6

2.4

2.3

2.2

2.1

2.0

1.7

1.3

1.6

1.5

1.4

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: adam/Piwigo#365