1

For the purpose of learning, I am playing with Postgress. In particular partitions and indexing.

The Tables

Let's say I have the following tables on my system:

webs
web_id --> BigInt
web_tld --> VarChar 64
Files (Paritioned with web_id key)
file_id --> BigInt
file_web_id --> Constrain pointing to web_id
file_url --> VarChar 64
file_filetype --> VarChar 32
html_tags
tag_id --> BigInt
tag_name --> VarChar 64
html_tag_value (Partitioned with web_id key)
tv_id --> BigIng
tv_value --> Text

Table Sizes

Data writting

  • The system adds 24000 new web_id per day -- For each web, will be 20000 file_id. -- Writting 86400 new file_id per day.
  • For each file, will be 100 html_tag_value.tv_id -- Writting 8640000 new html_tag_value.tv_id per day.
  • HTML_tags has a fixed size of 100000 rows.

In a week's time, the tables will be:

  • 168000 web_id
  • 604800 file_id
  • 60480000 html_tag_value.tv_id

If this numbers do not fit what I am trying to exemplify, just multiply all of them by 10000

Data removal

All tables will have data for the X time. Anything earlier than that is removed to keep a constant size.

The premises

  1. The DB is PG SQL.

  2. A system will be writting data at a constant pace.

  3. A system will read data at random times with similar queries and different size of results fetched.

  4. Each table has an index on its primary key, column ending in _id

  5. If a column will be used for seach, will have an index as well (filetypes.file_filetype)

  6. Data reading should go as fast as possible.

  7. Data writting does not have a tight time constrain. It can be a bit "slower".

I assume, having better and bigger servers will help, but on this case I would like to understand the use of data partitioning.

The approach.

I currently have indexes btree when the column value is not guarantee to be unique. Otherwise the indexes are hash.

From the pg documentation, my understanding is that I can create a new partition every X web_id rows, but it leads to inconsistencies on the table sizes. Also, when retrieving data using JOIN html_tags, it is significantly slow (say 10s of seconds).

The data stored in html_tag_value.tv_value, is currently stored as text, but it contains bools, datetimes and strings that I have casted to text to store. This seems like a horrible approach as I miss many features in terms of efficiency on the search.

Questions

  • How can the data partitions be improved? -- Setting a fixed number of rows on html_tag_value.tv_id?
  • How can I handle the html_tag_value.tv_values to make the most out of PG? (i.e: Seach by date range)
  • Is it worth to put the data into their respective tables, or have a "reading-table" with all the data put together and synced every so often?

Thanks for reading! :)

-- EDIT -- As for clarity on the questions:

  • What is a better partitioning stragy based on "web_id": -- Partition it every 100 web_ids, so all the html_tag_values are within the same table always? -- Or, partition html_tag_values every 1000000 values so all of them are the same size and I can expect a consistent time on queries, but not all the values from the same web_id are "together"?

  • How to store html_tag_values efficiently? Currently are type "text", but in reality they can be "bool", "datetimes", "str", ... --- Is it better have a table for each data type? --- Or split it into columns where I will only use one per row and the rest will be "null" as the value will be only of one data type?

4
  • it's an interesting topic but "How can X be improved" or "How can I use X in best possible way" are not concrete questions.
    – filiprem
    Nov 7, 2023 at 14:35
  • Fair point. I'll update the "questions" section
    – Javi M
    Nov 7, 2023 at 17:47
  • Table partitioning is not a performance tuning tool. In your case the only sensible partitioning key is the date/time for "Anything earlier than that is removed".
    – mustaccio
    Nov 7, 2023 at 17:58
  • In that case, if the system is not performing on a constant pace, it will lead to different sizes... Any comment regarding the data types approach? Latest question on the update.
    – Javi M
    Nov 7, 2023 at 18:11

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.