ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Сколько занимает места таблица
12 сообщений из 12, страница 1 из 1
Сколько занимает места таблица
    #39563152
Bsplesk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bsplesk
Гость
Есть у меня две таблички содержащие по одному столбцу с одинаковым наименованием, без index и constraint, но разным типом данных: smallint, boolean, содержащие по 7000000 тестовых записей 0/1.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
SELECT
    TABLE_NAME,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        TABLE_NAME,
        pg_table_size(TABLE_NAME) AS table_size,
        pg_indexes_size(TABLE_NAME) AS indexes_size,
        pg_total_relation_size(TABLE_NAME) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || TABLE_NAME || '"') AS TABLE_NAME
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes



Вывод и интересующих таблиц:
Код: plaintext
1.
2.
3.
4.
table_name                                                   |table_size |indexes_size |total_size |
-------------------------------------------------------------|-----------|-------------|-----------|
"public"."test_type_boolean"                                 |242 MB     |0 bytes      |242 MB     |
"public"."test_type_smallint"                                |242 MB     |0 bytes      |242 MB     |

Код: plsql
1.
2.
3.
4.
SELECT 
	relname AS name, relfilenode AS oid, (relpages / 128) as size_mb, 
	reltuples  as count FROM pg_class WHERE relname NOT LIKE 'pg%' and relname LIKE 'test%'  
	ORDER BY relpages DESC;



Вывод:
Код: plaintext
1.
2.
3.
name               |oid     |size_mb |count   |
-------------------|--------|--------|--------|
test_type_smallint |2627534 |241     |6999996 |
test_type_boolean  |2627535 |192     |5568640 |

Соответственно интересует разница в показаниях, и есть ли смысл использовать boolean тип, с точки зрения занимаемого места.
...
Рейтинг: 0 / 0
02.12.2017, 23:25
| Ответить | Цитировать | Написать  
Сколько занимает места таблица
    #39563157
Bsplesk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bsplesk
Гость
Код: plsql
1.
SELECT pg_relation_filepath('test_type_smallint');



du -h /var/lib/pgsql/9.6/data/base/2624085/2627534

Код: plaintext
242M	/var/lib/pgsql/9.6/data/base/2624085/2627534

Код: plsql
1.
SELECT pg_relation_filepath('test_type_boolean');



du -h /var/lib/pgsql/9.6/data/base/2624085/2627535

Код: plaintext
242M	/var/lib/pgsql/9.6/data/base/2624085/2627535

Печально, получается от boolean никакого толку, с точки зрения хранения :(.
...
Рейтинг: 0 / 0
02.12.2017, 23:39
| Ответить | Цитировать | Написать  
Сколько занимает места таблица
    #39563159
Bsplesk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bsplesk
Гость
При этом:
Код: plsql
1.
select pg_column_size( '1'::SMALLINT);



Код: plaintext
1.
2.
pg_column_size |
---------------|
2              |

Код: plsql
1.
select pg_column_size( '1'::BOOLEAN);



Код: plaintext
1.
2.
pg_column_size |
---------------|
1              |
...
Рейтинг: 0 / 0
02.12.2017, 23:47
| Ответить | Цитировать | Написать  
Сколько занимает места таблица
    #39563166
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
Bsplesk,

Надо смотреть полностью структуру таблицы. Есть фокусы выравнивания данных в памяти (и паровозиком в датафайлах на диске) когда одна и та же таблица, но с разным порядком полей занимает разное место.
Например вот тут затрагивался вопрос https://pgday.ru/ru/2016/papers/62 с 34 слайда. Но вменяемого полного описания механики мне нигде не попадалось.
...
Рейтинг: 0 / 0
02.12.2017, 23:55
| Ответить | Цитировать | Написать  
Сколько занимает места таблица
    #39563171
Bsplesk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bsplesk
Гость
Пасиб "читну на ночь", но походу придется купить "Man_ы" .... эх. а не хотелось.

Для прикола создал 3 аналогичную табличку с типом INTEGER, заполнил (причем не 0/1, а диапазоном), посмотрел на размер, он равен типам BOOLEAN/SMALLINT, хотя казалось бы ....

Код: plsql
1.
select pg_column_size( 250000::INTEGER);



Код: plaintext
1.
2.
pg_column_size |
---------------|
4              |

Код: plsql
1.
2.
3.
INSERT INTO test_type_integer (test_i) 
	SELECT random()*(10000000-10)+1::Int 
	FROM generate_series(1, 7000000) AS test_i;



-- место

Код: plaintext
1.
2.
3.
4.
table_name                                                   |table_size |indexes_size |total_size |
-------------------------------------------------------------|-----------|-------------|-----------|
"public"."test_type_boolean"                                 |242 MB     |0 bytes      |242 MB     |
"public"."test_type_smallint"                                |242 MB     |0 bytes      |242 MB     |
"public"."test_type_integer"                                 |242 MB     |0 bytes      |242 MB     |
...
Рейтинг: 0 / 0
03.12.2017, 00:15
| Ответить | Цитировать | Написать  
Сколько занимает места таблица
    #39563174
Bsplesk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bsplesk
Гость
и c bigint, аналогичный размер.
...
Рейтинг: 0 / 0
03.12.2017, 00:30
| Ответить | Цитировать | Написать  
Сколько занимает места таблица
    #39563182
Bsplesk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bsplesk
Гость
повторил, создал по 3 тестовых столбца, на integer/bigint разница появилась, вообщем интересно postgres скидывает/нарезает на диск данные. Видимо учитывает настройки дисковой подсистемы.

Интересно нет ли какого нибудь tool, который бы позволял просматривать/тюнить БД под конкретные данные, или подсказывал какие параметры файловой системы оптимальны для конкретных данных.....
...
Рейтинг: 0 / 0
03.12.2017, 00:49
| Ответить | Цитировать | Написать  
Сколько занимает места таблица
    #39563185
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
Bsplesk,

настройки ФС не учитывает.
Выравнивание структур в памяти - учитывает и добавляет пустые блоки для соблюдения этого выравнивания. Повторюсь, именно это и есть причина наблюдаемого поведения. Но нормального описания что как и куда выравнивается мне не попадалось. Обратите внимание на typalign в pg_type
...
Рейтинг: 0 / 0
03.12.2017, 00:59
| Ответить | Цитировать | Написать  
Сколько занимает места таблица
    #39563189
Bsplesk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bsplesk
Гость
MelkijBsplesk,

настройки ФС не учитывает.
Выравнивание структур в памяти - учитывает и добавляет пустые блоки для соблюдения этого выравнивания. Повторюсь, именно это и есть причина наблюдаемого поведения. Но нормального описания что как и куда выравнивается мне не попадалось. Обратите внимание на typalign в pg_type

Все пошел читать ... :) на ночь сказку.

