What's considered a "large" Odoo database in 2023?

[theangryangel]

I'm kinda curious what people consider to be a large Odoo database these days (ignoring filestore, just PostgreSQL)?

I'm struggling to find much public recent data out there.

Using pg_database_size the largest installation we've got is about 138GB. ~1.4 million stock.move, ~1.8million account.move.

Basically, I'm looking at where to focus time and resources next on this particular installation over the next few years, and I'm not clear what's a leading edge in terms of size and load.

[legalsylvain]

Hi, on my side, the biggest database in my company is about 108 GB. It is a 12 years old database (started in openerp V5, in v12 for the time being), in multi company with about 60 active companies.
Two big tables :

• mail_message : 14GB (19M records).
• stock_move : 13GB (20M records).
  I'm starting to think of it as a bit consequential.

[gdgellatly]

Our largest is around 4 years old and has more account.move and stock.move records than original post by a factor of 2, and it is a little over 45GB. 138GB seems an extremely large number for the number of records. I would check for things like undropped old binary columns or something somewhere, big TOAST tables etc.

But in terms of load and performance, we try to take db tuning seriously. Main thing after postgresql.conf is a connection pooler, and then custom indexes where needed (pg_stat_statements to assist) and finally where custom indexes aren't enough, materialized views for reporting. Our worst performing queries all relate to either CUPS Printers or Queue Jobs (which I don't care about as users unaffected), or else multicompany filtered queries usually in combination with a JOIN and a state, where the company queried is unusually dominant in the row distribution (something like a default purchase/sale/invoice list view, which I do care about).

[gurneyalex]

maybe the row distribution could be partially healed by a smart reordering of the rows with a CLUSTER command (and possibly some downtime)?

[theangryangel]

Thank you very much both. Data was super helpful <3

The abnormal size encouraged me to spend a little time on finding the immediate offenders. I’d been putting it off for other more “important” work.

Just to follow up, it seems there are 3 main culprits in this particular case:

• An app from the app store which fiddles with mail.message (which in combination with helpdesk being enabled is growing rather rapidly)
• a fields.Binary which should’ve been annotated with attachment=True long ago and was forgotten about 🤦 (I can only blame myself for this one)
• customer has written some materialised views for reporting, which seem like they need some attention

[matmicro]

Do you mean that binary field with attachment=True is a bad idea ?

[etobella]

The bad idea is not to do that. Attachment=True should be necessary in most cases.