neon-postgres-egress-optimizer

Name: neon-postgres-egress-optimizer
Author: neondatabase

1.7kinstalls12forks

How to install neon-postgres-egress-optimizer?

neondatabase/agent-skills/neon-postgres-egress-optimizer

$npx -y skills add neondatabase/agent-skills --skill neon-postgres-egress-optimizer

Installs into the current project.

Summary

Diagnose and fix excessive Postgres egress (network data transfer) in a codebase. Use when a user mentions high database bills, unexpected data transfer costs, network transfer charges, egress spikes, "why is my Neon bill so high", "database costs jumped", SELECT * optimization,

Files · 1

View on GitHub

SKILL.md

1	# Postgres Egress Optimizer
2
3	Guide the user through diagnosing and fixing application-side query patterns that cause excessive data transfer (egress) from their Postgres database. Most high egress bills come from the application fetching more data than it uses.
4
5	## Step 1: Diagnose
6
7	Identify which queries transfer the most data. The primary tool is the `pg_stat_statements` extension.
8
9	### Check if pg_stat_statements is available
10
11	```sql
12	SELECT 1 FROM pg_stat_statements LIMIT 1;
13	```
14
15	If this errors, the extension needs to be created:
16
17	```sql
18	CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
19	```
20
21	On Neon, it is available by default but may need this CREATE EXTENSION step.
22
23	### Handle empty stats
24
25	Stats are cleared when a Neon compute scales to zero and restarts. If the stats are empty or the compute recently woke up:
26
27	1. Reset the stats to start a clean measurement window: `SELECT pg_stat_statements_reset();`
28	2. Let the application run under representative traffic for at least an hour.
29	3. Return and run the diagnostic queries below.
30
31	If the user has stats from a production database, use those. If they have no access to production stats, proceed to Step 2 and analyze the codebase directly — code-level patterns are often sufficient to identify the worst offenders.
32
33	### Diagnostic queries
34
35	Run these to identify the top egress contributors. Focus on queries that return many rows, return wide rows (JSONB, TEXT, BYTEA columns), or are called very frequently.
36
37	Queries returning the most total rows:
38
39	```sql
40	SELECT query, calls, rows AS total_rows, rows / calls AS avg_rows_per_call
41	FROM pg_stat_statements
42	WHERE calls > 0
43	ORDER BY rows DESC
44	LIMIT 10;
45	```
46
47	Queries returning the most rows per execution (poorly scoped SELECTs, missing pagination):
48
49	```sql
50	SELECT query, calls, rows AS total_rows, rows / calls AS avg_rows_per_call
51	FROM pg_stat_statements
52	WHERE calls > 0
53	ORDER BY avg_rows_per_call DESC
54	LIMIT 10;
55	```
56
57	Most frequently called queries (candidates for caching):
58
59	```sql
60	SELECT query, calls, rows AS total_rows, rows / calls AS avg_rows_per_call
61	FROM pg_stat_statements
62	WHERE calls > 0
63	ORDER BY calls DESC
64	LIMIT 10;
65	```
66
67	Longest running queries (not a direct egress measure, but helps identify problem queries during a spike):
68
69	```sql
70	SELECT query, calls, rows AS total_rows,
71	round(total_exec_time::numeric, 2) AS total_exec_time_ms
72	FROM pg_stat_statements
73	WHERE calls > 0
74	ORDER BY total_exec_time DESC
75	LIMIT 10;
76	```
77
78	### Interpret the results
79
80	Rank findings by estimated egress impact:
81
82	- High row count + wide rows = biggest egress. A query returning 1,000 rows where each row includes a 50KB JSONB column transfers ~50MB per call.
83	- Extreme call frequency on even small queries adds up. A query called 50,000 times/day returning 10 rows each = 500,000 rows/day.
84	- Cross-reference with the schema to identify which columns are wide. Look for JSONB, TEXT, BYTEA, and large VARCHAR columns.
85
86	## Step 2: Analyze codebase
87
88	For each query identified in Step 1, or for each database query in the codebase if no stats are available, check:
89
90	- Does it select only the columns the response needs?
91	- Does it return a bounded number of rows (LIMIT/pagination)?
92	- Is it called frequently enough to benefit from caching?
93	- Does it fetch raw data that gets aggregated in application code?
94	- Does it use a JOIN that duplicates parent data across child rows?
95
96	## Step 3: Fix
97
98	Apply the appropriate fix for each problem found. Below are the most common egress anti-patterns and how to fix them.
99
100	### Unused columns (SELECT \*)
101
102	Problem: The query fetches all columns but the application only uses a few. Large columns (JSONB blobs, TEXT fields) get transferred over the wire and discarded.
103
104	Before:
105
106	```sql
107	SELECT * FROM products;
108	```
109
110	After:
111
112	```sql
113	SELECT id, name, price, image_urls FROM products;
114	```
115
116	### Missing pagination
117
118	Problem: A list endpoint returns all rows with no LIMIT. This is an unbounded egress risk — every new row in the table increases data transfer on every request. Flag this regardless of current table size.
119
120	This is easy to miss because the application may work fine with small datasets. But at scale, an unpaginated endpoint returning 10,000 rows with even moderate column widths can transfer hundreds of megabytes per day.
121
122	Before:
123
124	```sql
125	SELECT id, name, price FROM products;
126	```
127
128	**

