postgres-extensions-cli

name

postgres-extensions-cli

description

Discover, install, configure, and use PostgreSQL extensions on Azure Database for PostgreSQL Flexible Server via CLI. Use when the user wants to: (1) list available extensions, (2) install or upgrade extensions, (3) use pg_stat_statements for query profiling, (4) schedule jobs with pg_cron, (5) use PostGIS for geospatial queries, (6) use pg_trgm for fuzzy text search, (7) set up auto_explain for automatic plan logging, (8) use pg_partman for automated partition management, (9) use pg_repack for online table repacking, (10) use azure_ai for AI/ML integration, (11) use azure_storage for cloud storage access, (12) use pg_diskann for scalable vector search, (13) set up pgaudit for compliance auditing, (14) use timescaledb for time-series data, (15) use hypopg for hypothetical index testing, (16) use pg_hint_plan to control query plans, (17) use wal2json for change data capture, (18) manage extension lifecycle and dependencies. Triggers: "postgres extension", "install extension", "available extensions", "pg_stat_statements setup", "pg_cron", "schedule job postgres", "PostGIS", "geospatial postgres", "pg_trgm", "fuzzy search postgres", "auto_explain", "pg_partman", "partition management", "pg_repack", "online repack", "extension list azure postgres", "enable extension", "azure_ai", "azure_storage", "pg_diskann", "DiskANN", "pgaudit", "audit postgres", "timescaledb", "time series postgres", "hypopg", "hypothetical index", "pg_hint_plan", "force plan postgres", "wal2json", "change data capture postgres", "pg_squeeze", "logical replication postgres".

Update Check — ONCE PER SESSION (mandatory) The first time this skill is used in a session, run the check-updates skill before proceeding.

• GitHub Copilot CLI / VS Code: invoke the check-updates skill.
• Claude Code / Cowork / Cursor / Windsurf / Codex: compare local vs remote package.json version.
• Skip if the check was already performed earlier in this session.

PostgreSQL Extensions — CLI Skill

Table of Contents

Task	Reference	Notes
Azure PostgreSQL Topology	COMMON-CORE.md § Azure PostgreSQL Topology
Authentication	COMMON-CLI.md § Authentication Recipes	Entra ID or native auth
Extensions Management	COMMON-CLI.md § Extensions Management	Enable and install

For pgvector (vector similarity search), see pgvector-authoring-cli.

---

Tool Stack

Tool	Role	Install
psql	Primary: Install extensions, execute extension-specific SQL.	apt-get install postgresql-client / brew install libpq / winget install PostgreSQL.psql
az CLI	Allow-list extensions on Azure, configure shared_preload_libraries.	Pre-installed in most dev environments
jq	Parse JSON from az commands	Pre-installed or trivial

Agent check — verify before first operation:

psql --version 2>/dev/null || echo "INSTALL: apt-get install postgresql-client OR brew install libpq"

---

Extension Lifecycle on Azure

Step 1: Check Available Extensions

-- List all extensions available on the server
SELECT name, default_version, comment
FROM pg_available_extensions
ORDER BY name;

Step 2: Allow Extension on Azure

# Allow one or more extensions (comma-separated)
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name azure.extensions \
  --value "pg_stat_statements,pg_cron,pg_trgm,postgis,auto_explain"

Step 3: Configure Preload Libraries (If Required)

Some extensions require shared_preload_libraries — this needs a server restart.

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name shared_preload_libraries \
  --value "pg_stat_statements,pg_cron,auto_explain"

# Restart required
az postgres flexible-server restart \
  --resource-group $RG --name $SERVER

Step 4: Install Extension in Database

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Check Installed Extensions

SELECT extname, extversion FROM pg_extension ORDER BY extname;

Upgrade an Extension

ALTER EXTENSION pg_stat_statements UPDATE;

---

Extension Quick Reference

