PostgreSQL 17: New Features for Application Developers

PostgreSQL 17 landed with a feature set that finally makes several long-requested SQL capabilities mainstream. While database administrators will focus on vacuum improvements and logical replication enhancements, application developers get equally compelling additions: JSON_TABLE for querying JSON as relational data, significant MERGE statement improvements, new aggregate functions, and performance gains that affect every query.

This guide focuses exclusively on features that change how you write application code — the SQL patterns, ORM integration points, and architectural decisions that become possible or better in PostgreSQL 17.

Key Takeaways

• JSON_TABLE is now standard — query JSON arrays as if they were tables without custom functions
• MERGE statement gained RETURNING clause — get affected rows without a second query
• ANY_VALUE() aggregate makes grouping queries simpler without full GROUP BY lists
• Incremental sorting improvements reduce query plan cost on ordered data
• COPY command supports ON_ERROR to skip bad rows instead of failing entirely
• pg_stat_io view gives precise I/O statistics per relation for performance analysis
• Logical replication now supports sequences, closing a major gap for active-active setups
• VACUUM improvements reduce bloat accumulation — fewer emergency autovacuums

---

JSON_TABLE: Querying JSON as Relational Data

The most impactful SQL feature for application developers is JSON_TABLE. Applications frequently store arrays or nested objects in JSONB columns. Before PostgreSQL 17, querying this data required awkward jsonb_array_elements function calls with lateral joins.

Consider a JSONB column storing an order's line items:

-- orders.metadata column:
-- {
--   "items": [
--     {"sku": "PROD-001", "qty": 2, "price": 49.99},
--     {"sku": "PROD-002", "qty": 1, "price": 129.99}
--   ]
-- }

Before PostgreSQL 17 (using jsonb_array_elements):

SELECT
  o.id,
  o.created_at,
  item->>'sku' AS sku,
  (item->>'qty')::int AS qty,
  (item->>'price')::numeric AS price
FROM orders o,
     jsonb_array_elements(o.metadata->'items') AS item
WHERE o.organization_id = $1;

PostgreSQL 17 with JSON_TABLE:

SELECT
  o.id,
  o.created_at,
  items.sku,
  items.qty,
  items.price
FROM orders o,
     JSON_TABLE(
       o.metadata,
       '$.items[*]'
       COLUMNS (
         sku VARCHAR PATH '$.sku',
         qty INTEGER PATH '$.qty',
         price NUMERIC PATH '$.price'
       )
     ) AS items
WHERE o.organization_id = $1;

The JSON_TABLE version is more readable, gives explicit column types, and participates in the query planner's join optimization. It also handles missing keys gracefully with DEFAULT NULL ON EMPTY and DEFAULT 0 ON ERROR column options.

More complex example — nested JSON with error handling:

SELECT
  products.id,
  specs.weight_kg,
  specs.color,
  specs.dimensions_cm
FROM products,
     JSON_TABLE(
       products.specifications,
       '$.specs'
       COLUMNS (
         weight_kg NUMERIC PATH '$.weight'
           DEFAULT 0 ON EMPTY DEFAULT 0 ON ERROR,
         color VARCHAR PATH '$.color'
           DEFAULT 'unknown' ON EMPTY,
         dimensions_cm VARCHAR PATH '$.dimensions'
           DEFAULT NULL ON EMPTY
       )
     ) AS specs
WHERE products.organization_id = $1;

---

MERGE with RETURNING

The MERGE statement (introduced in PostgreSQL 15) allows conditional insert-or-update logic in a single statement. PostgreSQL 17 adds RETURNING, which was previously impossible:

-- Before PostgreSQL 17: MERGE had no RETURNING
-- Required a separate SELECT after MERGE

-- PostgreSQL 17: MERGE with RETURNING
MERGE INTO contacts AS target
USING (
  SELECT $1::uuid AS organization_id,
         $2::varchar AS email,
         $3::varchar AS name
) AS source
ON target.organization_id = source.organization_id
   AND target.email = source.email
WHEN MATCHED THEN
  UPDATE SET
    name = source.name,
    updated_at = NOW()
WHEN NOT MATCHED THEN
  INSERT (organization_id, email, name, created_at, updated_at)
  VALUES (source.organization_id, source.email, source.name, NOW(), NOW())
RETURNING
  id,
  email,
  created_at,
  CASE WHEN xmax = 0 THEN 'inserted' ELSE 'updated' END AS operation;

The xmax = 0 trick identifies whether the row was inserted (xmax = 0) or updated (xmax != 0). This pattern is essential for idempotent data import pipelines where you need to know what actually changed.

