Thread: Autovacuum and frequent pg_bulkload
Hello, i use PostgreSql 8.4.22 and two times a day, i import logs wth pg_bulkload and i delete old data. I move some millions ofrecords in a day. I noticed that only autovacuum seems not to be able to free unused space. I need a periodical vacuum full but this operations takes many hours. Do you think it is due to pk_bulkload that is not able to "see" free-marked space to use when loading new data? Thank you, /F
On 11/20/2016 04:51 AM, Job wrote: > Hello, > > i use PostgreSql 8.4.22 and two times a day, i import logs wth pg_bulkload and i delete old data. I move some millionsof records in a day. FYI, Postgres 8.4 is over two years past EOL. > > I noticed that only autovacuum seems not to be able to free unused space. Do you run ANALYZE at any point in your procedure? What are the steps in the load/delete cycle? > I need a periodical vacuum full but this operations takes many hours. > > Do you think it is due to pk_bulkload that is not able to "see" free-marked space to use when loading new data? > > Thank you, > /F > -- Adrian Klaver adrian.klaver@aklaver.com
Hi Adrian, Thank you for your fast reply! >FYI, Postgres 8.4 is over two years past EOL. Yes, i am aware; we will migrate next year but for application compatibility reason we have at the moment to the remain here.. >What are the steps in the load/delete cycle? We need to load, with pg_bulkload, log datas for reporting. We load every hours about one million of row and twice a day we generated aggregated report. Then we delete old rows (we cannot use truncate because we only delere rows older than 24 hours). I think, but i can be wrong, that pg_bulkload do not reuse free space marked previously by the delete.. Only vacuum full empty free space, but tablea became locked. Thank you Francesco
On 11/20/2016 07:20 AM, Job wrote: > Hi Adrian, > Thank you for your fast reply! > >> FYI, Postgres 8.4 is over two years past EOL. > Yes, i am aware; we will migrate next year but for application compatibility reason we have at the moment to the remainhere.. > > >> What are the steps in the load/delete cycle? > > We need to load, with pg_bulkload, log datas for reporting. > We load every hours about one million of row and twice a day we generated aggregated report. > Then we delete old rows (we cannot use truncate because we only delere rows older than 24 hours). > I think, but i can be wrong, that pg_bulkload do not reuse free space marked previously by the delete.. Can you be more specific. In other words show the actual commands you use? Also how are you defining free space, space the OS sees or space Postgres sees? How are you determining the free space? > > Only vacuum full empty free space, but tablea became locked. VACUUM full returns space to the OS if that is what you mean? > > Thank you > Francesco > -- Adrian Klaver adrian.klaver@aklaver.com