PostgreSQL FAQ & Answers

Can GIN indexes be built in parallel in PostgreSQL 18?

99% confidence

PostgreSQL 18 Parallel GIN Index Builds

Yes. PostgreSQL 18 adds support for parallel builds of GIN indexes, significantly reducing index creation time for full-text search and JSONB columns.

Creating a Parallel GIN Index

-- Automatic: PostgreSQL chooses workers based on table size
CREATE INDEX CONCURRENTLY idx_docs_fts 
ON documents USING GIN (to_tsvector('english', content));

-- Manual: Force specific worker count
SET max_parallel_maintenance_workers = 4;
CREATE INDEX idx_json_gin ON events USING GIN (payload jsonb_path_ops);

Configuration

-- Global setting for parallel maintenance operations
max_parallel_maintenance_workers = 2  -- default

-- Per-table override
ALTER TABLE documents SET (parallel_workers = 4);

-- Check current settings
SHOW max_parallel_maintenance_workers;

Performance Comparison

Table Size	Workers	PG17 Time	PG18 Time	Improvement
10 GB	1	45 min	45 min	-
10 GB	4	45 min	14 min	3.2x
100 GB	4	8 hrs	2.5 hrs	3.2x

Common GIN Use Cases

-- Full-text search
CREATE INDEX idx_fts ON articles USING GIN (to_tsvector('english', body));

-- JSONB containment queries
CREATE INDEX idx_jsonb ON events USING GIN (metadata);

-- Array overlap/containment
CREATE INDEX idx_tags ON posts USING GIN (tags);

-- Trigram similarity (pg_trgm)
CREATE INDEX idx_trgm ON users USING GIN (name gin_trgm_ops);

Monitoring Build Progress

SELECT
    p.pid,
    p.phase,
    p.blocks_total,
    p.blocks_done,
    round(100.0 * p.blocks_done / nullif(p.blocks_total, 0), 1) AS pct_done
FROM pg_stat_progress_create_index p;

Note

Parallel index builds were already available for B-tree (since PG11). PostgreSQL 18 extends this to GIN.

Source: PostgreSQL 18 Release Notes
https://www.postgresql.org/docs/18/release-18.html

Sources

What JSONB indexing strategy should I use in PostgreSQL?

99% confidence

PostgreSQL JSONB Indexing Strategies

Choose the right index type based on your query patterns:

Index Types Comparison

Index Type	Best For	Operators Supported
GIN (default)	Containment, key existence	`@>`, `?`, `?&`, `?\|`
GIN (jsonb_path_ops)	Containment only (smaller, faster)	`@>` only
B-tree (on expression)	Equality on extracted values	`=`, `<`, `>`, etc.
Hash (on expression)	Equality only	`=`

GIN Index (Most Common)

-- Default GIN: supports all JSONB operators
CREATE INDEX idx_data_gin ON events USING GIN (data);

-- Queries that use this index:
SELECT * FROM events WHERE data @> '{"type": "click"}';
SELECT * FROM events WHERE data ? 'user_id';
SELECT * FROM events WHERE data ?& array['type', 'timestamp'];

GIN with jsonb_path_ops (Optimized)

-- 2-3x smaller, faster for @> only
CREATE INDEX idx_data_pathops ON events USING GIN (data jsonb_path_ops);

-- Only supports containment:
SELECT * FROM events WHERE data @> '{"type": "click"}';

-- Does NOT support:
SELECT * FROM events WHERE data ? 'user_id';  -- Uses seq scan

B-tree on Extracted Value

-- Best for equality/range on specific keys
CREATE INDEX idx_user_id ON events ((data->>'user_id'));
CREATE INDEX idx_timestamp ON events (((data->>'timestamp')::timestamptz));

-- Queries that use this index:
SELECT * FROM events WHERE data->>'user_id' = '12345';
SELECT * FROM events WHERE (data->>'timestamp')::timestamptz > '2025-01-01';

Partial Index (Performance Boost)

-- Index only relevant rows
CREATE INDEX idx_clicks ON events USING GIN (data jsonb_path_ops)
WHERE data->>'type' = 'click';

-- Smaller index, faster for filtered queries
SELECT * FROM events WHERE data @> '{"action": "purchase"}'
AND data->>'type' = 'click';

Decision Guide

Need to search ANY key/value? -> GIN (default)
Only use @> containment?      -> GIN (jsonb_path_ops)
Query specific scalar value?  -> B-tree expression index
Filter + containment?         -> Partial GIN index

Performance Example

-- Before optimization: 1200ms
SELECT * FROM events WHERE data @> '{"user_id": "12345"}';

-- After GIN jsonb_path_ops + partial index: 75ms
CREATE INDEX idx_events_jsonb ON events
USING GIN (data jsonb_path_ops)
WHERE data ? 'user_id';

Source: PostgreSQL Documentation - JSON Types
https://www.postgresql.org/docs/current/datatype-json.html

Sources

postgresql.org postgresql.org

99% confidence

replication

2 questions

How do failover logical replication slots work in PostgreSQL 17?

PostgreSQL 17 Failover Logical Replication Slots

PostgreSQL 17 enables logical replication slots to survive primary failover by synchronizing them to standby servers.

Prerequisites

Physical streaming replication between primary and standby
Hot standby enabled on standby
hot_standby_feedback = on on standby
primary_slot_name configured on standby

Configuration

On Primary (postgresql.conf):

-- List standbys that should receive slot sync
synchronized_standby_slots = 'standby1_slot'

On Standby (postgresql.conf):

-- Enable slot synchronization
sync_replication_slots = on
hot_standby_feedback = on
primary_slot_name = 'standby1_slot'

Creating Failover-Enabled Slots

-- Method 1: Direct slot creation with failover flag
SELECT pg_create_logical_replication_slot(
    'my_slot',
    'pgoutput',
    false,      -- temporary
    false,      -- two_phase
    true        -- failover (NEW in PG17)
);

-- Method 2: Via subscription
CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=primary dbname=mydb'
    PUBLICATION my_pub
    WITH (failover = true);

Monitoring

-- Check slot sync status on standby
SELECT slot_name, synced, active
FROM pg_replication_slots;

-- synced = true means slot is ready for failover

-- Check if slot changes are synchronized
SELECT * FROM pg_stat_replication_slots;

Failover Process

Primary fails
Standby promotes to new primary
Synced slots (where synced = true) become active
Subscribers reconnect to new primary
Logical replication continues from last confirmed LSN

Important Notes

Only slots with synced = true at failover time can be used
Physical slot between primary/standby is required
Slot sync happens periodically via slotsync worker
Logical replication changes aren't consumed until standby confirms receipt

Source: PostgreSQL 17 Documentation - Logical Replication Failover
https://www.postgresql.org/docs/17/logical-replication-failover.html

Sources

postgresql.org postgresql.fastware.com

99% confidence

What is the idle_replication_slot_timeout parameter in PostgreSQL 18?

PostgreSQL 18 idle_replication_slot_timeout

idle_replication_slot_timeout automatically invalidates replication slots that have been inactive for a specified period, preventing WAL bloat.

The Problem It Solves

