Diaspora Database size growing fast

[Frederico Araujo]

Hi, 

I notice that lately my pod database is starting to grow quite fast. 

Right now it's 943 MB  [ SELECT pg_size_pretty(pg_database_size('diaspora')); ]

The gz archives are growing about 2-4MB per day, currently the gz dump is 460MB, 

which in the course of a few months I will have to attach a new partition to the VPS server.

The Pod is about 18 months old. 

I dide cleared the DB at the time of mongo --> mysql transaction because I could not get migrations to complete and I did not have experience with mongo either. 

so I started fresh, the db should be actually 15 months I guess.

My questions is: 

Is there anything can be done to reduce size or delete old records that does not affect users? 

maybe prune really old records that im sure users wont access? I know it's a bad idea, but who knows.. 

I found that the biggest tables are: 

likes:

 217144 records

 349 MB (347MB after full vacuum)

comments:

 109614 records

 211 MB (190MB after full vacuum)

participations:

 9239 records

 158 MB (155MB after full vacuum)

taggings:

 179676 records

 44 MB (44MB after full vacuum)

posts:

 93855 records

 69 MB (67MB after full vacuum)

share_visibilities:

 31830 records

 22 MB (17MB after full vacuum)

I wonder why the Likes table is using quite a lot of database space.

Commands I used for reference:

vacuum full likes;

vacuum full comments;

vacuum full participations;

vacuum full taggings;

vacuum full share_visibilities;

vacuum full posts;

select pg_size_pretty(pg_total_relation_size('likes'));

select pg_size_pretty(pg_total_relation_size('comments'));

select pg_size_pretty(pg_total_relation_size('participations'));

select pg_size_pretty(pg_total_relation_size('taggings'));

select pg_size_pretty(pg_total_relation_size('share_visibilities'));

select pg_size_pretty(pg_total_relation_size('posts'));

[Dennis C]

Its a symptom of our current federation architecture,  esentially you need a copy of everything you know about. We're trying to figure out how to make it easier for smaller pods.

Dennis

Sent from my phone

[Frederico Araujo]

Ok, good to know it's being addressed. 

Is there any issue for tracking this? 

I have not found any specific issue on github https://github.com/diaspora/diaspora/issues/search?q=federation 

cheers