TRENDING

TECHNOLOGY NEWS FROM AROUND THE WORLD

→
PostgreSQL 9.2: why JSON support and index-only scans mattered
Software Engineering · 2012
Software Engineering2012

PostgreSQL 9.2: why JSON support and index-only scans mattered

PostgreSQL 9.2 was released on 10 September 2012 and introduced features including a JSON data type and index-only scans. Storing JSON does not require abandoning relational design.

Yazılımcının GünlüğüPublished: Nov 17, 20263 min read
JOIN THE DISCUSSION
✎ Write a comment
Reading progress0%

Historical and technical context

PostgreSQL 9.2 was released on 10 September 2012 and introduced features including a JSON data type and index-only scans. Storing JSON does not require abandoning relational design. Stable, related attributes can remain in tables while selected flexible properties use a document-oriented representation.

Architectural decisions and constraints

An index-only scan may answer a query from an appropriate index without fetching every underlying table row. It is not guaranteed for every statement: the columns requested, index coverage, and visibility information affect the plan. Declaring an index useful without examining an actual execution plan is an expensive shortcut.

Database access flowConceptual sketch · summary of the article's technical flow
A four-stage architecture illustration prepared specifically for this article.

A practical scenario

Consider an order listing filtered by date, status, and customer. Index columns, sort order, and query predicates must be considered together. A controlled experiment should compare EXPLAIN output, buffers read, high-percentile latency, and the additional work imposed on inserts and updates.

Verification and implications

Unbounded JSON structures can weaken integrity and queryability, while indexing everything makes writes more expensive. The key lesson is to combine flexible data representation with explicit schema guarantees and measured index design. Historical features of PostgreSQL 9.2 should not be confused with the behavior of current releases.

Define the data contract first

SQL changes need explicit keys, uniqueness rules, null semantics, time zones and transaction boundaries. Test retries and concurrent workers, not only a single successful request. Count source and destination records before migrations, retain checkpoints and keep a rollback path. Strong data contracts prevent duplicated or missing records when jobs are repeated.

Measure execution plans under representative load

An index is not a universal speed improvement. Inspect query plans, rows read, statistics freshness and write overhead together. A query made faster for one dashboard may slow down frequent ingestion. Compare latency percentiles and system resource use under realistic data distribution before calling the optimization successful.

Be the first reader ✎ Write a comment

Comments

No comments yet. Be the first to contribute.

What might reader discussion look like?Illustrative

These names, comments and editor replies are illustrative, not real visitor posts. Interactions are not saved. Use the real form below to contribute.

Riley Thompson

Reading “PostgreSQL 9.2: JSON desteği ve index-only scan neyi değiştirdi?” raised this question for me: migration and rollback plans should be explained.

Sample editor reply

Editor note: Forward-looking claims in an announcement are not the same as established performance results. Further evidence may change the assessment.

Shannon Blake

It is important to distinguish what the source verifies from projections about the future.

Taylor Reed

The next version's measurements should be compared with the initial announcement.

✎ Write a comment

Your first and last name will appear publicly alongside your comment.