(From a MySQL/InnoDB point of view.)
That sounds like a "FIFO" or "queue". There should be only a few messages in the table at a time, correct?
A Rule of Thumb is that Partitioning should not be used on a table with less than a million rows.
I agree with others that
PARTITIONing is likely to be slower, not faster. I have yet to find any application that would benefit from
Partitioning is useless.
The table, if you implement it as a table, probably should have
id BIGINT AUTO_INCREMENT NOT NULL,
PRIMARY KEY(user_id, datetime, id), -- most actions will benefit from this
INDEX(id) -- to keep AUTO_INCREMENT happy
Note that the clustering achieved by the above PK is likely to help a little with performance when a user suddenly syncs a batch of messages and
Your biggest scaling scare will be when part of the network goes down for a few days and there are lots of messages queued but not being flushed out. The table will grow to a big size. The issue is that
DELETEs do not give freed-up space back to the OS. So, provision a large enough disk so that you won't run out of disk space during a long outage.
The argument for "splitting the I/O" is backward. The typical machine [today] has a single, big, disk drive. That is, if anything, a bottleneck for the deletes -- all are competing for that one resource. Furthermore, the buffer_pool (a cache) is where every action must be cached. When the deletes are scattered around the disk, more I/O and cache space are needed. That is, it is [slightly] better to have the deletes clustered together. In MySQL [I don't know about Postgres], the PK controls the clustering of the data -- hence my explicit recommendation for the PK.
Another potential problem is trying to delete a million rows in a single statement. This may interfere with other activities. To prevent the interference, break it up into a thousand rows at a time.