Storing YAML Text in a Database Column and Its Pitfalls

A configuration document with a few hundred bytes of YAML looks like it belongs in a text column. You paste it in, you read it back, and the application parses it when it needs it. Weeks later someone asks a question that a database is supposed to answer: which accounts use port 5432, which documents changed, which ones are valid. The text column cannot answer any of them well. This post shows the failures with a small table in PostgreSQL 18, and then the alternative. It sits in the cluster on using YAML with databases, and the way out of the column is the subject of migrating YAML data files to a database.

The setup

Three documents say the same thing about a server in different ways: one in block style, one with a comment and flow style, one with the port quoted and padded with spaces:

a = "server:\n  port: 5432\n  host: db\n"
b = "# primary\nserver: {host: db, port: 5432}\n"
c = "server:\n  port:   '5432'\n  host: db\n"

Document c is not like the others. It writes the port as the string '5432', so it differs in type. The table holds each document twice: as the original text, and as jsonb produced by parsing the YAML on the way in.

create table cfg (id int primary key, body text, doc jsonb);

Text compares characters

The first question is how many different configurations there are:

same text: [(3,)]
distinct jsonb: [(2,)]

count(distinct body) says three, since the three strings differ. count(distinct doc) says two, because a and b parse to the same object: jsonb stores the content, with key order and whitespace normalized away. Comments are gone too, since they are not data. Documents a and b are the same configuration with different characters, and the text column cannot see it. For deduplication, change detection and "did this edit change anything", the text column gives false positives.

Searching text follows the formatting

The second question is which documents use port 5432. A LIKE on the text:

text LIKE: [(1,), (2,)]

It found a and b, and missed c. That is correct in one sense, since c stores a string, and wrong in another, since a person would say c also configures port 5432. A search for port:5432 without the space would miss a; port: 5432 misses a file with two spaces after the colon; and a flow-style document matches only when the spacing happens to agree. Text search over YAML is a search over typography.

With jsonb, the question is a path query:

jsonb port=5432: [(1,), (2,), (3,)]
jsonb types: [(1, 'number'), (2, 'number'), (3, 'string')]

The text comparison doc #>> '{server,port}' = '5432' matched all three, and jsonb_typeof showed that document 3 stores the port as a string. Comparing as an integer ((doc #>> '{server,port}')::int = 5432) also matched all three. Now the question has an answer, and the type difference that a person might not have noticed is visible. A GIN index over the jsonb column makes these lookups fast on large tables; see indexing JSONB with GIN for how.

The database accepts garbage

A text column validates nothing. We inserted a broken document:

garbage accepted as text; rows: 4

The insert succeeded. The only way to find the problem is to parse every row:

bad = []
for id_, body in rows:
    try:
        yaml.safe_load(body)
    except yaml.YAMLError as e:
        bad.append((id_, type(e).__name__))

rows that do not parse: [(4, 'ParserError')]

That works, in a batch job, long after the bad row was written and the user who wrote it has left. PostgreSQL has no built-in YAML type or validator, so you cannot add a CHECK constraint for syntax. You can add one for jsonb shape (check (jsonb_typeof(doc) = 'object') or jsonb_matches_schema from an extension), and the cleaner design is to make invalid data unrepresentable: parse at the application boundary, reject bad input with an error the user can read, and store the parsed result.

What else goes wrong

A few more problems show up over time.

Size and updates. An edit to one setting rewrites the whole text value, and concurrent edits to different keys conflict. With jsonb you can change one key in place (jsonb_set), and with separate columns you can update independently. See updating one key inside a JSONB value.

Line endings and encoding. A document pasted from Windows carries \r\n, and a copy through a form may add a trailing newline. Both change the text and nothing else. Diffs and checksums over text report changes that are not changes.

Type surprises are deferred. The Norway problem and its relatives live at parse time. With text in the column, the type of a value depends on which parser reads it, and on which day, so the same row can mean different things to two services. If you parse once at write time, the type is fixed in jsonb and every reader sees it. See the Norway problem.

Security. A column of YAML that some other component loads with the unsafe loader is a code-execution path. The rule in loading YAML in Python with PyYAML safe_load applies to data from the database as much as to data from a user.

Indexing and constraints. Constraints, foreign keys and uniqueness over a value inside the text are impossible. If port numbers must be unique per host, that rule needs the value in a column.

What to store instead

The decision depends on what the YAML is for.

The "both" row needs a rule: the jsonb column is derived, and the application writes both in one transaction, so they cannot disagree. A generated column cannot parse YAML, since PostgreSQL has no function for it, so the application does it. If the text is edited elsewhere, a trigger cannot help either; keep writes going through one code path.

Converting a column that already exists

If you inherited a text column, add a jsonb column and fill it in a script, reporting rows that fail to parse before you drop anything:

for id_, body in rows:
    try:
        doc = yaml.safe_load(body)
    except yaml.YAMLError:
        failures.append(id_)
        continue
    conn.execute("update cfg set doc = %s where id = %s", (Jsonb(doc), id_))

Review the failures by hand. They are the rows that were broken all along, and some of them will turn out to be in use by software that tolerates them.

When storing YAML text in a column is still reasonable

You never query inside it. If the value is an opaque payload that a service loads whole, such as a stored template, text is simple and honest. Add a size limit.

The exact bytes matter. For an audit trail of what a user submitted, store the original. Parsing is the lossy step.

The database is SQLite or a small embedded store, and the load is trivial. With few rows and no queries, the simplest thing is acceptable, and moving to JSON later is a migration you can run. SQLite has JSON functions, which makes the move easy (reading YAML into SQLite for quick queries).

Users author comments you must preserve. Keep the text for display, and a parsed copy for queries.

The PostgreSQL operators used above are described in the JSON functions and operators chapter, and the type itself in the JSON types documentation. Check a document by eye with the YAML formatter before it goes into the table.