0

I'm now working on the project that automates "contracts" creation and signing. Recently I received a requirements to implement search functionality for "contracts", so I struggle to find performant way for doing it (I'm kinda new to postgres so far).

Contract table looks like

CREATE TABLE contracts(
id UUID,
status TEXT,
name TEXT,
issuers JSOB,
approver JSONB,
period JSONB
created TIMESTAMP WITHOUT TIME ZONE
)

status - contract status, one of draft, signed, active, expired

issuers - json array of objects like {"id": , email: , name: , ...}

approver - json obj like {"id": , email: , name: , ...}

period - json obj like {"start": , "end": ...}

Search requirements:

  1. "prefix" search with user-specified value where any of contract name, contract issuer (any) email, contract issuer (any) name, approver name, approver email matches condition.
  2. Filtering by contract status using IN operator.
  3. Ordering by created timestamp, period start date, period end date
  4. Paging

As application is user-centric all queries will contain either issuer id, approver id or both as additional filter criteria.

Search examples:

  1. For issuer with id "1" display contracts matching predicate "test@gmail" order by created timestamp. Such search is supposed to return following contracts

    {"name": "test@gmail contract name e.g.", issuers:[{id: "1"...}...], ...}

    {"name": "Name", issuers: [{id: "1"...}, {... email: "[email protected]"...}...]}

    {"name": "Name1",issuers:[{id: "1"...}...], approver: {.."email": "[email protected]"..}}

  2. For approver with id "11" display contracts matching predicate "aggre" order by start date. Such search is supposed to return following contracts

    {"name": "aggreement 11232", approver:{"id": "11"...}, ...}

    {"name": "Name", issuers: [{"name: "Aggre Xyz"...},...], approver:["id": "11"...}}

  3. For issuer with id "1" and approver with id "11" display contracts matching predicate "aggre" order by start date. Such search is supposed to return following contracts

    {"name": "aggreement 11232", approver:{"id": "11"...}, issuers: [{id: "1"...} ...}

Considerations:

  1. With current db layout I have only gin index for the rescue, which will not help with the sorting as far as I know, apart from other complications it brings.
  2. Rebuilding the schema to more classical one to many contract-issuer relation + saving approver and period objects fields as columns. On the one hand it will let utilize btree indexes for at least issuer id/approver id filtering + will let perform efficient sorting. On the other hand finding matches by issuer name/email doesn't seem easy and would involve many joins + plus it's a huge change for the schema.
  3. Adding separate "search-optimized" table.

Two options

3.1

CREATE TABLE search(
    issuer_id UUID,
    approver_id UUID,
    attribute_name TEXT,
    attribute_value TEXT,
    contract_id
    )

attribute_name - contract-name, issuer_name...

With that approach search seems to be pretty easy, but amount of additional rows is a concern

3.2 Smth like this with attributes merged in array

CREATE TABLE search(
issuer_id UUID,
approver_id UUID,
search_attributes TEXT[],
contract_id
)

search attributes will contain all the strings that might match for "prefix" search. While it complicates the writes (one more db object to be updated...) it seems to be beneficial for reads.

SELECT *
FROM contracts
INNER JOIN (
   SELECT contract_id
   FROM search
   WHERE issuer_id = 'id'
   AND EXISTS(
    SELECT 1
    FROM unnest(search_attributes) attr,
    WHERE attr LIKE 'input%'
)

I would appreciate any suggestion.

3
  • "for the rescue"? Is this a typo? If literal, I don't understand what it means here.
    – jjanes
    Nov 20 at 16:28
  • "will contain either issuer id, approver id or both as additional filter criteria." Will those additional id criteria be exact? It would be odd to have a both a prefix search and an exact search on the same field in the same query.
    – jjanes
    Nov 20 at 16:37
  • Hi @jjanes "for the rescue" - I meant as I have json array the only index type available is gin index. "will contain either issuer id, approver id or both as additional filter criteria." - these are exact values - in fact they both are user ids. I've added examples for the search request I need to implement. Sorry for inconvenience, hope with examples it would be understandable.
    – EPe
    Nov 20 at 17:36

1 Answer 1

0

There is no perfect way to do this. You need to find something which is "good enough", and that will depend on how selective all of these different conditions are, both individually and in combination.

For the approver id, you could query with @> on a GIN index:

where approver @> '{"id":"123456"}'

And for issuer id you could do the same on a another GIN index, but syntax adapted for array:

where issuers @> '[{"id":"123456"}]'

If these are highly selective, then you are done. The few rows which meet the index condition can just be filtered one at a time for the other conditions, and then sorted. If they are not very selective, such that the number of rows returned is too many to efficiently filter, then you have many options, none of them all that good.

Because the approver column is not an array, you could instead use a btree index on that on contracts ((approver->>'id')) with the corresponding search condition

where approver->>'id' = '123456'

The nice thing about that is that you could then add another column in the index which could help with some other aspect, like sorting by one particular column.

If you really need more selectivity than these give you, you might need a function which would transform your jsonb into text[] which lists all the values you want a prefix search on, but then you would still need some kind of custom GIN class to implement that prefix searching. The only one I know of which does something like that is parray_gin, but that is not specialized for prefix but rather for any LIKE query so presumably it is not maximally efficient for prefix. Another option may be to use the partial match feature of FTS, but to do this successfully you would probably need to implement and custom text search configuration or just bypass such configuration altogether and implement your own analogs of to_tsvector, to_tsquery which would produce multiple tokens only by breaking up the JSONB into it parts, but not by breaking individual parts up into words.

For more ideas, you should provide a complete example query with a EXPLAIN (ANALYZE, BUFFERS) for it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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