Databases & Data mysql postgresql databases

MySQL vs PostgreSQL on a Million-Row SMS Delivery Table

MySQL vs PostgreSQL. Each query covers the same one million messages
MySQL vs PostgreSQL. Each query covers the same one million messages

How long does it take to count the busiest recipients in a month of SMS traffic? This benchmark runs eleven aggregation queries against the same two million messages in MySQL and PostgreSQL. Each pair starts with the same date filter. What changes is the number of groups the database must form, whether it must remove duplicate phones, and how it finds the first three results.

Notes

Each query ran once to warm the engine, then three times on the same connection. Times are median client elapsed times, including the fetch. The SQL transcripts show the rows returned by the timed queries. Execution plans came from separate runs after timing. All eleven result sets matched between engines.

The comparisons use exact counts. Queries that preview three groups sort by their keys. Ranked queries sort by count and then by the same keys to resolve ties. Those details matter here because many groups have identical counts. The ordered previews can require more work than the unordered LIMIT queries in the original ClickHouse vs PostgreSQL benchmark.

Benchmark Environment

MySQL · 2,000,000 rows, including 1,000,000 in the measured date range
PostgreSQL · The same 2,000,000 rows, loaded from the same TSV

The data covers 60 days beginning September 5, 2025. A deterministic generator selects from 1,000 accounts, 20 gateways, 100 senders, and two million possible phones. Both tables have one B-tree beginning with creation time and containing every column used by the queries. A 30-day filter selects exactly half the table. Download the generator, benchmark script, and raw execution plans.

  • CPU · AMD EPYC 9V74 host, with a quota equivalent to 8 CPU cores.
  • RAM · 8 GiB container limit, shared with the operating system.
  • Storage · Container overlay filesystem. Disk model and host cache state were unavailable.
  • OS · Ubuntu 24.04.3 LTS, Linux 6.18.57, x86-64.
  • Deployment · Local single-node servers, one benchmark client, with the engines timed in separate runs. PostgreSQL used a user-identity compatibility shim in this environment.
  • MySQL · 8.4.7, InnoDB. Buffer pool 128 MiB. Per-table internal temporary-table limit 16 MiB.
  • PostgreSQL · 16.2. Shared buffers 128 MiB, work_mem 4 MiB, and up to two parallel workers per Gather. The table was vacuumed and analyzed after loading. MySQL statistics were refreshed with ANALYZE TABLE.

MySQL

Table schema

mysql> SHOW CREATE TABLE outgoing_sms;

