Unlock PostgreSQL 9.5: Faster Sorting, Grouping Sets, Upserts & JSONB Tips

What’s New in PostgreSQL 9.5

PostgreSQL 9.5 was officially released with sorting operations up to 2‑12× faster, index creation up to 20× quicker, and a broad set of OLAP syntax features such as GROUPING SETS , CUBE , and ROLLUP . These enhancements enable powerful data‑pivoting directly inside the database.

Grouping Sets, CUBE and ROLLUP

Using grouping sets returns a count for each specified key while leaving other keys NULL. CUBE expands this by generating every possible combination of the selected columns, so you obtain totals for each department, department + gender, department + role, and department + role + gender. ROLLUP behaves like CUBE but only for the hierarchical levels you explicitly list, e.g., ROLLUP(department, role, gender) will not produce the independent department‑gender grouping.

Importing External Schemas (Data Pump)

If you have an external database ready, you can import its schemas into PostgreSQL using the data‑pump approach. Create the target tables as needed and then load the data directly, simplifying migration workflows.

pg_rewind for High Availability

When managing PostgreSQL instances with HA, pg_rewind becomes a valuable tool. Traditional replication requires copying the entire physical database, but if a failure occurs, pg_rewind can quickly revert the instance to a consistent state, saving massive data transfer time.

Upsert Support

One of the headline features of PostgreSQL 9.5 is native upsert (INSERT … ON CONFLICT). When an insert conflicts with existing rows, you can either:

Do nothing (skip the row)

Perform a partial update of the conflicting row

JSONB Enhancements

PostgreSQL 9.5 also improves JSONB . The output format in psql is now more readable, making it easier to inspect JSON data directly from the console. For example, running a simple SELECT on a JSONB column will display nicely formatted JSON.

For a complete list of new features, refer to the official PostgreSQL 9.5 release notes.