Abandoned replication slots prevent WAL cleanup, leading to:

Disk space exhaustion
Potential database unavailability
Manual intervention required

Configuration

-- Set timeout (default: 0 = disabled)
ALTER SYSTEM SET idle_replication_slot_timeout = '1d';  -- 1 day
SELECT pg_reload_conf();

-- Check current setting
SHOW idle_replication_slot_timeout;

Valid Values

Value	Meaning
`0`	Disabled (default)
`30min`	30 minutes
`1h`	1 hour
`1d`	1 day
`7d`	1 week

Comparison with max_slot_wal_keep_size

Parameter	Triggers On	Use Case
`max_slot_wal_keep_size`	WAL size exceeds limit	Protect disk space
`idle_replication_slot_timeout`	Time since last activity	Clean up abandoned slots

Example Scenario

-- Create a slot
SELECT pg_create_logical_replication_slot('test_slot', 'pgoutput');

-- Slot becomes inactive (subscriber disconnects and never reconnects)
-- After idle_replication_slot_timeout passes, slot is invalidated

-- Check slot status
SELECT slot_name, active, invalidation_reason
FROM pg_replication_slots;

-- invalidation_reason will show 'idle_timeout' if expired

Best Practice

-- Combine both protections
max_slot_wal_keep_size = '100GB'      -- WAL size limit
idle_replication_slot_timeout = '7d'   -- Time limit

Source: PostgreSQL 18 Documentation - Replication Configuration
https://www.postgresql.org/docs/18/runtime-config-replication.html

Sources

postgresql.org morling.dev

99% confidence

authentication

2 questions

Does PostgreSQL 18 support OAuth 2.0 authentication?

PostgreSQL 18 OAuth 2.0 Authentication

Yes. PostgreSQL 18 introduces OAuth 2.0 authentication support, allowing integration with modern identity providers like Okta, Auth0, Azure AD, and Keycloak.

Configuration (pg_hba.conf)

# OAuth 2.0 authentication
host    all    all    0.0.0.0/0    oauth issuer="https://auth.example.com" client_id="pg_client"

How It Works

Client requests access token from OAuth provider
Client connects to PostgreSQL with token
PostgreSQL validates token with issuer
Connection established if token valid

Server Configuration

-- postgresql.conf
oauth_issuer = 'https://auth.example.com'
oauth_client_id = 'postgresql-server'
oauth_client_secret = 'your-secret'  -- Or use file

Client Connection

# Using psql with OAuth token
PGOAUTHTOKEN="eyJhbG..." psql -h myserver -U myuser -d mydb

# Using libpq connection string
psql "host=myserver user=myuser oauth_token=eyJhbG..."

Supported Flows

Flow	Use Case
Client Credentials	Service-to-service
Authorization Code	Interactive users
Device Authorization	CLI tools

Provider Examples

# Azure AD
host all all 0.0.0.0/0 oauth \
    issuer="https://login.microsoftonline.com/{tenant}/v2.0" \
    client_id="your-app-id"

# Okta
host all all 0.0.0.0/0 oauth \
    issuer="https://your-domain.okta.com/oauth2/default" \
    client_id="your-client-id"

# Keycloak
host all all 0.0.0.0/0 oauth \
    issuer="https://keycloak.example.com/realms/myrealm" \
    client_id="postgresql"

Security Notes

Tokens validated via OIDC discovery document
JWT signature verification automatic
Token expiration enforced
MD5 password auth deprecated in favor of SCRAM-SHA-256 or OAuth

Source: PostgreSQL 18 Release Notes
https://www.postgresql.org/docs/18/release-18.html

Sources

Is MD5 password authentication deprecated in PostgreSQL 18?

99% confidence

PostgreSQL 18 MD5 Authentication Deprecation

Yes. PostgreSQL 18 officially deprecates MD5 password authentication in favor of SCRAM-SHA-256.

Deprecation Notice

When using MD5 authentication in PostgreSQL 18, you'll see warnings in logs:

WARNING: md5 authentication is deprecated and will be removed in a future release
HINT: Use scram-sha-256 authentication instead.

Migration Steps

1. Check Current Configuration

-- Check current password encryption
SHOW password_encryption;  -- Should be 'scram-sha-256'

-- Check pg_hba.conf entries
-- Look for 'md5' in auth-method column

2. Update Server Configuration

-- postgresql.conf
password_encryption = scram-sha-256  -- Already default since PG14

3. Re-encrypt User Passwords

-- Users must reset passwords to use SCRAM
ALTER USER myuser PASSWORD 'new_secure_password';

-- Verify password type
SELECT usename, passwd LIKE 'SCRAM%' AS is_scram
FROM pg_shadow
WHERE usename = 'myuser';

4. Update pg_hba.conf

# Before (deprecated)
host    all    all    0.0.0.0/0    md5

# After (recommended)
host    all    all    0.0.0.0/0    scram-sha-256

Why SCRAM-SHA-256 Is Better

Aspect	MD5	SCRAM-SHA-256
Algorithm strength	Weak (broken)	Strong
Replay attacks	Vulnerable	Protected
Man-in-middle	Vulnerable	Protected
Channel binding	No	Yes
Password storage	Weak hash	Salted, iterated

Compatibility Notes

-- Clients must support SCRAM
-- libpq 10+ supports SCRAM
-- Most drivers updated years ago

-- Check client library version
SELECT version();  -- Server version
-- Client: psql --version, check driver docs

Migration Timeline

Version	Status
PG 10	SCRAM-SHA-256 introduced
PG 14	SCRAM-SHA-256 default for new passwords
PG 18	MD5 deprecated (warnings)
PG 19+	MD5 may be removed

Source: PostgreSQL 18 Release Notes
https://www.postgresql.org/docs/18/release-18.html

Sources

How does PostgreSQL 18 automatically remove unnecessary table self-joins?

99% confidence

query_optimization

2 questions

PostgreSQL 18 Self-Join Elimination

PostgreSQL 18 automatically removes unnecessary self-joins from queries, improving performance without query rewrites.

The Optimization

When a table is joined to itself on its primary key (or unique columns), and all referenced columns come from one instance, PostgreSQL 18 eliminates the redundant scan.

Example

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT,
    total NUMERIC,
    status TEXT
);

-- This query has a redundant self-join
SELECT o1.id, o1.total, o2.status
FROM orders o1
JOIN orders o2 ON o1.id = o2.id;

-- PostgreSQL 18 optimizes to:
-- SELECT id, total, status FROM orders;

EXPLAIN Comparison

-- PostgreSQL 17 plan
EXPLAIN SELECT o1.id, o1.total, o2.status FROM orders o1 JOIN orders o2 ON o1.id = o2.id;

-- Hash Join  (cost=...)
--   Hash Cond: (o1.id = o2.id)
--   ->  Seq Scan on orders o1
--   ->  Hash
--         ->  Seq Scan on orders o2

-- PostgreSQL 18 plan (self-join eliminated)
EXPLAIN SELECT o1.id, o1.total, o2.status FROM orders o1 JOIN orders o2 ON o1.id = o2.id;

-- Seq Scan on orders  (cost=...)

When It Applies

