PostgreSQL에서 공간 계산 및 절약

PostgreSQL에서 공간 계산 및 절약

---

다음과 같이 pg에 테이블이 있습니다.

CREATE TABLE t (
    a BIGSERIAL NOT NULL,               -- 8 b
    b SMALLINT,                         -- 2 b
    c SMALLINT,                         -- 2 b
    d REAL,                             -- 4 b
    e REAL,                             -- 4 b
    f REAL,                             -- 4 b
    g INTEGER,                          -- 4 b
    h REAL,                             -- 4 b
    i REAL,                             -- 4 b
    j SMALLINT,                         -- 2 b
    k INTEGER,                          -- 4 b
    l INTEGER,                          -- 4 b
    m REAL,                             -- 4 b
    CONSTRAINT a_pkey PRIMARY KEY (a)
);

위의 내용은 행당 최대 50 바이트를 추가합니다. 내 경험에 따르면 위의 사용자가 만든 인덱스도없이 시스템 오버 헤드에 대해 40 %에서 50 %가 더 필요합니다. 따라서 행당 약 75 바이트입니다. 저는 테이블에 1,450 억 행이 넘는 많은 행을 가질 것이므로 테이블은 13-14 테라 바이트를 밀어 낼 것입니다. 이 테이블을 압축하는 데 사용할 수있는 트릭은 무엇입니까? 내 가능한 아이디어는 ...

변환 real값을 integer. 로 저장할 수있는 경우 smallint필드 당 2 바이트가 절약됩니다.

b .. m 열을 배열로 변환합니다. 해당 열을 검색 할 필요는 없지만 한 번에 하나의 열 값을 반환 할 수 있어야합니다. 따라서 g 열이 필요하면 다음과 같이 할 수 있습니다.

SELECT a, arr[5] FROM t;

어레이 옵션으로 공간을 절약 할 수 있습니까? 속도 불이익이 있습니까?

다른 아이디어가 있습니까?

---

배열에 여러 숫자 필드를 저장할 때 얻을 수있는 것이없고 잃을 것이 없습니다.

각 숫자 유형 의 크기 는 명확하게 문서화되어 있으므로 원하는 범위 해상도와 호환되는 가장 작은 크기의 유형을 사용하면됩니다. 그게 당신이 할 수있는 전부입니다.

행을 따라 열에 대한 일부 바이트 정렬 요구 사항이 있는지 여부는 생각하지 않습니다 (확실하지 않습니다).이 경우 열의 순서를 변경하면 사용 된 공간이 변경 될 수 있습니다.하지만 그렇게 생각하지 않습니다.

BTW, 행당 약 23 바이트 의 수정 오버 헤드가 있습니다.

---

"열 테트리스"

실제로 무언가 를 할 수 있지만 더 깊은 이해가 필요합니다. 키워드는 정렬 패딩 입니다. 모든 데이터 유형에는 특정 정렬 요구 사항이 있습니다.

열 을 유리하게 정렬하여 열 사이의 패딩으로 인한 공간 손실을 최소화 할 수 있습니다 . 다음 (극단적 인) 예제는 많은 물리적 디스크 공간을 낭비합니다.

CREATE TABLE t (
    e int2    -- 6 bytes of padding after int2
  , a int8
  , f int2    -- 6 bytes of padding after int2
  , b int8
  , g int2    -- 6 bytes of padding after int2
  , c int8
  , h int2    -- 6 bytes of padding after int2
  , d int8)

행당 24 바이트 를 절약하려면 대신 다음을 사용하세요.

CREATE TABLE t (
    a int8
  , b int8
  , c int8
  , d int8
  , e int2
  , f int2
  , g int2
  , h int2)   -- 4 int2 occupy 8 byte (MAXALIGN), no padding at the end

SQL 바이올린.

경험상 8 바이트 열을 먼저 넣은 다음 4 바이트, 2 바이트 및 1 바이트 열을 마지막으로 넣으면 잘못 될 수 없습니다.

boolean, uuid and a few other types need no alignment padding. text, varchar and other "varlena" (variable length) types nominally require "int" alignment (4 bytes on most machines). But in fact there is no alignment padding in disk format (unlike in RAM). I verified in many tests. Eventually, I found the explanation in a note in the source code:

Note also that we allow the nominal alignment to be violated when storing "packed" varlenas;

Normally, you may save a couple of bytes per row at best playing "column tetris". None of this is necessary in most cases. But with billions of rows it can mean a couple of gigabytes easily.

You can test the actual column / row size with the function pg_column_size().
Some types occupy more space in RAM than on disk (compressed or "packed" format). You can get bigger results for constants (RAM format) than for table columns when testing the same value (or row of values vs. table row) with pg_column_size().

Finally, some types can be compressed or "toasted" (stored out of line) or both.

Overhead per tuple (row)

4 bytes per row for the item pointer - not subject to above considerations.
And at least 24 bytes (23 + padding) for the tuple header. The manual on Database Page Layout:

There is a fixed-size header (occupying 23 bytes on most machines), followed by an optional null bitmap, an optional object ID field, and the user data.

For the padding between header and user data, you need to know MAXALIGN on your server - typically 8 bytes on a 64-bit OS (or 4 bytes on a 32-bit OS). If you are not sure, check out pg_controldata.

Run the following in your Postgres binary dir to get a definitive answer:

./pg_controldata /path/to/my/dbcluster

The manual:

The actual user data (columns of the row) begins at the offset indicated by t_hoff, which must always be a multiple of the MAXALIGN distance for the platform.

So you typically get the storage optimum by packing data in multiples of 8 bytes.

There is nothing to gain in the example you posted. It's already packed tightly. 2 bytes of padding after the last int2, 4 bytes at the end. You could consolidate the padding to 6 bytes at the end, which wouldn't change anything.

Overhead per data page

Data page size is typically 8 KB. Some overhead / bloat at this level, too: Remainders not big enough to fit another tuple, and more importantly dead rows or a percentage reserved with the FILLFACTOR setting.

There are a couple of other factors for size on disk to take into account:

• How many records can I store in 5 MB of PostgreSQL on Heroku?
• Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
• Configuring PostgreSQL for read performance

Array types?

With an array type like you were evaluating, you would add 24 bytes of overhead for the type. Plus, array elements occupy space as usual. Nothing to gain there.

참고URL : https://stackoverflow.com/questions/2966524/calculating-and-saving-space-in-postgresql