People who liked this also viewed

6 picks

Type

neon-postgres-egress-optimizer

1.7kinstalls12forks

View on GitHub

How to install neon-postgres-egress-optimizer?

neondatabase/agent-skills/neon-postgres-egress-optimizer

$npx -y skills add neondatabase/agent-skills --skill neon-postgres-egress-optimizer

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# Postgres Egress Optimizer
2
3	Guide the user through diagnosing and fixing application-side query patterns that cause excessive data transfer (egress) from their Postgres database. Most high egress bills come from the application fetching more data than it uses.
4
5	## Step 1: Diagnose
6
7	Identify which queries transfer the most data. The primary tool is the `pg_stat_statements` extension.
8
9	### Check if pg_stat_statements is available
10
11	```sql
12	SELECT 1 FROM pg_stat_statements LIMIT 1;
13	```
14
15	If this errors, the extension needs to be created:
16
17	```sql
18	CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
19	```
20
21	On Neon, it is available by default but may need this CREATE EXTENSION step.
22
23	### Handle empty stats
24
25	Stats are cleared when a Neon compute scales to zero and restarts. If the stats are empty or the compute recently woke up:
26
27	1. Reset the stats to start a clean measurement window: `SELECT pg_stat_statements_reset();`
28	2. Let the application run under representative traffic for at least an hour.
29	3. Return and run the diagnostic queries below.
30
31	If the user has stats from a production database, use those. If they have no access to production stats, proceed to Step 2 and analyze the codebase directly — code-level patterns are often sufficient to identify the worst offenders.
32
33	### Diagnostic queries
34
35	Run these to identify the top egress contributors. Focus on queries that return many rows, return wide rows (JSONB, TEXT, BYTEA columns), or are called very frequently.
36
37	Queries returning the most total rows:
38
39	```sql
40	SELECT query, calls, rows AS total_rows, rows / calls AS avg_rows_per_call
41	FROM pg_stat_statements
42	WHERE calls > 0
43	ORDER BY rows DESC
44	LIMIT 10;
45	```
46
47	Queries returning the most rows per execution (poorly scoped SELECTs, missing pagination):
48
49	```sql
50	SELECT query, calls, rows AS total_rows, rows / calls AS avg_rows_per_call
51	FROM pg_stat_statements
52	WHERE calls > 0
53	ORDER BY avg_rows_per_call DESC
54	LIMIT 10;
55	```
56
57	Most frequently called queries (candidates for caching):
58
59	```sql
60	SELECT query, calls, rows AS total_rows, rows / calls AS avg_rows_per_call
61	FROM pg_stat_statements
62	WHERE calls > 0
63	ORDER BY calls DESC
64	LIMIT 10;
65	```
66
67	Longest running queries (not a direct egress measure, but helps identify problem queries during a spike):
68
69	```sql
70	SELECT query, calls, rows AS total_rows,
71	round(total_exec_time::numeric, 2) AS total_exec_time_ms
72	FROM pg_stat_statements
73	WHERE calls > 0
74	ORDER BY total_exec_time DESC
75	LIMIT 10;
76	```
77
78	### Interpret the results
79
80	Rank findings by estimated egress impact:
81
82	- High row count + wide rows = biggest egress. A query returning 1,000 rows where each row includes a 50KB JSONB column transfers ~50MB per call.
83	- Extreme call frequency on even small queries adds up. A query called 50,000 times/day returning 10 rows each = 500,000 rows/day.
84	- Cross-reference with the schema to identify which columns are wide. Look for JSONB, TEXT, BYTEA, and large VARCHAR columns.
85
86	## Step 2: Analyze codebase
87
88	For each query identified in Step 1, or for each database query in the codebase if no stats are available, check:
89
90	- Does it select only the columns the response needs?
91	- Does it return a bounded number of rows (LIMIT/pagination)?
92	- Is it called frequently enough to benefit from caching?
93	- Does it fetch raw data that gets aggregated in application code?
94	- Does it use a JOIN that duplicates parent data across child rows?
95
96	## Step 3: Fix
97
98	Apply the appropriate fix for each problem found. Below are the most common egress anti-patterns and how to fix them.
99
100	### Unused columns (SELECT \*)
101
102	Problem: The query fetches all columns but the application only uses a few. Large columns (JSONB blobs, TEXT fields) get transferred over the wire and discarded.
103
104	Before:
105
106	```sql
107	SELECT * FROM products;
108	```
109
110	After:
111
112	```sql
113	SELECT id, name, price, image_urls FROM products;
114	```
115
116	### Missing pagination
117
118	Problem: A list endpoint returns all rows with no LIMIT. This is an unbounded egress risk — every new row in the table increases data transfer on every request. Flag this regardless of current table size.
119
120	This is easy to miss because the application may work fine with small datasets. But at scale, an unpaginated endpoint returning 10,000 rows with even moderate column widths can transfer hundreds of megabytes per day.
121
122	Before:
123
124	```sql
125	SELECT id, name, price FROM products;
126	```
127
128	**

People who liked this also viewed

6 picks

Type