{"id":79039,"date":"2020-04-23T19:43:26","date_gmt":"2020-04-23T17:43:26","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/ekonomim-kopeechku-na-bolshih-obemah-v-postgresql"},"modified":"2020-04-23T19:43:26","modified_gmt":"2020-04-23T17:43:26","slug":"ekonomim-kopeechku-na-bolshih-obemah-v-postgresql","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/ekonomim-kopeechku-na-bolshih-obemah-v-postgresql","title":{"rendered":"Saving a penny on large volumes in PostgreSQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Continuing the topic of recording large streams of data raised in the previous article about partitioning<\/a><\/noindex>, in this one we will consider methods by which you can reduce the 'physical' size of stored<\/b> data in PostgreSQL, and their impact on server performance.<\/p>\n

We will talk about TOAST settings and data alignment.<\/b>. On average, these methods will save not too many resources, but without any modification of the application code.<\/p>\n

\"Saving
\nHowever, our experience turned out to be quite productive in this regard, as the storage for almost any monitoring is, by its nature, mostly append-only<\/b> in terms of the data being written. And if you're curious about how to make the database write to disk instead of 200MB\/s<\/b> half that \u2014 please read on.
\n<\/a><\/noindex><\/p>\n

Little secrets of big data<\/h2>\n

\nAccording to the profile of our service, it regularly receives text packets from logs.<\/a><\/noindex>And since the IBS complex, whose databases we monitor, is a multi-component product with complex data structures, the queries<\/b>.<\/p>\n

to achieve maximum performance end up being quite<\/a><\/noindex>like 'multi-volume' with complex algorithmic logic. Thus, the size of each individual query instance or the resulting execution plan in the incoming log turns out to be, on average, quite large.<\/b> Let's take a look at the structure of one of the tables where we write 'raw' data \u2014 that is, straight from the original log entry: CREATE TABLE rawdata_orig(\n pack -- PK\n uuid NOT NULL\n, recno -- PK\n smallint NOT NULL\n, dt -- section key\n date\n, data -- the most important\n text\n, PRIMARY KEY(pack, recno)\n);<\/a><\/noindex>A typical such table (already partitioned, of course, so this is a section template), where the most important part is the text. Sometimes it's quite large.<\/p>\n

Remember that the 'physical' size of a single record in PG cannot exceed one page of data, but the 'logical' size is a completely different matter. To write a large value (varchar\/text\/bytea) into a field, the<\/p>\n

TOAST technology is used.<\/code><\/pre>\n
\nA typical table like this (already partitioned, of course, so this is a section template), where the most important part is the text. Sometimes, it's quite extensive.<\/p>\n
Recall that the 'physical' size of a single entry in PG cannot exceed one data page, but the 'logical' size is a different matter. To write a large value in a field (varchar\/text\/bytea), the TOAST technology<\/a><\/noindex>:<\/p>\n
PostgreSQL uses a fixed page size (usually 8 KB) and does not allow tuples to span multiple pages. Therefore, it is not possible to store very large field values directly. To overcome this limitation, large field values are compressed and\/or split into multiple physical rows. This happens transparently to the user and has a minimal impact on most of the server code. This method is known as TOAST \u2026<\/p><\/blockquote>\n
\nIn fact, for each table with 'potentially large' fields, a paired table with 'slices' is automatically created for each 'large' record in segments of 2KB:<\/a><\/noindex> TOAST(\n  chunk_id\n    integer\n, chunk_seq\n    integer\n, chunk_data\n    bytea\n, PRIMARY KEY(chunk_id, chunk_seq)\n);<\/p>\n
That is, if we need to write a row with a 'large' value, the actual record will occur<\/code><\/pre>\n
\nnot only in the main table and its PK but also in TOAST and its PK. data<\/code>Reducing TOAST impact However, most records are not that large,<\/b>.<\/p>\n
and should fit within 8KB.<\/h4>\n
\n\u2014 How can we save on this?.. Here, the column attribute<\/b> STORAGE<\/p>\n
comes to our aid: EXTENDED<\/code><\/a><\/noindex> allows both compression and separate storage. This<\/p>\n
\n