Repository navigation
|
I'd like to delete the usage information up to the current point. I tried "TRUNCATE TABLE "LiteLLM_SpendLogs" RESTART IDENTITY CASCADE;" (I couldn't find any litellm ability to do it other than direct postgresql) and it removed everything in the table, but when I go to the UI and look at the Usage all the old information (global usage, team usage, user usage, etc..) is still there. I restarted litellm, redis, postgresql. Eventually I want to be able to run a usage/accounting report (probably postgreql query) and then clear the usage data to start collecting for the next reporting period. |
Replies: 3 comments
|
Truncating Spend also lives in:
Restarting LiteLLM, Redis, and Postgres after a logs-only truncate will not zero those. For a reporting period, export first, then reset: curl -H "Authorization: Bearer $LITELLM_MASTER_KEY" \
"http://localhost:4000/global/spend/report?start_date=YYYY-MM-DD&end_date=YYYY-MM-DD"
curl -X POST -H "Authorization: Bearer $LITELLM_MASTER_KEY" \
http://localhost:4000/global/spend/reset
Do not use |
|
Ok, thanks. I was hoping for a way to manage that database over the long term so it doesn't just grow forever. "maximum_spend_logs_retention_period" and associated settings seem like they're close, but I was looking for an endpoint where I can run them synchronously with my report generation, data archiving, etc... to make sure data accounting/compliance data isn't lost The {user,team,key,global,...}/spend/report endpoint all require an Enterprise license so i can't count on them and was just querying the LiteLLM_SpendLogs table directly. |
|
If you're running open-source LiteLLM without an Enterprise license, you don't need the paid The key is treating Here is the exact maintenance workflow you can run synchronously from a cron or migration script during your accounting cut-off: 1. Synchronous Archival (Before Purging)Export the closed billing window directly to your cold storage / CSV / S3 bucket so you retain 100% compliance auditability without keeping rows in live Postgres: -- Dump records older than your cutoff into cold storage
COPY (
SELECT * FROM "LiteLLM_SpendLogs"
WHERE "startTime" < (NOW() - INTERVAL '90 days')
) TO '/var/backups/litellm_spend_logs_archive.csv' WITH CSV HEADER;2. Non-blocking Chunked PurgeNever run an unbounded DO $$
DECLARE
rows_deleted INT;
BEGIN
LOOP
DELETE FROM "LiteLLM_SpendLogs"
WHERE ctid IN (
SELECT ctid FROM "LiteLLM_SpendLogs"
WHERE "startTime" < (NOW() - INTERVAL '90 days')
LIMIT 5000
);
GET DIAGNOSTICS rows_deleted = ROW_COUNT;
EXIT WHEN rows_deleted = 0;
COMMIT; -- Releases locks and yields WAL to concurrent proxy requests
END LOOP;
END $$;3. Pruning UI AggregatesThe Usage dashboard plots historical bars from the daily aggregation tables rather than scanning the raw spend logs. If you want the graphs to start fresh for the new accounting period, prune those tables to the same cutoff: DELETE FROM "LiteLLM_DailyUserSpend" WHERE "date" < '2026-06-01';
DELETE FROM "LiteLLM_DailyTeamSpend" WHERE "date" < '2026-06-01';
DELETE FROM "LiteLLM_DailyTagSpend" WHERE "date" < '2026-06-01';4. Zeroing Period Spend CountersThe current spend displayed on keys and teams lives in -- Reset cumulative period counters
UPDATE "LiteLLM_VerificationToken" SET "spend" = 0.0;
UPDATE "LiteLLM_TeamTable" SET "spend" = 0.0;If you use Redis caching ( redis-cli --scan --pattern "spend:key:*" | xargs -r redis-cli DEL
redis-cli --scan --pattern "spend:team:*" | xargs -r redis-cli DELRunning this sequence synchronously in an automated monthly batch script gives you deterministic archiving, zero table locks on proxy traffic, and a clean UI state without needing Enterprise endpoints. |
If you're running open-source LiteLLM without an Enterprise license, you don't need the paid
/spend/reportendpoints to establish a clean, compliance-safe retention and archiving lifecycle.The key is treating
LiteLLM_SpendLogs(audit log), the daily aggregation tables (what the UI graphs query), and the running spend counters as three separate concerns.Here is the exact maintenance workflow you can run synchronously from a cron or migration script during your accounting cut-off:
1. Synchronous Archival (Before Purging)
Export the closed billing window directly to your cold storage / CSV / S3 bucket so you retain 100% compliance auditability without keeping rows in live Postgres:
-- Dump…