Thousands of partitions performance questions

Поиск
Список
Период
Сортировка
От Shai Cantor
Тема Thousands of partitions performance questions
Дата
Msg-id CABJyNM58xvi+rhnhj-MmMvAXU8ShN6_Kz2c34DkzahzDk5pHEg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Thousands of partitions performance questions
Список pgsql-general
Дерево обсуждения

  • General
    • Our system gathers data from CI builds regarding a customer's code base
  • Data includes
    • type
      • methods/lines/branches A.K.A - code elements
      • files
  • Queries
    • The data is queried for a specific build only
    • Queries are aggregations on the code elements and files with some filtering and sorting
  • Volume
    • I expect to have about 1500 builds a day, 45000 builds a month
    • A build can have around 300000 code elements and around 30000 files
  • Retention
    • Thought about keeping 90 days of builds as retention
  • Plan
    • 2 tables
      • code elements
      • files
    • create a partition for each build
    • each day delete partitions older than 90 days
    • create 2 schemas for each client
      • <customer-id> schema which holds the parent tables
      • <customer-id>_partitions schema that will hold the partitions
    • upon a new build
      • create a partition for the code elements table and for the files table in the "<customer-id>_partitions" schema
  • Questions
    • Will the db hold 135000 (45000 * 3 months) partitions under the assumption that I query only 1 partition?
    • Should I model it differently in terms of schema, partitions etc.?

В списке pgsql-general по дате отправления: