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
- About (history)
- Docs
- Donate to PostgreSQL
- Wiki
- Planet PostgreSQL
- IRC
- Mailing lists:
- pgsql-announce
- pgsql-hackers (developers)
- pgsql-general
- pgsql-jobs
- User Groups
Events
- SEAPUG Summer BBQ, 6 July in Seattle
- SFBA PostgreSQL Meetup, 12 July
- Chicago PostgreSQL Meetup, 19 July
- PGDay UK 2023, 12 September in London
- PGConf 2023, 3-5 October in New York City
- PGDay Israel 2023, 19 October
- PGConf.EU 2023, 12-15 December in Prague
Podcasts
Related Fediverse communities
- c/SQL on programming.dev
- #sql on Mastodon
- #postgresql on Mastodon
founded 2 years ago
MODERATORS
you are viewing a single comment's thread
view the rest of the comments
view the rest of the comments
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