Last checked and updated on 10 Jun 2026
What Is New in PostgreSQL 9.4
| Category | Key Features |
|---|---|
| Data Types | JSONB data type, ALTER SYSTEM command |
| Performance | Refresh Materialized Views Concurrently, GIN index improvements, Logical Decoding |
| SQL Features | WITH ORDINALITY, WITH TIES, ROLLUP, CUBE, GROUPING SETS |
| Replication | Replication Slots, Logical Decoding |
| System Administration | Dynamic Background Workers, Big SERIAL |
What are the major new data types and storage features?
The headline feature is JSONB, a binary storage format for JSON data. This isn't just storing JSON as text; it's a decomposed binary format that allows for indexing and much faster querying. In practice, this made PostgreSQL a serious contender for document-oriented workloads right alongside NoSQL systems.
Another key addition is the ALTER SYSTEM command. This lets you change postgresql.conf parameters directly from a SQL session, which is far more scriptable than manually editing the config file.
How did PostgreSQL 9.4 improve performance and indexing?
GIN indexes received significant optimizations, making them faster to build and smaller on disk. This matters because GIN is the primary index type for complex data like arrays, full-text search, and the new JSONB type.
The REFRESH MATERIALIZED VIEW CONCURRENTLY command was a huge operational win. It allows you to update a materialized view without locking it, meaning queries can still read the old data while the refresh happens in the background.
What new SQL capabilities were introduced?
This release added powerful OLAP features for analytical queries. The WITH ORDINALITY clause returns a row number alongside set-returning function results. WITH TIES includes all rows that tie for the last place in a LIMIT query.
For grouping, PostgreSQL now supports the SQL standard ROLLUP, CUBE, and GROUPING SETS operations. These are essential for generating multi-level reports and subtotals directly in the database.
How did replication and system management get better?
Replication Slots provide a major reliability improvement for streaming replication. They prevent the primary server from deleting WAL segments until they have been received by all registered standby servers, eliminating the risk of standbys falling behind and being unable to catch up.
Logical Decoding, the foundation for tools like pgLogical, gives access to a stream of database changes in a portable, consumer-friendly format. This opens the door for custom replication solutions and event-driven architectures.
FAQ
What's the real-world difference between JSON and JSONB?
JSONB is almost always the better choice. It's faster to query because it supports indexing, but slightly slower to insert due to the processing overhead. Use regular JSON only if you need to preserve exact whitespace or key order.
Can I use REFRESH MATERIALIZED VIEW CONCURRENTLY on any view?
No. The materialized view must have a unique index for the concurrent refresh to work. The process uses this index to perform a differential update instead of a full rebuild.
What is a replication slot and why do I need one?
A replication slot is a persistent record on the primary server that tracks how much WAL data a standby has consumed. You need them to ensure the primary never deletes WAL data that a standby still requires, making your replication setup much more robust.
When would I use ALTER SYSTEM instead of editing postgresql.conf?
Use ALTER SYSTEM when you're automating configuration management with scripts or tools. It allows you to apply configuration changes through a standard SQL interface, which is easier to version control and deploy programmatically.
Is Logical Decoding the same as Streaming Replication?
No. Streaming Replication (physical replication) copies raw disk blocks and requires an identical primary and standby. Logical Decoding streams row-based changes, allowing for more flexibility, like replicating between different major versions or replicating only a subset of tables.