Condition	Self-Join Eliminated?
Join on PRIMARY KEY	Yes
Join on UNIQUE column	Yes
Join on non-unique column	No
LEFT JOIN (outer)	Depends on NULL handling
Columns from both aliases used	No (both needed)

Real-World Scenarios

-- ORM-generated queries often have redundant joins
-- Example: Eager loading that references same table

-- View that accidentally self-joins
CREATE VIEW order_details AS
SELECT o.id, o.total, o2.status
FROM orders o
JOIN orders o2 ON o.id = o2.id;  -- PG18 optimizes this

-- Subquery correlation patterns
SELECT * FROM orders o
WHERE total = (SELECT total FROM orders WHERE id = o.id);

Performance Impact

Reduces I/O (one scan instead of two)
Eliminates join computation overhead
Better for read-heavy OLTP workloads
Benefits ORM-heavy applications

Source: PostgreSQL 18 Release Notes
https://www.postgresql.org/docs/18/release-18.html

Sources

What is IN to ANY transformation in PostgreSQL 18?

99% confidence

PostgreSQL 18 IN to ANY Transformation

PostgreSQL 18 automatically converts IN (VALUES ...) to = ANY(...) for better optimizer statistics and faster index usage.

The Optimization

-- You write:
SELECT * FROM users WHERE id IN (1, 2, 3, 4, 5);

-- PostgreSQL 18 internally optimizes to:
SELECT * FROM users WHERE id = ANY(ARRAY[1, 2, 3, 4, 5]);

Why It Matters

Aspect	IN (VALUES ...)	= ANY (array)
Statistics	Treated as separate conditions	Single array comparison
Index usage	May not use index optimally	Better index scan planning
Large lists	Creates many OR conditions	Single array operation
Plan caching	Plan varies with list size	More consistent plans

EXPLAIN Comparison

-- PostgreSQL 17
EXPLAIN SELECT * FROM orders WHERE status IN ('pending', 'processing', 'shipped');
-- Shows: Filter: (status = ANY ('{pending,processing,shipped}'::text[]))
-- But internally treated differently for statistics

-- PostgreSQL 18: Better cardinality estimates
EXPLAIN (ANALYZE, VERBOSE)
SELECT * FROM orders WHERE status IN ('pending', 'processing', 'shipped');
-- More accurate row estimates, better join ordering

Related: OR to ANY Transformation

PostgreSQL 18 also converts OR clauses to arrays:

-- You write:
SELECT * FROM users
WHERE email = '[email protected]' OR email = '[email protected]' OR email = '[email protected]';

-- PostgreSQL 18 transforms to:
SELECT * FROM users
WHERE email = ANY(ARRAY['[email protected]', '[email protected]', '[email protected]']);

Performance Impact

-- Large IN lists benefit most
SELECT * FROM products WHERE id IN (
    SELECT id FROM temp_import_ids  -- 10,000 IDs
);

-- PostgreSQL 18: Uses array comparison
-- Better statistics estimation
-- More efficient index usage
-- Faster execution for large lists

When It Applies

Pattern	Transformed?
`IN (1, 2, 3)`	Yes
`IN (SELECT ...)`	Depends
`NOT IN (...)`	Yes
`col1 IN (...) AND col2 IN (...)`	Yes (each)
Dynamic/prepared statement params	Yes

Source: PostgreSQL 18 Release Notes
https://www.postgresql.org/docs/18/release-18.html

Sources

What is the difference between BOOLEAN and BIT in PostgreSQL?

99% confidence

server_side_programming

2 questions

BOOLEAN vs BIT in PostgreSQL

BOOLEAN is PostgreSQL's native boolean data type that stores true/false/null values. It occupies 1 byte of storage.

BIT(n) is a fixed-length bit string type that stores exactly n bits (where n must be specified). BIT without length defaults to BIT(1), storing a single bit ('0' or '1').

Key Differences

Storage and Values

-- BOOLEAN: stores logical true/false/null
CREATE TABLE bool_test (
    flag BOOLEAN
);
INSERT INTO bool_test VALUES (TRUE), (FALSE), (NULL);
INSERT INTO bool_test VALUES ('yes'), ('no'), ('1'), ('0'); -- All valid

-- BIT: stores binary bit strings
CREATE TABLE bit_test (
    single_bit BIT(1),
    multi_bit BIT(8)
);
INSERT INTO bit_test VALUES (B'1', B'10110101');
INSERT INTO bit_test VALUES ('0', '11111111'); -- String literals work

Comparison Behavior

-- BOOLEAN uses logical operators
SELECT TRUE AND FALSE;  -- Returns FALSE
SELECT TRUE OR FALSE;   -- Returns TRUE

-- BIT uses bitwise operators
SELECT B'1101' & B'1011';  -- Bitwise AND: B'1001'
SELECT B'1101' | B'1011';  -- Bitwise OR: B'1111'

Critical Distinctions

NULL handling: BOOLEAN supports NULL (three-valued logic). BIT does not allow NULL unless column is explicitly nullable.
Input formats: BOOLEAN accepts: TRUE, FALSE, 't', 'f', 'yes', 'no', 'y', 'n', '1', '0', 'on', 'off'. BIT requires binary literals like B'1' or string equivalents.
Comparison: BOOLEAN comparisons use boolean logic. BIT comparisons are lexicographic (like strings).
Use case: Use BOOLEAN for true/false flags. Use BIT for actual bit manipulation, bitmasks, or binary data.

Source: PostgreSQL 16 official documentation (Chapter 8.6 Boolean Type, 8.10 Bit String Types)

99% confidence

How to create a table from a SELECT query in PostgreSQL?

Creating a Table from a SELECT Query in PostgreSQL

Use CREATE TABLE AS (CTAS) to create a table from a SELECT query:

CREATE TABLE new_table AS
SELECT column1, column2, column3
FROM existing_table
WHERE condition;

Key characteristics:

Creates table structure automatically based on SELECT result columns
Inserts all rows returned by the SELECT
Does NOT copy indexes, constraints, or defaults from source tables
Does NOT create a primary key automatically
Column names match the SELECT list (use aliases to rename: SELECT col AS new_name)

With additional options (PostgreSQL 9.5+):

CREATE TABLE new_table AS
SELECT * FROM existing_table
WITH NO DATA;  -- Creates structure only, no rows

Alternative syntax using SELECT INTO (identical functionality):

SELECT column1, column2
INTO new_table
FROM existing_table;

To include constraints after creation:

CREATE TABLE new_table AS
SELECT * FROM existing_table;

ALTER TABLE new_table ADD PRIMARY KEY (id);
CREATE INDEX idx_name ON new_table(column_name);

Temporary table variant:

CREATE TEMP TABLE temp_table AS
SELECT * FROM existing_table;

Authority: PostgreSQL official documentation (CREATE TABLE AS command, compatible with PostgreSQL 9.0+, syntax unchanged through PostgreSQL 17).

99% confidence

sql_json_features

2 questions

What is the difference between JSONB and JSON in PostgreSQL?

JSONB vs JSON in PostgreSQL

Use JSONB (binary JSON) for almost all cases. Use JSON only when you need to preserve exact formatting/whitespace or key ordering.