Для примера попробовал массивы и jsonb, jsonb - ожидаемо медленно+большой объем, а вот массив разочаровал, думал он оптимально сложит, а нет ... или "паразитные" много занимают. но и insert у массива медленней обычных столбцов.


Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
create table test_type_jsonb (
	test_abc jsonb
);


create table test_type_array (
	test_abc BOOLEAN [3]
);



Код: plaintext
1.
2.
3.
4.
5.
6.
7.
table_name                                                   |table_size |indexes_size |total_size |
-------------------------------------------------------------|-----------|-------------|-----------|
"public"."test_type_jsonb"                                   |593 MB     |0 bytes      |593 MB     |
"public"."test_type_array"                                   |348 MB     |0 bytes      |348 MB     |
"public"."test_type_big_int"                                 |348 MB     |0 bytes      |348 MB     |
"public"."test_type_integer"                                 |296 MB     |0 bytes      |296 MB     |
"public"."test_type_smallint"                                |242 MB     |0 bytes      |242 MB     |
"public"."test_type_boolean"                                 |242 MB     |0 bytes      |242 MB     |
...
Рейтинг: 0 / 0
03.12.2017, 01:27
| Ответить | Цитировать | Написать  
Сколько занимает места таблица
    #39563191
Bsplesk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Bsplesk
Гость
C jsonb не верно в сравнении с массивом, ибо с ключами:
Код: plsql
1.
2.
3.
4.
5.
6.
INSERT INTO test_type_jsonb (test_abc) 
	SELECT jsonb_build_object( 'a',random()::int ,
                     'b', random()::INT,
                     'c', random()::INT
                   ) 
	FROM generate_series(1, 7000000) AS test_i;



Без ключей лучше:

Код: plsql
1.
2.
3.
4.
5.
6.
INSERT INTO test_type_jsonb (test_abc) 
	SELECT jsonb_build_array(random()::int ,
                     random()::INT,
                     random()::INT
                   ) 
	FROM generate_series(1, 7000000) AS test_i;




Код: plaintext
1.
2.
table_name                                                   |table_size |indexes_size |total_size |
-------------------------------------------------------------|-----------|-------------|-----------|
"public"."test_type_jsonb"                                   |484 MB     |0 bytes      |484 MB     |
...
Рейтинг: 0 / 0
03.12.2017, 01:39
| Ответить | Цитировать | Написать  
Сколько занимает места таблица
    #39563235
Melkij
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Melkij
Участник
Bsplesk,

у массива есть константный заголовок, на память что-то около 16 байт. Поэтому одно значения в массиве хранить нецелесообразно.
...
Рейтинг: 0 / 0
03.12.2017, 12:21
| Ответить | Цитировать | Написать  
Сколько занимает места таблица
    #39563247
Фотография Maxim Boguk
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Maxim Boguk
Участник
Bspleskповторил, создал по 3 тестовых столбца, на integer/bigint разница появилась, вообщем интересно postgres скидывает/нарезает на диск данные. Видимо учитывает настройки дисковой подсистемы.

Интересно нет ли какого нибудь tool, который бы позволял просматривать/тюнить БД под конкретные данные, или подсказывал какие параметры файловой системы оптимальны для конкретных данных.....

Это не по файловую систему а про выравнивание структур на диске и в памяти.
Вам сюда вот: https://www.postgresql.org/docs/10/static/storage-page-layout.html
23 байта заголовок (округленный до MAXALIGN т.е. обычно 24 байта)
и далее данные таблицы но тоже суммарно до MAXALIGN по длине (т.е. не каждый по отдельности) округлены, т.е. до до 8 байт в общем случае обычно.

То есть минимум что может занимать строка - 32 байта... и что там boolean что bigint - размер таблицы будет одинаковый.
...
Рейтинг: 0 / 0
03.12.2017, 13:10
| Ответить | Цитировать | Написать  
12 сообщений из 12, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Сколько занимает места таблица
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=53&fpage=62&tid=1996061]:
 0ms
get settings:
 9ms
get forum list:
 15ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 36ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 50ms
get tp. blocked users:
 2ms
others: 291ms
total:  422ms
созд. / загр.: 422ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]