1

I'm planning an open-source a backend service whose primary job is to deliver sync messages between users' devices. I want the self-hosted and hosted versions of the service to share the same architecture, so my DB choices are MySQL with InnoDB and PostgreSQL.

The primary load on the database will be on the messages table that stores sync messages between users' devices. It will support the following operations:

  • Insert a message for a user
  • Query messages for a user sorted by date
  • Delete delivered messages for a user

There will be no online queries on the messages table that involve multiple users.

I'm thinking that the simplest way to scale performance would be to create a hash partition on the user id in the messages table. I'm wondering if MySQL with InnoDB or PostgreSQL is better suited for this use case?

P.S.: There is a question with a similar title, but it was asked before Postgres supported table partitioning.

7
  • 2
    Very likely, partitioning will make this workload slower, no matter what database system you choose. Find better criteria to decide between the MySQL and PostgreSQL. Feb 7, 2023 at 10:13
  • 1
    Why do you think partitioning would make this faster?
    – user1822
    Feb 7, 2023 at 11:11
  • @a_horse_with_no_name I'm guessing the delete query will be the most expensive since it can span multiple messages randomly distributed across a date range (device comes online, fetches the messages received while it was offline) and I thought partitioning could help with this + having the option to split the table across disks if disk I/O becomes a problem sounds nice.
    – Agost Biro
    Feb 7, 2023 at 11:36
  • 2
    I cannot offer a technically founded opinion on MySQL, but it is more a matter of physics than of implementation that deleting only parts of a partition won't be faster that doing that in a non-partitioned table. Feb 7, 2023 at 11:40
  • 1
    Doesn't look like a good use case for a relational database. How many messages per user do you expect to store at any one time? What is the expected message size?
    – mustaccio
    Feb 7, 2023 at 13:06

2 Answers 2

1

I'm thinking that the simplest way to scale performance would be to create a hash partition on the user id in the messages table.

Why? Partitioning is a linear segregation of the data. B-Tree indexing is a logarithmic segregation of the data. Therefore indexes are essentially exponentially faster at searching. I would not worry about doing anything extracurricular for performance until you prove out you need to, stick to the script. Indexes are extremely quick to search even with quadrillions (literally, and more) of rows in them.

I'm wondering if MySQL with InnoDB or PostgreSQL is better suited for this use case?

There's going to not really be much of a difference performance-wise between these two database systems (or any modern database system). But I'd personally choose PostgreSQL because of it's abundance of features and extensions, and the size of the community of people using it.

I disagree with mustaccio's statement:

Doesn't look like a good use case for a relational database.

Based on your use cases which do all the filtering by UserId and sorting by Date, I think a relational database is just fine. If you needed to do extensive and complex searching within the Message content itself, in your database layer only, then something like ElasticSearch may be better. But you have not noted that to be the case.


Source: Worked with large amounts (10s of billions of rows, multi-terabytes) of Message data for financial trades, and used a relational database without partitioning. Most queries were sub-second to a few seconds max, depending on the goal of the query.

1

(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 BY HASH.

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 DELETEs them.

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.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.