this post was submitted on 06 Nov 2024
10 points (100.0% liked)

PostgreSQL

677 readers
1 users here now

The world's most advanced open source relational database

Project
Events
Podcasts
Related Fediverse communities

founded 2 years ago
MODERATORS
 

What are your experiences with @postgresql jsonb columns as document store?

Is it easy to use from a Spring App? How fast is it? What are its limitations?

#postgresql #json #databases

you are viewing a single comment's thread
view the rest of the comments
[–] Loup 2 points 1 month ago

I had a use case for something similar to this in the past and it worked pretty well. Data projections were stored as json payloads in jsonb columns that was served through an API.

The tables contained the appropriate key(s) and the jsonb column only. The majority of the json data was updated using the functions that postresql provides to update specific sections of the document. There were a few tables where the value of the jsonb columns was replaced completely instead of updating a specific segment. At least one table had the jsonb column changed to a json column because it was replaced so often and never searched.

This was done in a spring boot application and jOOQ. At the time the json and jsonb data types were not supported by jOOQ so I had to build that piece but it was worth it. This may be supported out of the box now.

I would take the approach again in the future