Extension	Preload Required	Purpose
pg_stat_statements	Yes	Query performance profiling
pg_cron	Yes	Scheduled jobs inside PostgreSQL
auto_explain	Yes	Automatic EXPLAIN logging
pg_trgm	No	Trigram-based fuzzy text search
postgis	No	Geospatial data types and queries
pg_partman	Yes	Automated partition management
pg_repack	No	Online table and index repacking
pgcrypto	No	Cryptographic functions
uuid-ossp	No	UUID generation
hstore	No	Key-value store in a column
btree_gin	No	GIN index for scalar types
btree_gist	No	GiST index for scalar types
pg_buffercache	No	Inspect shared buffer cache
pg_prewarm	Yes	Preload tables into buffer cache
azure_ai	No	Azure AI and ML Services integration
azure_storage	Yes	Azure Blob Storage integration
pg_diskann	No	DiskANN scalable vector search index
pgaudit	Yes	SQL statement audit logging
timescaledb	Yes	Time-series data at scale
hypopg	No	Hypothetical indexes for plan testing
pg_hint_plan	Yes	Control query execution plans via hints
pg_squeeze	Yes	Online table compaction (minimal locks)
pglogical	Yes	Logical replication management
wal2json	Yes	Logical decoding output as JSON (CDC)
anon	Yes	Data anonymization and masking
citext	No	Case-insensitive text data type
ltree	No	Hierarchical tree-like data type
plv8	No	JavaScript (V8) procedural language
pgstattuple	No	Tuple-level statistics and bloat analysis
age	Yes	Graph database capabilities (Preview)

---

pg_stat_statements — Query Performance Profiling

Setup

# Allow and preload
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name azure.extensions --value "pg_stat_statements"

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name shared_preload_libraries --value "pg_stat_statements"

# Configure tracking
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name pg_stat_statements.track --value "all"

az postgres flexible-server restart \
  --resource-group $RG --name $SERVER

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Key Queries

-- Top 10 queries by total execution time
SELECT
    left(query, 120) AS query_preview,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS avg_ms,
    rows,
    round((shared_blks_hit::numeric / nullif(shared_blks_hit + shared_blks_read, 0)) * 100, 2) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Queries with most temp file usage (work_mem too low)
SELECT
    left(query, 120) AS query_preview,
    calls,
    temp_blks_written,
    round(mean_exec_time::numeric, 2) AS avg_ms
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;

---

pg_cron — Scheduled Jobs

Setup

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name azure.extensions --value "pg_cron"

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name shared_preload_libraries --value "pg_cron"

# pg_cron runs in the configured database (default: postgres)
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name cron.database_name --value "your-database"

az postgres flexible-server restart \
  --resource-group $RG --name $SERVER

CREATE EXTENSION IF NOT EXISTS pg_cron;

Schedule Jobs

-- Nightly VACUUM ANALYZE at 3 AM UTC
SELECT cron.schedule('nightly-vacuum', '0 3 * * *',
    $$VACUUM ANALYZE$$
);

-- Hourly partition maintenance
SELECT cron.schedule('hourly-partition-maint', '0 * * * *',
    $$SELECT partman.run_maintenance()$$
);

-- Every 5 minutes: refresh materialized view
SELECT cron.schedule('refresh-dashboard', '*/5 * * * *',
    $$REFRESH MATERIALIZED VIEW CONCURRENTLY dashboard_summary$$
);

-- Weekly: clean up old data
SELECT cron.schedule('weekly-cleanup', '0 2 * * 0',
    $$DELETE FROM audit_logs WHERE created_at < now() - interval '90 days'$$
);

-- Run a job in a different database
SELECT cron.schedule_in_database('vacuum-analytics', '0 4 * * *',
    $$VACUUM ANALYZE$$,
    'analytics_db'
);

Manage Jobs

-- List all scheduled jobs
SELECT jobid, schedule, command, nodename, nodeport, database, username, active
FROM cron.job ORDER BY jobid;

-- View job execution history
SELECT jobid, job_pid, status, return_message,
       start_time, end_time, end_time - start_time AS duration
FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 20;

-- Disable a job
SELECT cron.alter_job(job_id := 1, active := false);

-- Delete a job
SELECT cron.unschedule('nightly-vacuum');
-- or by ID
SELECT cron.unschedule(1);

---

PostGIS — Geospatial Data

Setup

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name azure.extensions --value "postgis"

CREATE EXTENSION IF NOT EXISTS postgis;

Create Geospatial Table

CREATE TABLE locations (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    geom geometry(Point, 4326),  -- WGS 84 coordinate system
    address text,
    created_at timestamptz NOT NULL DEFAULT now()
);