CREATE TABLE `outgoing_sms` (
  `account_id` int NOT NULL,
  `gateway` varchar(8) COLLATE ascii_bin NOT NULL,
  `phone` bigint NOT NULL,
  `created_at` datetime NOT NULL,
  `sender` varchar(8) COLLATE ascii_bin NOT NULL,
  KEY `outgoing_sms_time` (`created_at`,`phone`,`account_id`,`sender`,`gateway`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin;

Table row count

mysql> SELECT COUNT(*) AS total_rows FROM outgoing_sms;

+------------+
| total_rows |
+------------+
|    2000000 |
+------------+

Table and index space used

mysql> SELECT data_length AS table_bytes, index_length AS index_bytes,
       data_length + index_length AS total_bytes
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_name = 'outgoing_sms';

+-------------+-------------+-------------+
| table_bytes | index_bytes | total_bytes |
+-------------+-------------+-------------+
|   107610112 |    84623360 |   192233472 |
+-------------+-------------+-------------+

Sample rows in creation order

mysql> SELECT account_id, gateway, phone, created_at, sender
FROM outgoing_sms ORDER BY created_at LIMIT 3;

+------------+---------+------------+---------------------+--------+
| account_id | gateway | phone      | created_at          | sender |
+------------+---------+------------+---------------------+--------+
|        850 | g03     | 1001088252 | 2025-09-05 00:00:00 | s45    |
|        730 | g04     | 1000875344 | 2025-09-05 00:00:02 | s44    |
|         13 | g08     | 1001822960 | 2025-09-05 00:00:05 | s61    |
+------------+---------+------------+---------------------+--------+

PostgreSQL

Table schema

CREATE TABLE outgoing_sms (
  account_id INTEGER NOT NULL,
  gateway VARCHAR(8) COLLATE "C" NOT NULL,
  phone BIGINT NOT NULL,
  created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
  sender VARCHAR(8) COLLATE "C" NOT NULL
);

CREATE INDEX outgoing_sms_time ON outgoing_sms (created_at, phone, account_id, sender, gateway);

Table row count

sms_bench=> SELECT COUNT(*) AS total_rows FROM outgoing_sms;

total_rows
----------
   2000000
(1 row)

Table and index space used

sms_bench=> SELECT pg_table_size('outgoing_sms') AS table_bytes,
       pg_indexes_size('outgoing_sms') AS index_bytes,
       pg_total_relation_size('outgoing_sms') AS total_bytes;

table_bytes | index_bytes | total_bytes
------------+-------------+------------
  120643584 |    99074048 |   219717632
(1 row)

Sample rows in creation order

sms_bench=> SELECT account_id, gateway, phone, created_at, sender
FROM outgoing_sms ORDER BY created_at LIMIT 3;

account_id | gateway | phone      | created_at          | sender
-----------+---------+------------+---------------------+-------
       850 | g03     | 1001088252 | 2025-09-05 00:00:00 | s45   
       730 | g04     | 1000875344 | 2025-09-05 00:00:02 | s44   
        13 | g08     | 1001822960 | 2025-09-05 00:00:05 | s61   
(3 rows)

Benchmark Queries

1. COUNT + GROUP BY phone

MySQL

mysql> SELECT phone, COUNT(*) AS n
FROM outgoing_sms
WHERE created_at >= '2025-09-05 00:00:00'
  AND created_at <  '2025-10-05 00:00:00'
GROUP BY phone
ORDER BY phone
LIMIT 3;

+------------+---+
| phone      | n |
+------------+---+
| 1000000003 | 2 |
| 1000000007 | 1 |
| 1000000013 | 2 |
+------------+---+

Median time: 2.883 s

PostgreSQL

sms_bench=> SELECT phone, COUNT(*) AS n
FROM outgoing_sms
WHERE created_at >= '2025-09-05 00:00:00'
  AND created_at <  '2025-10-05 00:00:00'
GROUP BY phone
ORDER BY phone
LIMIT 3;

phone      | n
-----------+--
1000000003 | 2
1000000007 | 1
1000000013 | 2
(3 rows)

Median time: 0.092 s

Results

Engine Median time Result Execution plan
MySQL 2.883 s First 3 phones by number Covering range scan, temporary aggregation, then sort by phone
PostgreSQL 0.092 s First 3 phones by number Two parallel workers, sorted partial groups, then Gather Merge

Summary. The first three phones received two, one, and two messages. MySQL counted all 786,934 phone groups in a temporary table before selecting those three. PostgreSQL scanned and sorted the qualifying input in parallel. Once the sorted streams were ready, its final aggregate could stop after the third phone. The 92 ms median still includes the scan and sort.

2. COUNT + GROUP BY phone (top-N)

MySQL

mysql> SELECT phone, COUNT(*) AS n
FROM outgoing_sms
WHERE created_at >= '2025-09-05 00:00:00'
  AND created_at <  '2025-10-05 00:00:00'
GROUP BY phone
ORDER BY n DESC, phone
LIMIT 3;

+------------+---+
| phone      | n |
+------------+---+
| 1000755183 | 7 |
| 1000046038 | 6 |
| 1000106281 | 6 |
+------------+---+

Median time: 2.919 s

PostgreSQL

sms_bench=> SELECT phone, COUNT(*) AS n
FROM outgoing_sms
WHERE created_at >= '2025-09-05 00:00:00'
  AND created_at <  '2025-10-05 00:00:00'
GROUP BY phone
ORDER BY n DESC, phone
LIMIT 3;

phone      | n
-----------+--
1000755183 | 7
1000046038 | 6
1000106281 | 6
(3 rows)

Median time: 0.455 s

Results

Engine Median time Result Execution plan
MySQL 2.919 s Highest counts of 7, 6, and 6 Same temporary aggregation as query 1, then sort by count
PostgreSQL 0.455 s Highest counts of 7, 6, and 6 HashAggregate in 17 batches, then top-N heapsort

Summary. Phone 1000755183 ranked first with seven messages. Ranking moved PostgreSQL from 0.092 s to 0.455 s because every phone’s count now had to compete for a place. Its hash aggregate produced 786,934 rows and reported 31,272 kB of disk use. MySQL had already built the full group table in query 1, so changing the order barely moved its median.

3. COUNT DISTINCT phone

MySQL

mysql> SELECT COUNT(DISTINCT phone) AS n
FROM outgoing_sms
WHERE created_at >= '2025-09-05 00:00:00'
  AND created_at <  '2025-10-05 00:00:00';

+--------+
| n      |
+--------+
| 786934 |
+--------+

Median time: 0.862 s

PostgreSQL

sms_bench=> SELECT COUNT(DISTINCT phone) AS n
FROM outgoing_sms
WHERE created_at >= '2025-09-05 00:00:00'
  AND created_at <  '2025-10-05 00:00:00';

n     
------
786934
(1 row)

Median time: 0.288 s

Results

Engine Median time Result Execution plan
MySQL 0.862 s 786,934 distinct phones Range scan over the covering index, then exact distinct count
PostgreSQL 0.288 s 786,934 distinct phones Sequential scan, external merge sort on phone, then aggregate

Summary. One million messages reached 786,934 recipients. The remaining 213,066 messages are sends beyond the first for a phone in this window. MySQL could read the phone numbers directly from the date index. PostgreSQL scanned the table and sorted the qualifying numbers, using 11,768 kB of disk for the sort. Both returned the same exact count.

4. COUNT + GROUP BY account_id, phone

MySQL

mysql> SELECT account_id, phone, COUNT(*) AS n
FROM outgoing_sms
WHERE created_at >= '2025-09-05 00:00:00'
  AND created_at <  '2025-10-05 00:00:00'
GROUP BY account_id, phone
ORDER BY account_id, phone
LIMIT 3;

+------------+------------+---+
| account_id | phone      | n |
+------------+------------+---+
|          0 | 1000001187 | 1 |
|          0 | 1000002204 | 1 |
|          0 | 1000004105 | 1 |
+------------+------------+---+

Median time: 3.214 s

PostgreSQL

sms_bench=> SELECT account_id, phone, COUNT(*) AS n
FROM outgoing_sms
WHERE created_at >= '2025-09-05 00:00:00'
  AND created_at <  '2025-10-05 00:00:00'
GROUP BY account_id, phone
ORDER BY account_id, phone
LIMIT 3;

account_id | phone      | n
-----------+------------+--
         0 | 1000001187 | 1
         0 | 1000002204 | 1
         0 | 1000004105 | 1
(3 rows)

Median time: 0.129 s

Results

Engine Median time Result Execution plan
MySQL 3.214 s First 3 account–phone pairs Temporary aggregation on two keys, then sort by the pair
PostgreSQL 0.129 s First 3 account–phone pairs Parallel scan and sort, followed by a sorted group merge

Summary. Adding the account ID increases the number of distinct groups from 786,934 phones to 999,741 account–phone pairs. MySQL built almost one group for every qualifying message. PostgreSQL sorted the pairs in parallel and stopped its final aggregation after the first three pairs for account 0. An account-level report can be much more expensive than its three-row output suggests.

5. COUNT + GROUP BY account_id, phone (top-N)

MySQL

mysql> SELECT account_id, phone, COUNT(*) AS n
FROM outgoing_sms
WHERE created_at >= '2025-09-05 00:00:00'
  AND created_at <  '2025-10-05 00:00:00'
GROUP BY account_id, phone
ORDER BY n DESC, account_id, phone
LIMIT 3;

+------------+------------+---+
| account_id | phone      | n |
+------------+------------+---+
|         10 | 1001417884 | 2 |
|         22 | 1001546798 | 2 |
|         23 | 1001300547 | 2 |
+------------+------------+---+

Median time: 3.135 s

PostgreSQL

sms_bench=> SELECT account_id, phone, COUNT(*) AS n
FROM outgoing_sms
WHERE created_at >= '2025-09-05 00:00:00'
  AND created_at <  '2025-10-05 00:00:00'
GROUP BY account_id, phone
ORDER BY n DESC, account_id, phone
LIMIT 3;

account_id | phone      | n
-----------+------------+--
        10 | 1001417884 | 2
        22 | 1001546798 | 2
        23 | 1001300547 | 2
(3 rows)

Median time: 0.527 s

Results

Engine Median time Result Execution plan
MySQL 3.135 s Top 3 pairs each have 2 messages Temporary table for all pairs, followed by the ranking sort
PostgreSQL 0.527 s Top 3 pairs each have 2 messages 999,741 hash groups and a top-N heapsort

Summary. The most frequent account–phone pair occurs just twice. With so many tied counts, ordering by account and phone makes the three displayed pairs reproducible. PostgreSQL’s plan recorded 47,136 kB of disk use for the hash aggregate and just 25 kB of memory for the final top-three sort. The expensive step was establishing the counts.

6. COUNT DISTINCT phone + GROUP BY account_id

MySQL

mysql> SELECT account_id, COUNT(DISTINCT phone) AS n
FROM outgoing_sms
WHERE created_at >= '2025-09-05 00:00:00'
  AND created_at <  '2025-10-05 00:00:00'
GROUP BY account_id
ORDER BY account_id
LIMIT 3;

+------------+------+
| account_id | n    |
+------------+------+
|          0 | 1008 |
|          1 | 1011 |
|          2 |  996 |
+------------+------+

Median time: 0.550 s

PostgreSQL

sms_bench=> SELECT account_id, COUNT(DISTINCT phone) AS n
FROM outgoing_sms
WHERE created_at >= '2025-09-05 00:00:00'
  AND created_at <  '2025-10-05 00:00:00'
GROUP BY account_id
ORDER BY account_id
LIMIT 3;

account_id | n   
-----------+-----
         0 | 1008
         1 | 1011
         2 |  996
(3 rows)

Median time: 0.127 s

Results

Engine Median time Result Execution plan
MySQL 0.550 s 1,008, 1,011, and 996 recipients Sort by account ID, then count distinct phones within each group
PostgreSQL 0.127 s 1,008, 1,011, and 996 recipients Parallel scan and sort, then GroupAggregate above Gather Merge

Summary. This time each output row represents an account. Account 0 reached 1,008 phones, account 1 reached 1,011, and account 2 reached 996. After sorting the qualifying messages, PostgreSQL’s merge handed only 3,016 rows to the grouped aggregate before the limit was satisfied. The initial scan and per-worker sorts still covered the full window.

7. COUNT DISTINCT phone + GROUP BY account_id (top-N)

MySQL

mysql> SELECT account_id, COUNT(DISTINCT phone) AS n
FROM outgoing_sms
WHERE created_at >= '2025-09-05 00:00:00'
  AND created_at <  '2025-10-05 00:00:00'
GROUP BY account_id
ORDER BY n DESC, account_id
LIMIT 3;

+------------+------+
| account_id | n    |
+------------+------+
|        555 | 1096 |
|        594 | 1090 |
|        505 | 1083 |
+------------+------+

Median time: 0.751 s

PostgreSQL

sms_bench=> SELECT account_id, COUNT(DISTINCT phone) AS n
FROM outgoing_sms
WHERE created_at >= '2025-09-05 00:00:00'
  AND created_at <  '2025-10-05 00:00:00'
GROUP BY account_id
ORDER BY n DESC, account_id
LIMIT 3;

account_id | n   
-----------+-----
       555 | 1096
       594 | 1090
       505 | 1083
(3 rows)

Median time: 0.523 s

Results

Engine Median time Result Execution plan
MySQL 0.751 s Account 555 leads with 1,096 Count distinct recipients for all accounts, then rank
PostgreSQL 0.523 s Account 555 leads with 1,096 External merge sort, 1,000 completed groups, then top-N

Summary. Account 555 reached 1,096 distinct recipients. To establish that lead, both engines had to finish all 1,000 account totals. PostgreSQL switched from the parallel ordered preview in query 6 to a serial scan and sort. Its median rose from 0.127 s to 0.523 s. MySQL moved from 0.550 s to 0.751 s.

8. COUNT + GROUP BY gateway, sender, phone

MySQL

mysql> SELECT gateway, sender, phone, COUNT(*) AS n
FROM outgoing_sms
WHERE created_at >= '2025-09-05 00:00:00'
  AND created_at <  '2025-10-05 00:00:00'
GROUP BY gateway, sender, phone
ORDER BY gateway, sender, phone
LIMIT 3;

+---------+--------+------------+---+
| gateway | sender | phone      | n |
+---------+--------+------------+---+
| g00     | s00    | 1000003645 | 1 |
| g00     | s00    | 1000005159 | 1 |
| g00     | s00    | 1000013682 | 1 |
+---------+--------+------------+---+

Median time: 5.443 s

PostgreSQL

sms_bench=> SELECT gateway, sender, phone, COUNT(*) AS n
FROM outgoing_sms
WHERE created_at >= '2025-09-05 00:00:00'
  AND created_at <  '2025-10-05 00:00:00'
GROUP BY gateway, sender, phone
ORDER BY gateway, sender, phone
LIMIT 3;

gateway | sender | phone      | n
--------+--------+------------+--
g00     | s00    | 1000003645 | 1
g00     | s00    | 1000005159 | 1
g00     | s00    | 1000013682 | 1
(3 rows)

Median time: 0.371 s

Results

Engine Median time Result Execution plan
MySQL 5.443 s First 3 gateway–sender–phone groups Temporary aggregation of 999,852 three-column keys
PostgreSQL 0.371 s First 3 gateway–sender–phone groups Parallel sorted partial groups and a final merge

Summary. A gateway and sender identify a delivery route. Add the recipient and the one million messages form 999,852 route–phone groups. That is only 111 more groups than the account–phone query, yet MySQL’s median rose from 3.214 s to 5.443 s. The temporary table now had to group on two text labels and a phone number.

9. COUNT + GROUP BY gateway, sender, phone (top-N)

MySQL

mysql> SELECT gateway, sender, phone, COUNT(*) AS n
FROM outgoing_sms
WHERE created_at >= '2025-09-05 00:00:00'
  AND created_at <  '2025-10-05 00:00:00'
GROUP BY gateway, sender, phone
ORDER BY n DESC, gateway, sender, phone
LIMIT 3;

+---------+--------+------------+---+
| gateway | sender | phone      | n |
+---------+--------+------------+---+
| g00     | s20    | 1001186696 | 2 |
| g00     | s47    | 1000809694 | 2 |
| g00     | s53    | 1000312106 | 2 |
+---------+--------+------------+---+

Median time: 5.459 s

PostgreSQL

sms_bench=> SELECT gateway, sender, phone, COUNT(*) AS n
FROM outgoing_sms
WHERE created_at >= '2025-09-05 00:00:00'
  AND created_at <  '2025-10-05 00:00:00'
GROUP BY gateway, sender, phone
ORDER BY n DESC, gateway, sender, phone
LIMIT 3;

gateway | sender | phone      | n
--------+--------+------------+--
g00     | s20    | 1001186696 | 2
g00     | s47    | 1000809694 | 2
g00     | s53    | 1000312106 | 2
(3 rows)

Median time: 0.573 s

Results

Engine Median time Result Execution plan
MySQL 5.459 s Top 3 groups each have 2 messages Aggregate into a temporary table, then rank 999,852 groups
PostgreSQL 0.573 s Top 3 groups each have 2 messages HashAggregate in 17 batches, then top-N heapsort

Summary. No route sent more than two messages to the same phone. MySQL took about as long to rank these groups as it did to display them in key order. PostgreSQL switched to hashing and finished in 0.573 s. Its plan reported 47,360 kB of disk use for aggregation, which matters if several such reports run together.

10. COUNT DISTINCT phone + GROUP BY gateway, sender

MySQL

mysql> SELECT gateway, sender, COUNT(DISTINCT phone) AS n
FROM outgoing_sms
WHERE created_at >= '2025-09-05 00:00:00'
  AND created_at <  '2025-10-05 00:00:00'
GROUP BY gateway, sender
ORDER BY gateway, sender
LIMIT 3;

+---------+--------+-----+
| gateway | sender | n   |
+---------+--------+-----+
| g00     | s00    | 467 |
| g00     | s01    | 518 |
| g00     | s02    | 532 |
+---------+--------+-----+

Median time: 0.720 s

PostgreSQL

sms_bench=> SELECT gateway, sender, COUNT(DISTINCT phone) AS n
FROM outgoing_sms
WHERE created_at >= '2025-09-05 00:00:00'
  AND created_at <  '2025-10-05 00:00:00'
GROUP BY gateway, sender
ORDER BY gateway, sender
LIMIT 3;

gateway | sender | n  
--------+--------+----
g00     | s00    | 467
g00     | s01    | 518
g00     | s02    | 532
(3 rows)

Median time: 0.382 s

Results

Engine Median time Result Execution plan
MySQL 0.720 s 467, 518, and 532 recipients Covering range scan, route sort, then grouped distinct count
PostgreSQL 0.382 s 467, 518, and 532 recipients Parallel scan and sort on gateway, sender, and phone

Summary. Moving phone from GROUP BY into COUNT(DISTINCT) reduces the possible output from 999,852 route–phone groups to 2,000 routes. The first three routes on gateway g00 reached 467, 518, and 532 phones. PostgreSQL’s GroupAggregate stopped after three routes. Its merge supplied 1,518 sorted input rows to that node.

11. COUNT DISTINCT phone + GROUP BY gateway, sender (top-N)

MySQL

mysql> SELECT gateway, sender, COUNT(DISTINCT phone) AS n
FROM outgoing_sms
WHERE created_at >= '2025-09-05 00:00:00'
  AND created_at <  '2025-10-05 00:00:00'
GROUP BY gateway, sender
ORDER BY n DESC, gateway, sender
LIMIT 3;

+---------+--------+-----+
| gateway | sender | n   |
+---------+--------+-----+
| g04     | s16    | 577 |
| g14     | s20    | 565 |
| g06     | s92    | 564 |
+---------+--------+-----+

Median time: 0.921 s

PostgreSQL

sms_bench=> SELECT gateway, sender, COUNT(DISTINCT phone) AS n
FROM outgoing_sms
WHERE created_at >= '2025-09-05 00:00:00'
  AND created_at <  '2025-10-05 00:00:00'
GROUP BY gateway, sender
ORDER BY n DESC, gateway, sender
LIMIT 3;

gateway | sender | n  
--------+--------+----
g04     | s16    | 577
g14     | s20    | 565
g06     | s92    | 564
(3 rows)

Median time: 1.444 s

Results

Engine Median time Result Execution plan
MySQL 0.921 s 577, 565, and 564 recipients Sort by route, finish 2,000 grouped counts, then rank
PostgreSQL 1.444 s 577, 565, and 564 recipients Serial scan and external sort, then aggregate and rank all routes

Summary. Gateway g04 and sender s16 had the greatest reach with 577 phones. MySQL finished in 0.921 s against PostgreSQL’s 1.444 s. PostgreSQL’s two-worker preview plan gave way to a serial sort of one million rows that used 25,488 kB of disk. The final top-three sort used only 25 kB of memory.

Conclusions

PostgreSQL had the lower median on ten of the eleven queries. Its largest time savings came when the grouping key produced nearly one group per message. For the ranked gateway–sender–phone report, PostgreSQL took 0.573 s and MySQL took 5.459 s. Both returned the same three rows.

MySQL was faster when ranking 2,000 routes by distinct recipients. The contrast with query 10 is useful for a reporting API. Showing the first three routes took PostgreSQL 0.382 s. Asking for the three routes with the greatest reach changed its plan and raised the time to 1.444 s. The SQL changed by one sort key. The amount of aggregation needed before the first result changed with it.

On disk, the table and index accounted for 183.33 MiB in InnoDB’s allocation statistics and 209.54 MiB in PostgreSQL’s relation sizes. Those counters use different accounting rules. This test covers one read-only client, a date filter matching half the table, and uniformly generated messages. For a production SMS dashboard, rerun the busiest report with the actual date range and the busiest tenant’s phone distribution. The plans here show exactly which scan, sort, or temporary aggregate to compare.

Comments (0)

Related articles