Which postgres data type should I use for large XML message come from network?

Question

I have incoming xml which is potentially as big as 5M and I need to store it it with postgres 9.1. Which data type should I use ?

bytea 
character varying
text

or something else ?

BTW The xml itself contains some binary data in base64 format, does that make any difference when choosing data type in postgres ?

Thank

Answer 1

You have two options:

1. VARCHAR or TEXT. It will allow you to store and retrieve your XML file from DB. But nothing more.
2. XML . Will allow you to store, retrieve, validate, edit, search ... the XML files, but may (or may not) involve some overhead on storing files to DB.

There is no reason to store XML files as BYTEA at all. 5 MB size is nothing special for Postgres. In my last project I worked strings with up to 0.5 GB length in Postgres.