Оптимизация пространства хранения, скорости запросов, данных столбца JSON в таблице Postgres

Рассмотрим следующую таблицу, в которой фиксируются изменения цен на разные товары, принадлежащие разным компаниям разных категорий.

     Column    |  Type  | Modifiers
-----------------+--------+-----------
 category_id   | bigint | not null
 product_id    | bigint | not null
 industry_id   | bigint | not null
 time          | bigint | not null
 price         | bigint | not null
 product_info  | json   | not null

Indexes:
    "price_change_pk" PRIMARY KEY, btree (category_id, product_id, price, "time")

Foreign-key constraints:
    "orders_industry_id" FOREIGN KEY (industry_id) REFERENCES industry_info(industry_id)
    "orders_product_id" FOREIGN KEY (product_id) REFERENCES device_info(product_id)
    "orders_category_id" FOREIGN KEY (categoy_id) REFERENCES category_info(category_id)

Чтобы быть понятными значения столбца будут:

category_id - отдельная таблица будет иметь идентификатор (уникальное значение bigint), сопоставленный с именем категории - 100s категорий

(Электроника, мода, здоровье, спорт, игрушки, книги)

industry_id - отдельная таблица будет иметь идентификатор (уникальное значение bigint), сопоставленный с названием отрасли - несколько тысяч отраслей в категории

(Nokia, Apple, Microsoft, PeterEngland, Rubik, Nivia, Cosco)

product_id - отдельная таблица будет иметь идентификатор (уникальное значение bigint), сопоставленный с названием продукта - миллионы продуктов в отрасли

time (unix time as bigint) - время изменения цены,

price - несколько тысяч различных значений - (200, 10000, 14999, 30599, 450)

product_info - json, который содержит дополнительную информацию о продукте (количество пар ключ / значение может отличаться)

{seller:"ABC Assured", discount:10, model:XYZ, EMIoption:true, EMIvalue:12, festival_offer:28, market_stat:comingsoon}

Таблица запрашивается несколькими способами для анализа тенденции изменения цены продукта в виде графика в диапазоне день / неделя / месяц в виде диапазонов час / день / неделя / месяц. Тенденция может быть основана на нет. продуктов, уникальные продукты, которые изменяются.

Например, Google Sample Trend

Хранение JSON как есть (как string) использует больше памяти. Поэтому я попытался сохранить ключ-значение в json с увеличивающимся серийным идентификатором в отдельной таблице, и эти идентификаторы используются.

подобно

Keys (citext, bigint)
seller - 1
discount - 2
model - 3
EMIoption - 4
EMIvalue - 5
festival_offer - 6
...
...
currency - 25

Values (citext, bigint)
ABC Assured - 1
10 - 2
XYZ - 3
true - 4
12 - 5
28 - 6
comingsoon - 7
...
...
ZYX - 106
rupees - 107
american dollars - 108
canadian dollars - 109
Prime seller - 110

{seller:"ABC Assured", discount:10, model:XYZ, EMIoption:true, EMIvalue:12, festival_offer:28, market_stat:comingsoon, curreny: rupees}

становится

