TechnicalJuly 26, 2026

Demystifying PostgreSQL: Aggregations, Window Frames, Table Partitioning vs Indexing, JSONB Optimization, & VACUUM

An engaging, mentor-guided dialogue covering CTEs, COALESCE, TO_CHAR, Joins, Roll-over Aggregations (`ROWS BETWEEN`), Table Partitioning vs Indexing, JSON vs JSONB storage optimization (GIN indexes), and VACUUM maintenance.

It was 4:30 PM on a Tuesday when Arjun, a Software Engineer, walked over to Vinil, the Solution Architect. Arjun was scaling a real-time IoT and telemetry platform for Smart Cities and high-concurrency microservices. His queries were hitting performance bottlenecks, dead-tuple bloat, and unoptimized JSON payloads.

What followed was a comprehensive architectural masterclass turning core PostgreSQL mechanics into intuitive, practical engineering solutions.

Act 1: Handling Missing Data Gracefully (`COALESCE`)

Arjun: "Vinil, I'm calculating average noise and air pollution levels across city sensors. But whenever a sensor drops offline, PostgreSQL returns NULL for the average or total sums, which crashes our frontend dashboard graphs!"

Vinil: "In SQL, any arithmetic operation or aggregate over empty sets can result in NULL. To fix this cleanly, wrap your aggregations in COALESCE(val, fallback)."

SELECT 
  sensor_id,
  COALESCE(AVG(decibel_level), 0.0) AS avg_noise_db,
  COALESCE(COUNT(reading_id), 0) AS total_readings,
  COALESCE(SUM(alert_count), 0) AS total_alerts
FROM noise_sensors
WHERE recorded_at >= NOW() - INTERVAL '1 hour'
GROUP BY sensor_id;

Arjun: "So if AVG(decibel_level) is NULL because no readings arrived, COALESCE falls back to 0.0 instead of breaking our API contract!"

Vinil: "Exactly. Always use COALESCE() when serving public REST or GraphQL endpoints."

Act 2: Organizing Complex Queries with CTEs (`WITH` Clause)

Arjun: "My SQL queries are becoming massive—aggregating sensor data first, joining with region tables, and filtering high-traffic zones. Nested subqueries look like a pyramid of doom!"

Vinil: "Use Common Table Expressions (CTEs) via the WITH keyword. CTEs break queries into clean, named temporary tables."

WITH hourly_sensor_stats AS (
  SELECT 
    sensor_id,
    date_trunc('hour', recorded_at) AS time_bucket,
    COALESCE(AVG(pm25_level), 0) AS avg_pm25,
    COALESCE(MAX(pm25_level), 0) AS peak_pm25
  FROM air_quality_sensors
  WHERE recorded_at >= NOW() - INTERVAL '24 hours'
  GROUP BY sensor_id, date_trunc('hour', recorded_at)
),
active_zones AS (
  SELECT zone_id, zone_name 
  FROM city_zones
  WHERE is_active = TRUE
)
SELECT 
  z.zone_name,
  s.time_bucket,
  ROUND(AVG(s.avg_pm25), 2) AS zone_avg_pm25,
  MAX(s.peak_pm25) AS zone_peak_pm25
FROM hourly_sensor_stats s
JOIN active_zones z ON s.sensor_id = z.zone_id
GROUP BY z.zone_name, s.time_bucket
ORDER BY s.time_bucket DESC;

Act 3: Time-Series Bucketing & Formatting (`date_bin` & `TO_CHAR`)

Arjun: "How do we group readings into custom 15-minute intervals and format timestamps cleanly for mobile devices?"

Vinil: "Use date_bin(stride, source, origin) for interval bucketing, and TO_CHAR() for formatting:"

SELECT 
  date_bin('15 minutes', recorded_at, TIMESTAMP '2026-01-01 00:00:00') AS bucket_15m,
  TO_CHAR(recorded_at, 'Mon DD, YYYY - HH12:MI AM') AS formatted_time,
  COALESCE(AVG(vehicle_speed), 0) AS avg_speed
FROM traffic_events
WHERE recorded_at >= NOW() - INTERVAL '6 hours'
GROUP BY bucket_15m, recorded_at
ORDER BY bucket_15m DESC;

Act 4: Roll-over Aggregations & Window Frames (`ROWS BETWEEN`)

Arjun: "I need to calculate a rolling 5-reading moving average and track pressure drops between consecutive readings. How does window frame aggregation work?"

Vinil: "Window functions execute calculations across a set of rows specified by OVER (PARTITION BY ... ORDER BY ...). You control the sliding frame using ROWS BETWEEN or RANGE BETWEEN."

`ROWS BETWEEN` vs `RANGE BETWEEN`

ROWS BETWEEN: Operates on physical row counts (e.g. 4 preceding rows + current row), regardless of duplicate timestamp values.
RANGE BETWEEN: Operates on logical values (e.g. all rows within a 10-minute timestamp range of the current row).

SELECT 
  sensor_id,
  recorded_at,
  pressure_psi,
  -- Delta from previous reading
  pressure_psi - LAG(pressure_psi, 1) OVER (PARTITION BY sensor_id ORDER BY recorded_at) AS pressure_delta,
  -- 5-row rolling moving average (Roll-over aggregation)
  AVG(pressure_psi) OVER (
    PARTITION BY sensor_id 
    ORDER BY recorded_at 
    ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
  ) AS rolling_avg_5pings,
  -- 10-minute logical rolling window
  SUM(alert_count) OVER (
    PARTITION BY sensor_id 
    ORDER BY recorded_at 
    RANGE BETWEEN INTERVAL '10 minutes' PRECEDING AND CURRENT ROW
  ) AS rolling_10min_alerts
