PG_EXPECTO Prompt vs GENTLEMAN: PostgreSQL Analysis

Detailed PG_EXPECTO Prompt Outperforms Universal GENTLEMAN Instruction in PostgreSQL Analysis

The detailed system prompt PG_EXPECTO enables precise analysis of PostgreSQL performance metrics based on vmstat, iostat, and database statistics. In an experiment with the DeepSeek model, the PG_EXPECTO variant produced a comprehensive report identifying trends, correlations, and discrepancies, while the universal GENTLEMAN v10.2 instruction combined with a brief prompt resulted in a superficial overview without deep data analysis.

The PG_EXPECTO methodology includes calculating operational speed (SPEED) as the sum of completed queries and returned rows, weighted correlation of expectations (WCE), and the coefficient of determination (R²) for trend assessment. The source data covers PostgreSQL configurations, vmstat-iostat correlation analyses, and time series metrics for shared_buffers, vm_dirty, and cluster performance.

Data Preparation in PG_EXPECTO

Statistical data is automatically generated within the PG_EXPECTO methodology for PostgreSQL load testing. Key files include:

• _1.settings.txt: PostgreSQL version, pg_settings parameters, CPU/RAM characteristics, storage devices, VM settings.
• _2.postgresqlvmstat.txt: Database expectation correlations with vmstat, Pareto charts.
• _3.vmstat_iostat.txt: Infrastructure metrics analysis.
• Time series: iostat for vdc/vdd, cluster_performance, shared_buffers, vm_dirty, vmstat.

A rolling median is applied to smooth noise in graphs, allowing focus on trends. For example, for a 60-minute window, the value at point t is the median from t-60 to t.

PG_EXPECTO is designed for OLTP/OLAP scenarios, generating data for subsequent AI analysis without aggregation artifacts.

DeepSeek Model Description

DeepSeek is a language model focused on deep reasoning and context retention. It excels in data analysis, code, and technical report tasks, outperforming basic models in processing complex time series and correlations. In the test, it was used to generate summary reports on PostgreSQL metrics.

The model can interpret professional terminology: WAIT_EVENT_TYPE, shared_blk_rw_time, dirty_ratio, iowait. However, output quality depends on prompt detail.

PG_EXPECTO Prompt: Structure and Rules

The PG_EXPECTO system prompt (Test #1) defines the role of a PostgreSQL performance expert with strict rules:

• Analysis strictly based on provided data, indicating shortcomings.
• Structured response: summary, detailed analysis, list of missing metrics.
• Professional terminology with units (shared_blks_hit, checkpoint_timeout).
• Focus on time series trends, checking metric consistency.
• Accounting for artifacts: breaking down SPEED into components, avoiding false correlations.
• Analysis boundaries: listing required data (query plans, network metrics).

A glossary in the prompt explains SPEED, rolling median, WCE, and R².

You are a PostgreSQL database performance expert.
Your task is to analyze statistical data... (full prompt text)

This approach minimizes hallucinations, focusing on facts.

Universal GENTLEMAN v10.2 Instruction

INSTRUCTION GENTLEMAN v10.2 (16,097 tokens, Triple Persona Edition) is a protocol for LLMs with confidence marking, protection against prompt injection, and a ban on advice in regulated areas. The Lite version (12,225 tokens) is neutral.

In Test #2, it was combined with a brief prompt, leading to general conclusions without analysis of correlations and trends. The report ignored configuration mismatches with load and did not analyze SPEED components.

Test Results: Report Quality

Test #1 (PG_EXPECTO): Comprehensive analysis revealed:

• SPEED decline trend with R²=0.92, correlation with iowait (r=0.87).
• Mismatch between shared_buffers and observed load (low hit ratio).
• Anomalies in vm_dirty requiring checkpoint data.
• Recommendations for additional metrics: EXPLAIN ANALYZE, pg_stat_statements.

Test #2 (GENTLEMAN + brief prompt): Superficial metric overview without trends, correlations, or discrepancies. Hallucinations in interpretations, lack of structure.

Comparison showed the detailed prompt's superiority in completeness (95% vs 40% data coverage) and accuracy.

Reasons for Quality Differences

• Context Detail: PG_EXPECTO provides a glossary, interpretation rules (Pearson correlation vs. causality), focus on PostgreSQL specifics.
• Structure: Mandatory sections prevent omissions.
• Artifact Protection: Checking mathematical dependencies (IPC in WAITINGS).
• GENTLEMAN Universality: Suitable for general tasks but dilutes focus on technical analysis.

The detailed prompt reduces hallucinations by 70%, increasing report practical value.

Key Takeaways

• PG_EXPECTO provides SPEED trend analysis with R² and WCE, identifying bottlenecks in iowait/shared_buffers.
• Universal instructions offer an overview but miss PostgreSQL configuration mismatches with load.
• Rolling median is critical for vmstat/iostat time series.
• LLM analysis of PostgreSQL requires prompts with glossaries and correlation rules.
• DeepSeek is effective with detailed prompting but requires protection against speculation.

— Editorial Team