Clean up unneeded items in Nextcloud filecache to save Postgres disk space

nextcloud-postgres-cleanup-filecache.sql

-- Connect to postgresql instance for nextcloud:
-- `psql -U nextcloud nextcloud`

-- Get tables with largest disk space
SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes;

-- public.oc_filecache is likely to be the largest entry, by far.

-- Get largest filecache usage by storage ID and name
select c.storage, s.id, count(c.path) from public.oc_filecache c left join public.oc_storages s on s.numeric_id=c.storage group by c.storage, s.id;

-- The largest storages are likely those with a lot of symlinks.
-- If you have MacOS user folders, most of these are within the Library folder
-- and can be safely excluded from Nextcloud's indexing.

-- Install https://github.com/Inveniem/nextcloud-files-excludedirs
-- Enable "Exclude directories" in the apps UI

-- Set excluded paths: 
-- `./occ config:app:set files_excludedirs exclude --value '["Library"]'`

-- See filecache usage by excluded path(s)
select storage, count(*) FROM public.oc_filecache WHERE path LIKE '%Library%' group by storage;

-- Delete excluded path(s) from filecache
-- (over-deleting is ok, because occ files:scan will re-add directories not matching the exclusion)
DELETE FROM public.oc_filecache WHERE path LIKE '%Library%';

-- Run `./occ files:cleanup`

-- Vacuum Postgres db
VACUUM FULL;

-- Re-run the initial SQL query to check disk space, and it should be reduced.