[BUG] LOG_SQL defaults to true and the logs table grows without bound

[OrcVole]

Fider Cloud or Self Hosted

Self hosted, Fider 0.36.1, PostgreSQL, deployed via the Cloudron package.

Summary

Fider defaults to writing every log entry into a logs table in its own database, with no retention, pruning, rotation or size cap of any kind. On a long lived install this table becomes the largest thing in the database by orders of magnitude, and there is nothing in Fider that will ever remove a row from it.

Detail

app/pkg/env/env.go:

Log struct {
    Level      string `env:"LOG_LEVEL,default=INFO"`
    Structured bool   `env:"LOG_STRUCTURED,default=false"`
    Console    bool   `env:"LOG_CONSOLE,default=true"`
    Sql        bool   `env:"LOG_SQL,default=true"`
    File       bool   `env:"LOG_FILE,default=false"`
    OutputFile string `env:"LOG_FILE_OUTPUT,default=logs/output.log"`
}

app/services/log/sql/sql.go inserts one row per log entry:

INSERT INTO logs (tag, level, text, created_at, properties) VALUES ($1, $2, $3, $4, $5)

The only filter is the level check. There is no retention window, no periodic delete, no partitioning and no cap.

What it looked like in practice

A self hosted install, running continuously for about thirteen months, with one tenant, one user and one post. It was run at LOG_LEVEL=DEBUG, which matters because Fider traces each SQL statement it executes at DEBUG and each trace line then becomes another row in this same table.

After thirteen months:

logs total relation size	22 GB (16 GB heap, 5 GB TOAST, 528 MB index)
logs_id_seq	about 24.6 million
every other table in the database	112 kB or less
autovacuum, vacuum or analyze ever run on it	never, all three columns null in pg_stat_user_tables

So the database was 99.96 per cent application log. Backups of that instance took over three hours and were dominated entirely by this table. Truncating it took the database from 22 GB to 9.7 MB.

At INFO the growth is far slower, but it is still unbounded, and the table is still never vacuumed.

Why the default is the problem

LOG_SQL=true reads naturally as "log SQL statements", not "write my logs into my database". An operator who has not read app/pkg/env/env.go has no particular reason to think that leaving the defaults alone means accumulating an unbounded table inside the same database they are backing up. The .example.env shipped in the repository sets LOG_LEVEL=DEBUG and LOG_SQL=true together, which is the worst combination, and is what a self hoster copying it will start from.

Suggestions, roughly in order of preference

1. Default LOG_SQL to false. Console logging is already on by default and is what most deployments actually read. Anyone who wants the database sink can opt in.
2. Add a retention setting, for example LOG_SQL_RETENTION_DAYS, with a sane default, and prune in the existing background job runner.
3. Rename or document the variable. If the default stays, at minimum say plainly in .example.env that this writes into the application database and grows without limit.
4. Do not ship LOG_LEVEL=DEBUG with LOG_SQL=true in .example.env, since that is the combination that makes it grow fastest and it is the file people copy.

Happy to open a PR for 1 or 2 if a maintainer indicates which is preferred.

Environment

• Fider 0.36.1, self hosted via the Cloudron package, PostgreSQL addon
• Observed on a low traffic install after about thirteen months of continuous running