mirror of
https://github.com/tiennm99/litellm.git
synced 2026-06-17 22:48:35 +00:00
Yassin Kortam
a992ed18df
High-volume deployments see LiteLLM_SpendLogs grow unbounded because retention via DELETE leaves dead tuples that autovacuum cannot reclaim fast enough. With a range-partitioned table, retention drops whole partitions instead: an instant metadata operation that returns disk to the OS immediately. The feature is gated behind general_settings.use_spend_logs_partitioning (default false). With the flag off, the cleanup job never queries the catalog and behaves exactly as today. With it on, the job verifies the table is partitioned, pre-creates upcoming partitions, and drops expired ones; expired rows the drops cannot reach (DEFAULT partition, partitions spanning the cutoff) are still deleted row-wise so retention is never bypassed. If the table is not partitioned it falls back to batched DELETE only. Converting an existing table is a manual, documented operation in db_scripts/partition_spend_logs.sql; db_scripts/unpartition_spend_logs.sql rolls it back. Both scripts rename the old table's indexes aside before recreating them, since a table rename keeps the schema-unique index names and would otherwise silently skip the CREATE INDEX IF NOT EXISTS block. Granularity and pre-create lookahead are tunable via SPEND_LOG_PARTITION_INTERVAL (day/week/month, invalid values fall back to day) and SPEND_LOG_PARTITION_PRECREATE_AHEAD.
70 lines
3.0 KiB
PL/PgSQL
70 lines
3.0 KiB
PL/PgSQL
-- Rolls back db_scripts/partition_spend_logs.sql: converts the native
|
|
-- range-partitioned "LiteLLM_SpendLogs" table back into a plain,
|
|
-- non-partitioned table matching the default LiteLLM schema.
|
|
--
|
|
-- When/why: run this if you want to stop using partition-based retention and
|
|
-- return to DELETE-based cleanup, or to restore the original single-column
|
|
-- primary key ("request_id") that the partitioned layout had to widen to a
|
|
-- composite ("request_id", "startTime").
|
|
--
|
|
-- IMPORTANT
|
|
-- * Test on a staging copy first and take a backup.
|
|
-- * Postgres cannot convert a partitioned table back in place, so this
|
|
-- renames the partitioned table aside and creates a fresh plain table.
|
|
-- * The composite PK could in principle hold the same "request_id" in more
|
|
-- than one partition, so rows are copied with ON CONFLICT DO NOTHING to
|
|
-- restore the single-column PK without failing on such duplicates.
|
|
-- * For large tables the INSERT ... SELECT copies every surviving row and may
|
|
-- run long; do it during a low-traffic window.
|
|
-- * Also remove use_spend_logs_partitioning from proxy_config.yaml (or set it
|
|
-- to false) so the cleanup job returns to DELETE-based retention.
|
|
|
|
BEGIN;
|
|
|
|
ALTER TABLE "LiteLLM_SpendLogs" RENAME TO "LiteLLM_SpendLogs_partitioned";
|
|
|
|
-- Renaming a table does NOT rename its indexes, and index names are unique per
|
|
-- schema. Move the partitioned table's indexes aside so the CREATE INDEX
|
|
-- statements below actually create indexes on the new plain table instead of
|
|
-- being silently skipped by IF NOT EXISTS, and so the new PK keeps the
|
|
-- canonical name.
|
|
ALTER INDEX IF EXISTS "LiteLLM_SpendLogs_pkey"
|
|
RENAME TO "LiteLLM_SpendLogs_partitioned_pkey";
|
|
ALTER INDEX IF EXISTS "LiteLLM_SpendLogs_pkey1"
|
|
RENAME TO "LiteLLM_SpendLogs_partitioned_pkey1";
|
|
ALTER INDEX IF EXISTS "LiteLLM_SpendLogs_startTime_idx"
|
|
RENAME TO "LiteLLM_SpendLogs_partitioned_startTime_idx";
|
|
ALTER INDEX IF EXISTS "LiteLLM_SpendLogs_startTime_request_id_idx"
|
|
RENAME TO "LiteLLM_SpendLogs_partitioned_startTime_request_id_idx";
|
|
ALTER INDEX IF EXISTS "LiteLLM_SpendLogs_end_user_idx"
|
|
RENAME TO "LiteLLM_SpendLogs_partitioned_end_user_idx";
|
|
ALTER INDEX IF EXISTS "LiteLLM_SpendLogs_session_id_idx"
|
|
RENAME TO "LiteLLM_SpendLogs_partitioned_session_id_idx";
|
|
|
|
CREATE TABLE "LiteLLM_SpendLogs" (
|
|
LIKE "LiteLLM_SpendLogs_partitioned" INCLUDING DEFAULTS INCLUDING GENERATED
|
|
);
|
|
|
|
ALTER TABLE "LiteLLM_SpendLogs"
|
|
ADD PRIMARY KEY ("request_id");
|
|
|
|
CREATE INDEX IF NOT EXISTS "LiteLLM_SpendLogs_startTime_idx"
|
|
ON "LiteLLM_SpendLogs" ("startTime");
|
|
|
|
CREATE INDEX IF NOT EXISTS "LiteLLM_SpendLogs_startTime_request_id_idx"
|
|
ON "LiteLLM_SpendLogs" ("startTime", "request_id");
|
|
|
|
CREATE INDEX IF NOT EXISTS "LiteLLM_SpendLogs_end_user_idx"
|
|
ON "LiteLLM_SpendLogs" ("end_user");
|
|
|
|
CREATE INDEX IF NOT EXISTS "LiteLLM_SpendLogs_session_id_idx"
|
|
ON "LiteLLM_SpendLogs" ("session_id");
|
|
|
|
INSERT INTO "LiteLLM_SpendLogs"
|
|
SELECT * FROM "LiteLLM_SpendLogs_partitioned"
|
|
ON CONFLICT ("request_id") DO NOTHING;
|
|
|
|
DROP TABLE "LiteLLM_SpendLogs_partitioned";
|
|
|
|
COMMIT;
|