CODEWITHSUNDEEP
ruby-on-railspostgresqloptimizationactiverecordmany-to-many
Nox
Nox

Reputation: 246

Postgresql Many to Many size optimization

As I was looking at my DB on my project, I realized that the heaviest table I have is a relationship table between two other tables.

A table integration, user and the famous integration_customers (linking user to integration).

Here is the structure of this Integration Customers table:

1   "id"    "int4"  "NO"    NULL    "nextval('integration_customers_id_seq'::regclass)" ""  NULL
2   "user_id"   "int4"  "NO"    NULL    NULL    ""  NULL
3   "integration_id"    "int4"  "NO"    NULL    NULL    ""  NULL
4   "created_at"    "timestamp" "NO"    NULL    NULL    ""  NULL
5   "updated_at"    "timestamp" "NO"    NULL    NULL    ""  NULL
6   "parameters"    "jsonb" "YES"   NULL    "'{}'::jsonb"   ""  NULL
7   "marketing_info_email"  "bool"  "YES"   NULL    "true"  ""  NULL
8   "subscriber_id" "varchar"   "YES"   NULL    NULL    ""  NULL

And my indexes are:

"integration_customers_pkey"    "BTREE" t   "id"    ""  NULL
"index_integration_customers_on_user_id_and_integration_id" "BTREE" t   "user_id,integration_id"    ""  NULL
"index_integration_customers_on_user_id"    "BTREE" f   "user_id"   ""  NULL
"index_integration_customers_on_integration_id" "BTREE" f   "integration_id"    ""  NULL

I just find the size difference between the relationship table and the two others huge and I was wondering if I was missing something in the architecture or if it was normal? If this is abnormal, what did I do wrong? If it is normal, is there a way to optimize it?

Happy to provide more information if needed :)

Thank you all very much for your time!

Upvotes: 0

Views: 49

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247950

Maybe the size is just what you have to expect because the table contains more data. Remember that an m-to-n relationship table can easily have more entries than the referenced tables.

Maybe the table is bloated. Install the pgstattuple extension and use the function with the same name on the table to find out if that is the case. If yes, schedule a VACUUM (FULL) and tune autovacuum to be more aggressive.

Upvotes: 1

Related Questions