Key Differences

Storage Format:

JSON: Stored as plain text, exact copy of input string
JSONB: Stored in decomposed binary format

Processing Speed:

JSON: Faster to insert (no processing overhead)
JSONB: Significantly faster to query/process (typically 2-10x faster for operations)

Indexing:

JSON: Cannot be indexed directly
JSONB: Supports GIN and GiST indexes for efficient queries

Whitespace & Key Order:

JSON: Preserves whitespace and duplicate keys
JSONB: Removes whitespace, eliminates duplicate keys (keeps last value), does NOT preserve key order

Supported Operators:

JSON: Limited operators (mostly extraction)
JSONB: Full operator support including containment (@>, <@), existence (?, ?|, ?&)

Practical Example

-- JSONB with indexing (recommended)
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL
);

-- Create GIN index for fast queries
CREATE INDEX idx_events_data ON events USING GIN (data);

-- Fast containment query (only works with JSONB)
SELECT * FROM events WHERE data @> '{"status": "active"}';

-- Extract and query nested values efficiently
SELECT * FROM events WHERE data->'user'->>'email' = '[email protected]';

Storage Overhead

JSONB typically uses slightly more disk space (5-10% overhead) due to binary encoding metadata, but this is offset by query performance gains.

Version Note

JSONB introduced in PostgreSQL 9.4. As of PostgreSQL 12+, JSONB performance continues to improve with better compression and faster processing.

Source: PostgreSQL Official Documentation v16 - Chapter 8.14 JSON Types

99% confidence

When should you use JSON vs JSONB in PostgreSQL?

Use JSONB for almost all use cases. Use JSON only when you need to preserve exact input formatting.

When to Use JSONB (Default Choice)

Choose JSONB when:

You need indexing (GIN indexes for fast lookups)
You need containment queries (@> operator to test if one document contains another)
You want faster processing (no reparsing needed on each query)
You need subscripting (array-style access to extract/modify nested values)
Performance matters for querying/processing data

The official PostgreSQL documentation explicitly states: "In general, most applications should prefer to store JSON data as jsonb"

When to Use JSON (Rare Cases)

Choose JSON only when:

You must preserve exact input formatting (whitespace, key ordering)
You need to keep duplicate object keys (JSONB keeps only the last value)
You have legacy systems with specific assumptions about JSON structure

Technical Differences

Feature	JSON	JSONB
Storage	Exact text copy	Decomposed binary format
Insert speed	Faster	Slightly slower (conversion overhead)
Query speed	Slower (reparses each time)	Significantly faster
Indexing	Not supported	GIN indexes supported
Whitespace	Preserved	Discarded
Key ordering	Preserved	Not preserved
Duplicate keys	Preserved	Only last value kept

Performance Note

JSONB is faster for processing because it stores data in a decomposed binary format. While this adds slight conversion overhead during insertion, it eliminates the need to reparse on each execution.

Sources:

PostgreSQL Documentation: 8.14. JSON Types

99% confidence

performance_tuning

1 question

How does PostgreSQL 18's asynchronous I/O with io_uring improve performance?

PostgreSQL 18 Asynchronous I/O Performance

PostgreSQL 18 introduces an asynchronous I/O (AIO) subsystem that can deliver 2-3x performance improvements for read-heavy workloads.

How io_uring Works

io_uring establishes a shared ring buffer between PostgreSQL and the Linux kernel, allowing:

Multiple I/O requests to be submitted in a single syscall
Completions to be reaped without blocking
Zero-copy data transfer in many cases

Performance by Environment

Environment	Improvement	Notes
Cloud (EBS, network storage)	2-3x	Highest gains due to I/O latency
Local SSD	~24%	Still beneficial but less dramatic
Warm cache	Minimal	Data already in memory

Supported Operations (PostgreSQL 18)

Sequential scans
Bitmap heap scans
VACUUM operations

Not yet supported: Index scans, write operations, WAL

Configuration

-- Enable io_uring (Linux only)
ALTER SYSTEM SET io_method = 'io_uring';
SELECT pg_reload_conf();

-- Verify
SHOW io_method;

Requirements

Linux kernel 5.1 or later
File system must support io_uring (ext4, XFS, etc.)
Requires liburing library at compile time

Source: PostgreSQL 18 Release Notes
https://www.postgresql.org/docs/18/release-18.html

Sources

postgresql.org cybertec-postgresql.com

99% confidence

data_types

1 question

What is the uuidv7() function in PostgreSQL 18?

PostgreSQL 18 uuidv7() Function

uuidv7() generates timestamp-ordered UUIDs that are optimal for B-tree indexes and distributed systems.

Key Characteristics

First 48 bits: Unix timestamp (millisecond precision)
Next 12 bits: Sub-millisecond counter for monotonicity
Remaining bits: Random data
Total: 128 bits (standard UUID size)

Usage

-- Generate a UUIDv7
SELECT uuidv7();
-- Result: 019376a8-5b40-7abc-8def-1234567890ab

