postgresql-optimization

Name: postgresql-optimization
Author: github

14kinstalls4.6kforks

How to install postgresql-optimization?

github/awesome-copilot/postgresql-optimization

$npx -y skills add github/awesome-copilot --skill postgresql-optimization

Installs into the current project.

Summary

PostgreSQL-specific development assistant focusing on unique PostgreSQL features, advanced data types, and PostgreSQL-exclusive capabilities. Covers JSONB operations, array types, custom types, range/geometric types, full-text search, window functions, and PostgreSQL extensions e

Files · 1

View on GitHub

SKILL.md

1	# PostgreSQL Development Assistant
2
3	Expert PostgreSQL guidance for ${selection} (or entire project if no selection). Focus on PostgreSQL-specific features, optimization patterns, and advanced capabilities.
4
5	## � PostgreSQL-Specific Features
6
7	### JSONB Operations
8	```sql
9	-- Advanced JSONB queries
10	CREATE TABLE events (
11	id SERIAL PRIMARY KEY,
12	data JSONB NOT NULL,
13	created_at TIMESTAMPTZ DEFAULT NOW()
14	);
15
16	-- GIN index for JSONB performance
17	CREATE INDEX idx_events_data_gin ON events USING gin(data);
18
19	-- JSONB containment and path queries
20	SELECT * FROM events
21	WHERE data @> '{"type": "login"}'
22	AND data #>> '{user,role}' = 'admin';
23
24	-- JSONB aggregation
25	SELECT jsonb_agg(data) FROM events WHERE data ? 'user_id';
26	```
27
28	### Array Operations
29	```sql
30	-- PostgreSQL arrays
31	CREATE TABLE posts (
32	id SERIAL PRIMARY KEY,
33	tags TEXT[],
34	categories INTEGER[]
35	);
36
37	-- Array queries and operations
38	SELECT * FROM posts WHERE 'postgresql' = ANY(tags);
39	SELECT * FROM posts WHERE tags && ARRAY['database', 'sql'];
40	SELECT * FROM posts WHERE array_length(tags, 1) > 3;
41
42	-- Array aggregation
43	SELECT array_agg(DISTINCT category) FROM posts, unnest(categories) as category;
44	```
45
46	### Window Functions & Analytics
47	```sql
48	-- Advanced window functions
49	SELECT
50	product_id,
51	sale_date,
52	amount,
53	-- Running totals
54	SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) as running_total,
55	-- Moving averages
56	AVG(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg,
57	-- Rankings
58	DENSE_RANK() OVER (PARTITION BY EXTRACT(month FROM sale_date) ORDER BY amount DESC) as monthly_rank,
59	-- Lag/Lead for comparisons
60	LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sale_date) as prev_amount
61	FROM sales;
62	```
63
64	### Full-Text Search
65	```sql
66	-- PostgreSQL full-text search
67	CREATE TABLE documents (
68	id SERIAL PRIMARY KEY,
69	title TEXT,
70	content TEXT,
71	search_vector tsvector
72	);
73
74	-- Update search vector
75	UPDATE documents
76	SET search_vector = to_tsvector('english', title \|\| ' ' \|\| content);
77
78	-- GIN index for search performance
79	CREATE INDEX idx_documents_search ON documents USING gin(search_vector);
80
81	-- Search queries
82	SELECT * FROM documents
83	WHERE search_vector @@ plainto_tsquery('english', 'postgresql database');
84
85	-- Ranking results
86	SELECT *, ts_rank(search_vector, plainto_tsquery('postgresql')) as rank
87	FROM documents
88	WHERE search_vector @@ plainto_tsquery('postgresql')
89	ORDER BY rank DESC;
90	```
91
92	## � PostgreSQL Performance Tuning
93
94	### Query Optimization
95	```sql
96	-- EXPLAIN ANALYZE for performance analysis
97	EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
98	SELECT u.name, COUNT(o.id) as order_count
99	FROM users u
100	LEFT JOIN orders o ON u.id = o.user_id
101	WHERE u.created_at > '2024-01-01'::date
102	GROUP BY u.id, u.name;
103
104	-- Identify slow queries from pg_stat_statements
105	SELECT query, calls, total_time, mean_time, rows,
106	100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
107	FROM pg_stat_statements
108	ORDER BY total_time DESC
109	LIMIT 10;
110	```
111
112	### Index Strategies
113	```sql
114	-- Composite indexes for multi-column queries
115	CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);
116
117	-- Partial indexes for filtered queries
118	CREATE INDEX idx_active_users ON users(created_at) WHERE status = 'active';
119
120	-- Expression indexes for computed values
121	CREATE INDEX idx_users_lower_email ON users(lower(email));
122
123	-- Covering indexes to avoid table lookups
124	CREATE INDEX idx_orders_covering ON orders(user_id, status) INCLUDE (total, created_at);
125	```
126
127	### Connection & Memory Management
128	```sql
129	-- Check connection usage
130	SELECT count(*) as connections, state
131	FROM pg_stat_activity
132	GROUP BY state;
133
134	-- Monitor memory usage
135	SELECT name, setting, unit
136	FROM pg_settings
137	WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem');
138	```
139
140	## �️ PostgreSQL Advanced Data Types
141
142	### Custom Types & Domains
143	```sql
144	-- Create custom types
145	CREATE TYPE address_type AS (
146	street TEXT,
147	city TEXT,
148	postal_code TEXT,
149	country TEXT
150	);
151
152	CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');
153
154	-- Use domains for data validation
155	CREATE DOMAIN email_address AS TEXT
156	CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
157
158	-- Table using custom types
159	CREATE TABLE customers (
160	id SERIAL PRIMARY KEY,
161	email email_address NOT NULL,
162	address address_type,
163	status order_status DEFAULT 'pending'
164	);
165	```
166
167	### Range Types
168	```sql
169	-- PostgreSQL range types
170	CREATE TABLE reservations (
171	id SERIAL PRIMARY KEY,
172	room_id INTEGER,
173	reservation_period tstzrange,
174	pr

People who liked this also viewed

6 picks

Type

postgresql-optimization

14kinstalls4.6kforks

View on GitHub

How to install postgresql-optimization?

github/awesome-copilot/postgresql-optimization

$npx -y skills add github/awesome-copilot --skill postgresql-optimization

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# PostgreSQL Development Assistant
2
3	Expert PostgreSQL guidance for ${selection} (or entire project if no selection). Focus on PostgreSQL-specific features, optimization patterns, and advanced capabilities.
4
5	## � PostgreSQL-Specific Features
6
7	### JSONB Operations
8	```sql
9	-- Advanced JSONB queries
10	CREATE TABLE events (
11	id SERIAL PRIMARY KEY,
12	data JSONB NOT NULL,
13	created_at TIMESTAMPTZ DEFAULT NOW()
14	);
15
16	-- GIN index for JSONB performance
17	CREATE INDEX idx_events_data_gin ON events USING gin(data);
18
19	-- JSONB containment and path queries
20	SELECT * FROM events
21	WHERE data @> '{"type": "login"}'
22	AND data #>> '{user,role}' = 'admin';
23
24	-- JSONB aggregation
25	SELECT jsonb_agg(data) FROM events WHERE data ? 'user_id';
26	```
27
28	### Array Operations
29	```sql
30	-- PostgreSQL arrays
31	CREATE TABLE posts (
32	id SERIAL PRIMARY KEY,
33	tags TEXT[],
34	categories INTEGER[]
35	);
36
37	-- Array queries and operations
38	SELECT * FROM posts WHERE 'postgresql' = ANY(tags);
39	SELECT * FROM posts WHERE tags && ARRAY['database', 'sql'];
40	SELECT * FROM posts WHERE array_length(tags, 1) > 3;
41
42	-- Array aggregation
43	SELECT array_agg(DISTINCT category) FROM posts, unnest(categories) as category;
44	```
45
46	### Window Functions & Analytics
47	```sql
48	-- Advanced window functions
49	SELECT
50	product_id,
51	sale_date,
52	amount,
53	-- Running totals
54	SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) as running_total,
55	-- Moving averages
56	AVG(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg,
57	-- Rankings
58	DENSE_RANK() OVER (PARTITION BY EXTRACT(month FROM sale_date) ORDER BY amount DESC) as monthly_rank,
59	-- Lag/Lead for comparisons
60	LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sale_date) as prev_amount
61	FROM sales;
62	```
63
64	### Full-Text Search
65	```sql
66	-- PostgreSQL full-text search
67	CREATE TABLE documents (
68	id SERIAL PRIMARY KEY,
69	title TEXT,
70	content TEXT,
71	search_vector tsvector
72	);
73
74	-- Update search vector
75	UPDATE documents
76	SET search_vector = to_tsvector('english', title \|\| ' ' \|\| content);
77
78	-- GIN index for search performance
79	CREATE INDEX idx_documents_search ON documents USING gin(search_vector);
80
81	-- Search queries
82	SELECT * FROM documents
83	WHERE search_vector @@ plainto_tsquery('english', 'postgresql database');
84
85	-- Ranking results
86	SELECT *, ts_rank(search_vector, plainto_tsquery('postgresql')) as rank
87	FROM documents
88	WHERE search_vector @@ plainto_tsquery('postgresql')
89	ORDER BY rank DESC;
90	```
91
92	## � PostgreSQL Performance Tuning
93
94	### Query Optimization
95	```sql
96	-- EXPLAIN ANALYZE for performance analysis
97	EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
98	SELECT u.name, COUNT(o.id) as order_count
99	FROM users u
100	LEFT JOIN orders o ON u.id = o.user_id
101	WHERE u.created_at > '2024-01-01'::date
102	GROUP BY u.id, u.name;
103
104	-- Identify slow queries from pg_stat_statements
105	SELECT query, calls, total_time, mean_time, rows,
106	100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
107	FROM pg_stat_statements
108	ORDER BY total_time DESC
109	LIMIT 10;
110	```
111
112	### Index Strategies
113	```sql
114	-- Composite indexes for multi-column queries
115	CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);
116
117	-- Partial indexes for filtered queries
118	CREATE INDEX idx_active_users ON users(created_at) WHERE status = 'active';
119
120	-- Expression indexes for computed values
121	CREATE INDEX idx_users_lower_email ON users(lower(email));
122
123	-- Covering indexes to avoid table lookups
124	CREATE INDEX idx_orders_covering ON orders(user_id, status) INCLUDE (total, created_at);
125	```
126
127	### Connection & Memory Management
128	```sql
129	-- Check connection usage
130	SELECT count(*) as connections, state
131	FROM pg_stat_activity
132	GROUP BY state;
133
134	-- Monitor memory usage
135	SELECT name, setting, unit
136	FROM pg_settings
137	WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem');
138	```
139
140	## �️ PostgreSQL Advanced Data Types
141
142	### Custom Types & Domains
143	```sql
144	-- Create custom types
145	CREATE TYPE address_type AS (
146	street TEXT,
147	city TEXT,
148	postal_code TEXT,
149	country TEXT
150	);
151
152	CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');
153
154	-- Use domains for data validation
155	CREATE DOMAIN email_address AS TEXT
156	CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
157
158	-- Table using custom types
159	CREATE TABLE customers (
160	id SERIAL PRIMARY KEY,
161	email email_address NOT NULL,
162	address address_type,
163	status order_status DEFAULT 'pending'
164	);
165	```
166
167	### Range Types
168	```sql
169	-- PostgreSQL range types
170	CREATE TABLE reservations (
171	id SERIAL PRIMARY KEY,
172	room_id INTEGER,
173	reservation_period tstzrange,
174	pr

People who liked this also viewed

6 picks

Type