-- Spatial index
CREATE INDEX idx_locations_geom ON locations USING gist(geom);

Common Geospatial Queries

-- Insert a point (longitude, latitude)
INSERT INTO locations (name, geom, address)
VALUES ('Office', ST_SetSRID(ST_MakePoint(-122.3321, 47.6062), 4326), 'Seattle, WA');

-- Find locations within 5 km of a point
SELECT name, address,
       ST_Distance(geom::geography, ST_SetSRID(ST_MakePoint(-122.3321, 47.6062), 4326)::geography) AS distance_m
FROM locations
WHERE ST_DWithin(geom::geography, ST_SetSRID(ST_MakePoint(-122.3321, 47.6062), 4326)::geography, 5000)
ORDER BY distance_m;

-- Find the nearest 10 locations
SELECT name, address,
       ST_Distance(geom::geography, ST_SetSRID(ST_MakePoint(-122.3321, 47.6062), 4326)::geography) AS distance_m
FROM locations
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(-122.3321, 47.6062), 4326)
LIMIT 10;

-- Bounding box query
SELECT name FROM locations
WHERE geom && ST_MakeEnvelope(-122.5, 47.5, -122.0, 47.8, 4326);

---

pg_trgm — Fuzzy Text Search

Setup

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Create Trigram Indexes

-- GIN index for LIKE/ILIKE and similarity
CREATE INDEX idx_users_name_trgm ON users USING gin(name gin_trgm_ops);

-- GiST index (for ORDER BY similarity)
CREATE INDEX idx_products_name_trgm ON products USING gist(name gist_trgm_ops);

Fuzzy Search Queries

-- Similarity search (threshold-based)
SELECT name, similarity(name, 'PostgreSQL') AS sim
FROM products
WHERE similarity(name, 'PostgreSQL') > 0.3
ORDER BY sim DESC
LIMIT 10;

-- LIKE with index acceleration (anchor-free patterns)
SELECT * FROM users WHERE name ILIKE '%john%';

-- Closest match by trigram distance
SELECT name, name <-> 'Postgre' AS distance
FROM products
ORDER BY name <-> 'Postgre'
LIMIT 5;

-- Configure similarity threshold
SET pg_trgm.similarity_threshold = 0.3;
SELECT * FROM products WHERE name % 'PostgreSQL';  -- uses the threshold

---

auto_explain — Automatic Query Plan Logging

Setup

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name azure.extensions --value "auto_explain"

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name shared_preload_libraries --value "auto_explain"

# Log plans for queries slower than 1 second
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name auto_explain.log_min_duration --value "1000"

# Include ANALYZE output
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name auto_explain.log_analyze --value "on"

# Include buffer usage
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name auto_explain.log_buffers --value "on"

# Nested statements (inside functions)
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name auto_explain.log_nested_statements --value "on"

az postgres flexible-server restart \
  --resource-group $RG --name $SERVER

Plans are logged to the PostgreSQL server log. Use Azure Monitor or download log files to review.

---

pg_partman — Automated Partition Management

Setup

CREATE EXTENSION IF NOT EXISTS pg_partman;
CREATE SCHEMA IF NOT EXISTS partman;

Create Time-Based Partitions

-- Create parent table
CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY,
    event_time timestamptz NOT NULL,
    event_type text NOT NULL,
    payload jsonb
) PARTITION BY RANGE (event_time);

-- Configure pg_partman to manage partitions
SELECT partman.create_parent(
    p_parent_table := 'public.events',
    p_control := 'event_time',
    p_interval := '1 month',
    p_premake := 3  -- create 3 future partitions
);

-- Run maintenance (creates/drops partitions as needed)
SELECT partman.run_maintenance();

Configure Retention

-- Auto-drop partitions older than 12 months
UPDATE partman.part_config
SET retention = '12 months',
    retention_keep_table = false,
    retention_keep_index = false
WHERE parent_table = 'public.events';

Automate with pg_cron

-- Run partition maintenance every hour
SELECT cron.schedule('partman-maintenance', '0 * * * *',
    $$SELECT partman.run_maintenance()$$
);

---

pg_repack — Online Table Repacking

Setup

CREATE EXTENSION IF NOT EXISTS pg_repack;

