Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a unique constraint on a character varying column that will mostly have lengths of approximately 600 but could get to 10 million.

I have read about B-Trees, but I can't determine how much disk space the index will consume or if such large-sized values will prevent the index from functioning performantly.

What are the impacts upon disk space and time of a B-Tree index on character varying columns with such potentially large lengths?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

In Postgres, a unique constraint is implemented with a unique B-tree index. Per documentation:

Adding a unique constraint will automatically create a unique btree index on the column or group of columns used in the constraint.

Indexes use the same basic storing mechanisms as tables: arrays of data pages. Indexes have some minor additional features. Per documentation:

In a table, an item is a row; in an index, an item is an index entry.

Every table and index is stored as an array of pages of a fixed size (usually 8 kB, although a different page size can be selected when compiling the server).

The maximum size of an index entry is a third of a data page, I quote Peter Eisentraut on this:
PostgreSQL primary key length limit

That's 2730 bytes (or a bit less). Meaning your UNIQUE constraint is not possible.

I would consider adding a redundant column with a hash value and create a unique index on that.
You could use the built-in function md5() or something more efficient for huge values.

Here is a related solution with a focus on performance:
Index max row size error

share|improve this answer
    
Thank you Erwin Brandstetter, incredible advice as usual! For performance, should I use bytea? If so, should I let Postgres do the conversions with md5 and decode or sha512() with this library zedwood.com/article/cpp-sha512-function? I'm fairly certain I'll never need to extract the hash, but I will need to compare with external data. Thank you so very much for sharing your great knowledge! –  user32234 Jun 27 at 1:04
    
@Gracchus: I suggest you put that in new questions (one topic per piece). Comments are not the place. You can always link to this one for reference. –  Erwin Brandstetter Jun 27 at 1:16
    
Thank you Erwin Brandstetter! I have posted per your recommendation: dba.stackexchange.com/questions/69170/… –  user32234 Jun 27 at 1:45
    
Erwin Brandstetter, please post a base64 encoded ed25519 public key, and please do not forget the signing key. –  user32234 Jun 27 at 13:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.