MonPG Engineering Engineering Team August 22, 2026 6 min read

The 1024-Byte Trap: MySQL GROUP_CONCAT vs PostgreSQL string_agg

The weekly export concatenated each account's tags with GROUP_CONCAT, and for eleven months nobody noticed that MySQL was silently cutting the lists off at 1024 bytes — a warning, not an error, buried in a log nobody read. Field notes on the truncation trap, the semantic differences, and the JSON aggregate that replaced both.

The export ran every Sunday night: one row per account, with a tags column built by GROUP_CONCAT(t.name ORDER BY t.name) so the customer-success team could filter in a spreadsheet. It ran for eleven months without a complaint. Then an enterprise account asked why half their tags were missing from the report, and the answer was in a MySQL warning that had fired 48,000 times: GROUP_CONCAT truncates its result at group_concat_max_len bytes, the default is 1024, truncation is a warning rather than an error, and the truncated string is returned to the client as if it were complete. Our biggest accounts — the ones with the most tags — had been receiving silently amputated data since the export existed. The cutoff even landed mid-tag, so the last visible tag was sometimes a misspelled fragment that looked like a data-entry bug.

When the same report was rebuilt on PostgreSQL during the migration, string_agg simply never truncated, which is its own kind of trap: the ported query was correct by accident, and nobody learned the underlying semantics until a code review asked why the MySQL version had a SET SESSION statement the PostgreSQL version did not need. String aggregation looks like the most trivial function family in either engine, and it hides real differences in limits, ordering guarantees, and type strictness. These are the notes from running both in production.

Why does MySQL truncate silently, and how do you make it stop?

Because group_concat_max_len is a session-variable ceiling with a four-decade-old default, and MySQL’s contract is that exceeding it truncates the result and raises warning 1260 — Row N was cut by GROUP_CONCAT() — which almost no application inspects. Even with STRICT_TRANS_TABLES enabled, truncation stays a warning; the strict modes that save you elsewhere, catalogued in the SQL mode versus strictness notes, do not convert this one into an error. The fix is mechanical but easy to get wrong: the variable is session-scoped, so setting it globally does not help existing pooled connections, and setting it in the application’s connection string fails on ORMs that strip unknown parameters. What worked for us was an explicit statement at the top of the export job:

-- MySQL: must be set in the SAME session that runs the query
SET SESSION group_concat_max_len = 1024 * 1024;

SELECT a.id,
       GROUP_CONCAT(t.name ORDER BY t.name SEPARATOR ', ') AS tags
FROM accounts a
JOIN account_tags at ON at.account_id = a.id
JOIN tags t ON t.id = at.tag_id
GROUP BY a.id;

-- PostgreSQL: no limit to configure; the ORDER BY lives inside the aggregate
SELECT a.id,
       string_agg(t.name, ', ' ORDER BY t.name) AS tags
FROM accounts a
JOIN account_tags at ON at.account_id = a.id
JOIN tags t ON t.id = at.tag_id
GROUP BY a.id;

We also added a guard to the export itself: if the maximum character_length of the tags column ever approached the configured ceiling, fail the job loudly. Silent truncation is only dangerous because it is silent; a length assertion turns it back into an ordinary error. On PostgreSQL the equivalent failure mode barely exists — string_agg will build a result up to the field size limit, which at a gigabyte is functionally unreachable for a tags column — but a truly unbounded aggregate is still a memory decision, and we have seen a reporting query concatenate its way into work_mem spill on both engines.

What actually differs beyond the length limit?

Three things, each of which has produced a bug for us. First, ordering: both engines support ORDER BY inside the aggregate, but on MySQL the ordering is part of GROUP_CONCAT’s own syntax and on PostgreSQL it is the standard aggregate ORDER BY clause — which means on PostgreSQL the same clause works on every aggregate, and on MySQL it works on GROUP_CONCAT alone. Second, DISTINCT: both accept it inside the aggregate, but MySQL’s GROUP_CONCAT(DISTINCT …) forbids ordering by expressions not in the distinct list, a restriction that surfaces only when someone tries to deduplicate and sort simultaneously — our port hit it in the third query we moved. Third, types: MySQL concatenates whatever it is given and returns a string, while string_agg requires text arguments, so numeric tags need t.code::text or the query fails at parse time. The strictness is annoying for an afternoon and then prevents the implicit-conversion surprises MySQL permits, like a numeric id being concatenated with a locale-dependent format.

NULL handling at least agrees: both skip NULL inputs, and both return NULL for a group with no rows, so outer-join groups need a COALESCE if the report wants an empty string rather than a blank cell. Performance-wise the two are comparable on indexed joins; the aggregation itself is never the bottleneck, the join feeding it is.

Why did the JSON aggregate replace both?

Because a comma-separated string is a data structure pretending not to be one, and the first time a tag contained a comma, the export’s downstream parser split it into two phantom tags and the spreadsheet filters went wrong for a different reason. Both engines offer structured alternatives that solve the truncation, escaping, and parsing problems at once: MySQL’s JSON_ARRAYAGG(t.name) produces a real JSON array, and PostgreSQL’s jsonb_agg(t.name ORDER BY t.name) does the same with full ordering support. The wider differences between the two JSON implementations — path syntax, indexing, containment operators — are their own subject, covered in the JSON versus JSONB comparison. Our rule after the incident: string aggregates are for human-readable display in exactly one query’s output; anything consumed by another system gets a JSON aggregate and a schema the consumer can validate. The Sunday export now ships a JSON column, and the spreadsheet macro parses it instead of splitting on commas.

Where MonPG fits when an aggregate goes wrong

Truncation never appears in a monitoring dashboard — the query was fast, the warning went nowhere, the data looked plausible. What monitoring would have caught is the second-order symptom: the export query’s returned-bytes-per-row flatlining at the 1024 ceiling as accounts grew, a distribution change that only per-statement statistics over time make visible. MonPG’s PostgreSQL monitoring tracks per-statement runtime and row statistics with history, which is the shape of evidence that turns “the report looks wrong” into “the report has been wrong since March”.

MonPG monitors PostgreSQL today; MySQL support is on the roadmap. When it lands, the MySQL side of this article is what it will watch: warnings-per-statement surfaced next to the statements that raise them, so a GROUP_CONCAT truncation stops being invisible. Until then, the MySQL monitoring page tracks that work. And the portable rule costs nothing: any aggregate that can be truncated must be either sized explicitly or asserted at the output, because a warning is not an error and eleven months is a long time to be wrong.