Repack Operations

# Repack a specific table (removes bloat without exclusive locks)
pg_repack --host=$FQDN --port=5432 --dbname=$DB --username=$USER \
  --no-superuser-check --table=your_table

# Repack only indexes of a table
pg_repack --host=$FQDN --port=5432 --dbname=$DB --username=$USER \
  --no-superuser-check --table=your_table --only-indexes

# Repack all tables in the database
pg_repack --host=$FQDN --port=5432 --dbname=$DB --username=$USER \
  --no-superuser-check

# Dry run
pg_repack --host=$FQDN --port=5432 --dbname=$DB --username=$USER \
  --no-superuser-check --table=your_table --dry-run

Note: pg_repack requires the client-side pg_repack binary. Install via apt-get install postgresql-16-repack or equivalent.

---

azure_ai — Azure AI and ML Services Integration

Setup

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name azure.extensions --value "azure_ai"

CREATE EXTENSION IF NOT EXISTS azure_ai;

Configure AI Endpoint

-- Set Azure OpenAI endpoint and key
SELECT azure_ai.set_setting('azure_openai.endpoint', 'https://your-openai.openai.azure.com');
SELECT azure_ai.set_setting('azure_openai.subscription_key', 'your-key');

-- Generate embeddings directly in SQL
SELECT azure_openai.create_embeddings('text-embedding-3-small', 'Hello world');

-- Generate embeddings and store in a vector column
UPDATE documents SET embedding = azure_openai.create_embeddings(
    'text-embedding-3-small', content
)
WHERE embedding IS NULL;

AI-Powered Queries

-- Semantic search using in-database embedding generation
SELECT id, content, 1 - (embedding <=> azure_openai.create_embeddings(
    'text-embedding-3-small', 'search query'
)) AS similarity
FROM documents
ORDER BY embedding <=> azure_openai.create_embeddings('text-embedding-3-small', 'search query')
LIMIT 10;

-- Sentiment analysis with Azure Cognitive Services
SELECT azure_cognitive.analyze_sentiment('This product is excellent!', 'en');

---

azure_storage — Azure Blob Storage Integration

Setup

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name azure.extensions --value "azure_storage"

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name shared_preload_libraries --value "azure_storage"

az postgres flexible-server restart \
  --resource-group $RG --name $SERVER

CREATE EXTENSION IF NOT EXISTS azure_storage;

Configure Storage Account

-- Set storage account access
SELECT azure_storage.account_add('your_storage_account', 'your-access-key');

Import/Export Data

-- Import CSV from Azure Blob Storage
SELECT * FROM azure_storage.blob_get(
    'your_storage_account', 'your-container', 'data/import.csv',
    options := azure_storage.options_csv_get(header := true)
) AS t(id int, name text, value numeric);

-- Export query results to Azure Blob Storage
SELECT azure_storage.blob_put(
    'your_storage_account', 'your-container', 'data/export.csv',
    (SELECT string_agg(row_to_csv, E'\n') FROM (
        SELECT row_to_csv(t) FROM your_table t LIMIT 10000
    ) sub)
);

---

pg_diskann — Scalable Vector Search (DiskANN)

Azure-specific extension for approximate nearest neighbor search at scale, an alternative to pgvector's HNSW/IVFFlat indexes.

Setup

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name azure.extensions --value "pg_diskann"

CREATE EXTENSION IF NOT EXISTS pg_diskann;

Create DiskANN Index

-- Create DiskANN index on a vector column (requires pgvector vector type)
CREATE INDEX idx_documents_diskann ON documents
USING diskann (embedding vector_cosine_ops);

-- Tune search parameters
SET diskann.search_list_size = 100;  -- higher = better recall, slower

When to Use DiskANN vs pgvector Indexes

Factor	DiskANN	HNSW (pgvector)	IVFFlat (pgvector)
Dataset size	Large (millions+)	Medium-Large	Medium
Build time	Faster	Slower	Fast
Memory usage	Lower (disk-based)	Higher (in-memory)	Medium
Query recall	High	High	Moderate
Best for	Scale + cost efficiency	Low-latency queries	Quick prototyping

---

pgaudit — SQL Audit Logging

