What's so exciting about Postgres?

JSON & JSONB

PostgreSQL's support for JSON and JSONB data types offers flexibility in handling semi-structured data. Craig Kerstiens explains that JSON was initially integrated into PostgreSQL as a simple text field, allowing developers to store and validate JSON data without complex migrations 1. JSONB, introduced later, provides a binary representation that enhances performance by enabling indexing and efficient querying 2. This makes JSONB ideal for applications requiring dynamic data structures, such as feature flags or user settings, where flexibility is key 3.

JSONB is usually what you want when you're developing. In most cases.

--- Craig Kerstiens

Kerstiens emphasizes that while JSON is useful for storing logs or API responses, JSONB is preferred for most development scenarios due to its advanced capabilities.

   

Extensions

PostgreSQL's extensibility is a standout feature, with over 250 extensions available that significantly enhance its capabilities. Craig Kerstiens highlights that these extensions, written in languages like C or SQL, allow developers to add new data types, functions, and access methods, effectively transforming PostgreSQL's core functionality 4. Examples include PostGIS for geospatial data and PLV8 for running JavaScript within the database 5.

Extensions are absolutely huge, and what you can do with them is kind of unbelievable.

--- Craig Kerstiens

This flexibility enables PostgreSQL to adapt to various use cases, from simple HTTP requests to complex data science tasks, demonstrating its versatility and power 6.

   

Pluggable Storage

The introduction of pluggable storage in PostgreSQL marks a significant advancement, allowing for customizable storage engines. Craig Kerstiens notes that this feature, still in its early stages, promises to address common issues like vacuuming by offering alternative backend types 7. Columnar storage, for example, optimizes data compression and retrieval, making it ideal for time-series data 8.

Pluggable storage to me is one of those next big frontiers.

--- Craig Kerstiens

This innovation could redefine PostgreSQL's role in data warehousing and analytics, expanding its utility beyond traditional transactional applications.