Sometimes , for example, with a large number of INSERT/UPDATE operations, the size of the table begins to grow nonlinearly. For example, in my case, with ~ 6 * 10 6 consecutive UPSERT operations UPSERT size (disk space) of the table increased from the usual ~ 200-500Mb to 8Gb. It is unpleasant to find a dozen such fat men instead of normal tables. Moreover, the usual autovacuum does not help against such tables and, accordingly, the autovacuum also powerless, which leads to the fact that the size of the database grows quietly and the truth opens when the disk space ends. I use the following type of UPSERT (and I know about the changes in 9.5):

 UPDATE <table_name> SET "<column_name>" = <data> FROM <another_table> WHERE <another_table>.<id> = <table_name>.<id>; INSERT INTO <table_name> ("<id>", "<column_name>") SELECT "<id>", "<column_name>" FROM <another_table> WHERE NOT EXISTS (SELECT "<id>" FROM <table_name> WHERE "<id>"=<another_table>.<id>);

What to do with it all? Regularly carrying out VACUUM FULL is not an option at all, for it is too long and manual.

    1 answer 1

    The PostgreSQL architecture is such that any change in the tables is always a new line at the physical storage level. The exception is the delete command, which only marks the deleted line as already invisible to other transactions. How exactly the lines are stored at the physical level can be found here:

    “What's inside” - keeping data low

    Therefore, it is very important not to make long transactions. Try to change a relatively small number of rows within a single transaction. Therefore, in short, the answer to your question: it is impossible to exclude the spreading of tables.

    So what to do?

    1. Be sure to always keep the auto vacuum on! And even more, it needs to be made more aggressive than it is out of the box. To do this, you need to change the settings in postgresql.conf with respect to:

    select * from pg_settings where name ~ 'autovacuum'

    autovacuum = on # Enable autovacuum subprocess? 'on'

    autovacuum_max_workers = 10 # max number of autovacuum subprocesses

    autovacuum_vacuum_scale_factor = 0.001 # fraction of table size before vacuum

    autovacuum_analyze_scale_factor = 0.005 # fraction of table size before analyze

    autovacuum_vacuum_cost_delay = 10ms # default vacuum cost delay for autovacuum, in milliseconds -1 means use vacuum_cost_delay

    Here you can additionally read about autovacuum

    All that you wanted to know about avtovakuum in PostgreSQL

    Additionally, it is possible at the level of individual tables to adjust the sensitivity of the auto-vacuum to the number of deleted rows. This is the command:

    ALTER TABLE public.mytable SET (
    autovacuum_enabled = true
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.05);

    Choose the parameters experimentally based on:

    Sets the percentage of the size of the table that will be added to autovacuum_vacuum_threshold when selecting the trigger threshold of the VACUUM command. The default value is 0.2 (20% of the table size). You can set this parameter only in postgresql.conf or in the command line when starting the server. However, you can override this value for selected tables by changing their storage options.

    1. If, however, the tables are swollen and you need to squeeze them, and you cannot launch VACUUM FULL, because users work and block their work is unacceptable, then you need to look towards using these tools:

      2.1 pgcompacttable

    call example for installed ActivePerl for windows c: \ perl64 \ bin \ perl.exe pgcompacttable.pl -h <server name> -p 5432 -U postgres -W <password> --all --verbose info> c: \ temp \ pgcompacttable.log

    What is being done inside?

    Briefly, if there is free space in the table, then with the update record a new version will go to this free space. In this case, unconditional preference is given to free space at the beginning of the table, if available. As a result, if you update the table (fake updates view field = field), starting from the last page, at some point all entries from the last page will go to the free space at the beginning of the table. Now, if we do the same thing N times, the last N pages of the database will be empty and the usual non-blocking Vacuum will be able to cut them off from the table and release (vacuum without full can free pages at the end of the table if they are completely free). To go from the end of the table, tid access is used (in fact, access with physical indication of page numbers and entries on the http://www.postgresql.org/docs/9.5/interactive/ddl-system-columns.html description of the ctid field). Everything else is the details of the implementation and optimization of speed is easier to see in the source. Vacuum fulll does about the same thing as hanging full (exclusive lock) on the table and wrapping all the pages in the table at once and not incrementally in pieces. This decision is noticeably slower, an indisputable fact (5-10 times accurate, when compared with pg_reorg). But it is incremental and allows you to limit the load. And not requiring the assembly of additional software on the server.

    2.2) pg_repack

    If you’re not a problem, you’ll be able to remove it from the table. Unlike the CLUSTER and VACUUM FULL it works online. pg_repack is CLUSTER directly.