database column size (BASE64 encoded document > 1GB)

[JoeFox82]

Hi together,

we're using PostgreSQL 15 for all of our mirth servers. We ran into the problem that it is not possible to send MDM messages that are bigger than 1 GB to the mirth server (TCP, MLLP, document is base64 encoded, embedded). We're using the attachment handler (regex). As far as I know the maxiumum filed size of postgres is 1GB so that is possible that this may be the problem. MySQL can have 4GB columns (LONGTEXT or LONGBLOB).
Does anyone else has this problem? Is it better to use MySQL or an other database to handle this problem?

Kind regards

[pacmano1]

It is a column type of bytea in postgres so effectively around a 1G limit. That is the largest column type for postgres.

Who/why are you getting documents of that size? It seems like the wrong protocol (HL7) and tool (mirth with any database since it is typically considered bad practice to store large blobs in DBs) to deliver large content items. Curious. You would typically transfer and store the data via blob services like AWS S3. you might get an HL7 message with a pointer to the location of the large file to download or retrieve via whatever mechanism the blob storage supports.

[JoeFox82]

The sending system sometimes creates documents with about 4,000 pages. We can't customize the sending system to filter this documents. The main problem is that we have to use the production mode for message storage (query). I think when we use the metadata mode there will be no problem because the documents will not be saved in the database. Sending the messages with a pointer is not a valid method we can use.
My idea was to change the database to MySQL for this customer but I don't know if that will solve the prolbem :-)

[pacmano1]

Is there something preventing you from trying this yourself? on a test machine? And sending a message to it and seeing if it works?

[JoeFox82]

No, it isn't. I'll try it next week. I was hoping that someone here in the forum would have an idea or that this problem has already occurred somewhere else.
I'll give you a feedback