{"1":1, "2":2", "3":3, "4":4, "5":5, "6":6, "7":7, "25":107}

{seller:"Prime seller", discount:10, model:XYZ, EMIoption:true, EMIvalue:12, festival_offer:28, market_stat:comingsoon, curreny: "canadian dollars"}

становится

{"1":110, "2":2", "3":3, "4":4, "5":5, "6":6, "7":7, "25":109}

Примерно для 20М набора данных он сократился примерно на 1,5 ГБ.

Увеличение значения ключа-кардинальности, увеличение серийных номеров. Поэтому я попытался сохранить десятичное число в шестнадцатеричном формате.

{"1":1, "2":2", "3":3, "4":4, "5":5, "6":6, "7":7, "25":107}

становится

{"1":1, "2":2", "3":3, "4":4, "5":5, "6":6, "7":7, "19":"6B"}

{"1":110, "2":2", "3":106, "4":4, "5":5, "6":6, "7":7, "25":109}

становится

{"1":, "2":2", "3":"6A", "4":4, "5":5, "6":6, "7":7, "19":"6D"}

Так же, как и хранение этих десятичных целых чисел в виде шестнадцатеричных целых чисел.

  1. Сэкономить место для хранения в дальнейшем? (потому что визуально кажется сжатым)
  2. Сохраняет ли JSON тип данных ключ-значение или они хранятся в виде строк?
  3. Делает данные сжатыми?
  4. Улучшить производительность чтения?
  5. Или в любом случае это может быть улучшено? (Индексация или какая-нибудь?)

В обычном приложении psql выполнение запросов занимает несколько минут. Поскольку он соответствует данным временных рядов, мы используем расширение TimescaleDB, а его механизм шардинга ускоряет выполнение запроса, но нам нужны результаты в секундах.

Примеры запросов: чтобы проверить, сколько раз цена изменялась на 500, для всех продуктов в данной категории в группе месяцев по дням.

select count(*), to_char(date_trunc('day', to_timestamp(time/1000) at time zone 'Asia/Kolkata'), 'YYYY/MM/DD') as unit, price 
from price_change 
where category_id = 1000000010 and time between 1514745000000 and 1517423400000 
  and price = 500 
group by price, unit;

Чтобы проверить, сколько раз цена изменялась на любой из (100 200 300 400 800 600 600 700 800 800 900 000) для всех продуктов в данной категории в группе за последние 10 месяцев, каждый месяц.

select count(*), to_char(date_trunc('month', to_timestamp(time/1000) at time zone 'Asia/Kolkata'), 'YYYY/MM/DD') as unit, price 
from price_change 
where category_id = 1000000010 and time between  1514745000000 and 1517423400000  
   and price in (100,200,300,400,500,600,700,800,900,1000) group by price, unit;

Чтобы выбрать информацию о товаре, цена которого была изменена в данном временном диапазоне, в данной категории

select product_id, product_name, price, to_char(date_trunc('day', to_timestamp(time/1000) at time zone 'Asia/Kolkata'), 'YYYY/MM/DD') as timestamp 
from price_change 
  join products using product_id 
where price_change.category_id = 1000000010 
  and price_change.time between 1514745000000 and 1517423400000;

Чтобы выбрать данные отрасли и идентификатора продукта, цена которого была изменена в заданном временном диапазоне, в данной категории

select industry_id, product_id, price 
from price_change 
  join industries using industry_id 
where price_change.category_id = 1000000010 
  and price_change.time between 1514745000000 and 1517423400000;

Чтобы выбрать детали изменения цены товара, во временном диапазоне со скидкой 10%, в определенной категории

select product_id, product_name, price, to_char(date_trunc('day', to_timestamp(time/1000) at time zone 'Asia/Kolkata'), 'YYYY/MM/DD') as timestamp 
from price_change 
  join products using product_id 
where price_change.category_id = 1000000010 
  and price_change.time between 1514745000000 and 1517423400000
  and product_info->>'discount'=10;

Чтобы выбрать сведения об изменении цены товара, за определенный промежуток времени, проданный конкретным продавцом, в определенной категории

select product_id, product_name, price, to_char(date_trunc('day', to_timestamp(time/1000) at time zone 'Asia/Kolkata'), 'YYYY/MM/DD') as timestamp 
from price_change 
  join products using product_id 
where price_change.category_id = 1000000010 
  and price_change.time between 1514745000000 and 1517423400000
  and product_info->>'seller'='ABC Assured';

В большинстве случаев запрос не будет содержать category_id в выбранных столбцах.

Источник

1 ответ

Было бы полезно, если бы вы также предоставили несколько примеров того, к чему вы обычно обращаетесь. Существуют различные способы оптимизации индексов / как данные записываются на диск, которые очень сильно зависят от того, какой тип запроса вы выполняете (более конкретно, что содержится в предложении where)? Если вы используете предложения where, которые рассматривают JSON, вам следует рассмотреть возможность разбиения их на столбцы или построения индексов в самом JSON.

Похоже, одной из ваших проблем является хранение. Поскольку TimescaleDB и PostgreSQL являются реляционными, они занимают больше памяти, чем, возможно, столбчатое хранилище, которое может иметь лучшие характеристики сжатия. Вы можете также рассмотреть возможность использования чего-то вроде ZFS для сжатия.

Источник
Другие вопросы по тегам