Query log
Contents
The query_log
table in PostHog's data warehouse provides access to query execution metadata and performance metrics. This table is built on top of the ClickHouse system.query_log
table and offers a simplified, team-filtered interface for analyzing query performance. It filters for completed HogQL queries for your team.
It provides data that enables you to:
- Monitor query performance and resource usage
- Debug slow or failing queries
- Analyze query patterns and usage metrics
- Track resource consumption (CPU, memory, I/O)
- Use date/time filters to limit query scope for better performance
- Consider using
event_date
for efficient partitioning - Large time ranges may require pagination for complex queries
Table schema
Core fields
Field | Type | Description |
---|---|---|
event_date | Date | Date when the query was executed |
event_time | DateTime | Exact timestamp when query started |
query_id | String | Unique identifier for the query |
endpoint | String | API endpoint that triggered the query |
query | String | The actual HogQL/SQL query that was executed |
query_start_time | DateTime | When query execution began |
query_duration_ms | Integer | Query execution time in milliseconds |
name | String | Name assigned to the QueryRequest |
created_by | Integer | User ID who initiated the query |
status | String | Query status (QueryFinish, ExceptionWhileProcessing, etc.) |
Performance metrics
Field | Type | Description |
---|---|---|
read_rows | Integer | Number of rows processed |
read_bytes | Integer | Bytes read during execution |
result_rows | Integer | Number of rows returned |
result_bytes | Integer | Size of result set in bytes |
memory_usage | Integer | Peak memory usage in bytes |
cpu_microseconds | Integer | CPU time consumed (for computation, excluding waiting) |
RealTimeMicroseconds | Integer | Wall clock time in microseconds |
S3 storage metrics
Field | Type | Description |
---|---|---|
S3ListObjects | Integer | Number of S3 list operations |
S3HeadObject | Integer | Number of S3 head operations |
S3GetObjectAttributes | Integer | Number of S3 get attributes calls |
S3GetObject | Integer | Number of S3 get operations |
ReadBufferFromS3Bytes | Integer | Bytes read from S3 |
Access control
Field | Type | Description |
---|---|---|
is_personal_api_key_request | Boolean | Whether request used personal API key |
api_key_label | String | Label of the API key used |
api_key_mask | String | Masked API key used |
Query log use cases and examples
Performance optimization
You can use the query log to identify slow queries. For example, to find slowest queries in the last 24 hours:
You can also use the query log for resource analysis. For example, to analyze resource consumption by API endpoint:
You can do something similar to track S3 operations and data transfer (applies to data warehouse queries, imported data, materialized views):
Debugging and troubleshooting
The query log can help you find failing queries and understand failure patterns.
The benefit of this is that you can see the actual query that was executed and the error that was returned.
Query analysis by request name and date
The name
field enables you to analyze query patterns by their assigned names, such as daily query volume by request name:
Or the most common request names by time period:
You can also use the name
property to see request performance trends over time:
You can use it to identify problematic request names too:
Usage analytics
The query log lets you see what types of queries and usage patterns are most popular in your project. For example, to track query activity by user:
API key access tracking
This can also be useful to track API access patterns and permissions for security or compliance purposes.
For example, you can compare access patterns between personal API keys vs other methods.
For more specific tracking, you can monitor personal API key usage by specific keys (api_key_mask
and api_key_label
are only available when is_personal_api_key_request
is true
).
To understand which keys are most active, you can see daily personal API key usage stats for the last 30 days.
Query log and its relationship to internal tables
Note: This section is mostly useful for the internal PostHog team.
The query_log
table is a virtual table that:
- Maps to the underlying
query_log_archive
ClickHouse table, which contains all system queries, not filtered by team - Automatically filters queries by
team_id
for data isolation - Only shows HogQL queries (
lc_query__kind = 'HogQLQuery'
) - Provides simplified field names and data types
This is similar to:
- System tables which access ClickHouse system information for broader analysis
- Events tables which correlate query performance with user activity patterns