FROM water_pipeline_sensors
WHERE recorded_at >= NOW() - INTERVAL '3 hours';

Act 5: Table Partitioning vs. Database Indexing: What’s the Difference?

Arjun: "I keep hearing engineers talk about Table Partitioning and Indexing. Aren’t they doing the same thing?"

Vinil: "They serve completely different architectural purposes! Let's clarify:"

1. Database Indexing (B-Tree, BRIN, GIN)

What it is: Data structures (like B-Trees) stored alongside your table pointing directly to specific tuple locations (ctid).
Use Case: Finding specific individual rows or small ranges (e.g., WHERE sensor_id = 'S-102').
Limitation: When a table grows to hundreds of millions of rows, B-Tree indexes become massive and no longer fit into RAM (shared_buffers), degrading write speed.

2. Table Partitioning (`PARTITION BY RANGE / LIST / HASH`)

What it is: Splitting a giant physical table into smaller, separate physical sub-tables on disk under a single parent table interface.
Use Case: Dropping old time-series data instantly (DROP TABLE sensor_telemetry_2025_01), and Partition Pruning (where PostgreSQL skips entire physical tables during queries).

-- Create Parent Table partitioned by time range
CREATE TABLE sensor_telemetry (
  telemetry_id BIGSERIAL,
  device_id VARCHAR(50) NOT NULL,
  recorded_at TIMESTAMPTZ NOT NULL,
  payload JSONB
) PARTITION BY RANGE (recorded_at);

-- Create Monthly Sub-Partitions
CREATE TABLE sensor_telemetry_2026_07 PARTITION OF sensor_telemetry
  FOR VALUES FROM ('2026-07-01 00:00:00+00') TO ('2026-08-01 00:00:00+00');

-- Index on the partition for fast lookup
CREATE INDEX idx_telemetry_device_time 
  ON sensor_telemetry_2026_07 (device_id, recorded_at DESC);

Vinil: "In short: Partitioning divides the storage on disk, while Indexing speeds up searches within those partitions."

Act 6: JSON vs. JSONB & High-Performance Optimization

Arjun: "We store flexible payload metadata in Postgres. Should we use JSON or JSONB data types?"

Vinil: "Always default to JSONB for production. Here is why:"

Storage Difference: `JSON` vs `JSONB`

JSON (Text Storage): Stores exact raw JSON text strings including whitespaces, duplicates, and key orders.
- Pros: Slightly faster insert speed (no parsing).
- Cons: Must re-parse JSON text every single query execution; cannot be indexed with GIN.
JSONB (Decomposed Binary Storage): Converts raw text into an optimized binary format on insert. Removes duplicate keys and whitespace.
- Pros: Extremely fast query processing; supports GIN (Generalized Inverted) indexes.
- Cons: Slightly higher insert processing CPU cost.

Optimized Rules for Using `JSONB`

Extract Fixed High-Frequency Columns: Never put fields you regularly filter by (like timestamp, device_id, tenant_id) inside JSONB. Keep them as native SQL columns!
Use GIN Containment Indexes:

-- Create a GIN Index with jsonb_path_ops for fast containment queries
CREATE INDEX idx_telemetry_payload_gin 
  ON sensor_telemetry USING GIN (payload jsonb_path_ops);

-- Fast containment query using @> operator (sub-millisecond lookup)
SELECT device_id, payload->>'firmware_version' AS firmware
FROM sensor_telemetry
WHERE payload @> '{"status": "ALERT", "config": {"mode": "TURBO"}}';

Use Arrow Operators Correctly:
- ->: Returns JSON object or array element (as jsonb).
- ->>: Returns JSON field text value (as text).

Act 7: Database Maintenance & De-bloating (`VACUUM` & `VACUUM ANALYZE`)

Arjun: "Our high-throughput tables are slowing down even though total row counts haven't changed. Why?"

Vinil: "PostgreSQL uses MVCC (Multi-Version Concurrency Control). When you UPDATE or DELETE a row, Postgres doesn't overwrite it—it marks the old tuple as 'dead' and writes a new row."

Vinil: "Over time, accumulated dead tuples create table bloat, wasting disk space and slowing down sequential scans."

Understanding `VACUUM` vs `VACUUM ANALYZE`

VACUUM: Reclaims storage occupied by dead tuples so Postgres can re-use that space for new inserts. It does not lock the table.
VACUUM ANALYZE: Reclaims space and updates database statistics in pg_statistic. The query planner relies on these statistics to choose between Index Scans and Sequential Scans!
VACUUM FULL: Rewrites the entire table to disk to reclaim maximum space, but takes an exclusive write lock. Use with caution!

-- Manual maintenance on active telemetry tables
VACUUM (VERBOSE, ANALYZE) sensor_telemetry_2026_07;

Vinil: "Ensure autovacuum is tuned aggressively on high-insert/update tables to prevent dead tuple buildup and Transaction ID Wraparound."

Summary Cheat-Sheet

Conclusion

Mastering PostgreSQL is all about combining proper storage types (JSONB), efficient disk layout (Table Partitioning), targeted indexing (B-Tree/GIN), clean query framing (CTEs, ROWS BETWEEN), and active maintenance (VACUUM ANALYZE). With these techniques, your databases will remain fast, reliable, and scalable under extreme enterprise workloads.

📢 Share this article

Share on LinkedIn

Act 1: Handling Missing Data Gracefully (COALESCE)

Act 2: Organizing Complex Queries with CTEs (WITH Clause)

Act 3: Time-Series Bucketing & Formatting (date_bin & TO_CHAR)

Act 4: Roll-over Aggregations & Window Frames (ROWS BETWEEN)

ROWS BETWEEN vs RANGE BETWEEN