full text search on hstore or json with materialized view?

From: Rj Ewing <ewing(dot)rj(at)gmail(dot)com>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: full text search on hstore or json with materialized view?
Date: 2017-04-18 21:38:15
Message-ID: CAOSSsV0YYuE2ToYNz1C9X1h5xkN4v5NmgKQhP0UnL2gQ5OhsCw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am evaluating postgres for as a datastore for our webapp. We are moving
away from a triple store db due to performance issues.

Our data model consists of sets of user defined attributes. Approx 10% of
the attributes tend to be 100% filled with 50% of the attributes having
approx 25% filled. This is fairly sparse data, and it seems that jsonb or
hstore will be best for us.

Unfortunately, from my understanding, postres doesn't support fulltext
search across hstore or jsonb key:values or even the entire document. While
this is not a deal breaker, this would be a great feature to have. We have
been experimenting w/ elasticsearch a bit, and particularly enjoy this
feature, however we don't really want to involve the complexity and
overhead of adding elasticsearch in front of our datasource right now.

An idea that has come up is to use a materialized view or secondary table
with triggers, where we would have 3 columns (id, key, value).

I think this would allow us to store a tsvector and gin index. Giving us
the ability to use fulltext search on k:v pairs, then join the original
data on the id field to return the entire record.

is anyone currently doing this? Is there a better alternative? Any
performance issues that immediately jump out ( I realize the writes will
take longer)?

the nature of our data is "relatively" static with bulk uploads (100 - 1000
records). So we can sacrifice some write performance.

RJ

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2017-04-18 22:00:53 Re: full text search on hstore or json with materialized view?
Previous Message Tom Lane 2017-04-18 20:06:18 Re: tuple statistics update