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 2 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! –  Cincinnatus 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/… –  Cincinnatus Jun 27 at 1:45
    
Erwin Brandstetter, please post a base64 encoded ed25519 public key, and please do not forget the signing key. –  Cincinnatus Jun 27 at 13:08

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.