Table bloat? No, never heard...



I think many well-known feature of PostgreSQL, which has the effect of inflating of tables, or table bloat. We know that it manifests itself in case of intensive data updates as frequent UPDATE and INSERT/DELETE operations. As a result of this inflation decreases the performance. Consider why this is happening and how this can be dealt with.


Tables in PostgreSQL are presented in pages of size 8Kb, in which the records are located. When one page is completely filled with entries to the table add a new page. When udeleni records by using the DELETE or modify it with a UPDATE, the place where was the old record cannot be re-used immediately. For this cleaning process autovacuum or VACUUM command, runs on the changed pages and marks that place as free, after which a new entry can safely be recorded in this place. If autovacuum is unable to cope, for example as a result of active changes, more data or simply due to bad settings, the table will unnecessarily be added to new pages as new records arrive. And even after cleaning comes to our deleted records, the new page will remain. It turns out that the table becomes more rarefied in terms of density records. This is called the effect of inflating of tables table bloat.

The cleaning procedure, autovacuum or VACUUM can reduce table size by removing completely blank page, but only on the condition that they are at the end of the table. To minimize a table in PostgreSQL has a VACUUM FULL or CLUSTER, but both these techniques require heavy and long locks on the table that is not always a suitable solution.

Consider one of the solutions. When you update a record using UPDATE, if the table has free space, the new version will go exactly in free space, without allocating new pages. Preference is given to the free location closest to the beginning of the table. If you update a table by using so-called fake updates, such some_column = some_column from the last page, at some point, all the records from the last page go into free space in earlier pages of the table. Thus, after several such operations, the last page will be empty and the usual non-blocking VACUUM will be able to cut them off from the table, thereby reducing the size.

In the end, using this technique, it is possible to compress the table, without causing any critical locks, and thus without interfering with other sessions and normal work base.
And now the most important)))) To automate this procedure, there is a utility pgcompactor.

Its main features are:
the
    the
  • requires no dependencies except Perl > =5.8.8, i.e. you can just copy pgcompactor to the server and work with it;
    • the
  • works through adapters DBD::Pg, DBD::PgPP or even a standard utility psql, if the first two the server;
    • the
  • processing of individual tables, all tables within the schema, database or the entire cluster.
    • the
  • possibility to exclude databases, schemas or tables from processing;
    • the
  • analysis of the effect of inflating and treatment of only those tables, which present, for more accurate calculations it is recommended to install the extension pgstattuple;
    • the
  • analyzing and rebuilding indexes with the effect of inflation;
    • the
  • analysis and rebuilding of the unique constraints (unique constraints) and primary keys (primary keys) with the effect of inflation;
    • the
  • incremental-use, i.e. you can stop the compression process without compromising anything;
    • the
  • dynamic adjustment to the current load of database to not affect the performance of user queries (with the possibility of adjustment at startup);
    • the
  • recommendations to administrators, followed by the DDL ready to rebuild database objects that can not be rebuilt automatically.

    • A couple of examples of usage:
    run on all cluster required rebuilding indexes:
    # pgcompactor --all --reindex

    running on a separate table (rebuilding indexes):
    # pgcompactor --reindex --dbname geodata --table cities --verbose info >pgcompactor.log 2>&1

    Result:
    Table size decreased from 9.2 GB to 5.6 GB. The total size of all indexes dropped from 7.5 GB to 2.8 GB


    Project URL: github.com/PostgreSQL-Consulting/pgcompacttable

    Many thanks to the authors of the utility for their work! This is a really useful tool.

    Thank you for your attention!
Article based on information from habrahabr.ru

Комментарии

Отправить комментарий

Популярные сообщения из этого блога

Performance comparison of hierarchical models, Django and PostgreSQL

Изображение
Good day, dear readers. Today's article will be devoted to the comparison of models of working with hierarchical data in PostgreSQL via Django app. In the article I specifically do not use a clean implementation in the database, because I'm interested in is performance in an environment close to combat. All tests were performed on my data and results are relevant for this data on your data the result may differ from that obtained in the article. Model implementation of the hierarchical structures in the database For working with such structures in PostgreSQL you can use the following modely : the Adjacency model (AM) — model, when the column is stored parent; the Nested Sets (NS) model when paired column stores the range of all sub-elements; the Materialised Path model (MP) model, when the column is stored the full path to the element. Also details about the implementation of hierarchical structures in a relational database, you can read here . To imp...
Далее...

Transport Tycoon Deluxe / Emscripten part 2

Изображение
Transport Tycoon (Transport tycoon) is pretty old, but still enjoyable, especially the maniacs, the game is in the genre of RTS. /.../ There is also OpenTTD, outdoor product manyatta iPhoto, resulting from the game can not be distracted even in the toilet and underground, setting her on the Communicator or PDA. lurkmore I can honestly say: I killed a huge amount of time playing Transport Tycoon Deluxe. I'm just a fan of this game. So when on Habre the article appeared about how the guys from mozilla have ported Doom, I hit on the idea – to migrate one of my favorite games here on the Internet. By the way, the article was scarce, but I learned the main thing – the porting can be done using emscripten . Since the inception of the thought of the possibility of such a project was not less than a year (my first attempt crashed on the rocks of misunderstanding). And now I'm in a hurry to please you all: I did it! Now to sit ass playing TTD you can be located anywhere con...
Далее...

google life search

Изображение
Yesterday Google announced the Live search So, what is it and how to use it? Did it make your search more relevant and convenient for users? How to get it, where to enter a query to see live results? The algorithm is still driven around and while fully works only for English queries. You enter the query and see the results of the search in the middle of the page in the section Latest result for “your request”. Can try enter your query here . For example, a request Obama. About the middle of the page is visible to block the Latest results that are updated in real-time. New results on request Obama constantly updated and added to the search results. If this text is annoying, you can pause by clicking "Pause", but completely remove the "live search" from the results it is impossible. Adam Smith, a Google employee, guiding the development of new search system in real time, says that not all queries can be seen regularly updated information: ...
Далее...