0

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?

Camila326
  • 35
  • 5

1 Answers1

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.

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

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600