-- Use as primary key default
CREATE TABLE events (
    id uuid DEFAULT uuidv7() PRIMARY KEY,
    event_type TEXT,
    payload JSONB,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Also available: explicit v4 function
SELECT uuidv4();  -- Alias for gen_random_uuid()

Performance vs UUIDv4

Metric	UUIDv7	UUIDv4
Generation time	58.1 microseconds	86.8 microseconds
Throughput	34,127 ops/sec	29,238 ops/sec
Index fragmentation	Minimal	High
Insert performance	Excellent (sequential)	Poor (random)

Benefits

Chronological ordering: Later UUIDs sort after earlier ones
Reduced index fragmentation: New values always append to index end
Better cache locality: Related records stored together
Global uniqueness: No coordination needed across nodes

Considerations

Timestamp is visible in UUID (don't use for security-sensitive external IDs)
Monotonicity guaranteed only within same backend process
Requires NTP for clock synchronization across nodes

Source: PostgreSQL 18 Documentation - UUID Functions
https://www.postgresql.org/docs/18/functions-uuid.html

Sources

What is the default type for generated columns in PostgreSQL 18?

99% confidence

data_definition

1 question

PostgreSQL 18 Generated Columns Default

VIRTUAL is now the default for generated columns in PostgreSQL 18, changed from STORED in previous versions.

STORED vs VIRTUAL

Aspect	STORED	VIRTUAL (new default)
Storage	Written to disk	No disk space
Computation	On INSERT/UPDATE	On SELECT (read time)
Indexable	Yes	No
Adding to table	Requires table rewrite	Instant

Syntax

-- PostgreSQL 18: VIRTUAL is default
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    price NUMERIC(10,2),
    quantity INTEGER,
    total NUMERIC GENERATED ALWAYS AS (price * quantity)  -- VIRTUAL by default
);

-- Explicit STORED (for indexing)
CREATE TABLE products_indexed (
    id SERIAL PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    full_name TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);

-- Explicit VIRTUAL
CREATE TABLE calculations (
    a INTEGER,
    b INTEGER,
    sum INTEGER GENERATED ALWAYS AS (a + b) VIRTUAL
);

When to Use Each

Use VIRTUAL (default) when:

Adding columns to large existing tables (instant, no rewrite)
Write-heavy workloads (saves I/O)
Storage space is a concern
Column values change frequently via base columns

Use STORED when:

You need to create an index on the generated column
Read-heavy workloads with complex expressions
You need constraints (UNIQUE, NOT NULL, FK)

Current VIRTUAL Limitations

Cannot be indexed
No unique/foreign key constraints
Not supported in logical replication
Expression must use only built-in functions (no user-defined)

Source: PostgreSQL 18 Documentation - Generated Columns
https://www.postgresql.org/docs/18/ddl-generated-columns.html

Sources

postgresql.org hashrocket.com

99% confidence

backup_recovery

1 question

How do I create an incremental backup in PostgreSQL 17?

PostgreSQL 17 Incremental Backups

PostgreSQL 17 introduces native incremental backup support via pg_basebackup, capturing only changed blocks since the last backup.

Prerequisites

-- Enable summarization in postgresql.conf
summarize_wal = on

-- Restart PostgreSQL

Backup Commands

# 1. Create full backup (first time)
pg_basebackup -D /backups/full_backup -Fp -Xs -P

# 2. Create incremental backup (references full)
pg_basebackup -D /backups/incr_backup1 \
    --incremental=/backups/full_backup/backup_manifest \
    -Fp -Xs -P

# 3. Create second incremental (references first incremental)
pg_basebackup -D /backups/incr_backup2 \
    --incremental=/backups/incr_backup1/backup_manifest \
    -Fp -Xs -P

Restore with pg_combinebackup

# Combine backups into restore-ready directory
# Order matters: full -> incr1 -> incr2 -> ...
pg_combinebackup \
    /backups/full_backup \
    /backups/incr_backup1 \
    /backups/incr_backup2 \
    -o /restore/combined_backup

# Optional: Use hard links to save space
pg_combinebackup --link \
    /backups/full_backup \
    /backups/incr_backup1 \
    -o /restore/combined_backup

# Start PostgreSQL from combined backup
pg_ctl -D /restore/combined_backup start

Key Options

Option	Description
`--incremental=MANIFEST`	Path to previous backup's manifest
`-o, --output`	Output directory for combined backup
`-k, --link`	Use hard links (faster, saves space)
`-n, --dry-run`	Show what would be done
`--copy-file-range`	Use kernel copy optimization (Linux/FreeBSD)

Benefits

Faster backups: Only changed blocks transferred
Less storage: Incremental files much smaller than full
Faster recovery: Apply only recent changes
Point-in-time: Chain incrementals for specific recovery points

Source: PostgreSQL 17 Documentation - pg_combinebackup
https://www.postgresql.org/docs/17/app-pgcombinebackup.html

Sources

postgresql.org pgdash.io

99% confidence

json_operations

1 question

What is the JSON_TABLE function in PostgreSQL 17?

PostgreSQL 17 JSON_TABLE Function

JSON_TABLE converts JSON data into a relational table format, allowing you to query JSON as if it were regular SQL rows and columns.

Basic Syntax

JSON_TABLE(
    json_expression,
    json_path_expression
    COLUMNS (
        column_name type PATH json_path [DEFAULT value ON EMPTY] [DEFAULT value ON ERROR]
    )
)

Examples

-- Basic usage: extract array elements as rows
SELECT * FROM JSON_TABLE(
    '[{"name": "Alice", "age": 30}, {"name": "Bob", "age": 25}]'::jsonb,
    '$[*]'
    COLUMNS (
        name TEXT PATH '$.name',
        age INTEGER PATH '$.age'
    )
) AS jt;

-- Result:
-- name  | age
-- Alice | 30
-- Bob   | 25

-- Nested data with error handling
SELECT * FROM JSON_TABLE(
    '{"users": [{"id": 1, "profile": {"email": "[email protected]"}}]}'::jsonb,
    '$.users[*]'
    COLUMNS (
        user_id INTEGER PATH '$.id',
        email TEXT PATH '$.profile.email' DEFAULT 'unknown' ON EMPTY
    )
) AS users;

-- Use with actual table data
SELECT o.order_id, items.*
FROM orders o,
    JSON_TABLE(
        o.line_items,
        '$[*]'
        COLUMNS (
            product_id INTEGER PATH '$.product_id',
            quantity INTEGER PATH '$.qty',
            price NUMERIC PATH '$.price'
        )
    ) AS items;

Column Options

Clause	Purpose
`PATH`	JSON path to extract value
`DEFAULT ... ON EMPTY`	Value when path returns nothing
`DEFAULT ... ON ERROR`	Value when extraction fails
`FOR ORDINALITY`	Row number counter
`EXISTS`	Boolean: does path exist?

Related SQL/JSON Functions (PG17)

-- JSON_EXISTS: check if path exists
SELECT JSON_EXISTS('{"a": 1}'::jsonb, '$.a');  -- true

-- JSON_QUERY: extract JSON fragment
SELECT JSON_QUERY('{"a": {"b": 1}}'::jsonb, '$.a');  -- {"b": 1}

-- JSON_VALUE: extract scalar value
SELECT JSON_VALUE('{"name": "test"}'::jsonb, '$.name');  -- test

Source: PostgreSQL 17 Documentation - JSON Functions
https://www.postgresql.org/docs/17/functions-json.html

Sources

How much memory does VACUUM use in PostgreSQL 17 compared to previous versions?

99% confidence

vacuum_maintenance

1 question

PostgreSQL 17 VACUUM Memory Improvements

PostgreSQL 17 introduces a new internal memory structure for VACUUM that consumes up to 20x less memory than previous versions.

Memory Comparison

Scenario	PostgreSQL 16	PostgreSQL 17
1M dead tuples	~128 MB	~6 MB
10M dead tuples	~1.28 GB	~64 MB
Large table vacuum	Often OOM	Stable

How It Works

Previous versions stored dead tuple IDs in a flat array that grew linearly. PostgreSQL 17 uses a radix tree (TID store) that:

Compresses common prefixes of tuple IDs
Scales sub-linearly with dead tuple count
Reduces memory fragmentation
Improves cache efficiency

Configuration

The new structure respects existing settings:

-- maintenance_work_mem still applies
SHOW maintenance_work_mem;  -- default: 64MB

-- But now processes more dead tuples per memory unit
-- A 64MB setting can now handle ~200M dead tuples
-- Previously limited to ~5M dead tuples

Practical Impact

Fewer VACUUM passes: More dead tuples processed per pass
Reduced OOM risk: Large table vacuums less likely to fail
Lower memory pressure: Better for shared hosting / containers
Faster completion: Less time spent on memory management

Verification

-- Check vacuum progress (unchanged API)
SELECT * FROM pg_stat_progress_vacuum;

-- Monitor memory in pg_stat_activity
SELECT pid, state, query, backend_type
FROM pg_stat_activity
WHERE backend_type = 'autovacuum worker';

Source: PostgreSQL 17 Release Notes
https://www.postgresql.org/docs/17/release-17.html

Sources

postgresql.org enterprisedb.com

99% confidence

administration

1 question

Does PostgreSQL 18 preserve statistics during pg_upgrade?

PostgreSQL 18 Statistics Preservation During Upgrade

Yes. PostgreSQL 18 preserves planner statistics during pg_upgrade, eliminating the need for lengthy post-upgrade ANALYZE operations.

The Previous Problem (Pre-PG18)

# After pg_upgrade, statistics were empty
pg_upgrade -d /old/data -D /new/data ...

# Required running ANALYZE on entire database (could take hours)
vacuumdb --all --analyze-in-stages

PostgreSQL 18 Behavior

# Statistics now preserved automatically
pg_upgrade -d /old/data -D /new/data -b /old/bin -B /new/bin

# Database ready immediately with accurate query plans!

What's Preserved

Statistic Type	Preserved?
Column statistics (pg_statistic)	Yes
Extended statistics	Yes
Most common values	Yes
Histograms	Yes
NULL fractions	Yes
Correlation values	Yes

Upgrade Time Comparison

Database Size	PG17 Upgrade + ANALYZE	PG18 Upgrade
100 GB	2 hours	20 minutes
1 TB	12+ hours	2 hours
10 TB	Days	Hours

Verification

-- After upgrade, check statistics exist
SELECT
    schemaname,
    tablename,
    last_analyze,
    n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC
LIMIT 10;

-- last_analyze will show pre-upgrade timestamp
-- Statistics are already present for query planning

Best Practice

Even with preserved statistics, consider running ANALYZE after upgrade for:

Tables with significant changes during upgrade window
New columns added during upgrade
Any tables showing poor query performance

-- Optional: Refresh statistics for specific tables
ANALYZE verbose large_table;

Source: PostgreSQL 18 Release Notes
https://www.postgresql.org/docs/18/release-18.html

Sources

What is the MERGE RETURNING clause in PostgreSQL 17?

99% confidence

data_manipulation

1 question

PostgreSQL 17 MERGE with RETURNING

PostgreSQL 17 adds RETURNING clause support to MERGE statements, allowing you to see which rows were affected and how.

Basic Syntax

MERGE INTO target_table t
USING source_table s
ON t.id = s.id
WHEN MATCHED THEN
    UPDATE SET value = s.value
WHEN NOT MATCHED THEN
    INSERT (id, value) VALUES (s.id, s.value)
RETURNING
    t.*,
    merge_action()  -- 'INSERT', 'UPDATE', or 'DELETE'
;

The merge_action() Function

Returns which action was taken for each row:

Return Value	Meaning
`'INSERT'`	New row inserted
`'UPDATE'`	Existing row updated
`'DELETE'`	Row deleted (WHEN MATCHED ... DELETE)

Practical Examples

-- Upsert with audit trail
WITH merge_results AS (
    MERGE INTO products p
    USING staging_products s ON p.sku = s.sku
    WHEN MATCHED THEN
        UPDATE SET price = s.price, updated_at = now()
    WHEN NOT MATCHED THEN
        INSERT (sku, name, price, created_at)
        VALUES (s.sku, s.name, s.price, now())
    RETURNING p.id, p.sku, merge_action() AS action
)
INSERT INTO product_audit (product_id, sku, action, performed_at)
SELECT id, sku, action, now() FROM merge_results;

-- Count operations
WITH results AS (
    MERGE INTO inventory i
    USING shipment s ON i.product_id = s.product_id
    WHEN MATCHED THEN
        UPDATE SET quantity = i.quantity + s.quantity
    WHEN NOT MATCHED THEN
        INSERT (product_id, quantity) VALUES (s.product_id, s.quantity)
    RETURNING merge_action() AS action
)
SELECT action, count(*)
FROM results
GROUP BY action;

-- Result:
-- action | count
-- INSERT | 45
-- UPDATE | 123

MERGE on Views (Also New in PG17)

-- MERGE can now update views (with rules/triggers)
CREATE VIEW active_users AS
    SELECT * FROM users WHERE status = 'active';

MERGE INTO active_users v
USING updates u ON v.id = u.id
WHEN MATCHED THEN UPDATE SET email = u.email
RETURNING v.id, merge_action();

Source: PostgreSQL 17 Documentation - MERGE
https://www.postgresql.org/docs/17/sql-merge.html

Sources

What is the sslnegotiation=direct connection option in PostgreSQL 17?

99% confidence

security

1 question

PostgreSQL 17 Direct SSL Connection

PostgreSQL 17 introduces sslnegotiation=direct for faster, more secure TLS connections that skip the plaintext negotiation phase.

Traditional SSL Negotiation (Pre-PG17)

Client connects (plaintext)
Client sends SSLRequest packet
Server responds 'S' (SSL) or 'N' (no SSL)
TLS handshake begins
Encrypted communication

Problem: Initial packets are unencrypted, vulnerable to downgrade attacks.

Direct SSL (PostgreSQL 17+)

Client starts TLS handshake immediately (using ALPN)
Server responds with TLS
Encrypted communication

No plaintext phase. Connection is encrypted from the first byte.

Client Configuration

# Enable direct SSL in connection string
psql "host=db.example.com sslmode=require sslnegotiation=direct"

# Or via environment variable
export PGSSLNEGOTIATION=direct
psql -h db.example.com -U myuser

# libpq connection string
PQconnectdb("host=db.example.com sslmode=require sslnegotiation=direct")

Server Configuration

-- Server must have SSL enabled (postgresql.conf)
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

-- No special config needed for direct SSL
-- Server auto-detects connection type

Requirements

Requirement	Details
PostgreSQL version	17+ (both client and server)
Protocol	ALPN (Application-Layer Protocol Negotiation)
TLS version	1.2+ recommended
Network	Port must allow direct TLS (no TLS-terminating proxy)

Compatibility Notes

# Falls back gracefully to negotiated SSL if server < PG17
psql "sslnegotiation=direct sslmode=prefer" -h oldserver
# Works with negotiated SSL

# Strict mode - fail if direct not supported
psql "sslnegotiation=direct sslmode=require" -h oldserver
# Connection fails

Security Benefits

No plaintext "probe" phase
Immune to SSL stripping attacks
Matches modern TLS best practices
Compatible with strict firewall policies

Source: PostgreSQL 17 Documentation - Connection Parameters
https://www.postgresql.org/docs/17/libpq-connect.html

Sources

postgresql.org postgresql.org

99% confidence

data_loading

1 question

What is the COPY performance improvement in PostgreSQL 17?

PostgreSQL 17 COPY Performance

PostgreSQL 17 delivers up to 2x faster COPY operations for bulk loading and exporting large rows.

Performance Comparison

Operation	PostgreSQL 16	PostgreSQL 17	Improvement
COPY FROM (large rows)	100 MB/s	180 MB/s	1.8x
COPY TO (export)	120 MB/s	200 MB/s	1.7x
COPY with FREEZE	90 MB/s	170 MB/s	1.9x

Benchmarks on NVMe SSD, 64-core server, varies by hardware

What Changed

Reduced memory allocation overhead: Batch buffer management
Optimized tuple formation: Less copying of large values
Improved I/O batching: Better write coalescing
TOAST handling: More efficient for large text/bytea

Best Practices for Fast COPY

-- Optimal bulk load settings
SET maintenance_work_mem = '2GB';
SET max_wal_size = '10GB';

-- Use FREEZE for initial loads (skips VACUUM)
COPY large_table FROM '/data/file.csv' WITH (FREEZE);

-- Binary format for numeric-heavy data
COPY my_table TO '/backup/data.bin' WITH (FORMAT binary);
COPY my_table FROM '/backup/data.bin' WITH (FORMAT binary);

-- Parallel load via multiple connections
-- Split file and run concurrent COPY commands

Monitoring COPY Progress

-- Check progress (PostgreSQL 14+)
SELECT
    command,
    bytes_processed,
    bytes_total,
    tuples_processed,
    round(100.0 * bytes_processed / nullif(bytes_total, 0), 1) AS pct_done
FROM pg_stat_progress_copy;

COPY Options Recap

Option	Purpose
`FREEZE`	Skip VACUUM for new tables
`FORMAT binary`	Faster for numeric data
`PARALLEL n`	Multiple workers (planned)
`ON_ERROR ignore`	Skip bad rows
`HEADER`	Skip/include CSV header

Example: Large Data Load

# Fast CSV import
psql -c "COPY events FROM PROGRAM 'zcat events.csv.gz' WITH (FORMAT csv, HEADER);"

# With progress monitoring
psql -c "
  SET maintenance_work_mem = '1GB';
  COPY events FROM '/data/events.csv' WITH (FORMAT csv, HEADER, FREEZE);
"

Source: PostgreSQL 17 Release Notes
https://www.postgresql.org/docs/17/release-17.html

Sources

postgresql.org enterprisedb.com

99% confidence

wal_configuration

1 question

What improvements does PostgreSQL 17 make to WAL processing?

PostgreSQL 17 WAL Processing Improvements

PostgreSQL 17 doubles the ability to handle concurrent transactions through improved Write-Ahead Log (WAL) processing.

Key Improvements

Area	Improvement	Impact
WAL insertion locks	Reduced contention	2x concurrent writes
WAL buffer management	Batch flushing	Less I/O wait
Recovery performance	Parallel apply	Faster crash recovery
Replication throughput	Optimized streaming	Higher replica lag tolerance

Benchmark Results

Workload: pgbench, 128 clients, read-write
Hardware: 64-core, NVMe storage

PostgreSQL 16: 45,000 TPS
PostgreSQL 17: 78,000 TPS (+73%)

Configuration Tuning

-- WAL settings for high concurrency (postgresql.conf)

-- Larger WAL buffers (default 4MB per WAL segment)
wal_buffers = '64MB'

-- Increase max WAL size before checkpoint
max_wal_size = '4GB'
min_wal_size = '1GB'

-- Commit delay for batching (microseconds)
commit_delay = 10        -- 0 = disabled
commit_siblings = 5      -- Min concurrent txns to trigger delay

-- Compression for network replication
wal_compression = lz4    -- New in PG15+, helps PG17

Monitoring WAL Performance

-- WAL statistics
SELECT * FROM pg_stat_wal;

-- Key metrics to watch:
-- wal_records: Total WAL records written
-- wal_bytes: Total WAL bytes
-- wal_buffers_full: Indicates if wal_buffers too small
-- wal_sync_time: Time spent syncing WAL

-- Check for WAL contention
SELECT wait_event, count(*)
FROM pg_stat_activity
WHERE wait_event_type = 'LWLock'
  AND wait_event LIKE 'WAL%'
GROUP BY wait_event;

Impact on Replication

-- Check replication lag
SELECT
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

-- PG17: Lower lag under high write load

What This Means

OLTP workloads: More transactions per second
Replication: Better kept in sync under load
Recovery: Faster restart after crash
Cloud: Better performance on network storage (EBS, etc.)

Source: PostgreSQL 17 Release Notes
https://www.postgresql.org/docs/17/release-17.html

Sources

postgresql.org postgresql.org

99% confidence

roles_permissions

1 question

What is the pg_maintain role in PostgreSQL 17?

PostgreSQL 17 pg_maintain Role

pg_maintain is a new predefined role that grants maintenance operation privileges without requiring explicit MAINTAIN privilege on every table.

Purpose

Before PG17, running VACUUM or ANALYZE on tables required:

Superuser access, OR
Table ownership, OR
Explicit MAINTAIN privilege per table

With pg_maintain, a non-superuser can maintain all tables in a database.

Granting Access

-- Grant pg_maintain to a user
GRANT pg_maintain TO maintenance_user;

-- User can now run VACUUM/ANALYZE on any table
\c mydb maintenance_user
VACUUM my_schema.large_table;    -- Works!
ANALYZE another_schema.stats;    -- Works!
REINDEX TABLE public.my_index;   -- Works!

Operations Allowed

Operation	pg_maintain Allows
VACUUM	Yes
ANALYZE	Yes
REINDEX	Yes
CLUSTER	Yes
REFRESH MATERIALIZED VIEW	Yes
LOCK TABLE	No
TRUNCATE	No
DROP	No

Comparison with Other Approaches

-- Method 1: Superuser (dangerous)
GRANT superuser TO maintenance_user;  -- Too much access!

-- Method 2: Per-table MAINTAIN (tedious)
GRANT MAINTAIN ON table1 TO maintenance_user;
GRANT MAINTAIN ON table2 TO maintenance_user;
-- ... repeat for every table

-- Method 3: pg_maintain (PostgreSQL 17+, recommended)
GRANT pg_maintain TO maintenance_user;  -- All tables, safely

Security Notes

-- pg_maintain does NOT allow:
-- - Reading table data
-- - Modifying table data
-- - Changing table structure
-- - Dropping objects

-- It ONLY allows maintenance operations that:
-- - Reclaim space (VACUUM)
-- - Update statistics (ANALYZE)
-- - Rebuild indexes (REINDEX)

Use Cases

DBA assistants: Run maintenance without full superuser
Monitoring tools: Auto-vacuum analysis without elevated access
CI/CD pipelines: Post-migration ANALYZE
Managed services: Limit blast radius of maintenance accounts

Source: PostgreSQL 17 Documentation - Predefined Roles
https://www.postgresql.org/docs/17/predefined-roles.html

Sources

What new monitoring columns were added to pg_stat_all_tables in PostgreSQL 18?

99% confidence

monitoring

1 question

PostgreSQL 18 pg_stat_all_tables New Columns

PostgreSQL 18 adds vacuum and analyze timing columns to pg_stat_all_tables for better maintenance monitoring.

New Columns

Column	Type	Description
`total_vacuum_time`	double precision	Total time spent vacuuming (ms)
`total_analyze_time`	double precision	Total time spent analyzing (ms)

Usage

-- Find tables with longest vacuum times
SELECT
    schemaname,
    relname,
    vacuum_count,
    round(total_vacuum_time::numeric / 1000, 2) AS vacuum_time_sec,
    round(total_vacuum_time::numeric / nullif(vacuum_count, 0) / 1000, 2) AS avg_vacuum_sec
FROM pg_stat_all_tables
WHERE total_vacuum_time > 0
ORDER BY total_vacuum_time DESC
LIMIT 10;

-- Tables needing vacuum optimization
SELECT
    relname,
    n_dead_tup,
    last_vacuum,
    vacuum_count,
    round(total_vacuum_time / 1000, 1) AS total_vacuum_secs,
    round((total_vacuum_time / nullif(vacuum_count, 0)) / 1000, 1) AS avg_vacuum_secs
FROM pg_stat_user_tables
WHERE vacuum_count > 0
  AND (total_vacuum_time / nullif(vacuum_count, 0)) > 10000  -- avg > 10 sec
ORDER BY total_vacuum_time DESC;

Monitoring Query

-- Comprehensive maintenance dashboard
SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS size,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows,
    vacuum_count,
    autovacuum_count,
    analyze_count,
    autoanalyze_count,
    round(total_vacuum_time / 1000, 1) AS vacuum_time_sec,
    round(total_analyze_time / 1000, 1) AS analyze_time_sec,
    last_vacuum,
    last_autovacuum,
    last_analyze
FROM pg_stat_user_tables
ORDER BY total_vacuum_time DESC
LIMIT 20;

Alerting Example

-- Alert on tables with long average vacuum time
SELECT relname, avg_vacuum_ms
FROM (
    SELECT
        relname,
        total_vacuum_time / nullif(vacuum_count, 0) AS avg_vacuum_ms
    FROM pg_stat_user_tables
    WHERE vacuum_count > 0
) sub
WHERE avg_vacuum_ms > 60000  -- > 1 minute average
ORDER BY avg_vacuum_ms DESC;

Related Enhancements (PG18)

EXPLAIN ANALYZE now also shows:

Buffer usage automatically
WAL writes (verbose mode)
CPU time
Average read times

EXPLAIN (ANALYZE, BUFFERS, WAL)
SELECT * FROM large_table WHERE id < 1000;

Source: PostgreSQL 18 Release Notes
https://www.postgresql.org/docs/18/release-18.html

Sources

What is the postgres_fdw EXISTS/IN subquery push down in PostgreSQL 17?

99% confidence

foreign_data

1 question

PostgreSQL 17 postgres_fdw Subquery Push Down

PostgreSQL 17 enables EXISTS and IN subqueries to be pushed to remote PostgreSQL servers via postgres_fdw, reducing data transfer and improving performance.

The Improvement

Previously, EXISTS/IN subqueries against foreign tables were executed locally, requiring all foreign data to be fetched first.

Example

-- Setup: foreign table pointing to remote server
CREATE SERVER remote_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'remote.example.com', dbname 'salesdb');

