We successfully designed the structure of our PostgreSQL database for storing messages, a year has passed, and users are actively filling it, so far it has millions of records, and… something has started to lag.
- Part 2: partitioning 'live'
The fact is that as the size of the table grows, the 'depth' of the indexes also increases — albeit logarithmically. But over time, this forces the server to handle much more data pages for the same read/write tasks, than at the beginning.
This is where partitioning comes into play..
I should note that we will not be discussing sharding, i.e., distributing data across different databases or servers. Because, even if you split the data among several servers, you will not escape the problem of 'bloated' indexes over time. It is clear that if you can afford to bring a new server online every day, your issues will lie in a completely different area than a specific database.
We will not look at specific scripts for implementing partitioning 'in hardware', but rather the approach — what and how should be 'sliced up', and what such a desire leads to.
Concept
Let’s define our goal once again: we want to ensure that today, tomorrow, and a year from now, the amount of readable PostgreSQL data during any read/write operation remains approximately the same.
For any chronologically accumulating data (messages, documents, logs, archives, …) the natural choice for a partitioning key is the date/time of the event. In our case, that event is the moment a message is sent..
We note that users almost always work only with the 'latest' data — reading the most recent messages, analyzing the latest logs,… No, they can scroll back in time, but they do this very rarely.
From these constraints, it becomes evident that the optimal solution for messages will be 'daily' sections — as our user will almost always read what has arrived 'today' or 'yesterday'.
If we write and read almost exclusively within one section throughout the day, this also gives us a more efficient use of memory and disk. — since all section indexes easily fit into memory, unlike the "large and bulky" ones throughout the table.
step-by-step
In general, everything said above sounds like one continuous profit. And it is achievable, but for that we will have to make a good effort — because the decision to partition one of the entities leads to the need to "split" related ones as well.
Message, its properties and projections
Since we decided to cut messages by dates, it is reasonable to also divide dependent entities-properties (attachments, list of recipients) according to the date of the message as well..
Since one of our standard tasks is precisely to view message registers (unread, incoming, all), it also makes sense to "pull" them into the partitioning by message dates.
We add a partitioning key (message date) to all tables: recipients, file, registers. It's not necessary to add it to the message itself, but to use the existing DateTime.
Topics
Since the topic is the same for several messages, it cannot be "cut" in the same model anymore; we have to rely on something else. In our case, the perfect fit is the date of the first message in the correspondence. — that is, the moment of creation of the topic itself.
We add a partitioning key (topic date) to all tables: topic, participant.
But now we have two immediate problems:
- in which section to search for messages by topic?
- in which section to find a topic from a message?
Of course, we can continue to search all sections, but that would be very unfortunate and would negate all our gains. Therefore, to know where exactly to search, we will make logical links/references to sections:
- we will add in the message a field with the topic date.
- to the topic we will add a set of message dates for this correspondence (it can be a separate table or an array of dates).
Since the modifications of the list of message dates for each individual correspondence will be few (after all, almost all messages fall into 1-2 neighboring days), I will stick exactly to this option.
In summary, the structure of our database has taken the following form considering the partitioning:
Tables : RU, if you have an aversion to Cyrillic in table/field names, it is better not to look.
-- sections by message date
CREATE TABLE "Message_YYYYMMDD"(
"Message"
uuid
PRIMARY KEY
, "Subject"
uuid
, "SubjectDate"
date
, "Author"
uuid
, "DateTime" -- used as date
timestamp
, "Text"
text
);
CREATE TABLE "Recipient_YYYYMMDD"(
"MessageDate"
date
, "Message"
uuid
, "Person"
uuid
, PRIMARY KEY("Message", "Person")
);
CREATE TABLE "File_YYYYMMDD"(
"MessageDate"
date
, "File"
uuid
PRIMARY KEY
, "Message"
uuid
, "BLOB"
uuid
, "Name"
text
);
CREATE TABLE "MessageRegistry_YYYYMMDD"(
"MessageDate"
date
, "Owner"
uuid
, "RegistryType"
smallint
, "DateTime"
timestamp
, "Message"
uuid
, PRIMARY KEY("Owner", "RegistryType", "Message")
);
CREATE INDEX ON "MessageRegistry_YYYYMMDD"("Owner", "RegistryType", "DateTime" DESC);
-- sections by subject date
CREATE TABLE "Subject_YYYYMMDD"(
"SubjectDate"
date
, "Subject"
uuid
PRIMARY KEY
, "Document"
uuid
, "Title"
text
);
CREATE TABLE "SubjectParticipant_YYYYMMDD"(
"SubjectDate"
date
, "Subject"
uuid
, "Person"
uuid
, PRIMARY KEY("Subject", "Person")
);
CREATE TABLE "SubjectMessageDates_YYYYMMDD"(
"SubjectDate"
date
, "Subject"
uuid
PRIMARY KEY
, "Date"
date
);
Saving a penny
Well, if we do not use a based on the distribution of the field values (through triggers and inheritance or PARTITION BY), but instead "manually" at the application level, it's noticeable that the partitioning key is already stored in the table name itself.
Therefore, if you are so concerned about the volume of stored data, you can eliminate these "extra" fields and directly reference specific tables. However, all queries from multiple sections will then have to be handled by the application.
Source: habr.com
