MySQL vs PostgreSQL for hash-based table partitioning

Question

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.

Answer 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.

Answer 2

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