Setup

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name azure.extensions --value "pgaudit"

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name shared_preload_libraries --value "pgaudit"

az postgres flexible-server restart \
  --resource-group $RG --name $SERVER

CREATE EXTENSION IF NOT EXISTS pgaudit;

Configure Audit Logging

# Log all DDL and DML statements
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name pgaudit.log --value "DDL,WRITE"

# Log all reads (use cautiously — generates high volume)
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name pgaudit.log --value "DDL,WRITE,READ"

Audit Log Categories

Category	Logs
READ	SELECT, COPY when source is a relation
WRITE	INSERT, UPDATE, DELETE, TRUNCATE, COPY when destination is a relation
FUNCTION	Function calls and DO blocks
ROLE	GRANT, REVOKE, CREATE/ALTER/DROP ROLE
DDL	All DDL not covered by ROLE
MISC	DISCARD, FETCH, CHECKPOINT, VACUUM, SET
ALL	All of the above

Per-Role Auditing

-- Audit only specific roles (reduce log volume)
ALTER ROLE your_app_user SET pgaudit.log = 'WRITE,DDL';

---

timescaledb — Time-Series Data

Setup

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name azure.extensions --value "timescaledb"

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name shared_preload_libraries --value "timescaledb"

az postgres flexible-server restart \
  --resource-group $RG --name $SERVER

CREATE EXTENSION IF NOT EXISTS timescaledb;

Create Hypertable

-- Create a regular table
CREATE TABLE metrics (
    time timestamptz NOT NULL,
    device_id bigint NOT NULL,
    temperature double precision,
    humidity double precision
);

-- Convert to hypertable (automatic time-based partitioning)
SELECT create_hypertable('metrics', by_range('time'));

-- Insert data (same as regular PostgreSQL)
INSERT INTO metrics (time, device_id, temperature, humidity)
VALUES (now(), 1, 22.5, 65.0);

Time-Series Queries

-- Aggregate by time bucket
SELECT time_bucket('1 hour', time) AS hour,
       device_id,
       avg(temperature) AS avg_temp,
       max(temperature) AS max_temp
FROM metrics
WHERE time > now() - interval '24 hours'
GROUP BY hour, device_id
ORDER BY hour DESC;

-- Continuous aggregates (materialized view, auto-refreshing)
CREATE MATERIALIZED VIEW hourly_metrics
WITH (timescaledb.continuous) AS
SELECT time_bucket('1 hour', time) AS hour,
       device_id,
       avg(temperature) AS avg_temp,
       count(*) AS readings
FROM metrics
GROUP BY hour, device_id;

-- Retention policy (auto-drop old data)
SELECT add_retention_policy('metrics', INTERVAL '90 days');

-- Compression policy (compress old chunks)
ALTER TABLE metrics SET (timescaledb.compress);
SELECT add_compression_policy('metrics', INTERVAL '7 days');

---

hypopg — Hypothetical Indexes

Test index performance impact without actually creating the index.

Setup

CREATE EXTENSION IF NOT EXISTS hypopg;

Test Index Impact

-- Create a hypothetical index (not physically built)
SELECT * FROM hypopg_create_index('CREATE INDEX ON orders(customer_id, order_date)');

-- Run EXPLAIN to see if the planner would use it
EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND order_date > '2026-01-01';

-- Check estimated size of the hypothetical index
SELECT indexname, pg_size_pretty(hypopg_relation_size(indexrelid))
FROM hypopg_list_indexes();

-- Remove hypothetical indexes
SELECT hypopg_reset();

---

pg_hint_plan — Query Plan Hints

Setup

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name azure.extensions --value "pg_hint_plan"

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name shared_preload_libraries --value "pg_hint_plan"

az postgres flexible-server restart \
  --resource-group $RG --name $SERVER

CREATE EXTENSION IF NOT EXISTS pg_hint_plan;

Common Hints

-- Force index scan
/*+ IndexScan(orders idx_orders_customer) */
SELECT * FROM orders WHERE customer_id = 42;

-- Force sequential scan
/*+ SeqScan(orders) */
SELECT * FROM orders WHERE customer_id = 42;

-- Force join order and method
/*+ Leading(orders customers) HashJoin(orders customers) */
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;

-- Force nested loop join
/*+ NestLoop(o c) */
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.id = 42;

