# GoToSocial SQL Snippets

## Introduction:

A selection of SQL scripts for the GoToSocial PostgreSQL database

## Files:

These three scripts are the routines that I run daily, hourly and monthly

Why?

All of the Fediverse software that I have used seem to retain too much data for too long

Personally I see no need to keep the e.g. account details for **every** account that has passed through my timeline

Likewise, I do not need to store, archive and backup a huge amount of emoji from various Fediverse instances

So I remove them, along with what I consider to be other extraneous info

(The list of data to be removed will probably grow as I dig deeper into the database)

* ### [gotosocial_tidy_daily.sql]

	<span style="color: red;">**WARNING:**</span>

	This script will:

	* Tidy (DELETE FROM) the gotosocial db accounts table
	  * Don't remove any accounts with the following id's
	  * Remove any accounts that are not followers
	  * Remove any accounts that are not followees
	  * Don't remove any accounts that are in the blocking table
	  * Don't remove any accounts that are in the muting table

	* Tidy (DELETE FROM) the gotosocial db accounts_stats table
	  * Don't remove any stats with the following id's
	  * Remove any stats that are not followers
	  * Remove any stats that are not followees

	* Tidy (DELETE FROM) the gotosocial db tokens table
	  * Keeping the (bot) sessions

	* Tidy (TRUNCATE) the gotosocial db tables
	  * public.clients
	  * public.emojis
	  * public.tombstones

	* Tidy (VACUUM.ANALYZE) the gotosocial db

* ### [gotosocial_tidy_hourly.sql]

	<span style="color: red;">**WARNING:**</span>

	This script will:

	* Tidy (DELETE FROM) the gotosocial db mentions table
	  * Delete all mentions over n days old

	* General (DELETE FROM) the gotosocial db statuses table
	  * Per account deletes will be done later
	  * Delete all statuses over n days old
	  * Don't remove any statuses from any accounts in the 'users' table

	* Per account (DELETE FROM) the gotosocial db statuses table
	  * Delete all statuses over n days old
	  * Probably makes sense to keep this in line with the 'General' setting
	  * Delete from: account_1_username account
	  * Retain pinned posts

	* Per account (DELETE FROM) the gotosocial db statuses table
	  * Delete all statuses over n days old
	  * Probably makes sense to keep account_2_username posts for longer
	  * Delete from: account_2_username account
	  * Retain pinned posts

	* Tidy (DELETE FROM) the gotosocial db status_faves table
	  * Delete all status_faves over n days old

	* Tidy (ANALYZE) any table that has had a DELETE performed upon it
	  * public."mentions
	  * public."statuses"
	  * public."status_faves

* ### [gotosocial_tidy_monthly.sql]

	This script will:

	* Perform a VACUUM FULL ANALYZE on the database and only needs to be run occasionally