CODEWITHSUNDEEP
databasepostgresql
William Yeung
William Yeung

Reputation: 10566

What is the size limit for PostgreSQL 9.0+ hstore?

As title, I have been searching around for a while and not able to find an answer. It only states key and value can't be longer than 65535 when it's on 8.4, but not being mentioned at all on 9.0 documentation.

Upvotes: 6

Views: 4261

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324455

hstore is a varlena, and is limited by the maximum size of TOASTed fields, about 1GB.

I do not recommend that you go anywhere near the size. Performance will be awful. Every time you update a row - including rows with hstore fields - PostgreSQL must write a new copy of the row. Needless to say, with gigabyte rows that's not going to be fun.

Read performance will be OK if you're reading all the keys/values, but poor if you're selectively reading just a few keys/values, as the hstore must be de-TOASTed before access.

It's hard to give more specific advice without knowing your design and use case; the why of this question.

Upvotes: 15

Related Questions