When working with databases that store file metadata, it’s often useful to track how file sizes change over time. If you have a table with the following structure:

id | timestamp | name_file | size

You may want to analyze the day-to-day changes in file size. This can help in monitoring storage usage, detecting anomalies, or understanding file growth trends.

SQL Query for Tracking File Size Changes

The following SQL query calculates the daily change in file sizes using PostgreSQL. It leverages the LAG() function to compare each day’s file size with the previous day’s size.

WITH daily_sizes AS (
    SELECT
        DATE("timestamp") AS date,
        name_file,
        SUM(size) AS total_size
    FROM your_table
    GROUP BY DATE("timestamp"), name_file
)
SELECT
    date,
    name_file,
    total_size,
    LAG(total_size) OVER (PARTITION BY name_file ORDER BY date) AS previous_size,
    total_size - LAG(total_size) OVER (PARTITION BY name_file ORDER BY date) AS size_change
FROM daily_sizes
ORDER BY name_file, date;

How It Works

1. Aggregate File Sizes by Date: The daily_sizes common table expression (CTE) groups file sizes by date and name_file.
2. Retrieve Previous Day’s Size: The LAG() function retrieves the total file size from the previous day for each file.
3. Calculate the Change: The difference between the current day’s size and the previous day’s size is computed.

Example Output

date	name_file	total_size	previous_size	size_change
2024-02-18	fileA.txt	500	NULL	NULL
2024-02-19	fileA.txt	700	500	200
2024-02-20	fileA.txt	650	700	-50
2024-02-18	fileB.txt	300	NULL	NULL
2024-02-19	fileB.txt	450	300	150

Filtering Out Initial Entries

If you want to exclude the first day’s records (which lack a previous size for comparison), you can add the following condition:

WHERE previous_size IS NOT NULL;

Use Cases

• Monitoring storage consumption over time
• Detecting sudden increases or decreases in file sizes
• Understanding file growth patterns for optimization