What Is PostgreSQL? – Definition and How It Works

Learn what PostgreSQL is, which features distinguish it, how JSONB and full-text search work, and why it suits collected data.

PostgreSQL is an open-source relational database known for standards compliance, correctness, and an unusually broad feature set for a general-purpose system.

1. What Distinguishes It

• Strong type system, including arrays, ranges, and user-defined types.
• JSONB — binary JSON with indexing, so semi-structured data is a first-class citizen.
• Full-text search built in, without a separate engine.
• Extensions, including geospatial, time-series, and vector similarity.
• Transactional DDL, so schema changes can be rolled back.
• Serious concurrency through multi-version concurrency control.

2. JSONB Matters for Collected Data

Collected records vary. JSONB lets a table hold the fields you have modelled as columns and the raw payload alongside them:

CREATE TABLE observations (
  id           bigserial PRIMARY KEY,
  source       text NOT NULL,
  external_id  text NOT NULL,
  price        numeric,
  collected_at timestamptz NOT NULL,
  raw          jsonb NOT NULL,
  UNIQUE (source, external_id, collected_at)
);
CREATE INDEX ON observations USING gin (raw);

Modelled fields stay queryable and typed; anything not yet modelled remains available in raw rather than being discarded.

3. Upserts Make Re-Runs Safe

INSERT INTO observations (source, external_id, price, collected_at, raw)
VALUES (...)
ON CONFLICT (source, external_id, collected_at) DO UPDATE
SET price = EXCLUDED.price, raw = EXCLUDED.raw;

Combined with a unique constraint, this makes a re-run idempotent — the property that turns a failed pipeline run from a data problem into a routine retry.

4. Full-Text Search Without Another System

Text can be converted to a searchable representation, indexed, and queried with ranking. For moderate corpora this removes an entire component from the architecture.

A dedicated search engine still wins on very large corpora and on relevance tuning, but "we need a search cluster" is a decision worth testing rather than assuming.

5. Concurrency Model

Readers do not block writers and writers do not block readers, because each transaction sees a consistent snapshot. That is why analytical queries can run against a live database without stalling ingestion.

The cost is that superseded row versions accumulate and must be cleaned up, which is what autovacuum does and why monitoring it matters.

6. Where It Fits

7. Practical Notes for Collection

• Index for the queries you actually run, since each index costs write throughput.
• Use timestamptz, not naive timestamps.
• Partition by time once tables grow large; it makes retention and pruning straightforward.
• Use numeric for money, never a float.
• Keep the raw payload so extraction can improve without recollection.

8. Summary

PostgreSQL is a standards-compliant relational database with a rich type system, JSONB for semi-structured data, built-in full-text search, and strong transactional guarantees.

For collected data it hits an unusual sweet spot: typed columns for what you have modelled, JSONB for what you have not, and upserts that make re-runs safe.

Potential Related Glossary

• Data Structure
• Data Wrangling
• Unstructured Data
• Parsing