---

pg_squeeze — Online Table Compaction

Alternative to pg_repack — removes dead tuples with minimal locking.

Setup

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name azure.extensions --value "pg_squeeze"

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name shared_preload_libraries --value "pg_squeeze"

az postgres flexible-server restart \
  --resource-group $RG --name $SERVER

CREATE EXTENSION IF NOT EXISTS pg_squeeze;

Schedule Automatic Compaction

-- Register a table for automatic squeezing
INSERT INTO squeeze.tables (tabschema, tabname, schedule, free_space_extra, min_size)
VALUES ('public', 'your_table', '0 3 * * *', 20, 1048576);

-- Manual squeeze
SELECT squeeze.squeeze_table('public', 'your_table');

---

age — Apache AGE Graph Database (Preview)

Graph database capabilities within PostgreSQL using openCypher query language.

Setup

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name azure.extensions --value "age"

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name shared_preload_libraries --value "age"

az postgres flexible-server restart \
  --resource-group $RG --name $SERVER

CREATE EXTENSION IF NOT EXISTS age;
-- Load AGE functions into search path
LOAD 'age';
SET search_path = ag_catalog, "$user", public;

Create and Query Graphs

-- Create a graph
SELECT create_graph('social_network');

-- Create vertices (nodes)
SELECT * FROM cypher('social_network', $$
    CREATE (:Person {name: 'Alice', age: 30}),
           (:Person {name: 'Bob', age: 25}),
           (:Person {name: 'Carol', age: 35})
$$) AS (v agtype);

-- Create edges (relationships)
SELECT * FROM cypher('social_network', $$
    MATCH (a:Person {name: 'Alice'}), (b:Person {name: 'Bob'})
    CREATE (a)-[:KNOWS {since: 2020}]->(b)
$$) AS (e agtype);

-- Query: find friends of friends
SELECT * FROM cypher('social_network', $$
    MATCH (p:Person {name: 'Alice'})-[:KNOWS]->()-[:KNOWS]->(fof:Person)
    RETURN fof.name AS friend_of_friend
$$) AS (friend_of_friend agtype);

-- Shortest path
SELECT * FROM cypher('social_network', $$
    MATCH path = shortestPath((a:Person {name: 'Alice'})-[*]-(b:Person {name: 'Carol'}))
    RETURN path
$$) AS (path agtype);

-- Drop a graph
SELECT drop_graph('social_network', true);

Note: AGE is in Preview on Azure. Use openCypher syntax within the cypher() function wrapper.

---

wal2json — Change Data Capture (CDC)

Setup

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name azure.extensions --value "wal2json"

az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name shared_preload_libraries --value "wal2json"

# Enable logical replication (required)
az postgres flexible-server parameter set \
  --resource-group $RG --server-name $SERVER \
  --name wal_level --value "logical"

az postgres flexible-server restart \
  --resource-group $RG --name $SERVER

Create Logical Replication Slot

-- Create a replication slot with wal2json output
SELECT pg_create_logical_replication_slot('my_slot', 'wal2json');

-- Read changes (JSON format)
SELECT * FROM pg_logical_slot_get_changes('my_slot', NULL, NULL);

-- Peek without consuming
SELECT * FROM pg_logical_slot_peek_changes('my_slot', NULL, NULL);

-- Drop slot when no longer needed
SELECT pg_drop_replication_slot('my_slot');

---

Must

• Allow extensions via azure.extensions parameter before installing
• Restart the server after changing shared_preload_libraries
• Create extensions in the target database (not just system catalog)
• Use IF NOT EXISTS for idempotent extension installation
• Use sslmode=require in all connections

Prefer

• pg_stat_statements on every production server
• pg_cron over external schedulers for database-internal tasks
• GIN indexes for pg_trgm (faster lookups vs GiST)
• auto_explain with a reasonable threshold (1-5 seconds) to avoid log noise
• pg_partman over manual partition DDL for time-series data

Avoid

• Installing extensions without verifying Azure support first
• Enabling auto_explain.log_analyze with a very low threshold (generates excessive IO)
• Running pg_repack during peak write hours
• Forgetting to schedule partman.run_maintenance() after setting up pg_partman
• Installing extensions you don't need (each adds overhead)