Trying to implement an API extraction that dumps the results from JSON apis daily in a postgres db. The goal for these data is to be used in analysis. Afterwards I will handle those using dbt to do the modeling part (extracting the JSON values).

Example JSON values from an object representing a task on a task management system:

 gid: 1234, // Unique id for the resource
 updated_at: "Wed, 22 Mar 2023 08:53:46 GMT",
 ... rest of the attributes

My current strategy is storing those in tables with this schema

gid info::jsonb updated_at::timestamp
12345 {...} Wed, 22 Mar 2023 08:53:46 GMT

If the extraction is to happen once a day every day, does the strategy of surrogate key of (gid, updated_at) sound ok to judge if the insert should happen or not ?

1 Answer 1


Yes, that seems reasonable.
Since you'll be scanning the jsonb on each pass each time for gid and updated_at, you'll want to create a GIN index for this purpose, e.g.

CREATE INDEX api_gid ON api USING GIN ((info-> 'gid'));

Ideally, you could create an index on both gid and updated_at, but that requires use of an extension called btree_gin. Without the extension, you could try creating a second index on info->'updated_at', but it would probably not get used.. depends on what else the query needs to do.

If you can install the btree_gin extension, then this syntax would work:

CREATE INDEX index_name ON items USING GIN (account_id, keywords);

Your Answer

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

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