Log Architecture & Structural Overview

The PostgreSQL Slow Query & Execution Log Parser is an essential telemetry stream within the Databases & Key-Value Stores ecosystem. Parse PostgreSQL query execution logs. Extract query duration, user, database, and SQL statements.

This schema defines a structure of 6 extracted attributes, including 2 numeric metrics and 4 string dimensions. In production observability architectures, these tokens provide high-cardinality indexing keys for telemetry pipelines before shipping to storage backends such as ClickHouse, Elasticsearch, Amazon S3, or Datadog.

Raw Telemetry Ingestion Profile

A typical raw event line for postgres-slow-query averages 168 bytes across 6 tokens. Modern collectors such as Fluent Bit and Vector require zero-backtracking regular expressions to avoid CPU spikes during traffic surges.

Extracted Field Schema & Data Types

The transpiled Grok pattern extracts the following schema fields from each raw event line. Data collectors cast these values according to the typed mappings below.

Field Name	Inferred Type	Description & Collector Semantics
timestamp	string	Timestamp of query execution log.
pid	integer	Backend Postgres server process ID.
user	string	Database username executing query.
database	string	Target database name.
duration_ms	float	Query execution duration in milliseconds.
query	string	Full SQL statement executed.

Common Regex Traps & Production Edge Cases

Engineers frequently encounter ingestion failures or pipeline drops due to subtle variations in real-world event logs. Watch out for these verified pitfalls:

1SQL statements can span multiple lines; ensure multiline aggregation is active on the file receiver.

2PostgreSQL log_line_prefix can be customized; verify your prefix configuration matches.

Production Collector Setup & Configurations

Pre-configured parser definitions ready to be dropped into your infrastructure repository.

Fluent Bit (parsers.conf)

Format: regex

# ==============================================================================
# Fluent Bit Parser Configuration (parsers.conf)
# ==============================================================================
[PARSER]
    Name        logpipeline_parser
    Format      regex
    Regex       ^(?<year>\b[0-9]{4}\b)-(?<month>(?:0?[1-9]|1[0-2]))-(?<day>(?:(?:0[1-9])|(?:[12][0-9])|(?:3[01])|[1-9])) (?<time>(?:(?:(?:2[0123]|[01]?[0-9])):(?:(?:[0-5][0-9]))(?::(?:(?:(?:[0-5]?[0-9]|60)(?:[:.,][0-9]+)?))))) (?<tz>\b\w+\b) \[(?<pid>(?:[+-]?(?:[0-9]+)))\] (?<user>(?:[a-zA-Z0-9._-]+))@(?<database>\b\w+\b) LOG:\s+duration: (?<duration_ms>(?:(?:(?:[+-]?(?:[0-9]+(?:\.[0-9]+)?|\.[0-9]+))))) ms\s+statement: (?<query>.*)$
    Time_Key    time
    Time_Format %Y-%m-%dT%H:%M:%S%z
    Types       pid:integer duration_ms:float

# ==============================================================================
# Fluent Bit Pipeline Filter (fluent-bit.conf)
# ==============================================================================
[FILTER]
    Name         parser
    Match        *
    Key_Name     log
    Parser       logpipeline_parser
    Reserve_Data On

Vector.dev (Remap VRL)

parse_regex!

# ==============================================================================
# Vector.dev Remap Language (VRL) Transform
# Use inside a 'remap' transform in vector.yaml
# ==============================================================================
.parsed, err = parse_regex(.message, r'^(?<year>\b[0-9]{4}\b)-(?<month>(?:0?[1-9]|1[0-2]))-(?<day>(?:(?:0[1-9])|(?:[12][0-9])|(?:3[01])|[1-9])) (?<time>(?:(?:(?:2[0123]|[01]?[0-9])):(?:(?:[0-5][0-9]))(?::(?:(?:(?:[0-5]?[0-9]|60)(?:[:.,][0-9]+)?))))) (?<tz>\b\w+\b) \[(?<pid>(?:[+-]?(?:[0-9]+)))\] (?<user>(?:[a-zA-Z0-9._-]+))@(?<database>\b\w+\b) LOG:\s+duration: (?<duration_ms>(?:(?:(?:[+-]?(?:[0-9]+(?:\.[0-9]+)?|\.[0-9]+))))) ms\s+statement: (?<query>.*)$')

if err == null {
    . = merge(., .parsed)
    del(.parsed)

    # Type coercions
    .pid = to_int!(.pid)
    .duration_ms = to_float!(.duration_ms)

} else {
    log("LogPipeline parsing warning: " + err, level: "warn")
}

# ==============================================================================
# vector.yaml Pipeline Component
# ==============================================================================
transforms:
  parse_logs:
    type: remap
    inputs: ["source_logs"]
    source: |
      .parsed, err = parse_regex(.message, r'^(?<year>\b[0-9]{4}\b)-(?<month>(?:0?[1-9]|1[0-2]))-(?<day>(?:(?:0[1-9])|(?:[12][0-9])|(?:3[01])|[1-9])) (?<time>(?:(?:(?:2[0123]|[01]?[0-9])):(?:(?:[0-5][0-9]))(?::(?:(?:(?:[0-5]?[0-9]|60)(?:[:.,][0-9]+)?))))) (?<tz>\b\w+\b) \[(?<pid>(?:[+-]?(?:[0-9]+)))\] (?<user>(?:[a-zA-Z0-9._-]+))@(?<database>\b\w+\b) LOG:\s+duration: (?<duration_ms>(?:(?:(?:[+-]?(?:[0-9]+(?:\.[0-9]+)?|\.[0-9]+))))) ms\s+statement: (?<query>.*)$')
      if err == null {
        . = merge(., .parsed)
        del(.parsed)
      }

