Customize PostgreSQL settings/limits

[msbt]

We are facing an issue with PostgreSQL on Cloudron and a fairly large table (+10Mio. rows). When issuing certain queries, involving many LEFT JOINS, PostgreSQL starts writing a lot of temporary data to disk.

The default setting temp_file_limit = -1 does not provide a limit for temporary data written to disk, the SELECT query however does encounter a hard limit, i.e. our server's available disk space.

We observed that a SELECT query can fill the entire disk, writing more data in the process that the size of the database. Ideally, we would avoid generating queries that trigger this behavior, but the software used to generate the queries is not written by us.

I would like PostgreSQL to terminate the query before filling the entire disk, triggering "out-of-disk-space" errors on the server and other applications. As far as I can tell, this is only achievable by setting temp_file_limit to something else than -1. I was able to verify this by setting the parameter temporarily, as outlined in https://forum.cloudron.io/topic/7714/postgresql-configuration-slow-log/7

However, we would need to make this change permanent. Is there a way to accomplish this? In a similar spirit, it might also be interesting to be able to tune work_mem to something else than 4MB, utilizing more memory, instead of disk space, when necessary.

[girish]

@msbt If the queries are so big that they fill up the disk, this seems like an app issue . Given that this is your app, can the queries be reworked to have a LIMIT to avoid this issue altogether ?

But to answer your question, there is no way to persist addon values. The addons are designed to be used by apps and not a generic managed postgres .

[msbt]

@girish it's not a custom app, it's a Cloudron Directus installation and the queries fetch data from it. I meant to say that I can't influence the structure of those queries (which already use LIMIT 1). So I'm not using PSQL as a custom DB, it's inside the Cloudron ecosystem

[girish]

@msbt ah ok. I am trying to avoid creating a system where users can provide a custom conf file. If this is "generic", this may make the data itself unportable in the long run. For example, if users change the encoding, collation etc. Our addons have code that patch dump files etc based on apps.

Also, my reading is that temp_file_limit is full installation level and it seems this doesn't cover temporary tables etc (per https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-TEMP-FILE-LIMIT) .

With that in mid, I am wondering if this can be solved at directus level. There's no way to limit directus to not make a massive query? That seems quite a flaw, no? Like why is it creating GBs of temporary files to start with.

[girish]

I have set a hardcoded temp_file_limit = 1GB now in the global config itself. I think exceeding that already means that something is really wrong.

[msbt]

@girish perfect, thanks - appreciate it! I'm almost certain there could be ways to improve those queries, but it's always better to have a failsafe in place