Skip to main content

Chapter 9 · Watch, then practise

Advanced Database Concepts

Compare three ways to extend a relational system: partition data, replicate changes and store structured documents.

3 questions · 3 with related videos. Matches are based on playlist titles; broader background matches are labeled.

What to study

  • Partitioning
  • Physical and logical replication
  • Synchronous and asynchronous updates
  • JSON and JSONB

Chapter playlists

Choose a playlist

Notes

Horizontal vs Vertical Database Partitioning

Hussein Nasser · 10:22

Choose a video · 2 lectures

Supplementary partitioning and replication lectures support comparing data distribution with keeping copies.

1. Partitioning versus replication

Do partitioning and replication solve the same problem?

Partitioning divides a logical table into pieces, for example by date, so suitable queries can avoid unrelated pieces. Replication maintains another copy of data for availability or read workloads. A system may use both. Local table partitioning does not by itself distribute data across servers.

Database Replication Crash Course ( with Postgres 13 )

Hussein Nasser · 32:24

Choose a video · 2 lectures

Replication setup and replication types are the matching topics; apply the asynchronous lag condition in the written example.

2. Replication tradeoff

Why can asynchronous replication lose recent commits during failover?

A primary can acknowledge a commit before a standby receives or durably stores it. If the primary fails during that gap, the standby may lack the change. Waiting for an appropriate standby acknowledgment reduces this risk but introduces coordination and latency costs.

JSON & JSONB Types in PostgreSQL | Part 1

Shiv Shankar Dayal · 26:42

The supplementary PostgreSQL JSON and JSONB lesson directly matches the data types being compared.

3. Documents inside a relational database

What is the main PostgreSQL difference between json and jsonb?

json retains the original JSON text and must parse it for processing. jsonb stores a decomposed representation and supports indexing, but does not preserve formatting or duplicate object keys. Choose according to query needs and whether exact input text must be retained.

References