Datadog Log Pipeline Grok Parser

match_rules

# ==============================================================================
# Datadog Log Processing Pipeline Grok Parser
# Navigate to: Logs -> Configuration -> Pipelines -> Add Processor -> Grok Parser
# ==============================================================================

# Match Rule:
rule %{YEAR:year}-%{MONTHNUM:month}-%{MONTHDAY:day} %{TIME:time} %{WORD:tz} \[%{INT:pid}\] %{USER:user}@%{WORD:database} LOG:\s+duration: %{NUMBER:duration_ms} ms\s+statement: %{GREEDYDATA:query}

# Complete Datadog Pipeline Processor JSON:
{
  "type": "grok-parser",
  "name": "LogPipeline Grok Parser",
  "is_enabled": true,
  "source": "message",
  "samples": [],
  "grok": {
    "match_rules": "rule %{YEAR:year}-%{MONTHNUM:month}-%{MONTHDAY:day} %{TIME:time} %{WORD:tz} \\[%{INT:pid}\\] %{USER:user}@%{WORD:database} LOG:\\s+duration: %{NUMBER:duration_ms} ms\\s+statement: %{GREEDYDATA:query}",
    "support_rules": ""
  }
}

# Target Fields Created:
# year (string), month (string), day (string), time (string), tz (string), pid (integer), user (string), database (string), duration_ms (float), query (string)

OpenTelemetry Collector (transform processor)

regex_parser

# ==============================================================================
# OpenTelemetry Collector Configuration (otel-collector-config.yaml)
# Option 1: Filelog Receiver with regex_parser Operator
# ==============================================================================
receivers:
  filelog:
    include: [ /var/log/**/*.log ]
    start_at: beginning
    operators:
      - type: regex_parser
        id: logpipeline_regex_parser
        regex: '^(?<year>\b[0-9]{4}\b)-(?<month>(?:0?[1-9]|1[0-2]))-(?<day>(?:(?:0[1-9])|(?:[12][0-9])|(?:3[01])|[1-9])) (?<time>(?:(?:(?:2[0123]|[01]?[0-9])):(?:(?:[0-5][0-9]))(?::(?:(?:(?:[0-5]?[0-9]|60)(?:[:.,][0-9]+)?))))) (?<tz>\b\w+\b) \[(?<pid>(?:[+-]?(?:[0-9]+)))\] (?<user>(?:[a-zA-Z0-9._-]+))@(?<database>\b\w+\b) LOG:\s+duration: (?<duration_ms>(?:(?:(?:[+-]?(?:[0-9]+(?:\.[0-9]+)?|\.[0-9]+))))) ms\s+statement: (?<query>.*)$'
        timestamp:
          parse_from: attributes.time
          layout: '%Y-%m-%dT%H:%M:%S%z'

# ==============================================================================
# Option 2: Transform Processor (OTel Transformation Language - OTTL)
# ==============================================================================
processors:
  transform:
    error_mode: ignore
    log_statements:
      - context: log
        statements:
          - merge_maps(attributes, extract_patterns(body, "^(?<year>\\b[0-9]{4}\\b)-(?<month>(?:0?[1-9]|1[0-2]))-(?<day>(?:(?:0[1-9])|(?:[12][0-9])|(?:3[01])|[1-9])) (?<time>(?:(?:(?:2[0123]|[01]?[0-9])):(?:(?:[0-5][0-9]))(?::(?:(?:(?:[0-5]?[0-9]|60)(?:[:.,][0-9]+)?))))) (?<tz>\\b\\w+\\b) \\[(?<pid>(?:[+-]?(?:[0-9]+)))\\] (?<user>(?:[a-zA-Z0-9._-]+))@(?<database>\\b\\w+\\b) LOG:\\s+duration: (?<duration_ms>(?:(?:(?:[+-]?(?:[0-9]+(?:\\.[0-9]+)?|\\.[0-9]+))))) ms\\s+statement: (?<query>.*)$"), "insert")

service:
  pipelines:
    logs:
      receivers: [filelog]
      processors: [transform]
      exporters: [otlp]

Logstash Filter Configuration

filter.grok

# ==============================================================================
# Logstash Pipeline Configuration (/etc/logstash/conf.d/logpipeline.conf)
# ==============================================================================
filter {
  grok {
    match => { "message" => "%{YEAR:year}-%{MONTHNUM:month}-%{MONTHDAY:day} %{TIME:time} %{WORD:tz} \[%{INT:pid:integer}\] %{USER:user}@%{WORD:database} LOG:\s+duration: %{NUMBER:duration_ms:float} ms\s+statement: %{GREEDYDATA:query}" }
    tag_on_failure => [ "_grokparsefailure" ]
  }

  date {
    match => [ "time", "ISO8601", "dd/MMM/yyyy:HH:mm:ss Z" ]
    target => "@timestamp"
    remove_field => [ "time" ]
  }
}