I'm troubleshooting slowness and issues in our mailman (container-based) setup. (See also "ORM exceptions" thread) And I've come across really odd contents of DB:

mailmandb=# select * from address, (select email, count(email) as instances from address group by email) as emails where emails.instances>1 and emails.email=address.email ; -[ RECORD 1 ]--+---------------------------------------------- id | 1001 email | some_person@some.org _original | Some_Person@some.org display_name | Some Person verified_on | registered_on | 2018-07-02 03:23:18.761679 user_id | 22543 preferences_id | 146541 email | some_person@some.org instances | 2 -[ RECORD 2 ]--+---------------------------------------------- id | 1002 email | some_person@some.org _original | display_name | verified_on | registered_on | 2018-07-02 03:23:19.036667 user_id | preferences_id | 146543 email | some_person@some.org instances | 2

mailmandb=# select * from member where address_id in (1002,1001);

      -[ RECORD 1 ]-----+-------------------------------------

id | 73682 _member_id | f4cdxxxx-5c64-xxxx-9ac8-1f5xxxxa0f1c role | 4 list_id | mylist.lists.here.stanford.edu moderation_action | address_id | 1002 preferences_id | 146544 user_id |

so:

1. address for the same email gets created twice
2. membership references "empty" address
3. membership does not reference proper user
4. preferences_id in all 3 records are different

is there a documentation on the DB contents someplace? I feel like our mailman instance slowness is related to DB content but cleaning up that content seems to be risky at best.

-- Sr System and DevOps Engineer SoM IRT