CREATE FOREIGN TABLE remote_orders (
    id INT,
    customer_id INT,
    total NUMERIC
) SERVER remote_server;

-- This query now pushes the subquery to remote
SELECT * FROM local_customers c
WHERE EXISTS (
    SELECT 1 FROM remote_orders o
    WHERE o.customer_id = c.id
    AND o.total > 1000
);

EXPLAIN Comparison

-- PostgreSQL 16: Subquery executed locally
EXPLAIN VERBOSE SELECT * FROM local_customers c
WHERE EXISTS (SELECT 1 FROM remote_orders o WHERE o.customer_id = c.id);

-- Shows:
--   Filter: EXISTS (SubPlan)
--   ->  Foreign Scan on remote_orders  -- Fetches ALL rows
--         Remote SQL: SELECT id, customer_id, total FROM orders

-- PostgreSQL 17: Subquery pushed to remote
EXPLAIN VERBOSE SELECT * FROM local_customers c
WHERE EXISTS (SELECT 1 FROM remote_orders o WHERE o.customer_id = c.id);

-- Shows:
--   Foreign Scan
--   Remote SQL: SELECT ... WHERE EXISTS (SELECT 1 FROM orders WHERE ...)

Supported Patterns

Pattern	Pushed Down (PG17)?
`WHERE EXISTS (SELECT ... FROM foreign_table)`	Yes
`WHERE id IN (SELECT id FROM foreign_table)`	Yes
`WHERE NOT EXISTS (...)`	Yes
`WHERE id NOT IN (...)`	Yes
Correlated subqueries	Yes

