I have a large DB (6TB) consisting of 3 tables: “messages”, “messagesDetails” , “unprocessedMessages”.
Here are simple descriptions of them:
TABLE [messages](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[messageTimestamp] [datetime] NOT NULL,
[messageBody] [varbinary](max) NULL,
CONSTRAINT [PK_messages] PRIMARY KEY CLUSTERED ([id] ASC)
TABLE [messagesDetails](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[messageId] [bigint] NOT NULL
CONSTRAINT [PK_messagesDetails] PRIMARY KEY CLUSTERED ([id] ASC)
TABLE [messagesDetails] WITH NOCHECK ADD CONSTRAINT [FK_messagesDetails_messages] FOREIGN KEY([messageId])
REFERENCES [messages] ([id])
TABLE [unprocessedMessage](
[messageId] [bigint] NOT NULL
)
CONSTRAINT [IX_unprocessedMessage] NON CLUSTERED INDEX ([messageId] ASC)
TABLE [unprocessedMessage] WITH NOCHECK ADD CONSTRAINT [FK_unprocessedMessage_messages] FOREIGN KEY([messageId])
REFERENCES [messages] ([id])
I have 3 processes that run over those tables
Inserts into messages and unprocessedMessages tables - thousands per second
Processing messages – inserts into messagesDetails and deletes from unprocessedMessages
Table rolling deletes from messages and messagesDetails –runs once per day, based on message. messageTimestamp column with 28 days (4 weeks) retention
The problem is with this table rolling deletes. It must run in delete batches of less than 5000 rows per operation in order to NOT to lock the entire message table. This lasts awfully long time – about hours.
The solution that I have in mind is:
Create 53 partitions – same as the number of weeks in the year,
Create computed column on message.messageTimestamp to calculate week number and partition “messages” table based on this column.
Truncate table based with calculated partition holding date i.e. 4 weeks from the current week
My questions are:
Is that a good idea?
Is this doable on excising table or will I have to recreate the whole table from scratch?
Will I need more space (ex. double of current space) to move data on partitions.
What about the second table “messagesDetails”.
a. Will Foreign Key on “messagesDetails” affect somehow this partitioning?
b. How to partition “messagesDetails” to improve deleting from it?
ORDER BY id
gives the same results asORDER BY messageTimestamp
)? IsmessageTimestamp
indexed?messageTimestamp
is also unique, otherwise there can be some nondeterministic results with sorting. But probably irrelevant to the point you were getting at, just throwing it out there as caution for anyone who assumes otherwise for other contexts.