Databases: Comparison, Choosing & Polyglot
jsonb: Indexes, Tradeoffs & Exercise
jsonb in a real applicationjsonb and when not to use itYou are finishing the books API. On this page you will optimisze it with a GIN index, understand its trade-offs, and then complete an exercise that adds a brand new book type comic books without touching the database schema at all.
Without an index, every @> query scans every row. For 3 books — fine. For 100,000 books — very slow.
A GIN (Generalized Inverted Index) indexes every key and value inside the JSON. PostgreSQL uses it to find matches directly without scanning every row.
docker exec -it my-postgres psql -U hyfuser -d jsonb_demo
-- Index the whole details column
-- supports @> queries and key existence checks
CREATE INDEX idx_books_details ON books USING GIN (details);
If you always filter by one specific field only, use a smaller expression index:
-- Index only the author field — smaller, faster for that query only
CREATE INDEX idx_books_author ON books ((details->>'author'));
| Index type | When to use |
|---|---|
GIN (details) |
You query many different JSON fields |
((details->>'field')) |
You always filter by one specific field |
<aside> 💡
GIN indexes make @> fast but are larger than normal indexes and slightly slower on writes. Add them when you have a clear need — not on every jsonb column by default.
</aside>
Flexible schema without a new database. You get document-store-like flexibility inside PostgreSQL. No need to add MongoDB just for this.
No migrations for new fields.
Adding a language field to a book does not require ALTER TABLE. Just include it in the JSON on insert.
Mix structured and flexible in one table.title is a typed column with constraints. details is flexible. Both in the same table, same database, same backups.
Invalid JSON rejected immediately. PostgreSQL validates JSON on insert corrupted data never enters your database.
One system. Structured data and flexible data live together in PostgreSQL same transactions, same tools you already know.
No constraints inside JSON.
You cannot add NOT NULL to a field inside jsonb. Enforce required fields in your Java code — not the database.
sql
-- This works on a normal column
ALTER TABLE books ADD CONSTRAINT title_not_empty CHECK (title <> '');
-- This does NOT work inside jsonb
-- No SQL way to say: details->>'author' must not be null
No foreign keys inside JSON.
Storing a category_id inside jsonb means PostgreSQL cannot verify it matches a real row. Always use a proper column for relationships.
Query complexity grows.WHERE author = ? is simple. WHERE details->>'author' = ? is harder to read and maintain. If you filter on a field in most queries — it belongs in a normal column.
Easy to overuse.
Using jsonb to avoid schema design is the most common mistake. If most of your queries use JSON operators — your schema needs rethinking.
Add a comic book type to the library. Comics have artist, series, and issue — none of which exist in the current schema. You will add them without changing the table at all.
Add this book using Postman — no schema change needed:
POST <http://localhost:8080/api/books>
Content-Type: application/json
{
"title": "Batman: Year One",
"details": {
"artist": "David Mazzucchelli",
"series": "Batman",
"issue": 1
}
}
Verify it was saved:
GET <http://localhost:8080/api/books>
Notice a completely new book type with completely new fields, and no ALTER TABLE was needed.
Add to BookRepository.java:
java
public List<Book> findByArtist(String artist) {
// use the ->> operator
// same pattern as findByAuthor from page 2
}
Add to BookController.java:
java
// GET /api/books?artist=David Mazzucchelli
@GetMapping(params = "artist")
public List<Book> getByArtist(@RequestParam String artist) {
return bookRepository.findByArtist(artist);
}
Test:
GET <http://localhost:8080/api/books?artist=David> Mazzucchelli
→ Batman: Year One
Add to BookRepository.java:
java
public void updateSeries(Long id, String series) {
// same pattern as updateGenre from page 2
// path is '{series}'
}
Add to BookController.java:
java
// PATCH /api/books/{id}/series?value=Detective Comics
@PatchMapping("/{id}/series")
public ResponseEntity<Void> updateSeries(
@PathVariable Long id, @RequestParam String value) {
bookRepository.updateSeries(id, value);
return ResponseEntity.noContent().build();
}
Test:
PATCH <http://localhost:8080/api/books/4/series?value=Detective> Comics
GET <http://localhost:8080/api/books>
Verify only series changed artist and issue are still there.
bash
docker exec -it my-postgres psql -U hyfuser -d jsonb_demo
sql
-- See all books and their details
SELECT id, title, details FROM books;
-- See all artists
SELECT title, details->>'artist' AS artist FROM books;
-- Use @> to find Batman books
SELECT title FROM books WHERE details @> '{"series": "Batman"}';
details->>'artist' for a novel row that has no artist field. What did PostgreSQL return? Did it throw an error? Why?artist field is now used in almost every comic search. Should it stay in jsonb or become a proper column? What would you need to change if you moved it?category_id inside jsonb to keep things flexible. What is the problem with this idea?| --- | --- | --- |
| --- | --- |