> Can you enable the parameter "streaming" to on on your system [1]? It allows > to > stream the in-progress transactions to the subscriber side. I feel this can > avoid > the case that there are many .spill files on the publisher side.
> Another approach is to tune the logical_decoding_work_mem parameter [2]. > This specifies the maximum amount of memory used by the logical decoding, and > some changes are spilled when it exceeds the limitation. Naively, this setting > can reduce the number of files. > [1]: https://www.postgresql.org/docs/14/sql-createsubscription.html > [2]: > https://www.postgresql.org/docs/14/runtime-config-resource.html#GUC-LOGICAL-DECODING-WORK-MEM > Best regards, > Hayato Kuroda > FUJITSU LIMITED Dear Hayato, Thanks for your suggestions that were both already tested. In our (real) case (a single transaction with 12 millions sub-transactions): 1) setting the subscription as streaming, just delay a bit the spill file surge. It does not prevent the creation of spill files. 2) we set logical_decoding_work_mem to 20GB, which probably also delayed the problem, but did not solve it. The real problem is spill file deletions that can take days in this particular case ! Marc