Using MERGE in Drizzle (via raw SQL until Drizzle adds native MERGE support):

import { sql } from 'drizzle-orm';
import { db } from '@ecosire/db';

async function upsertContact(
  orgId: string,
  email: string,
  name: string
) {
  const result = await db.execute(sql`
    MERGE INTO contacts AS target
    USING (SELECT ${orgId}::uuid, ${email}::varchar, ${name}::varchar)
      AS source(organization_id, email, name)
    ON target.organization_id = source.organization_id
       AND target.email = source.email
    WHEN MATCHED THEN
      UPDATE SET name = source.name, updated_at = NOW()
    WHEN NOT MATCHED THEN
      INSERT (organization_id, email, name, created_at, updated_at)
      VALUES (source.organization_id, source.email, source.name, NOW(), NOW())
    RETURNING id, email, CASE WHEN xmax = 0 THEN 'inserted' ELSE 'updated' END AS op
  `);

  return result.rows[0];
}

---

ANY_VALUE() Aggregate

A common GROUP BY frustration in PostgreSQL: you want to group by a column but also select a non-aggregated column from the same row. Previously you had to either add it to GROUP BY (changing grouping semantics) or use MAX() as a workaround.

-- Before: Awkward workaround
SELECT
  department_id,
  MAX(employee_name) AS any_employee_name, -- Wrong semantic
  COUNT(*) AS employee_count,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- PostgreSQL 17: ANY_VALUE() expresses intent clearly
SELECT
  department_id,
  ANY_VALUE(employee_name) AS sample_employee, -- Pick any one
  COUNT(*) AS employee_count,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

ANY_VALUE() is semantically correct — it explicitly says "I don't care which value from the group, just give me one." This is useful for diagnostic queries, debugging, and cases where any representative value is acceptable.

---

Incremental Sorting Improvements

PostgreSQL 17 significantly improved incremental sort performance. This affects any query with ORDER BY on a non-leading index column:

-- This benefits from incremental sorting in PostgreSQL 17
SELECT *
FROM orders
WHERE organization_id = $1    -- Index on (organization_id, created_at)
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- Before 17: Sort had to materialize all matching rows
-- After 17: Sort processes data in chunks, memory usage drops significantly

For pagination-heavy applications — dashboards, list views, reports — this improvement reduces memory pressure and improves response times without any query changes.

---

COPY with ON_ERROR

Data import pipelines frequently encounter malformed rows. Before PostgreSQL 17, a single bad row failed the entire COPY operation. Now you can skip bad rows:

-- Skip rows with format errors instead of failing
COPY contacts (name, email, phone, organization_id)
FROM '/tmp/import.csv'
WITH (
  FORMAT CSV,
  HEADER true,
  ON_ERROR IGNORE, -- Skip bad rows
  LOG_VERBOSITY VERBOSE -- Log what was skipped
);

The LOG_VERBOSITY VERBOSE option logs each skipped row to the PostgreSQL log, so you can audit what was rejected. This makes bulk import pipelines production-safe — a malformed row at line 47,832 no longer aborts a million-row import.

For programmatic imports via your ORM, you can use the PostgreSQL COPY protocol through the node-postgres driver:

import { Pool } from 'pg';
import { pipeline } from 'stream/promises';
import { createReadStream } from 'fs';

async function importContacts(csvPath: string, orgId: string) {
  const pool = new Pool({ connectionString: process.env.DATABASE_URL });
  const client = await pool.connect();

  try {
    const copyStream = client.query(
      `COPY contacts (name, email, organization_id)
       FROM STDIN
       WITH (FORMAT CSV, HEADER true, ON_ERROR IGNORE)`
    );

    await pipeline(createReadStream(csvPath), copyStream);
    console.log(`Imported rows: ${copyStream.rowCount}`);
  } finally {
    client.release();
  }
}

---

pg_stat_io: I/O Performance Analysis

PostgreSQL 17 adds pg_stat_io, a view that breaks down I/O statistics by relation, block type, and context. This is invaluable for diagnosing slow queries caused by excessive disk reads:

-- Top tables by cache misses (reads going to disk)
SELECT
  object,
  reads,
  hits,
  ROUND(hits::numeric / NULLIF(hits + reads, 0) * 100, 2) AS hit_rate_pct,
  evictions,
  reuses
FROM pg_stat_io
WHERE backend_type = 'client backend'
  AND object = 'relation'
  AND reads > 0
ORDER BY reads DESC
LIMIT 20;

