0

I have a text field in a PostgreSQL Database in which XML data is stored. Now I was wondering if it makes a difference to store the XML in minified or in pretty version.

According to http://bytesizematters.com the size of the text itself does differ:

Size comparison of minified vs pretty XML

Now I'm wondering if that will reflect on database size and performance. Maybe PostgreSQL is already optimizing text fields so much, that it actually doesn't matter.

Unexpectedly, I didn't find anything on that topic on the internet. So if you have some links on that, I'd be very happy to read and learn more about DB optimization.

Improve this question
4
  • Doesn't look like Postgres optimizes it dbfiddle.uk/d240yYAv SQL Server does though dbfiddle.uk/ya53s1HQ
    – Charlieface
    Aug 8 at 14:00
  • I don't think I'd call it optimization what SQL Server does, it is formatting, and actually adding whitespace. Also, your dbfiddle just shows the data, it says nothing about how the text is stored internally. thx anyway
    – bechtold
    Aug 9 at 14:27
  • @bechtold Charlieface's dbfiddle is showing the size of the data is the same in both cases (35 bytes), when there's extra whitespace (prettified) vs when it's been minified. I think his point was that some database systems like SQL Server automatically compress the table data. I don't believe PostgreSQL does so in the same way automatically (Page, Row, and Column compression algorithms), but I did see there are extensions that do offer such for PostgreSQL.
    – J.D.
    Aug 9 at 16:18
  • 1
    SQL Server adds the whitespace only for display. It's stored in a highly compressed format, the only reason it's larger than Postgres is because it uses UTF-16 which takes twice the space.
    – Charlieface
    Aug 10 at 8:04

1 Answer 1

Reset to default
1

Now I'm wondering if that will reflect on database size and performance.

That 20% difference in size is unlikely to affect the performance of your queries, unless your table grows to hundreds of millions of rows, and you're selecting the majority of the rows at the same time.

If optimizing the storage space is important to you, then there are extensions that apply different compression algorithms to the table for you.

Improve this answer

Your Answer

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

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