# Managing very large tables
**URL:** https://discuss.tryton.org/t/managing-very-large-tables/4407
**Category:** System Administrator
**Created:** [July 12, 2021, 3:16pm UTC](https://discuss.tryton.org/t/managing-very-large-tables/4407 "2021-07-12T15:16:15Z")
**Posts on this page:** 8
**Page:** 1
### Author:  [@msabater](https://discuss.tryton.org/u/msabater)
#### Post date: [July 12, 2021, 3:16pm UTC](https://discuss.tryton.org/t/managing-very-large-tables/4407/1 "2021-07-12T15:16:15Z")
Hi,
A customer that uses tryton has a fairly large database that suffers performance issues.
Three tables in particular (stock\_move, account\_move, account\_move\_line) have grown to reach tens of GBs.
We have tried several methods of optimization, such as profiling queries, adding indexes, removing locks, modifying processes and adding hardware. While these attempts have improved performance, the continous growth of these 3 tables is still a problem, so we are looking for more a more agressive approach.
The options we are working with include database clustering, table partitioning and just deleting old data.
Has anybody faced these problems? If so, how did you overcome then?
---
### Author:  [@albert](https://discuss.tryton.org/u/albert)
#### Post date: [July 12, 2021, 3:43pm UTC](https://discuss.tryton.org/t/managing-very-large-tables/4407/2 "2021-07-12T15:43:47Z")
> [@msabater](#):
>
> The options we are working with include database clustering, table partitioning and just deleting old data.
[Cluster](https://www.postgresql.org/docs/current/sql-cluster.html) is a task we periodically run on tables based on ID but that will not solve your issue. If you meant some kind of replication, I think it’s probably too complex, and probably better to start with table partitioning. Fortunately since v12 PostgreSQL supports foreign keys to reference partitioned tables.
You may be interested in [GitHub - pgpartman/pg\_partman: Partition management extension for PostgreSQL](https://github.com/pgpartman/pg_partman)
---
### Author:  [@ced](https://discuss.tryton.org/u/ced)
#### Post date: [July 12, 2021, 3:56pm UTC](https://discuss.tryton.org/t/managing-very-large-tables/4407/3 "2021-07-12T15:56:28Z")
> [@msabater](#):
>
> fairly large database that suffers performance issues
What are they? Which queries are too slow?
---
### Author:  [@msabater](https://discuss.tryton.org/u/msabater)
#### Post date: [July 12, 2021, 4:22pm UTC](https://discuss.tryton.org/t/managing-very-large-tables/4407/4 "2021-07-12T16:22:31Z")
The entire system slows down or just freezes. The problem is usually solved by recreating the container.
We are now auditing the system but the problem does not seem to be related to a specific query.
---
### Author:  [@ced](https://discuss.tryton.org/u/ced)
#### Post date: [July 12, 2021, 4:26pm UTC](https://discuss.tryton.org/t/managing-very-large-tables/4407/5 "2021-07-12T16:26:52Z")
I do not see how cold data could slow the system. For me you must first find the exact source of problem and not assume it is the database.
---
### Author:  [@albert](https://discuss.tryton.org/u/albert)
#### Post date: [July 12, 2021, 5:00pm UTC](https://discuss.tryton.org/t/managing-very-large-tables/4407/6 "2021-07-12T17:00:40Z")
> [@msabater](#):
>
> The entire system slows down or just freezes. The problem is usually solved by recreating the container.
Which container? Tryton’s one?
As Cédric suggests it’d be good to ensure the problem is at PostgreSQL side.
Some recommendations & ideas:
In order to see the activity that is going on in PostgreSQL server I’d recommend [pg\_activity](https://pypi.org/project/pg-activity/).
We also always enable log\_min\_duration\_statement in all production servers so that all queries that take longer than a certain threshold will be logged. See: [PostgreSQL: Documentation: 17: 19.8. Error Reporting and Logging](https://www.postgresql.org/docs/current/runtime-config-logging.html), then you can grep + count the number of queries above that threshold to keep an eye to understand if the situation is improving.
Maybe you’re reaching a limit on the number of connections on uwsgi or proxy you’re using? That may explain the freeze?
Depending on the Tryton version you’re using it’d recommend moving to v6. There you’ll find trytond-stat for better understanding what’s going on. Also SAO has several improvements that hopefully will minimize some performance issues present in previous versions (at least until 5.4).
In larger databases, counters can also be a problem, see [Counters & performance](https://discuss.tryton.org/t/counters-performance/1206).
---
### Author:  [@numaelis](https://discuss.tryton.org/u/numaelis)
#### Post date: [July 14, 2021, 5:08pm UTC](https://discuss.tryton.org/t/managing-very-large-tables/4407/7 "2021-07-14T17:08:09Z")
in all modules or a specific module?
---
### Author:  [@pokoli](https://discuss.tryton.org/u/pokoli)
#### Post date: [July 15, 2021, 8:06am UTC](https://discuss.tryton.org/t/managing-very-large-tables/4407/8 "2021-07-15T08:06:40Z")
> [@msabater](#):
>
> The entire system slows down or just freezes. The problem is usually solved by recreating the container.
If the system just freezes may be related to a Python process which is doing some calculations which take so much time. This is a common souce of performance problems and can be easly detected using pg\_stat\_activity. You will see that the query is most of the time in state `idle in transaction` and the last query is a select from a table to get the results to do the computation.