Does a long string with "unique" constaint take a lot of space?

Question

I've created a table "global_settings" for storing different settings of a web applications -- the ones, that I want to be able to edit on html page:

global_settings:
  id primary key,
  key varchar(100),
  value varchar(1000),
  extra_data jsonb

wherein key has been given UNIQUE constaint.

There'll be, let's say, 100 settings at most.

However, as I've already realized, key, at times, may reach 100 characters or so. It's because I want to make it explictly clear.

How much of additional disk space do these kinds of UNIQUE and long keys-indexes of VARCHAR add into a DB, on average? The size of int64 is 8 bytes, whereas this key will be ... over 10 times bigger. Hence, over 10 times as much of a disk space. Correct?

If I were to have thousands of these, it'd have made the size of the DB 10 times bigger compared to a situation when there was a key of int64?

Answer 1

A varchar value of 100 ASCII letter occupies 101 bytes on disk. An int8 (that's the internal name of bigint in Postgres, not int64) occupies 8 bytes.

So yes, over 12 times as big. Or even bigger with non-ASCII letters that occupy multiple bytes. But that hardly matters at all for only 100 rows. Even the existence of an index barely matters for such a small table.

For thousands of rows (or millions), yes, this size difference matters. For the table. But much more so for the index. Your rows are wide, so 100 bytes are only a few percent of the total row size. But the index grows to 10 times the size, which is bad for performance.

• Indexes: integer vs string performance if the number of nodes is the same
• What is the overhead for varchar(n)?

If you need keys that long, consider indexing a hash produced with md5() or hashtextextended(). See:

• Unable to use index in specific situations
• What is the optimal data type for an MD5 field?
• Would index lookup be noticeably faster with char vs varchar when all values are 36 chars