0

Assume we have a system which includes a relational database (Say, MySQL or Oracle, for instance) which is populated by a scheduled job which consumes data from an external API. Also assume there are no guarantees for the size of data (e.g. size of a particular value of a json) coming from the API.

In this kind of a scenario, what is the best practice to determine a proper column size for a particular attribute populated using data coming from the API? Is it an apt way to dynamically (programmatically) adjust the column size based on incoming data, or do we assign a relatively larger size to accommodate incoming larger data?

Thank You.

Dasun Pubudumal
  • 97
  • 1
  • 8
  • 2
    Does the [JSON](https://dev.mysql.com/doc/refman/5.7/en/json.html) corresponding to `LONGTEXT` (~2^32) probably enough? (same in [MariaDB](https://mariadb.com/kb/en/json-data-type/)). So if this works, don't worry about autosizeing. You aren't saving/loosing data or storage that way. – danblack Mar 22 '21 at 04:56
  • The column of JSON type have no size/length spec. part. – Akina Mar 22 '21 at 05:28
  • The JSON is not persisted as it is. What's persisted are the values inside JSON in different columns. – Dasun Pubudumal Mar 22 '21 at 05:46

1 Answers1

2

[A]ssume there are no guarantees for the size of data (e.g. size of a particular value of a json) coming from the API.

In this kind of a scenario, what is the best practice to determine a proper column size for a particular attribute populated using data coming from the API?

In Oracle, you can use a CLOB data type.

Say, [...] Oracle, for instance

In Oracle, a VARCHAR2 column is limited to 4000 bytes in the SQL scope. If you have no guarantees about the size of the data then use a CLOB. In Oracle 19c a CLOB datatype has a limit of 4GB * DB_BLOCK_SIZE initialization parameter (which give a total size of 8 TB to 128 TB). If you are exceeding that limit then you should seriously consider re-working how you are consuming the data.

MT0
  • 143,790
  • 11
  • 59
  • 117