-- Identify tables causing the most buffer evictions
SELECT
  schemaname,
  tablename,
  seq_scan,
  seq_tup_read,
  idx_scan,
  idx_tup_fetch,
  n_dead_tup,
  n_live_tup
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 20;

For tables with high sequential scan counts and low cache hit rates, you need either an index or a larger shared_buffers configuration.

---

Logical Replication for Sequences

PostgreSQL 17 adds logical replication support for sequences. This closes a critical gap for active-active replication setups — previously, sequences on standby servers diverged, causing duplicate key conflicts when promoting a standby.

For application developers, this means:

-- Configure sequence replication on primary
ALTER SEQUENCE contacts_id_seq SEQUENCE OWNED BY contacts.id;

-- Include sequences in publication
CREATE PUBLICATION app_publication
FOR TABLE contacts, orders, products
WITH (publish = 'insert,update,delete,truncate,sequence');

-- Subscriber automatically stays in sync with sequence values
CREATE SUBSCRIPTION app_subscription
CONNECTION 'host=primary-host dbname=mydb user=replicator'
PUBLICATION app_publication
WITH (streaming = parallel);

---

Performance Benchmarks

PostgreSQL 17's performance improvements are measurable in real applications:

These benchmarks are from real workloads on EC2 r6g.xlarge instances. Results vary by data distribution and query patterns.

---

Upgrading to PostgreSQL 17

For Docker-based development environments, update your compose file:

# docker-compose.dev.yml
services:
  postgres:
    image: postgres:17-alpine
    environment:
      POSTGRES_DB: ecosire_dev
      POSTGRES_USER: ecosire
      POSTGRES_PASSWORD: password
    ports:
      - "5433:5432"  # Use 5433 locally to avoid conflicts
    volumes:
      - postgres_data:/var/lib/postgresql/data
    command: >
      postgres
        -c shared_buffers=256MB
        -c effective_cache_size=1GB
        -c work_mem=16MB
        -c maintenance_work_mem=128MB
        -c checkpoint_completion_target=0.9
        -c wal_buffers=16MB
        -c max_connections=100

For production upgrades, use pg_upgrade:

# In-place major version upgrade
pg_upgrade \
  --old-datadir /var/lib/postgresql/16/main \
  --new-datadir /var/lib/postgresql/17/main \
  --old-bindir /usr/lib/postgresql/16/bin \
  --new-bindir /usr/lib/postgresql/17/bin \
  --check # Dry-run first

Always run --check first. For zero-downtime upgrades in production, use logical replication to migrate to a new PostgreSQL 17 instance while the old one stays active.

---

Frequently Asked Questions

Is JSON_TABLE faster than jsonb_array_elements?

JSON_TABLE generally performs similarly to jsonb_array_elements for equivalent queries, but it participates in the query planner's optimization passes more effectively. The real benefit is readability, maintainability, and standard SQL compliance — your queries look like SQL, not function-call soup. For performance-critical JSON queries at scale, ensure your JSONB columns have appropriate GIN indexes.

Should I upgrade from PostgreSQL 16 to 17 immediately?

Wait 3-6 months after a major PostgreSQL release before upgrading production systems. This gives time for your hosting provider to support the new version, for extensions you depend on to update, and for any initial bugs to be patched. For new projects or development environments, start with PostgreSQL 17 today. The upgrade path from 16 to 17 is straightforward using pg_upgrade.

How do I enable the new pg_stat_io view?

pg_stat_io is automatically enabled in PostgreSQL 17 — no configuration needed. Query it with SELECT * FROM pg_stat_io in any PostgreSQL 17 database. The view is cumulative since the last stats reset (pg_stat_reset()), so establish a baseline before making changes and compare afterward.

Can I use MERGE with Drizzle ORM?

Drizzle doesn't currently have native MERGE support — use db.execute(sql\MERGE ...`)with parameterized values. Never usesql.raw()` for user-provided data. Watch the Drizzle GitHub issues for MERGE support tracking, as it's a commonly requested feature.

What PostgreSQL version should I target for new projects?

Target PostgreSQL 17 for all new projects. It has the best performance, the newest SQL features, and will receive security updates until November 2029. Avoid targeting PostgreSQL 14 or earlier — they'll reach end-of-life within the next 2 years, and you'll need to upgrade anyway.

---

Next Steps

PostgreSQL 17's new features — particularly JSON_TABLE, MERGE with RETURNING, and the incremental sorting improvements — open architectural patterns that weren't feasible before. ECOSIRE runs PostgreSQL 17 in production for all database workloads, taking advantage of these improvements across a 65+ table schema.

Whether you need database architecture consulting, query optimization, or full-stack development with modern tooling, explore our engineering services.