PostgreSQL / No autovacuum

Discuss with the community any ideas you'd love to see in future DiskStations and DSM updates! We do our best to monitor and forward all of them, but we recommend to also use this form as our team will systematically see your suggestion:
https://www.synology.com/form/inquiry/feature
Forum rules
We do our best to monitor and forward your ideas to our team, but due to the large amount, we may not see every single one and recommend to also use this form as our team will systematically see your suggestion:
https://www.synology.com/form/inquiry/feature
asynouser
I'm New!
I'm New!
Posts: 1
Joined: Wed Nov 29, 2017 5:28 pm

PostgreSQL / No autovacuum

Postby asynouser » Wed Nov 29, 2017 5:55 pm

Hi,

I am very surprised that the autovacuum is disabled in the PostgreSQL database used by dsm.

Code: Select all

postgres=# show autovacuum;
 autovacuum
------------
 off
(1 row)


I ran a vacuum full on the database on the database became a lot smaller. (I just got my syno It's nearly empty)

Code: Select all

      Name      |      Owner      | Encoding  | Collate | Ctype |   Access privileges   |  Size   | Tablespace |                Description
----------------+-----------------+-----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
 download       | DownloadStation | SQL_ASCII | C       | C     |                       | 6793 kB | pg_default |
 mediaserver    | MediaIndex      | SQL_ASCII | C       | C     |                       | 50 MB   | pg_default |
 photo          | PhotoStation    | SQL_ASCII | C       | C     |                       | 24 MB   | pg_default |
 postgres       | postgres        | SQL_ASCII | C       | C     |                       | 6154 kB | pg_default | default administrative connection database
 template0      | postgres        | SQL_ASCII | C       | C     | =c/postgres          +| 5993 kB | pg_default | unmodifiable empty database
                |                 |           |         |       | postgres=CTc/postgres |         |            |
 template1      | postgres        | SQL_ASCII | C       | C     | =c/postgres          +| 6041 kB | pg_default | default template for new databases
                |                 |           |         |       | postgres=CTc/postgres |         |            |
 video_metadata | VideoStation    | SQL_ASCII | C       | C     |                       | 499 MB  | pg_default |
(7 rows)


after vacuum full:

Code: Select all

      Name      |      Owner      | Encoding  | Collate | Ctype |   Access privileges   |  Size   | Tablespace |                Description
----------------+-----------------+-----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
 download       | DownloadStation | SQL_ASCII | C       | C     |                       | 5505 kB | pg_default |
 mediaserver    | MediaIndex      | SQL_ASCII | C       | C     |                       | 26 MB   | pg_default |
 photo          | PhotoStation    | SQL_ASCII | C       | C     |                       | 20 MB   | pg_default |
 postgres       | postgres        | SQL_ASCII | C       | C     |                       | 5146 kB | pg_default | default administrative connection database
 template0      | postgres        | SQL_ASCII | C       | C     | =c/postgres          +| 5993 kB | pg_default | unmodifiable empty database
                |                 |           |         |       | postgres=CTc/postgres |         |            |
 template1      | postgres        | SQL_ASCII | C       | C     | =c/postgres          +| 5033 kB | pg_default | default template for new databases
                |                 |           |         |       | postgres=CTc/postgres |         |            |
 video_metadata | VideoStation    | SQL_ASCII | C       | C     |                       | 203 MB  | pg_default |
(7 rows)


I dont understand the rationale behind disabling autovacuum on a 9.3 PostgreSQL database.
It's the cleanup process and the process responsible for automatic stat calculation.

I didnt find much info on this except for [1]

Please note that :
    doing a "vacuum full" under heavy load is not the best idea. It takes an Exclusive lock on the tables preventing transaction to read / write from them during the operation [2]. I did it because I am alone and autovacuum was off.
    It also requiers some space to create the "new" table.
    vacuum full is usually not needed except if you delete a lot of lines (or dont run autovacuum).

[1] https://forum.synology.com/enu/viewtopic.php?t=43942
[2] https://www.postgresql.org/docs/9.3/sta ... acuum.html

Return to “Feature Requests & Product Improvement Suggestions”

Who is online

Users browsing this forum: No registered users and 5 guests