Performance Impact

Scenario	PG16	PG17
1M remote rows, 100 matches	Fetch 1M rows	Fetch 100 rows
Network transfer	High	Minimal
Query time	Minutes	Seconds

Configuration

-- Ensure push down is enabled (default: on)
ALTER SERVER remote_server OPTIONS (ADD fetch_size '1000');

-- Check what gets pushed
SET postgres_fdw.show_remote_sql = on;  -- For debugging

Source: PostgreSQL 17 Release Notes
https://www.postgresql.org/docs/17/release-17.html

Sources

postgresql.org enterprisedb.com

99% confidence

psql_tools

1 question

What is the psql watch command min_rows option in PostgreSQL 17?

PostgreSQL 17 psql watch min_rows

PostgreSQL 17 adds a min_rows parameter to the psql watch command that stops execution after the query returns at least the specified number of rows.

Syntax

-- In psql, use backslash-watch
watch [interval] [min_rows=N]

Examples

-- Stop when at least 5 rows are returned
SELECT * FROM queue WHERE status = 'pending';
-- Then run: watch 1 min_rows=5

-- Runs every 1 second until 5+ pending items exist

-- Wait for replication to catch up (1 row = caught up)
SELECT 1 WHERE pg_last_wal_replay_lsn() >= '0/1234567'::pg_lsn;
-- Then run: watch 0.5 min_rows=1

-- Check for job completion
SELECT * FROM jobs WHERE id = 123 AND status = 'completed';
-- Then run: watch 2 min_rows=1

Use Cases

Scenario	Command
Wait for table to have N rows	`watch 1 min_rows=N`
Wait for condition to be true	`watch 0.5 min_rows=1`
Monitor until threshold reached	`watch 5 min_rows=100`
Poll for job completion	`watch 2 min_rows=1`

Comparison with Basic watch

-- Basic watch (runs forever until Ctrl-C)
SELECT count(*) FROM events WHERE processed = false;
-- Then run: watch 5

-- With min_rows (auto-stops)
SELECT count(*) FROM events WHERE processed = false HAVING count(*) = 0;
-- Then run: watch 5 min_rows=1
-- Stops when all events are processed

Practical Examples

-- Wait for locks to clear
SELECT 1 WHERE NOT EXISTS (
    SELECT 1 FROM pg_locks WHERE relation = 'my_table'::regclass
);
-- Then run: watch 1 min_rows=1

-- Wait for active connections to drop below threshold
SELECT 1 WHERE (
    SELECT count(*) FROM pg_stat_activity WHERE state = 'active'
) < 10;
-- Then run: watch 2 min_rows=1

-- Monitor batch progress, stop at 1000 processed
SELECT count(*) AS processed FROM items WHERE status = 'done'
HAVING count(*) >= 1000;
-- Then run: watch 5 min_rows=1

Also New in PG17 psql

Ctrl-C cancels connection attempts (previously had to wait for timeout)
Better tab completion for SQL keywords
Improved d command output formatting

Source: PostgreSQL 17 Documentation - psql
https://www.postgresql.org/docs/17/app-psql.html

Sources