Two files, one repository
Zidong-LLC/BIBLIOTECA ships 2 formats across 16 indexed files. The question worth asking is whether the second one says anything the first does not.
CompareAGENTS.md ↔ CLAUDE.md
| Dimension | Shared | Only in A | Only in B | Overlap |
|---|---|---|---|---|
| Sections | 2 | 41 | 35 | 3% |
| Commands | 0 | 0 | 1 | 0% |
| Section tags | 4 | 3 | 3 | 40% |
What each file covers
Sections
2 shared · 41 only in A · 35 only in B- − Postgres Best Practices
- − Abstract
- − 1. Query Performance
- − 1.1 Add Indexes on WHERE and JOIN Columns
- − 1.2 Choose the Right Index Type for Your Data
- − 1.3 Create Composite Indexes for Multi-Column Queries
- − 1.4 Use Covering Indexes to Avoid Table Lookups
- − 1.5 Use Partial Indexes for Filtered Queries
- − 2. Connection Management
- − 2.1 Configure Idle Connection Timeouts
- − pgbouncer.ini
- − 2.2 Set Appropriate Connection Limits
- − 2.3 Use Connection Pooling for All Applications
- − 2.4 Use Prepared Statements Correctly with Pooling
- − 3. Security & RLS
- − 3.1 Apply Principle of Least Privilege
- − 3.2 Enable Row Level Security for Multi-Tenant Data
- − 3.3 Optimize RLS Policies for Performance
- − 4. Schema Design
- − 4.1 Choose Appropriate Data Types
- − 4.2 Index Foreign Key Columns
- − 4.3 Partition Large Tables for Better Performance
- − 4.4 Select Optimal Primary Key Strategy
- − 4.5 Use Lowercase Identifiers for Compatibility
- − 5. Concurrency & Locking
- − 5.1 Keep Transactions Short to Reduce Lock Contention
- − 5.2 Prevent Deadlocks with Consistent Lock Ordering
- − 5.3 Use Advisory Locks for Application-Level Locking
- − 5.4 Use SKIP LOCKED for Non-Blocking Queue Processing
- − 6. Data Access Patterns
- − 6.1 Batch INSERT Statements for Bulk Data
- − 6.2 Eliminate N+1 Queries with Batch Loading
- − 6.3 Use Cursor-Based Pagination Instead of OFFSET
- − 6.4 Use UPSERT for Insert-or-Update Operations
- − 7. Monitoring & Diagnostics
- − 7.1 Enable pg_stat_statements for Query Analysis
- − 7.2 Maintain Table Statistics with VACUUM and ANALYZE
- − 7.3 Use EXPLAIN ANALYZE to Diagnose Slow Queries
- − 8. Advanced Features
- − 8.1 Index JSONB Columns for Efficient Querying
- − 8.2 Use tsvector for Full-Text Search
- + Python Expert Guidelines
- + Correctness — **CRITICAL**
- + Type Safety — **HIGH**
- + Performance — **HIGH**
- + Style — **MEDIUM**
- + Correctness
- + Avoid Mutable Default Arguments
- + Proper Error Handling
- + Type Safety
- + Use Type Hints
- + Use Dataclasses
- + With additional configuration
- + Performance
- + Use List Comprehensions
- + Filtering with loop
- + Simple transformation
- + With filtering
- + Nested (use sparingly - break into functions if complex)
- + Use Context Managers
- + File is automatically closed, even if exception occurs
- + Multiple resources
- + Style
- + Follow PEP 8 Style Guide
- + Write Docstrings
- + Quick Reference
- + Python Code Checklist
- + Severity Levels
- + Code Review Output Format
- + Summary
- + Critical Issues 🔴
- + 1. [Issue Title]
- + Corrected code
- + High Priority 🟠
- + Medium Priority 🟡
- + Recommendations
- Table of Contents
- References
Commands
0 shared · 0 only in A · 1 only in B- + mypy
Section tags
4 shared · 3 only in A · 3 only in B- − security
- − database
- − agent-behaviour
- + lint-format
- + git-pr
- + docs
- code-style
- types
- performance
- do-not
Line diff
Zidong-LLC/BIBLIOTECA · skills/skills/databases/postgres-best-practices/AGENTS.md
@@ −1 @@
1# Postgres Best Practices
2
3**Version 1.0.0**
4Supabase
5January 2026
6
7> This document is optimized for AI agents and LLMs. Rules are prioritized by performance impact.
8
9---
10
11## Abstract
12
13Comprehensive Postgres performance optimization guide for developers using Supabase and Postgres. Contains performance rules across 8 categories, prioritized by impact from critical (query performance, connection management) to incremental (advanced features). Each rule includes detailed explanations, incorrect vs. correct SQL examples, query plan analysis, and specific performance metrics to guide automated optimization and code generation.
14
15---
16
17## Table of Contents
18
191. [Query Performance](#query-performance) - **CRITICAL**
20 - 1.1 [Add Indexes on WHERE and JOIN Columns](#11-add-indexes-on-where-and-join-columns)
21 - 1.2 [Choose the Right Index Type for Your Data](#12-choose-the-right-index-type-for-your-data)
22 - 1.3 [Create Composite Indexes for Multi-Column Queries](#13-create-composite-indexes-for-multi-column-queries)
23 - 1.4 [Use Covering Indexes to Avoid Table Lookups](#14-use-covering-indexes-to-avoid-table-lookups)
24 - 1.5 [Use Partial Indexes for Filtered Queries](#15-use-partial-indexes-for-filtered-queries)
25
262. [Connection Management](#connection-management) - **CRITICAL**
27 - 2.1 [Configure Idle Connection Timeouts](#21-configure-idle-connection-timeouts)
28 - 2.2 [Set Appropriate Connection Limits](#22-set-appropriate-connection-limits)
29 - 2.3 [Use Connection Pooling for All Applications](#23-use-connection-pooling-for-all-applications)
30 - 2.4 [Use Prepared Statements Correctly with Pooling](#24-use-prepared-statements-correctly-with-pooling)
31
323. [Security & RLS](#security-rls) - **CRITICAL**
33 - 3.1 [Apply Principle of Least Privilege](#31-apply-principle-of-least-privilege)
34 - 3.2 [Enable Row Level Security for Multi-Tenant Data](#32-enable-row-level-security-for-multi-tenant-data)
35 - 3.3 [Optimize RLS Policies for Performance](#33-optimize-rls-policies-for-performance)
36
374. [Schema Design](#schema-design) - **HIGH**
38 - 4.1 [Choose Appropriate Data Types](#41-choose-appropriate-data-types)
39 - 4.2 [Index Foreign Key Columns](#42-index-foreign-key-columns)
40 - 4.3 [Partition Large Tables for Better Performance](#43-partition-large-tables-for-better-performance)
41 - 4.4 [Select Optimal Primary Key Strategy](#44-select-optimal-primary-key-strategy)
42 - 4.5 [Use Lowercase Identifiers for Compatibility](#45-use-lowercase-identifiers-for-compatibility)
43
445. [Concurrency & Locking](#concurrency-locking) - **MEDIUM-HIGH**
45 - 5.1 [Keep Transactions Short to Reduce Lock Contention](#51-keep-transactions-short-to-reduce-lock-contention)
46 - 5.2 [Prevent Deadlocks with Consistent Lock Ordering](#52-prevent-deadlocks-with-consistent-lock-ordering)
47 - 5.3 [Use Advisory Locks for Application-Level Locking](#53-use-advisory-locks-for-application-level-locking)
48 - 5.4 [Use SKIP LOCKED for Non-Blocking Queue Processing](#54-use-skip-locked-for-non-blocking-queue-processing)
49
506. [Data Access Patterns](#data-access-patterns) - **MEDIUM**
51 - 6.1 [Batch INSERT Statements for Bulk Data](#61-batch-insert-statements-for-bulk-data)
52 - 6.2 [Eliminate N+1 Queries with Batch Loading](#62-eliminate-n1-queries-with-batch-loading)
53 - 6.3 [Use Cursor-Based Pagination Instead of OFFSET](#63-use-cursor-based-pagination-instead-of-offset)
54 - 6.4 [Use UPSERT for Insert-or-Update Operations](#64-use-upsert-for-insert-or-update-operations)
55
567. [Monitoring & Diagnostics](#monitoring-diagnostics) - **LOW-MEDIUM**
57 - 7.1 [Enable pg_stat_statements for Query Analysis](#71-enable-pgstatstatements-for-query-analysis)
58 - 7.2 [Maintain Table Statistics with VACUUM and ANALYZE](#72-maintain-table-statistics-with-vacuum-and-analyze)
59 - 7.3 [Use EXPLAIN ANALYZE to Diagnose Slow Queries](#73-use-explain-analyze-to-diagnose-slow-queries)
60
618. [Advanced Features](#advanced-features) - **LOW**
62 - 8.1 [Index JSONB Columns for Efficient Querying](#81-index-jsonb-columns-for-efficient-querying)
63 - 8.2 [Use tsvector for Full-Text Search](#82-use-tsvector-for-full-text-search)
64
65---
66
67## 1. Query Performance
68
69**Impact: CRITICAL**
70
71Slow queries, missing indexes, inefficient query plans. The most common source of Postgres performance issues.
72
73### 1.1 Add Indexes on WHERE and JOIN Columns
74
75**Impact: CRITICAL (100-1000x faster queries on large tables)**
76
77Queries filtering or joining on unindexed columns cause full table scans, which become exponentially slower as tables grow.
78
79**Incorrect (sequential scan on large table):**
80
81```sql
82-- No index on customer_id causes full table scan
83select * from orders where customer_id = 123;
84
85-- EXPLAIN shows: Seq Scan on orders (cost=0.00..25000.00 rows=100 width=85)
86```
87
88**Correct (index scan):**
89
90```sql
91-- Create index on frequently filtered column
92create index orders_customer_id_idx on orders (customer_id);
93
94select * from orders where customer_id = 123;
95
96-- EXPLAIN shows: Index Scan using orders_customer_id_idx (cost=0.42..8.44 rows=100 width=85)
97-- Index the referencing column
98create index orders_customer_id_idx on orders (customer_id);
99
100select c.name, o.total
101from customers c
102join orders o on o.customer_id = c.id;
103```
104
105For JOIN columns, always index the foreign key side:
106
107Reference: https://supabase.com/docs/guides/database/query-optimization
108
109---
110
111### 1.2 Choose the Right Index Type for Your Data
112
113**Impact: HIGH (10-100x improvement with correct index type)**
114
115Different index types excel at different query patterns. The default B-tree isn't always optimal.
116
117**Incorrect (B-tree for JSONB containment):**
118
119```sql
120-- B-tree cannot optimize containment operators
121create index products_attrs_idx on products (attributes);
122select * from products where attributes @> '{"color": "red"}';
123-- Full table scan - B-tree doesn't support @> operator
124```
125
126**Correct (GIN for JSONB):**
127
128```sql
129-- GIN supports @>, ?, ?&, ?| operators
130create index products_attrs_idx on products using gin (attributes);
131select * from products where attributes @> '{"color": "red"}';
132-- B-tree (default): =, <, >, BETWEEN, IN, IS NULL
133create index users_created_idx on users (created_at);
134
135-- GIN: arrays, JSONB, full-text search
136create index posts_tags_idx on posts using gin (tags);
137
138-- BRIN: large time-series tables (10-100x smaller)
139create index events_time_idx on events using brin (created_at);
140
141-- Hash: equality-only (slightly faster than B-tree for =)
142create index sessions_token_idx on sessions using hash (token);
143```
144
145Index type guide:
146
147Reference: https://www.postgresql.org/docs/current/indexes-types.html
148
149---
150
151### 1.3 Create Composite Indexes for Multi-Column Queries
152
153**Impact: HIGH (5-10x faster multi-column queries)**
154
155When queries filter on multiple columns, a composite index is more efficient than separate single-column indexes.
156
157**Incorrect (separate indexes require bitmap scan):**
158
159```sql
160-- Two separate indexes
161create index orders_status_idx on orders (status);
162create index orders_created_idx on orders (created_at);
163
164-- Query must combine both indexes (slower)
165select * from orders where status = 'pending' and created_at > '2024-01-01';
166```
167
168**Correct (composite index):**
169
170```sql
171-- Single composite index (leftmost column first for equality checks)
172create index orders_status_created_idx on orders (status, created_at);
173
174-- Query uses one efficient index scan
175select * from orders where status = 'pending' and created_at > '2024-01-01';
176-- Good: status (=) before created_at (>)
177create index idx on orders (status, created_at);
178
179-- Works for: WHERE status = 'pending'
180-- Works for: WHERE status = 'pending' AND created_at > '2024-01-01'
181-- Does NOT work for: WHERE created_at > '2024-01-01' (leftmost prefix rule)
182```
183
184**Column order matters** - place equality columns first, range columns last:
185
186Reference: https://www.postgresql.org/docs/current/indexes-multicolumn.html
187
188---
189
190### 1.4 Use Covering Indexes to Avoid Table Lookups
191
192**Impact: MEDIUM-HIGH (2-5x faster queries by eliminating heap fetches)**
193
194Covering indexes include all columns needed by a query, enabling index-only scans that skip the table entirely.
195
196**Incorrect (index scan + heap fetch):**
197
198```sql
199create index users_email_idx on users (email);
200
201-- Must fetch name and created_at from table heap
202select email, name, created_at from users where email = 'user@example.com';
203```
204
205**Correct (index-only scan with INCLUDE):**
206
207```sql
208-- Include non-searchable columns in the index
209create index users_email_idx on users (email) include (name, created_at);
210
211-- All columns served from index, no table access needed
212select email, name, created_at from users where email = 'user@example.com';
213-- Searching by status, but also need customer_id and total
214create index orders_status_idx on orders (status) include (customer_id, total);
215
216select status, customer_id, total from orders where status = 'shipped';
217```
218
219Use INCLUDE for columns you SELECT but don't filter on:
220
221Reference: https://www.postgresql.org/docs/current/indexes-index-only-scans.html
222
223---
224
225### 1.5 Use Partial Indexes for Filtered Queries
226
227**Impact: HIGH (5-20x smaller indexes, faster writes and queries)**
228
229Partial indexes only include rows matching a WHERE condition, making them smaller and faster when queries consistently filter on the same condition.
230
231**Incorrect (full index includes irrelevant rows):**
232
233```sql
234-- Index includes all rows, even soft-deleted ones
235create index users_email_idx on users (email);
236
237-- Query always filters active users
238select * from users where email = 'user@example.com' and deleted_at is null;
239```
240
241**Correct (partial index matches query filter):**
242
243```sql
244-- Index only includes active users
245create index users_active_email_idx on users (email)
246where deleted_at is null;
247
248-- Query uses the smaller, faster index
249select * from users where email = 'user@example.com' and deleted_at is null;
250-- Only pending orders (status rarely changes once completed)
251create index orders_pending_idx on orders (created_at)
252where status = 'pending';
253
254-- Only non-null values
255create index products_sku_idx on products (sku)
256where sku is not null;
257```
258
259Common use cases for partial indexes:
260
261Reference: https://www.postgresql.org/docs/current/indexes-partial.html
262
263---
264
265## 2. Connection Management
266
267**Impact: CRITICAL**
268
269Connection pooling, limits, and serverless strategies. Critical for applications with high concurrency or serverless deployments.
270
271### 2.1 Configure Idle Connection Timeouts
272
273**Impact: HIGH (Reclaim 30-50% of connection slots from idle clients)**
274
275Idle connections waste resources. Configure timeouts to automatically reclaim them.
276
277**Incorrect (connections held indefinitely):**
278
279```sql
280-- No timeout configured
281show idle_in_transaction_session_timeout; -- 0 (disabled)
282
283-- Connections stay open forever, even when idle
284select pid, state, state_change, query
285from pg_stat_activity
286where state = 'idle in transaction';
287-- Shows transactions idle for hours, holding locks
288```
289
290**Correct (automatic cleanup of idle connections):**
291
292```ini
293-- Terminate connections idle in transaction after 30 seconds
294alter system set idle_in_transaction_session_timeout = '30s';
295
296-- Terminate completely idle connections after 10 minutes
297alter system set idle_session_timeout = '10min';
298
299-- Reload configuration
300select pg_reload_conf();
301# pgbouncer.ini
302server_idle_timeout = 60
303client_idle_timeout = 300
304```
305
306For pooled connections, configure at the pooler level:
307
308Reference: https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT
309
310---
311
312### 2.2 Set Appropriate Connection Limits
313
314**Impact: CRITICAL (Prevent database crashes and memory exhaustion)**
315
316Too many connections exhaust memory and degrade performance. Set limits based on available resources.
317
318**Incorrect (unlimited or excessive connections):**
319
320```sql
321-- Default max_connections = 100, but often increased blindly
322show max_connections; -- 500 (way too high for 4GB RAM)
323
324-- Each connection uses 1-3MB RAM
325-- 500 connections * 2MB = 1GB just for connections!
326-- Out of memory errors under load
327```
328
329**Correct (calculate based on resources):**
330
331```sql
332-- Formula: max_connections = (RAM in MB / 5MB per connection) - reserved
333-- For 4GB RAM: (4096 / 5) - 10 = ~800 theoretical max
334-- But practically, 100-200 is better for query performance
335
336-- Recommended settings for 4GB RAM
337alter system set max_connections = 100;
338
339-- Also set work_mem appropriately
340-- work_mem * max_connections should not exceed 25% of RAM
341alter system set work_mem = '8MB'; -- 8MB * 100 = 800MB max
342select count(*), state from pg_stat_activity group by state;
343```
344
345Monitor connection usage:
346
347Reference: https://supabase.com/docs/guides/platform/performance#connection-management
348
349---
350
351### 2.3 Use Connection Pooling for All Applications
352
353**Impact: CRITICAL (Handle 10-100x more concurrent users)**
354
355Postgres connections are expensive (1-3MB RAM each). Without pooling, applications exhaust connections under load.
356
357**Incorrect (new connection per request):**
358
359```sql
360-- Each request creates a new connection
361-- Application code: db.connect() per request
362-- Result: 500 concurrent users = 500 connections = crashed database
363
364-- Check current connections
365select count(*) from pg_stat_activity; -- 487 connections!
366```
367
368**Correct (connection pooling):**
369
370```sql
371-- Use a pooler like PgBouncer between app and database
372-- Application connects to pooler, pooler reuses a small pool to Postgres
373
374-- Configure pool_size based on: (CPU cores * 2) + spindle_count
375-- Example for 4 cores: pool_size = 10
376
377-- Result: 500 concurrent users share 10 actual connections
378select count(*) from pg_stat_activity; -- 10 connections
379```
380
381Pool modes:
382- **Transaction mode**: connection returned after each transaction (best for most apps)
383- **Session mode**: connection held for entire session (needed for prepared statements, temp tables)
384
385Reference: https://supabase.com/docs/guides/database/connecting-to-postgres#connection-pooler
386
387---
388
389### 2.4 Use Prepared Statements Correctly with Pooling
390
391**Impact: HIGH (Avoid prepared statement conflicts in pooled environments)**
392
393Prepared statements are tied to individual database connections. In transaction-mode pooling, connections are shared, causing conflicts.
394
395**Incorrect (named prepared statements with transaction pooling):**
396
397```sql
398-- Named prepared statement
399prepare get_user as select * from users where id = $1;
400
401-- In transaction mode pooling, next request may get different connection
402execute get_user(123);
403-- ERROR: prepared statement "get_user" does not exist
404```
405
406**Correct (use unnamed statements or session mode):**
407
408```sql
409-- Option 1: Use unnamed prepared statements (most ORMs do this automatically)
410-- The query is prepared and executed in a single protocol message
411
412-- Option 2: Deallocate after use in transaction mode
413prepare get_user as select * from users where id = $1;
414execute get_user(123);
415deallocate get_user;
416
417-- Option 3: Use session mode pooling (port 5432 vs 6543)
418-- Connection is held for entire session, prepared statements persist
419-- Many drivers use prepared statements by default
420-- Node.js pg: { prepare: false } to disable
421-- JDBC: prepareThreshold=0 to disable
422```
423
424Check your driver settings:
425
426Reference: https://supabase.com/docs/guides/database/connecting-to-postgres#connection-pool-modes
427
428---
429
430## 3. Security & RLS
431
432**Impact: CRITICAL**
433
434Row-Level Security policies, privilege management, and authentication patterns.
435
436### 3.1 Apply Principle of Least Privilege
437
438**Impact: MEDIUM (Reduced attack surface, better audit trail)**
439
440Grant only the minimum permissions required. Never use superuser for application queries.
441
442**Incorrect (overly broad permissions):**
443
444```sql
445-- Application uses superuser connection
446-- Or grants ALL to application role
447grant all privileges on all tables in schema public to app_user;
448grant all privileges on all sequences in schema public to app_user;
449
450-- Any SQL injection becomes catastrophic
451-- drop table users; cascades to everything
452```
453
454**Correct (minimal, specific grants):**
455
456```sql
457-- Create role with no default privileges
458create role app_readonly nologin;
459
460-- Grant only SELECT on specific tables
461grant usage on schema public to app_readonly;
462grant select on public.products, public.categories to app_readonly;
463
464-- Create role for writes with limited scope
465create role app_writer nologin;
466grant usage on schema public to app_writer;
467grant select, insert, update on public.orders to app_writer;
468grant usage on sequence orders_id_seq to app_writer;
469-- No DELETE permission
470
471-- Login role inherits from these
472create role app_user login password 'xxx';
473grant app_writer to app_user;
474-- Revoke default public access
475revoke all on schema public from public;
476revoke all on all tables in schema public from public;
477```
478
479Revoke public defaults:
480
481Reference: https://supabase.com/blog/postgres-roles-and-privileges
482
483---
484
485### 3.2 Enable Row Level Security for Multi-Tenant Data
486
487**Impact: CRITICAL (Database-enforced tenant isolation, prevent data leaks)**
488
489Row Level Security (RLS) enforces data access at the database level, ensuring users only see their own data.
490
491**Incorrect (application-level filtering only):**
492
493```sql
494-- Relying only on application to filter
495select * from orders where user_id = $current_user_id;
496
497-- Bug or bypass means all data is exposed!
498select * from orders; -- Returns ALL orders
499```
500
501**Correct (database-enforced RLS):**
502
503```sql
504-- Enable RLS on the table
505alter table orders enable row level security;
506
507-- Create policy for users to see only their orders
508create policy orders_user_policy on orders
509 for all
510 using (user_id = current_setting('app.current_user_id')::bigint);
511
512-- Force RLS even for table owners
513alter table orders force row level security;
514
515-- Set user context and query
516set app.current_user_id = '123';
517select * from orders; -- Only returns orders for user 123
518create policy orders_user_policy on orders
519 for all
520 to authenticated
521 using (user_id = auth.uid());
522```
523
524Policy for authenticated role:
525
526Reference: https://supabase.com/docs/guides/database/postgres/row-level-security
527
528---
529
530### 3.3 Optimize RLS Policies for Performance
531
532**Impact: HIGH (5-10x faster RLS queries with proper patterns)**
533
534Poorly written RLS policies can cause severe performance issues. Use subqueries and indexes strategically.
535
536**Incorrect (function called for every row):**
537
538```sql
539create policy orders_policy on orders
540 using (auth.uid() = user_id); -- auth.uid() called per row!
541
542-- With 1M rows, auth.uid() is called 1M times
543```
544
545**Correct (wrap functions in SELECT):**
546
547```sql
548create policy orders_policy on orders
549 using ((select auth.uid()) = user_id); -- Called once, cached
550
551-- 100x+ faster on large tables
552-- Create helper function (runs as definer, bypasses RLS)
553create or replace function is_team_member(team_id bigint)
554returns boolean
555language sql
556security definer
557set search_path = ''
558as $$
559 select exists (
560 select 1 from public.team_members
561 where team_id = $1 and user_id = (select auth.uid())
562 );
563$$;
564
565-- Use in policy (indexed lookup, not per-row check)
566create policy team_orders_policy on orders
567 using ((select is_team_member(team_id)));
568create index orders_user_id_idx on orders (user_id);
569```
570
571Use security definer functions for complex checks:
572Always add indexes on columns used in RLS policies:
573
574Reference: https://supabase.com/docs/guides/database/postgres/row-level-security#rls-performance-recommendations
575
576---
577
578## 4. Schema Design
579
580**Impact: HIGH**
581
582Table design, index strategies, partitioning, and data type selection. Foundation for long-term performance.
583
584### 4.1 Choose Appropriate Data Types
585
586**Impact: HIGH (50% storage reduction, faster comparisons)**
587
588Using the right data types reduces storage, improves query performance, and prevents bugs.
589
590**Incorrect (wrong data types):**
591
592```sql
593create table users (
594 id int, -- Will overflow at 2.1 billion
595 email varchar(255), -- Unnecessary length limit
596 created_at timestamp, -- Missing timezone info
597 is_active varchar(5), -- String for boolean
598 price varchar(20) -- String for numeric
599);
600```
601
602**Correct (appropriate data types):**
603
604```sql
605create table users (
606 id bigint generated always as identity primary key, -- 9 quintillion max
607 email text, -- No artificial limit, same performance as varchar
608 created_at timestamptz, -- Always store timezone-aware timestamps
609 is_active boolean default true, -- 1 byte vs variable string length
610 price numeric(10,2) -- Exact decimal arithmetic
611);
612-- IDs: use bigint, not int (future-proofing)
613-- Strings: use text, not varchar(n) unless constraint needed
614-- Time: use timestamptz, not timestamp
615-- Money: use numeric, not float (precision matters)
616-- Enums: use text with check constraint or create enum type
617```
618
619Key guidelines:
620
621Reference: https://www.postgresql.org/docs/current/datatype.html
622
623---
624
625### 4.2 Index Foreign Key Columns
626
627**Impact: HIGH (10-100x faster JOINs and CASCADE operations)**
628
629Postgres does not automatically index foreign key columns. Missing indexes cause slow JOINs and CASCADE operations.
630
631**Incorrect (unindexed foreign key):**
632
633```sql
634create table orders (
635 id bigint generated always as identity primary key,
636 customer_id bigint references customers(id) on delete cascade,
637 total numeric(10,2)
638);
639
640-- No index on customer_id!
641-- JOINs and ON DELETE CASCADE both require full table scan
642select * from orders where customer_id = 123; -- Seq Scan
643delete from customers where id = 123; -- Locks table, scans all orders
644```
645
646**Correct (indexed foreign key):**
647
648```sql
649create table orders (
650 id bigint generated always as identity primary key,
651 customer_id bigint references customers(id) on delete cascade,
652 total numeric(10,2)
653);
654
655-- Always index the FK column
656create index orders_customer_id_idx on orders (customer_id);
657
658-- Now JOINs and cascades are fast
659select * from orders where customer_id = 123; -- Index Scan
660delete from customers where id = 123; -- Uses index, fast cascade
661select
662 conrelid::regclass as table_name,
663 a.attname as fk_column
664from pg_constraint c
665join pg_attribute a on a.attrelid = c.conrelid and a.attnum = any(c.conkey)
666where c.contype = 'f'
667 and not exists (
668 select 1 from pg_index i
669 where i.indrelid = c.conrelid and a.attnum = any(i.indkey)
670 );
671```
672
673Find missing FK indexes:
674
675Reference: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK
676
677---
678
679### 4.3 Partition Large Tables for Better Performance
680
681**Impact: MEDIUM-HIGH (5-20x faster queries and maintenance on large tables)**
682
683Partitioning splits a large table into smaller pieces, improving query performance and maintenance operations.
684
685**Incorrect (single large table):**
686
687```sql
688create table events (
689 id bigint generated always as identity,
690 created_at timestamptz,
691 data jsonb
692);
693
694-- 500M rows, queries scan everything
695select * from events where created_at > '2024-01-01'; -- Slow
696vacuum events; -- Takes hours, locks table
697```
698
699**Correct (partitioned by time range):**
700
701```sql
702create table events (
703 id bigint generated always as identity,
704 created_at timestamptz not null,
705 data jsonb
706) partition by range (created_at);
707
708-- Create partitions for each month
709create table events_2024_01 partition of events
710 for values from ('2024-01-01') to ('2024-02-01');
711
712create table events_2024_02 partition of events
713 for values from ('2024-02-01') to ('2024-03-01');
714
715-- Queries only scan relevant partitions
716select * from events where created_at > '2024-01-15'; -- Only scans events_2024_01+
717
718-- Drop old data instantly
719drop table events_2023_01; -- Instant vs DELETE taking hours
720```
721
722When to partition:
723- Tables > 100M rows
724- Time-series data with date-based queries
725- Need to efficiently drop old data
726
727Reference: https://www.postgresql.org/docs/current/ddl-partitioning.html
728
729---
730
731### 4.4 Select Optimal Primary Key Strategy
732
733**Impact: HIGH (Better index locality, reduced fragmentation)**
734
735Primary key choice affects insert performance, index size, and replication
736efficiency.
737
738**Incorrect (problematic PK choices):**
739
740```sql
741-- identity is the SQL-standard approach
742create table users (
743 id serial primary key -- Works, but IDENTITY is recommended
744);
745
746-- Random UUIDs (v4) cause index fragmentation
747create table orders (
748 id uuid default gen_random_uuid() primary key -- UUIDv4 = random = scattered inserts
749);
750```
751
752**Correct (optimal PK strategies):**
753
754```sql
755-- Use IDENTITY for sequential IDs (SQL-standard, best for most cases)
756create table users (
757 id bigint generated always as identity primary key
758);
759
760-- For distributed systems needing UUIDs, use UUIDv7 (time-ordered)
761-- Requires pg_uuidv7 extension: create extension pg_uuidv7;
762create table orders (
763 id uuid default uuid_generate_v7() primary key -- Time-ordered, no fragmentation
764);
765
766-- Alternative: time-prefixed IDs for sortable, distributed IDs (no extension needed)
767create table events (
768 id text default concat(
769 to_char(now() at time zone 'utc', 'YYYYMMDDHH24MISSMS'),
770 gen_random_uuid()::text
771 ) primary key
772);
773```
774
775Guidelines:
776- Single database: `bigint identity` (sequential, 8 bytes, SQL-standard)
777- Distributed/exposed IDs: UUIDv7 (requires pg_uuidv7) or ULID (time-ordered, no
778 fragmentation)
779- `serial` works but `identity` is SQL-standard and preferred for new
780 applications
781- Avoid random UUIDs (v4) as primary keys on large tables (causes index
782 fragmentation)
783[Identity Columns](https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-PARMS-GENERATED-IDENTITY)
784
785---
786
787### 4.5 Use Lowercase Identifiers for Compatibility
788
789**Impact: MEDIUM (Avoid case-sensitivity bugs with tools, ORMs, and AI assistants)**
790
791PostgreSQL folds unquoted identifiers to lowercase. Quoted mixed-case identifiers require quotes forever and cause issues with tools, ORMs, and AI assistants that may not recognize them.
792
793**Incorrect (mixed-case identifiers):**
794
795```sql
796-- Quoted identifiers preserve case but require quotes everywhere
797CREATE TABLE "Users" (
798 "userId" bigint PRIMARY KEY,
799 "firstName" text,
800 "lastName" text
801);
802
803-- Must always quote or queries fail
804SELECT "firstName" FROM "Users" WHERE "userId" = 1;
805
806-- This fails - Users becomes users without quotes
807SELECT firstName FROM Users;
808-- ERROR: relation "users" does not exist
809```
810
811**Correct (lowercase snake_case):**
812
813```sql
814-- Unquoted lowercase identifiers are portable and tool-friendly
815CREATE TABLE users (
816 user_id bigint PRIMARY KEY,
817 first_name text,
818 last_name text
819);
820
821-- Works without quotes, recognized by all tools
822SELECT first_name FROM users WHERE user_id = 1;
823-- ORMs often generate quoted camelCase - configure them to use snake_case
824-- Migrations from other databases may preserve original casing
825-- Some GUI tools quote identifiers by default - disable this
826
827-- If stuck with mixed-case, create views as a compatibility layer
828CREATE VIEW users AS SELECT "userId" AS user_id, "firstName" AS first_name FROM "Users";
829```
830
831Common sources of mixed-case identifiers:
832
833Reference: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
834
835---
836
837## 5. Concurrency & Locking
838
839**Impact: MEDIUM-HIGH**
840
841Transaction management, isolation levels, deadlock prevention, and lock contention patterns.
842
843### 5.1 Keep Transactions Short to Reduce Lock Contention
844
845**Impact: MEDIUM-HIGH (3-5x throughput improvement, fewer deadlocks)**
846
847Long-running transactions hold locks that block other queries. Keep transactions as short as possible.
848
849**Incorrect (long transaction with external calls):**
850
851```sql
852begin;
853select * from orders where id = 1 for update; -- Lock acquired
854
855-- Application makes HTTP call to payment API (2-5 seconds)
856-- Other queries on this row are blocked!
857
858update orders set status = 'paid' where id = 1;
859commit; -- Lock held for entire duration
860```
861
862**Correct (minimal transaction scope):**
863
864```sql
865-- Validate data and call APIs outside transaction
866-- Application: response = await paymentAPI.charge(...)
867
868-- Only hold lock for the actual update
869begin;
870update orders
871set status = 'paid', payment_id = $1
872where id = $2 and status = 'pending'
873returning *;
874commit; -- Lock held for milliseconds
875-- Abort queries running longer than 30 seconds
876set statement_timeout = '30s';
877
878-- Or per-session
879set local statement_timeout = '5s';
880```
881
882Use `statement_timeout` to prevent runaway transactions:
883
884Reference: https://www.postgresql.org/docs/current/tutorial-transactions.html
885
886---
887
888### 5.2 Prevent Deadlocks with Consistent Lock Ordering
889
890**Impact: MEDIUM-HIGH (Eliminate deadlock errors, improve reliability)**
891
892Deadlocks occur when transactions lock resources in different orders. Always
893acquire locks in a consistent order.
894
895**Incorrect (inconsistent lock ordering):**
896
897```sql
898-- Transaction A -- Transaction B
899begin; begin;
900update accounts update accounts
901set balance = balance - 100 set balance = balance - 50
902where id = 1; where id = 2; -- B locks row 2
903
904update accounts update accounts
905set balance = balance + 100 set balance = balance + 50
906where id = 2; -- A waits for B where id = 1; -- B waits for A
907
908-- DEADLOCK! Both waiting for each other
909```
910
911**Correct (lock rows in consistent order first):**
912
913```sql
914-- Explicitly acquire locks in ID order before updating
915begin;
916select * from accounts where id in (1, 2) order by id for update;
917
918-- Now perform updates in any order - locks already held
919update accounts set balance = balance - 100 where id = 1;
920update accounts set balance = balance + 100 where id = 2;
921commit;
922-- Single statement acquires all locks atomically
923begin;
924update accounts
925set balance = balance + case id
926 when 1 then -100
927 when 2 then 100
928end
929where id in (1, 2);
930commit;
931-- Check for recent deadlocks
932select * from pg_stat_database where deadlocks > 0;
933
934-- Enable deadlock logging
935set log_lock_waits = on;
936set deadlock_timeout = '1s';
937```
938
939Alternative: use a single statement to update atomically:
940Detect deadlocks in logs:
941[Deadlocks](https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-DEADLOCKS)
942
943---
944
945### 5.3 Use Advisory Locks for Application-Level Locking
946
947**Impact: MEDIUM (Efficient coordination without row-level lock overhead)**
948
949Advisory locks provide application-level coordination without requiring database rows to lock.
950
951**Incorrect (creating rows just for locking):**
952
953```sql
954-- Creating dummy rows to lock on
955create table resource_locks (
956 resource_name text primary key
957);
958
959insert into resource_locks values ('report_generator');
960
961-- Lock by selecting the row
962select * from resource_locks where resource_name = 'report_generator' for update;
963```
964
965**Correct (advisory locks):**
966
967```sql
968-- Session-level advisory lock (released on disconnect or unlock)
969select pg_advisory_lock(hashtext('report_generator'));
970-- ... do exclusive work ...
971select pg_advisory_unlock(hashtext('report_generator'));
972
973-- Transaction-level lock (released on commit/rollback)
974begin;
975select pg_advisory_xact_lock(hashtext('daily_report'));
976-- ... do work ...
977commit; -- Lock automatically released
978-- Returns immediately with true/false instead of waiting
979select pg_try_advisory_lock(hashtext('resource_name'));
980
981-- Use in application
982if (acquired) {
983 -- Do work
984 select pg_advisory_unlock(hashtext('resource_name'));
985} else {
986 -- Skip or retry later
987}
988```
989
990Try-lock for non-blocking operations:
991
992Reference: https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS
993
994---
995
996### 5.4 Use SKIP LOCKED for Non-Blocking Queue Processing
997
998**Impact: MEDIUM-HIGH (10x throughput for worker queues)**
999
1000When multiple workers process a queue, SKIP LOCKED allows workers to process different rows without waiting.
1001
1002**Incorrect (workers block each other):**
1003
1004```sql
1005-- Worker 1 and Worker 2 both try to get next job
1006begin;
1007select * from jobs where status = 'pending' order by created_at limit 1 for update;
1008-- Worker 2 waits for Worker 1's lock to release!
1009```
1010
1011**Correct (SKIP LOCKED for parallel processing):**
1012
1013```sql
1014-- Each worker skips locked rows and gets the next available
1015begin;
1016select * from jobs
1017where status = 'pending'
1018order by created_at
1019limit 1
1020for update skip locked;
1021
1022-- Worker 1 gets job 1, Worker 2 gets job 2 (no waiting)
1023
1024update jobs set status = 'processing' where id = $1;
1025commit;
1026-- Atomic claim-and-update in one statement
1027update jobs
1028set status = 'processing', worker_id = $1, started_at = now()
1029where id = (
1030 select id from jobs
1031 where status = 'pending'
1032 order by created_at
1033 limit 1
1034 for update skip locked
1035)
1036returning *;
1037```
1038
1039Complete queue pattern:
1040
1041Reference: https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE
1042
1043---
1044
1045## 6. Data Access Patterns
1046
1047**Impact: MEDIUM**
1048
1049N+1 query elimination, batch operations, cursor-based pagination, and efficient data fetching.
1050
1051### 6.1 Batch INSERT Statements for Bulk Data
1052
1053**Impact: MEDIUM (10-50x faster bulk inserts)**
1054
1055Individual INSERT statements have high overhead. Batch multiple rows in single statements or use COPY.
1056
1057**Incorrect (individual inserts):**
1058
1059```sql
1060-- Each insert is a separate transaction and round trip
1061insert into events (user_id, action) values (1, 'click');
1062insert into events (user_id, action) values (1, 'view');
1063insert into events (user_id, action) values (2, 'click');
1064-- ... 1000 more individual inserts
1065
1066-- 1000 inserts = 1000 round trips = slow
1067```
1068
1069**Correct (batch insert):**
1070
1071```sql
1072-- Multiple rows in single statement
1073insert into events (user_id, action) values
1074 (1, 'click'),
1075 (1, 'view'),
1076 (2, 'click'),
1077 -- ... up to ~1000 rows per batch
1078 (999, 'view');
1079
1080-- One round trip for 1000 rows
1081-- COPY is fastest for bulk loading
1082copy events (user_id, action, created_at)
1083from '/path/to/data.csv'
1084with (format csv, header true);
1085
1086-- Or from stdin in application
1087copy events (user_id, action) from stdin with (format csv);
10881,click
10891,view
10902,click
1091\.
1092```
1093
1094For large imports, use COPY:
1095
1096Reference: https://www.postgresql.org/docs/current/sql-copy.html
1097
1098---
1099
1100### 6.2 Eliminate N+1 Queries with Batch Loading
1101
1102**Impact: MEDIUM-HIGH (10-100x fewer database round trips)**
1103
1104N+1 queries execute one query per item in a loop. Batch them into a single query using arrays or JOINs.
1105
1106**Incorrect (N+1 queries):**
1107
1108```sql
1109-- First query: get all users
1110select id from users where active = true; -- Returns 100 IDs
1111
1112-- Then N queries, one per user
1113select * from orders where user_id = 1;
1114select * from orders where user_id = 2;
1115select * from orders where user_id = 3;
1116-- ... 97 more queries!
1117
1118-- Total: 101 round trips to database
1119```
1120
1121**Correct (single batch query):**
1122
1123```sql
1124-- Collect IDs and query once with ANY
1125select * from orders where user_id = any(array[1, 2, 3, ...]);
1126
1127-- Or use JOIN instead of loop
1128select u.id, u.name, o.*
1129from users u
1130left join orders o on o.user_id = u.id
1131where u.active = true;
1132
1133-- Total: 1 round trip
1134-- Instead of looping in application code:
1135-- for user in users: db.query("SELECT * FROM orders WHERE user_id = $1", user.id)
1136
1137-- Pass array parameter:
1138select * from orders where user_id = any($1::bigint[]);
1139-- Application passes: [1, 2, 3, 4, 5, ...]
1140```
1141
1142Application pattern:
1143
1144Reference: https://supabase.com/docs/guides/database/query-optimization
1145
1146---
1147
1148### 6.3 Use Cursor-Based Pagination Instead of OFFSET
1149
1150**Impact: MEDIUM-HIGH (Consistent O(1) performance regardless of page depth)**
1151
1152OFFSET-based pagination scans all skipped rows, getting slower on deeper pages. Cursor pagination is O(1).
1153
1154**Incorrect (OFFSET pagination):**
1155
1156```sql
1157-- Page 1: scans 20 rows
1158select * from products order by id limit 20 offset 0;
1159
1160-- Page 100: scans 2000 rows to skip 1980
1161select * from products order by id limit 20 offset 1980;
1162
1163-- Page 10000: scans 200,000 rows!
1164select * from products order by id limit 20 offset 199980;
1165```
1166
1167**Correct (cursor/keyset pagination):**
1168
1169```sql
1170-- Page 1: get first 20
1171select * from products order by id limit 20;
1172-- Application stores last_id = 20
1173
1174-- Page 2: start after last ID
1175select * from products where id > 20 order by id limit 20;
1176-- Uses index, always fast regardless of page depth
1177
1178-- Page 10000: same speed as page 1
1179select * from products where id > 199980 order by id limit 20;
1180-- Cursor must include all sort columns
1181select * from products
1182where (created_at, id) > ('2024-01-15 10:00:00', 12345)
1183order by created_at, id
1184limit 20;
1185```
1186
1187For multi-column sorting:
1188
1189Reference: https://supabase.com/docs/guides/database/pagination
1190
1191---
1192
1193### 6.4 Use UPSERT for Insert-or-Update Operations
1194
1195**Impact: MEDIUM (Atomic operation, eliminates race conditions)**
1196
1197Using separate SELECT-then-INSERT/UPDATE creates race conditions. Use INSERT ... ON CONFLICT for atomic upserts.
1198
1199**Incorrect (check-then-insert race condition):**
1200
1201```sql
1202-- Race condition: two requests check simultaneously
1203select * from settings where user_id = 123 and key = 'theme';
1204-- Both find nothing
1205
1206-- Both try to insert
1207insert into settings (user_id, key, value) values (123, 'theme', 'dark');
1208-- One succeeds, one fails with duplicate key error!
1209```
1210
1211**Correct (atomic UPSERT):**
1212
1213```sql
1214-- Single atomic operation
1215insert into settings (user_id, key, value)
1216values (123, 'theme', 'dark')
1217on conflict (user_id, key)
1218do update set value = excluded.value, updated_at = now();
1219
1220-- Returns the inserted/updated row
1221insert into settings (user_id, key, value)
1222values (123, 'theme', 'dark')
1223on conflict (user_id, key)
1224do update set value = excluded.value
1225returning *;
1226-- Insert only if not exists (no update)
1227insert into page_views (page_id, user_id)
1228values (1, 123)
1229on conflict (page_id, user_id) do nothing;
1230```
1231
1232Insert-or-ignore pattern:
1233
1234Reference: https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT
1235
1236---
1237
1238## 7. Monitoring & Diagnostics
1239
1240**Impact: LOW-MEDIUM**
1241
1242Using pg_stat_statements, EXPLAIN ANALYZE, metrics collection, and performance diagnostics.
1243
1244### 7.1 Enable pg_stat_statements for Query Analysis
1245
1246**Impact: LOW-MEDIUM (Identify top resource-consuming queries)**
1247
1248pg_stat_statements tracks execution statistics for all queries, helping identify slow and frequent queries.
1249
1250**Incorrect (no visibility into query patterns):**
1251
1252```sql
1253-- Database is slow, but which queries are the problem?
1254-- No way to know without pg_stat_statements
1255```
1256
1257**Correct (enable and query pg_stat_statements):**
1258
1259```sql
1260-- Enable the extension
1261create extension if not exists pg_stat_statements;
1262
1263-- Find slowest queries by total time
1264select
1265 calls,
1266 round(total_exec_time::numeric, 2) as total_time_ms,
1267 round(mean_exec_time::numeric, 2) as mean_time_ms,
1268 query
1269from pg_stat_statements
1270order by total_exec_time desc
1271limit 10;
1272
1273-- Find most frequent queries
1274select calls, query
1275from pg_stat_statements
1276order by calls desc
1277limit 10;
1278
1279-- Reset statistics after optimization
1280select pg_stat_statements_reset();
1281-- Queries with high mean time (candidates for optimization)
1282select query, mean_exec_time, calls
1283from pg_stat_statements
1284where mean_exec_time > 100 -- > 100ms average
1285order by mean_exec_time desc;
1286```
1287
1288Key metrics to monitor:
1289
1290Reference: https://supabase.com/docs/guides/database/extensions/pg_stat_statements
1291
1292---
1293
1294### 7.2 Maintain Table Statistics with VACUUM and ANALYZE
1295
1296**Impact: MEDIUM (2-10x better query plans with accurate statistics)**
1297
1298Outdated statistics cause the query planner to make poor decisions. VACUUM reclaims space, ANALYZE updates statistics.
1299
1300**Incorrect (stale statistics):**
1301
1302```sql
1303-- Table has 1M rows but stats say 1000
1304-- Query planner chooses wrong strategy
1305explain select * from orders where status = 'pending';
1306-- Shows: Seq Scan (because stats show small table)
1307-- Actually: Index Scan would be much faster
1308```
1309
1310**Correct (maintain fresh statistics):**
1311
1312```sql
1313-- Manually analyze after large data changes
1314analyze orders;
1315
1316-- Analyze specific columns used in WHERE clauses
1317analyze orders (status, created_at);
1318
1319-- Check when tables were last analyzed
1320select
1321 relname,
1322 last_vacuum,
1323 last_autovacuum,
1324 last_analyze,
1325 last_autoanalyze
1326from pg_stat_user_tables
1327order by last_analyze nulls first;
1328-- Increase frequency for high-churn tables
1329alter table orders set (
1330 autovacuum_vacuum_scale_factor = 0.05, -- Vacuum at 5% dead tuples (default 20%)
1331 autovacuum_analyze_scale_factor = 0.02 -- Analyze at 2% changes (default 10%)
1332);
1333
1334-- Check autovacuum status
1335select * from pg_stat_progress_vacuum;
1336```
1337
1338Autovacuum tuning for busy tables:
1339
1340Reference: https://supabase.com/docs/guides/database/database-size#vacuum-operations
1341
1342---
1343
1344### 7.3 Use EXPLAIN ANALYZE to Diagnose Slow Queries
1345
1346**Impact: LOW-MEDIUM (Identify exact bottlenecks in query execution)**
1347
1348EXPLAIN ANALYZE executes the query and shows actual timings, revealing the true performance bottlenecks.
1349
1350**Incorrect (guessing at performance issues):**
1351
1352```sql
1353-- Query is slow, but why?
1354select * from orders where customer_id = 123 and status = 'pending';
1355-- "It must be missing an index" - but which one?
1356```
1357
1358**Correct (use EXPLAIN ANALYZE):**
1359
1360```sql
1361explain (analyze, buffers, format text)
1362select * from orders where customer_id = 123 and status = 'pending';
1363
1364-- Output reveals the issue:
1365-- Seq Scan on orders (cost=0.00..25000.00 rows=50 width=100) (actual time=0.015..450.123 rows=50 loops=1)
1366-- Filter: ((customer_id = 123) AND (status = 'pending'::text))
1367-- Rows Removed by Filter: 999950
1368-- Buffers: shared hit=5000 read=15000
1369-- Planning Time: 0.150 ms
1370-- Execution Time: 450.500 ms
1371-- Seq Scan on large tables = missing index
1372-- Rows Removed by Filter = poor selectivity or missing index
1373-- Buffers: read >> hit = data not cached, needs more memory
1374-- Nested Loop with high loops = consider different join strategy
1375-- Sort Method: external merge = work_mem too low
1376```
1377
1378Key things to look for:
1379
1380Reference: https://supabase.com/docs/guides/database/inspect
1381
1382---
1383
1384## 8. Advanced Features
1385
1386**Impact: LOW**
1387
1388Full-text search, JSONB optimization, PostGIS, extensions, and advanced Postgres features.
1389
1390### 8.1 Index JSONB Columns for Efficient Querying
1391
1392**Impact: MEDIUM (10-100x faster JSONB queries with proper indexing)**
1393
1394JSONB queries without indexes scan the entire table. Use GIN indexes for containment queries.
1395
1396**Incorrect (no index on JSONB):**
1397
1398```sql
1399create table products (
1400 id bigint primary key,
1401 attributes jsonb
1402);
1403
1404-- Full table scan for every query
1405select * from products where attributes @> '{"color": "red"}';
1406select * from products where attributes->>'brand' = 'Nike';
1407```
1408
1409**Correct (GIN index for JSONB):**
1410
1411```sql
1412-- GIN index for containment operators (@>, ?, ?&, ?|)
1413create index products_attrs_gin on products using gin (attributes);
1414
1415-- Now containment queries use the index
1416select * from products where attributes @> '{"color": "red"}';
1417
1418-- For specific key lookups, use expression index
1419create index products_brand_idx on products ((attributes->>'brand'));
1420select * from products where attributes->>'brand' = 'Nike';
1421-- jsonb_ops (default): supports all operators, larger index
1422create index idx1 on products using gin (attributes);
1423
1424-- jsonb_path_ops: only @> operator, but 2-3x smaller index
1425create index idx2 on products using gin (attributes jsonb_path_ops);
1426```
1427
1428Choose the right operator class:
1429
1430Reference: https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING
1431
1432---
1433
1434### 8.2 Use tsvector for Full-Text Search
1435
1436**Impact: MEDIUM (100x faster than LIKE, with ranking support)**
1437
1438LIKE with wildcards can't use indexes. Full-text search with tsvector is orders of magnitude faster.
1439
1440**Incorrect (LIKE pattern matching):**
1441
1442```sql
1443-- Cannot use index, scans all rows
1444select * from articles where content like '%postgresql%';
1445
1446-- Case-insensitive makes it worse
1447select * from articles where lower(content) like '%postgresql%';
1448```
1449
1450**Correct (full-text search with tsvector):**
1451
1452```sql
1453-- Add tsvector column and index
1454alter table articles add column search_vector tsvector
1455 generated always as (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(content,''))) stored;
1456
1457create index articles_search_idx on articles using gin (search_vector);
1458
1459-- Fast full-text search
1460select * from articles
1461where search_vector @@ to_tsquery('english', 'postgresql & performance');
1462
1463-- With ranking
1464select *, ts_rank(search_vector, query) as rank
1465from articles, to_tsquery('english', 'postgresql') query
1466where search_vector @@ query
1467order by rank desc;
1468-- AND: both terms required
1469to_tsquery('postgresql & performance')
1470
1471-- OR: either term
1472to_tsquery('postgresql | mysql')
1473
1474-- Prefix matching
1475to_tsquery('post:*')
1476```
1477
1478Search multiple terms:
1479
1480Reference: https://supabase.com/docs/guides/database/full-text-search
1481
1482---
1483
1484## References
1485
1486- https://www.postgresql.org/docs/current/
1487- https://supabase.com/docs
1488- https://wiki.postgresql.org/wiki/Performance_Optimization
1489- https://supabase.com/docs/guides/database/overview
1490- https://supabase.com/docs/guides/auth/row-level-security
1491
Zidong-LLC/BIBLIOTECA · skills/skills/programming-languages/python-expert/AGENTS.md
@@ +1 @@
1# Python Expert Guidelines
2
3**A comprehensive guide for AI agents writing and reviewing Python code**, organized by priority and impact.
4
5---
6
7## Table of Contents
8
9### Correctness — **CRITICAL**
101. [Avoid Mutable Default Arguments](#avoid-mutable-default-arguments)
112. [Proper Error Handling](#proper-error-handling)
12
13### Type Safety — **HIGH**
143. [Use Type Hints](#use-type-hints)
154. [Use Dataclasses](#use-dataclasses)
16
17### Performance — **HIGH**
185. [Use List Comprehensions](#use-list-comprehensions)
196. [Use Context Managers](#use-context-managers)
20
21### Style — **MEDIUM**
227. [Follow PEP 8 Style Guide](#follow-pep-8-style-guide)
238. [Write Docstrings](#write-docstrings)
24
25---
26
27## Correctness
28
29### Avoid Mutable Default Arguments
30
31**Impact: CRITICAL** | **Category: correctness** | **Tags:** bugs, defaults, mutable, gotcha
32
33Mutable default arguments (like lists or dicts) are shared across all calls to the function.
34
35#### Why This Matters
36
37Because the default value is evaluated only once at function definition time, subsequent calls will persist changes made to the default object, leading to extremely subtle and frustrating bugs.
38
39#### ❌ Incorrect
40
41```python
42def add_item(item, items=[]): # BUG: [] is shared!
43 items.append(item)
44 return items
45
46print(add_item("a")) # ['a']
47print(add_item("b")) # ['a', 'b'] - Unexpected!
48```
49
50#### ✅ Correct
51
52```python
53def add_item(item: str, items: list[str] | None = None) -> list[str]:
54 """Add an item to a list, creating a new list if none provided.
55
56 Args:
57 item: The item to add
58 items: Optional existing list to add to
59
60 Returns:
61 The list with the new item added
62 """
63 if items is None:
64 items = []
65 items.append(item)
66 return items
67```
68
69[➡️ Full details: correctness-mutable-defaults.md](rules/correctness-mutable-defaults.md)
70
71---
72
73### Proper Error Handling
74
75**Impact: CRITICAL** | **Category: correctness** | **Tags:** errors, exceptions, reliability
76
77Always handle errors explicitly. Don't use bare except clauses or ignore errors silently.
78
79#### ❌ Incorrect
80
81```python
82try:
83 result = risky_operation()
84except:
85 pass # Silent failure!
86```
87
88#### ✅ Correct
89
90```python
91try:
92 config = json.loads(config_file.read())
93except json.JSONDecodeError as e:
94 logger.error(f"Invalid JSON in config file: {e}")
95 config = get_default_config()
96except FileNotFoundError:
97 logger.warning("Config file not found, using defaults")
98 config = get_default_config()
99```
100
101[➡️ Full details: correctness-error-handling.md](rules/correctness-error-handling.md)
102
103---
104
105## Type Safety
106
107### Use Type Hints
108
109**Impact: HIGH** | **Category: type-safety** | **Tags:** types, mypy, annotations, documentation
110
111Type hints enable static analysis, improve IDE support, and serve as documentation.
112
113#### Why This Matters
114
115Python's dynamic nature can lead to runtime errors that are hard to catch. Type hints allow tools like `mypy` to verify code correctness before execution.
116
117#### ❌ Incorrect
118
119```python
120def get_user(id):
121 return users.get(id)
122```
123
124#### ✅ Correct
125
126```python
127from typing import Optional, Dict, Any
128
129def get_user(user_id: int) -> Optional[Dict[str, Any]]:
130 """Fetch user by ID.
131
132 Args:
133 user_id: The unique identifier for the user
134
135 Returns:
136 User dictionary if found, None otherwise
137 """
138 return users.get(user_id)
139```
140
141[➡️ Full details: type-hints.md](rules/type-hints.md)
142
143---
144
145### Use Dataclasses
146
147**Impact: HIGH** | **Category: type-safety** | **Tags:** dataclasses, classes, data, boilerplate
148
149Use the `@dataclass` decorator for classes that primarily store data.
150
151#### Why This Matters
152
153Dataclasses automatically generate `__init__`, `__repr__`, and `__eq__` methods, reducing boilerplate and ensuring consistent behavior for data containers.
154
155#### ❌ Incorrect
156
157```python
158class User:
159 def __init__(self, id, name, email):
160 self.id = id
161 self.name = name
162 self.email = email
163
164 def __repr__(self):
165 return f"User(id={self.id}, name={self.name}, email={self.email})"
166
167 def __eq__(self, other):
168 return self.id == other.id and self.name == other.name
169```
170
171#### ✅ Correct
172
173```python
174from dataclasses import dataclass
175
176@dataclass
177class User:
178 id: int
179 name: str
180 email: str
181
182# With additional configuration
183@dataclass(frozen=True) # Immutable
184class Config:
185 api_key: str
186 timeout: int = 30
187```
188
189[➡️ Full details: type-dataclasses.md](rules/type-dataclasses.md)
190
191---
192
193## Performance
194
195### Use List Comprehensions
196
197**Impact: HIGH** | **Category: performance** | **Tags:** comprehensions, pythonic, efficiency
198
199Use list comprehensions for simple transformations and filtering.
200
201#### Why This Matters
202
203List comprehensions are more concise, readable to experienced Pythonistas, and generally faster than equivalent `for` loops because they are optimized in the CPython interpreter.
204
205#### ❌ Incorrect
206
207```python
208squares = []
209for x in range(10):
210 squares.append(x ** 2)
211
212# Filtering with loop
213evens = []
214for x in range(20):
215 if x % 2 == 0:
216 evens.append(x)
217```
218
219#### ✅ Correct
220
221```python
222# Simple transformation
223squares = [x ** 2 for x in range(10)]
224
225# With filtering
226evens = [x for x in range(20) if x % 2 == 0]
227
228# Nested (use sparingly - break into functions if complex)
229matrix = [[i * j for j in range(3)] for i in range(3)]
230```
231
232[➡️ Full details: performance-comprehensions.md](rules/performance-comprehensions.md)
233
234---
235
236### Use Context Managers
237
238**Impact: HIGH** | **Category: performance** | **Tags:** context-managers, with, resources, cleanup
239
240Always use context managers (`with` statements) for resource cleanup.
241
242#### Why This Matters
243
244Manual cleanup is error-prone. If an exception occurs before `close()` is called, the resource (file handle, database connection, lock) may remain open, leading to leaks and system instability.
245
246#### ❌ Incorrect
247
248```python
249f = open('file.txt')
250data = f.read()
251f.close() # May never be called if exception occurs!
252```
253
254#### ✅ Correct
255
256```python
257with open('file.txt') as f:
258 data = f.read()
259# File is automatically closed, even if exception occurs
260
261# Multiple resources
262with open('input.txt') as infile, open('output.txt', 'w') as outfile:
263 outfile.write(infile.read().upper())
264```
265
266[➡️ Full details: performance-context-managers.md](rules/performance-context-managers.md)
267
268---
269
270## Style
271
272### Follow PEP 8 Style Guide
273
274**Impact: MEDIUM** | **Category: style** | **Tags:** pep8, python, style, conventions
275
276Python's official style guide ensures readable, consistent code.
277
278#### Why This Matters
279
280Readability is a core Python philosophy. Consistent naming and formatting make the codebase maintainable and reduce friction for teams.
281
282#### ❌ Incorrect
283
284```python
285def CalculateTotal(itemPrice,qty):
286 return itemPrice*qty
287
288class user_account:
289 pass
290
291x=1+2
292```
293
294#### ✅ Correct
295
296```python
297def calculate_total(item_price: float, quantity: int) -> float:
298 """Calculate the total price for items."""
299 return item_price * quantity
300
301
302class UserAccount:
303 """Represents a user account in the system."""
304 pass
305
306
307x = 1 + 2
308```
309
310[➡️ Full details: style-pep8.md](rules/style-pep8.md)
311
312---
313
314### Write Docstrings
315
316**Impact: MEDIUM** | **Category: style** | **Tags:** documentation, docstrings, google-style
317
318Write comprehensive docstrings for all public functions, classes, and modules.
319
320#### Why This Matters
321
322Good documentation makes code self-explanatory and enables IDEs to provide better autocomplete and hover information. It also serves as the primary reference for API users.
323
324#### ❌ Incorrect
325
326```python
327def process(data, config):
328 # processes the data
329 return result
330```
331
332#### ✅ Correct
333
334```python
335def process_user_data(
336 data: Dict[str, Any],
337 config: ProcessConfig
338) -> ProcessResult:
339 """Process user data according to the provided configuration.
340
341 Takes raw user data and applies transformations, validation,
342 and enrichment based on the configuration settings.
343
344 Args:
345 data: Raw user data as a dictionary containing at minimum
346 'user_id' and 'email' keys.
347 config: Processing configuration specifying transformations
348 to apply and validation rules.
349
350 Returns:
351 ProcessResult containing the transformed data and any
352 validation warnings encountered.
353
354 Raises:
355 ValidationError: If required fields are missing from data.
356 ConfigError: If config contains invalid transformation rules.
357
358 Example:
359 >>> config = ProcessConfig(normalize_email=True)
360 >>> result = process_user_data({'user_id': 1, 'email': 'TEST@Example.com'}, config)
361 >>> result.data['email']
362 'test@example.com'
363 """
364 ...
365```
366
367[➡️ Full details: style-docstrings.md](rules/style-docstrings.md)
368
369---
370
371## Quick Reference
372
373### Python Code Checklist
374
375**Correctness (CRITICAL - address first)**
376- [ ] No mutable default arguments
377- [ ] Specific exception handling (no bare `except:`)
378- [ ] Edge cases handled
379- [ ] Input validation present
380
381**Type Safety (HIGH)**
382- [ ] Type hints on all functions
383- [ ] Return types specified
384- [ ] Using dataclasses for data containers
385- [ ] Generic types where appropriate
386
387**Performance (HIGH)**
388- [ ] List comprehensions over loops where readable
389- [ ] Context managers for all resources
390- [ ] Generators for large data
391- [ ] Built-in functions leveraged
392
393**Style (MEDIUM)**
394- [ ] PEP 8 compliant
395- [ ] Docstrings on public functions
396- [ ] Meaningful variable names
397- [ ] 88-100 character line limit
398
399---
400
401## Severity Levels
402
403| Level | Description | Examples | Action |
404|-------|-------------|----------|--------|
405| **CRITICAL** | Bugs, data corruption, security issues | Mutable defaults, bare except | Fix immediately |
406| **HIGH** | Correctness risks, maintainability issues | Missing types, resource leaks | Fix before merge |
407| **MEDIUM** | Code quality, readability | Style violations, missing docs | Fix or accept with TODO |
408| **LOW** | Minor improvements, preferences | Minor formatting | Optional |
409
410---
411
412## Code Review Output Format
413
414When reviewing Python code, structure your output as:
415
416```markdown
417## Summary
418[Brief overview of the code and main issues found]
419
420## Critical Issues 🔴
421
422### 1. [Issue Title]
423**File:** `path/to/file.py:line`
424**Issue:** [Description of the problem]
425**Impact:** [Why this matters]
426**Fix:**
427```python
428# Corrected code
429```
430
431## High Priority 🟠
432
433### 1. [Issue Title]
434[Continue pattern...]
435
436## Medium Priority 🟡
437
438[Continue pattern...]
439
440## Recommendations
441- [General improvement suggestion]
442- [Best practice to adopt]
443
444## Summary
445- 🔴 CRITICAL: X
446- 🟠 HIGH: X
447- 🟡 MEDIUM: X
448
449**Recommendation:** [Overall assessment and next steps]
450```
451
452---
453
454## References
455
456- Individual rule files in `rules/` directory
457- [PEP 8 - Style Guide for Python Code](https://peps.python.org/pep-0008/)
458- [PEP 257 - Docstring Conventions](https://peps.python.org/pep-0257/)
459- [PEP 484 - Type Hints](https://peps.python.org/pep-0484/)
460- [Python typing module documentation](https://docs.python.org/3/library/typing.html)
461
@@ −1 +1 @@
1−# Postgres Best Practices
1+# Python Expert Guidelines
22
3−**Version 1.0.0**
4−Supabase
5−January 2026
3+**A comprehensive guide for AI agents writing and reviewing Python code**, organized by priority and impact.
64
7−> This document is optimized for AI agents and LLMs. Rules are prioritized by performance impact.
8−
95 ---
106
11−## Abstract
12−
13−Comprehensive Postgres performance optimization guide for developers using Supabase and Postgres. Contains performance rules across 8 categories, prioritized by impact from critical (query performance, connection management) to incremental (advanced features). Each rule includes detailed explanations, incorrect vs. correct SQL examples, query plan analysis, and specific performance metrics to guide automated optimization and code generation.
14−
15−---
16−
177 ## Table of Contents
188
19−1. [Query Performance](#query-performance) - **CRITICAL**
20− - 1.1 [Add Indexes on WHERE and JOIN Columns](#11-add-indexes-on-where-and-join-columns)
21− - 1.2 [Choose the Right Index Type for Your Data](#12-choose-the-right-index-type-for-your-data)
22− - 1.3 [Create Composite Indexes for Multi-Column Queries](#13-create-composite-indexes-for-multi-column-queries)
23− - 1.4 [Use Covering Indexes to Avoid Table Lookups](#14-use-covering-indexes-to-avoid-table-lookups)
24− - 1.5 [Use Partial Indexes for Filtered Queries](#15-use-partial-indexes-for-filtered-queries)
9+### Correctness — **CRITICAL**
10+1. [Avoid Mutable Default Arguments](#avoid-mutable-default-arguments)
11+2. [Proper Error Handling](#proper-error-handling)
2512
26−2. [Connection Management](#connection-management) - **CRITICAL**
27− - 2.1 [Configure Idle Connection Timeouts](#21-configure-idle-connection-timeouts)
28− - 2.2 [Set Appropriate Connection Limits](#22-set-appropriate-connection-limits)
29− - 2.3 [Use Connection Pooling for All Applications](#23-use-connection-pooling-for-all-applications)
30− - 2.4 [Use Prepared Statements Correctly with Pooling](#24-use-prepared-statements-correctly-with-pooling)
13+### Type Safety — **HIGH**
14+3. [Use Type Hints](#use-type-hints)
15+4. [Use Dataclasses](#use-dataclasses)
3116
32−3. [Security & RLS](#security-rls) - **CRITICAL**
33− - 3.1 [Apply Principle of Least Privilege](#31-apply-principle-of-least-privilege)
34− - 3.2 [Enable Row Level Security for Multi-Tenant Data](#32-enable-row-level-security-for-multi-tenant-data)
35− - 3.3 [Optimize RLS Policies for Performance](#33-optimize-rls-policies-for-performance)
17+### Performance — **HIGH**
18+5. [Use List Comprehensions](#use-list-comprehensions)
19+6. [Use Context Managers](#use-context-managers)
3620
37−4. [Schema Design](#schema-design) - **HIGH**
38− - 4.1 [Choose Appropriate Data Types](#41-choose-appropriate-data-types)
39− - 4.2 [Index Foreign Key Columns](#42-index-foreign-key-columns)
40− - 4.3 [Partition Large Tables for Better Performance](#43-partition-large-tables-for-better-performance)
41− - 4.4 [Select Optimal Primary Key Strategy](#44-select-optimal-primary-key-strategy)
42− - 4.5 [Use Lowercase Identifiers for Compatibility](#45-use-lowercase-identifiers-for-compatibility)
21+### Style — **MEDIUM**
22+7. [Follow PEP 8 Style Guide](#follow-pep-8-style-guide)
23+8. [Write Docstrings](#write-docstrings)
4324
44−5. [Concurrency & Locking](#concurrency-locking) - **MEDIUM-HIGH**
45− - 5.1 [Keep Transactions Short to Reduce Lock Contention](#51-keep-transactions-short-to-reduce-lock-contention)
46− - 5.2 [Prevent Deadlocks with Consistent Lock Ordering](#52-prevent-deadlocks-with-consistent-lock-ordering)
47− - 5.3 [Use Advisory Locks for Application-Level Locking](#53-use-advisory-locks-for-application-level-locking)
48− - 5.4 [Use SKIP LOCKED for Non-Blocking Queue Processing](#54-use-skip-locked-for-non-blocking-queue-processing)
49−
50−6. [Data Access Patterns](#data-access-patterns) - **MEDIUM**
51− - 6.1 [Batch INSERT Statements for Bulk Data](#61-batch-insert-statements-for-bulk-data)
52− - 6.2 [Eliminate N+1 Queries with Batch Loading](#62-eliminate-n1-queries-with-batch-loading)
53− - 6.3 [Use Cursor-Based Pagination Instead of OFFSET](#63-use-cursor-based-pagination-instead-of-offset)
54− - 6.4 [Use UPSERT for Insert-or-Update Operations](#64-use-upsert-for-insert-or-update-operations)
55−
56−7. [Monitoring & Diagnostics](#monitoring-diagnostics) - **LOW-MEDIUM**
57− - 7.1 [Enable pg_stat_statements for Query Analysis](#71-enable-pgstatstatements-for-query-analysis)
58− - 7.2 [Maintain Table Statistics with VACUUM and ANALYZE](#72-maintain-table-statistics-with-vacuum-and-analyze)
59− - 7.3 [Use EXPLAIN ANALYZE to Diagnose Slow Queries](#73-use-explain-analyze-to-diagnose-slow-queries)
60−
61−8. [Advanced Features](#advanced-features) - **LOW**
62− - 8.1 [Index JSONB Columns for Efficient Querying](#81-index-jsonb-columns-for-efficient-querying)
63− - 8.2 [Use tsvector for Full-Text Search](#82-use-tsvector-for-full-text-search)
64−
6525 ---
6626
67−## 1. Query Performance
27+## Correctness
6828
69−**Impact: CRITICAL**
29+### Avoid Mutable Default Arguments
7030
71−Slow queries, missing indexes, inefficient query plans. The most common source of Postgres performance issues.
31+**Impact: CRITICAL** | **Category: correctness** | **Tags:** bugs, defaults, mutable, gotcha
7232
73−### 1.1 Add Indexes on WHERE and JOIN Columns
33+Mutable default arguments (like lists or dicts) are shared across all calls to the function.
7434
75−**Impact: CRITICAL (100-1000x faster queries on large tables)**
35+#### Why This Matters
7636
77−Queries filtering or joining on unindexed columns cause full table scans, which become exponentially slower as tables grow.
37+Because the default value is evaluated only once at function definition time, subsequent calls will persist changes made to the default object, leading to extremely subtle and frustrating bugs.
7838
79−**Incorrect (sequential scan on large table):**
39+#### ❌ Incorrect
8040
81−```sql
82−-- No index on customer_id causes full table scan
83−select * from orders where customer_id = 123;
41+```python
42+def add_item(item, items=[]): # BUG: [] is shared!
43+ items.append(item)
44+ return items
8445
85−-- EXPLAIN shows: Seq Scan on orders (cost=0.00..25000.00 rows=100 width=85)
46+print(add_item("a")) # ['a']
47+print(add_item("b")) # ['a', 'b'] - Unexpected!
8648 ```
8749
88−**Correct (index scan):**
50+#### ✅ Correct
8951
90−```sql
91−-- Create index on frequently filtered column
92−create index orders_customer_id_idx on orders (customer_id);
93−
94−select * from orders where customer_id = 123;
95−
96−-- EXPLAIN shows: Index Scan using orders_customer_id_idx (cost=0.42..8.44 rows=100 width=85)
97−-- Index the referencing column
98−create index orders_customer_id_idx on orders (customer_id);
99−
100−select c.name, o.total
101−from customers c
102−join orders o on o.customer_id = c.id;
52+```python
53+def add_item(item: str, items: list[str] | None = None) -> list[str]:
54+ """Add an item to a list, creating a new list if none provided.
55+
56+ Args:
57+ item: The item to add
58+ items: Optional existing list to add to
59+
60+ Returns:
61+ The list with the new item added
62+ """
63+ if items is None:
64+ items = []
65+ items.append(item)
66+ return items
10367 ```
10468
105−For JOIN columns, always index the foreign key side:
69+[➡️ Full details: correctness-mutable-defaults.md](rules/correctness-mutable-defaults.md)
10670
107−Reference: https://supabase.com/docs/guides/database/query-optimization
108−
10971 ---
11072
111−### 1.2 Choose the Right Index Type for Your Data
73+### Proper Error Handling
11274
113−**Impact: HIGH (10-100x improvement with correct index type)**
75+**Impact: CRITICAL** | **Category: correctness** | **Tags:** errors, exceptions, reliability
11476
115−Different index types excel at different query patterns. The default B-tree isn't always optimal.
77+Always handle errors explicitly. Don't use bare except clauses or ignore errors silently.
11678
117−**Incorrect (B-tree for JSONB containment):**
79+#### ❌ Incorrect
11880
119−```sql
120−-- B-tree cannot optimize containment operators
121−create index products_attrs_idx on products (attributes);
122−select * from products where attributes @> '{"color": "red"}';
123−-- Full table scan - B-tree doesn't support @> operator
81+```python
82+try:
83+ result = risky_operation()
84+except:
85+ pass # Silent failure!
12486 ```
12587
126−**Correct (GIN for JSONB):**
88+#### ✅ Correct
12789
128−```sql
129−-- GIN supports @>, ?, ?&, ?| operators
130−create index products_attrs_idx on products using gin (attributes);
131−select * from products where attributes @> '{"color": "red"}';
132−-- B-tree (default): =, <, >, BETWEEN, IN, IS NULL
133−create index users_created_idx on users (created_at);
134−
135−-- GIN: arrays, JSONB, full-text search
136−create index posts_tags_idx on posts using gin (tags);
137−
138−-- BRIN: large time-series tables (10-100x smaller)
139−create index events_time_idx on events using brin (created_at);
140−
141−-- Hash: equality-only (slightly faster than B-tree for =)
142−create index sessions_token_idx on sessions using hash (token);
90+```python
91+try:
92+ config = json.loads(config_file.read())
93+except json.JSONDecodeError as e:
94+ logger.error(f"Invalid JSON in config file: {e}")
95+ config = get_default_config()
96+except FileNotFoundError:
97+ logger.warning("Config file not found, using defaults")
98+ config = get_default_config()
14399 ```
144100
145−Index type guide:
101+[➡️ Full details: correctness-error-handling.md](rules/correctness-error-handling.md)
146102
147−Reference: https://www.postgresql.org/docs/current/indexes-types.html
148−
149103 ---
150104
151−### 1.3 Create Composite Indexes for Multi-Column Queries
105+## Type Safety
152106
153−**Impact: HIGH (5-10x faster multi-column queries)**
107+### Use Type Hints
154108
155−When queries filter on multiple columns, a composite index is more efficient than separate single-column indexes.
109+**Impact: HIGH** | **Category: type-safety** | **Tags:** types, mypy, annotations, documentation
156110
157−**Incorrect (separate indexes require bitmap scan):**
111+Type hints enable static analysis, improve IDE support, and serve as documentation.
158112
159−```sql
160−-- Two separate indexes
161−create index orders_status_idx on orders (status);
162−create index orders_created_idx on orders (created_at);
113+#### Why This Matters
163114
164−-- Query must combine both indexes (slower)
165−select * from orders where status = 'pending' and created_at > '2024-01-01';
166−```
115+Python's dynamic nature can lead to runtime errors that are hard to catch. Type hints allow tools like `mypy` to verify code correctness before execution.
167116
168−**Correct (composite index):**
117+#### ❌ Incorrect
169118
170−```sql
171−-- Single composite index (leftmost column first for equality checks)
172−create index orders_status_created_idx on orders (status, created_at);
173−
174−-- Query uses one efficient index scan
175−select * from orders where status = 'pending' and created_at > '2024-01-01';
176−-- Good: status (=) before created_at (>)
177−create index idx on orders (status, created_at);
178−
179−-- Works for: WHERE status = 'pending'
180−-- Works for: WHERE status = 'pending' AND created_at > '2024-01-01'
181−-- Does NOT work for: WHERE created_at > '2024-01-01' (leftmost prefix rule)
119+```python
120+def get_user(id):
121+ return users.get(id)
182122 ```
183123
184−**Column order matters** - place equality columns first, range columns last:
124+#### ✅ Correct
185125
186−Reference: https://www.postgresql.org/docs/current/indexes-multicolumn.html
126+```python
127+from typing import Optional, Dict, Any
187128
188−---
189−
190−### 1.4 Use Covering Indexes to Avoid Table Lookups
191−
192−**Impact: MEDIUM-HIGH (2-5x faster queries by eliminating heap fetches)**
193−
194−Covering indexes include all columns needed by a query, enabling index-only scans that skip the table entirely.
195−
196−**Incorrect (index scan + heap fetch):**
197−
198−```sql
199−create index users_email_idx on users (email);
200−
201−-- Must fetch name and created_at from table heap
202−select email, name, created_at from users where email = 'user@example.com';
129+def get_user(user_id: int) -> Optional[Dict[str, Any]]:
130+ """Fetch user by ID.
131+
132+ Args:
133+ user_id: The unique identifier for the user
134+
135+ Returns:
136+ User dictionary if found, None otherwise
137+ """
138+ return users.get(user_id)
203139 ```
204140
205−**Correct (index-only scan with INCLUDE):**
141+[➡️ Full details: type-hints.md](rules/type-hints.md)
206142
207−```sql
208−-- Include non-searchable columns in the index
209−create index users_email_idx on users (email) include (name, created_at);
210−
211−-- All columns served from index, no table access needed
212−select email, name, created_at from users where email = 'user@example.com';
213−-- Searching by status, but also need customer_id and total
214−create index orders_status_idx on orders (status) include (customer_id, total);
215−
216−select status, customer_id, total from orders where status = 'shipped';
217−```
218−
219−Use INCLUDE for columns you SELECT but don't filter on:
220−
221−Reference: https://www.postgresql.org/docs/current/indexes-index-only-scans.html
222−
223143 ---
224144
225−### 1.5 Use Partial Indexes for Filtered Queries
145+### Use Dataclasses
226146
227−**Impact: HIGH (5-20x smaller indexes, faster writes and queries)**
147+**Impact: HIGH** | **Category: type-safety** | **Tags:** dataclasses, classes, data, boilerplate
228148
229−Partial indexes only include rows matching a WHERE condition, making them smaller and faster when queries consistently filter on the same condition.
149+Use the `@dataclass` decorator for classes that primarily store data.
230150
231−**Incorrect (full index includes irrelevant rows):**
151+#### Why This Matters
232152
233−```sql
234−-- Index includes all rows, even soft-deleted ones
235−create index users_email_idx on users (email);
153+Dataclasses automatically generate `__init__`, `__repr__`, and `__eq__` methods, reducing boilerplate and ensuring consistent behavior for data containers.
236154
237−-- Query always filters active users
238−select * from users where email = 'user@example.com' and deleted_at is null;
239−```
155+#### ❌ Incorrect
240156
241−**Correct (partial index matches query filter):**
242−
243−```sql
244−-- Index only includes active users
245−create index users_active_email_idx on users (email)
246−where deleted_at is null;
247−
248−-- Query uses the smaller, faster index
249−select * from users where email = 'user@example.com' and deleted_at is null;
250−-- Only pending orders (status rarely changes once completed)
251−create index orders_pending_idx on orders (created_at)
252−where status = 'pending';
253−
254−-- Only non-null values
255−create index products_sku_idx on products (sku)
256−where sku is not null;
157+```python
158+class User:
159+ def __init__(self, id, name, email):
160+ self.id = id
161+ self.name = name
162+ self.email = email
163+
164+ def __repr__(self):
165+ return f"User(id={self.id}, name={self.name}, email={self.email})"
166+
167+ def __eq__(self, other):
168+ return self.id == other.id and self.name == other.name
257169 ```
258170
259−Common use cases for partial indexes:
171+#### ✅ Correct
260172
261−Reference: https://www.postgresql.org/docs/current/indexes-partial.html
173+```python
174+from dataclasses import dataclass
262175
263−---
176+@dataclass
177+class User:
178+ id: int
179+ name: str
180+ email: str
264181
265−## 2. Connection Management
266−
267−**Impact: CRITICAL**
268−
269−Connection pooling, limits, and serverless strategies. Critical for applications with high concurrency or serverless deployments.
270−
271−### 2.1 Configure Idle Connection Timeouts
272−
273−**Impact: HIGH (Reclaim 30-50% of connection slots from idle clients)**
274−
275−Idle connections waste resources. Configure timeouts to automatically reclaim them.
276−
277−**Incorrect (connections held indefinitely):**
278−
279−```sql
280−-- No timeout configured
281−show idle_in_transaction_session_timeout; -- 0 (disabled)
282−
283−-- Connections stay open forever, even when idle
284−select pid, state, state_change, query
285−from pg_stat_activity
286−where state = 'idle in transaction';
287−-- Shows transactions idle for hours, holding locks
182+# With additional configuration
183+@dataclass(frozen=True) # Immutable
184+class Config:
185+ api_key: str
186+ timeout: int = 30
288187 ```
289188
290−**Correct (automatic cleanup of idle connections):**
189+[➡️ Full details: type-dataclasses.md](rules/type-dataclasses.md)
291190
292−```ini
293−-- Terminate connections idle in transaction after 30 seconds
294−alter system set idle_in_transaction_session_timeout = '30s';
295−
296−-- Terminate completely idle connections after 10 minutes
297−alter system set idle_session_timeout = '10min';
298−
299−-- Reload configuration
300−select pg_reload_conf();
301−# pgbouncer.ini
302−server_idle_timeout = 60
303−client_idle_timeout = 300
304−```
305−
306−For pooled connections, configure at the pooler level:
307−
308−Reference: https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT
309−
310191 ---
311192
312−### 2.2 Set Appropriate Connection Limits
193+## Performance
313194
314−**Impact: CRITICAL (Prevent database crashes and memory exhaustion)**
195+### Use List Comprehensions
315196
316−Too many connections exhaust memory and degrade performance. Set limits based on available resources.
197+**Impact: HIGH** | **Category: performance** | **Tags:** comprehensions, pythonic, efficiency
317198
318−**Incorrect (unlimited or excessive connections):**
199+Use list comprehensions for simple transformations and filtering.
319200
320−```sql
321−-- Default max_connections = 100, but often increased blindly
322−show max_connections; -- 500 (way too high for 4GB RAM)
201+#### Why This Matters
323202
324−-- Each connection uses 1-3MB RAM
325−-- 500 connections * 2MB = 1GB just for connections!
326−-- Out of memory errors under load
327−```
203+List comprehensions are more concise, readable to experienced Pythonistas, and generally faster than equivalent `for` loops because they are optimized in the CPython interpreter.
328204
329−**Correct (calculate based on resources):**
205+#### ❌ Incorrect
330206
331−```sql
332−-- Formula: max_connections = (RAM in MB / 5MB per connection) - reserved
333−-- For 4GB RAM: (4096 / 5) - 10 = ~800 theoretical max
334−-- But practically, 100-200 is better for query performance
207+```python
208+squares = []
209+for x in range(10):
210+ squares.append(x ** 2)
335211
336−-- Recommended settings for 4GB RAM
337−alter system set max_connections = 100;
338−
339−-- Also set work_mem appropriately
340−-- work_mem * max_connections should not exceed 25% of RAM
341−alter system set work_mem = '8MB'; -- 8MB * 100 = 800MB max
342−select count(*), state from pg_stat_activity group by state;
212+# Filtering with loop
213+evens = []
214+for x in range(20):
215+ if x % 2 == 0:
216+ evens.append(x)
343217 ```
344218
345−Monitor connection usage:
219+#### ✅ Correct
346220
347−Reference: https://supabase.com/docs/guides/platform/performance#connection-management
221+```python
222+# Simple transformation
223+squares = [x ** 2 for x in range(10)]
348224
349−---
225+# With filtering
226+evens = [x for x in range(20) if x % 2 == 0]
350227
351−### 2.3 Use Connection Pooling for All Applications
352−
353−**Impact: CRITICAL (Handle 10-100x more concurrent users)**
354−
355−Postgres connections are expensive (1-3MB RAM each). Without pooling, applications exhaust connections under load.
356−
357−**Incorrect (new connection per request):**
358−
359−```sql
360−-- Each request creates a new connection
361−-- Application code: db.connect() per request
362−-- Result: 500 concurrent users = 500 connections = crashed database
363−
364−-- Check current connections
365−select count(*) from pg_stat_activity; -- 487 connections!
228+# Nested (use sparingly - break into functions if complex)
229+matrix = [[i * j for j in range(3)] for i in range(3)]
366230 ```
367231
368−**Correct (connection pooling):**
232+[➡️ Full details: performance-comprehensions.md](rules/performance-comprehensions.md)
369233
370−```sql
371−-- Use a pooler like PgBouncer between app and database
372−-- Application connects to pooler, pooler reuses a small pool to Postgres
373−
374−-- Configure pool_size based on: (CPU cores * 2) + spindle_count
375−-- Example for 4 cores: pool_size = 10
376−
377−-- Result: 500 concurrent users share 10 actual connections
378−select count(*) from pg_stat_activity; -- 10 connections
379−```
380−
381−Pool modes:
382−- **Transaction mode**: connection returned after each transaction (best for most apps)
383−- **Session mode**: connection held for entire session (needed for prepared statements, temp tables)
384−
385−Reference: https://supabase.com/docs/guides/database/connecting-to-postgres#connection-pooler
386−
387234 ---
388235
389−### 2.4 Use Prepared Statements Correctly with Pooling
236+### Use Context Managers
390237
391−**Impact: HIGH (Avoid prepared statement conflicts in pooled environments)**
238+**Impact: HIGH** | **Category: performance** | **Tags:** context-managers, with, resources, cleanup
392239
393−Prepared statements are tied to individual database connections. In transaction-mode pooling, connections are shared, causing conflicts.
240+Always use context managers (`with` statements) for resource cleanup.
394241
395−**Incorrect (named prepared statements with transaction pooling):**
242+#### Why This Matters
396243
397−```sql
398−-- Named prepared statement
399−prepare get_user as select * from users where id = $1;
244+Manual cleanup is error-prone. If an exception occurs before `close()` is called, the resource (file handle, database connection, lock) may remain open, leading to leaks and system instability.
400245
401−-- In transaction mode pooling, next request may get different connection
402−execute get_user(123);
403−-- ERROR: prepared statement "get_user" does not exist
404−```
246+#### ❌ Incorrect
405247
406−**Correct (use unnamed statements or session mode):**
407−
408−```sql
409−-- Option 1: Use unnamed prepared statements (most ORMs do this automatically)
410−-- The query is prepared and executed in a single protocol message
411−
412−-- Option 2: Deallocate after use in transaction mode
413−prepare get_user as select * from users where id = $1;
414−execute get_user(123);
415−deallocate get_user;
416−
417−-- Option 3: Use session mode pooling (port 5432 vs 6543)
418−-- Connection is held for entire session, prepared statements persist
419−-- Many drivers use prepared statements by default
420−-- Node.js pg: { prepare: false } to disable
421−-- JDBC: prepareThreshold=0 to disable
248+```python
249+f = open('file.txt')
250+data = f.read()
251+f.close() # May never be called if exception occurs!
422252 ```
423253
424−Check your driver settings:
254+#### ✅ Correct
425255
426−Reference: https://supabase.com/docs/guides/database/connecting-to-postgres#connection-pool-modes
256+```python
257+with open('file.txt') as f:
258+ data = f.read()
259+# File is automatically closed, even if exception occurs
427260
428−---
429−
430−## 3. Security & RLS
431−
432−**Impact: CRITICAL**
433−
434−Row-Level Security policies, privilege management, and authentication patterns.
435−
436−### 3.1 Apply Principle of Least Privilege
437−
438−**Impact: MEDIUM (Reduced attack surface, better audit trail)**
439−
440−Grant only the minimum permissions required. Never use superuser for application queries.
441−
442−**Incorrect (overly broad permissions):**
443−
444−```sql
445−-- Application uses superuser connection
446−-- Or grants ALL to application role
447−grant all privileges on all tables in schema public to app_user;
448−grant all privileges on all sequences in schema public to app_user;
449−
450−-- Any SQL injection becomes catastrophic
451−-- drop table users; cascades to everything
261+# Multiple resources
262+with open('input.txt') as infile, open('output.txt', 'w') as outfile:
263+ outfile.write(infile.read().upper())
452264 ```
453265
454−**Correct (minimal, specific grants):**
266+[➡️ Full details: performance-context-managers.md](rules/performance-context-managers.md)
455267
456−```sql
457−-- Create role with no default privileges
458−create role app_readonly nologin;
459−
460−-- Grant only SELECT on specific tables
461−grant usage on schema public to app_readonly;
462−grant select on public.products, public.categories to app_readonly;
463−
464−-- Create role for writes with limited scope
465−create role app_writer nologin;
466−grant usage on schema public to app_writer;
467−grant select, insert, update on public.orders to app_writer;
468−grant usage on sequence orders_id_seq to app_writer;
469−-- No DELETE permission
470−
471−-- Login role inherits from these
472−create role app_user login password 'xxx';
473−grant app_writer to app_user;
474−-- Revoke default public access
475−revoke all on schema public from public;
476−revoke all on all tables in schema public from public;
477−```
478−
479−Revoke public defaults:
480−
481−Reference: https://supabase.com/blog/postgres-roles-and-privileges
482−
483268 ---
484269
485−### 3.2 Enable Row Level Security for Multi-Tenant Data
270+## Style
486271
487−**Impact: CRITICAL (Database-enforced tenant isolation, prevent data leaks)**
272+### Follow PEP 8 Style Guide
488273
489−Row Level Security (RLS) enforces data access at the database level, ensuring users only see their own data.
274+**Impact: MEDIUM** | **Category: style** | **Tags:** pep8, python, style, conventions
490275
491−**Incorrect (application-level filtering only):**
276+Python's official style guide ensures readable, consistent code.
492277
493−```sql
494−-- Relying only on application to filter
495−select * from orders where user_id = $current_user_id;
278+#### Why This Matters
496279
497−-- Bug or bypass means all data is exposed!
498−select * from orders; -- Returns ALL orders
499−```
280+Readability is a core Python philosophy. Consistent naming and formatting make the codebase maintainable and reduce friction for teams.
500281
501−**Correct (database-enforced RLS):**
282+#### ❌ Incorrect
502283
503−```sql
504−-- Enable RLS on the table
505−alter table orders enable row level security;
284+```python
285+def CalculateTotal(itemPrice,qty):
286+ return itemPrice*qty
506287
507−-- Create policy for users to see only their orders
508−create policy orders_user_policy on orders
509− for all
510− using (user_id = current_setting('app.current_user_id')::bigint);
288+class user_account:
289+ pass
511290
512−-- Force RLS even for table owners
513−alter table orders force row level security;
514−
515−-- Set user context and query
516−set app.current_user_id = '123';
517−select * from orders; -- Only returns orders for user 123
518−create policy orders_user_policy on orders
519− for all
520− to authenticated
521− using (user_id = auth.uid());
291+x=1+2
522292 ```
523293
524−Policy for authenticated role:
294+#### ✅ Correct
525295
526−Reference: https://supabase.com/docs/guides/database/postgres/row-level-security
296+```python
297+def calculate_total(item_price: float, quantity: int) -> float:
298+ """Calculate the total price for items."""
299+ return item_price * quantity
527300
528−---
529301
530−### 3.3 Optimize RLS Policies for Performance
302+class UserAccount:
303+ """Represents a user account in the system."""
304+ pass
531305
532−**Impact: HIGH (5-10x faster RLS queries with proper patterns)**
533306
534−Poorly written RLS policies can cause severe performance issues. Use subqueries and indexes strategically.
535−
536−**Incorrect (function called for every row):**
537−
538−```sql
539−create policy orders_policy on orders
540− using (auth.uid() = user_id); -- auth.uid() called per row!
541−
542−-- With 1M rows, auth.uid() is called 1M times
307+x = 1 + 2
543308 ```
544309
545−**Correct (wrap functions in SELECT):**
310+[➡️ Full details: style-pep8.md](rules/style-pep8.md)
546311
547−```sql
548−create policy orders_policy on orders
549− using ((select auth.uid()) = user_id); -- Called once, cached
550−
551−-- 100x+ faster on large tables
552−-- Create helper function (runs as definer, bypasses RLS)
553−create or replace function is_team_member(team_id bigint)
554−returns boolean
555−language sql
556−security definer
557−set search_path = ''
558−as $$
559− select exists (
560− select 1 from public.team_members
561− where team_id = $1 and user_id = (select auth.uid())
562− );
563−$$;
564−
565−-- Use in policy (indexed lookup, not per-row check)
566−create policy team_orders_policy on orders
567− using ((select is_team_member(team_id)));
568−create index orders_user_id_idx on orders (user_id);
569−```
570−
571−Use security definer functions for complex checks:
572−Always add indexes on columns used in RLS policies:
573−
574−Reference: https://supabase.com/docs/guides/database/postgres/row-level-security#rls-performance-recommendations
575−
576312 ---
577313
578−## 4. Schema Design
314+### Write Docstrings
579315
580−**Impact: HIGH**
316+**Impact: MEDIUM** | **Category: style** | **Tags:** documentation, docstrings, google-style
581317
582−Table design, index strategies, partitioning, and data type selection. Foundation for long-term performance.
318+Write comprehensive docstrings for all public functions, classes, and modules.
583319
584−### 4.1 Choose Appropriate Data Types
320+#### Why This Matters
585321
586−**Impact: HIGH (50% storage reduction, faster comparisons)**
322+Good documentation makes code self-explanatory and enables IDEs to provide better autocomplete and hover information. It also serves as the primary reference for API users.
587323
588−Using the right data types reduces storage, improves query performance, and prevents bugs.
324+#### ❌ Incorrect
589325
590−**Incorrect (wrong data types):**
591−
592−```sql
593−create table users (
594− id int, -- Will overflow at 2.1 billion
595− email varchar(255), -- Unnecessary length limit
596− created_at timestamp, -- Missing timezone info
597− is_active varchar(5), -- String for boolean
598− price varchar(20) -- String for numeric
599−);
326+```python
327+def process(data, config):
328+ # processes the data
329+ return result
600330 ```
601331
602−**Correct (appropriate data types):**
332+#### ✅ Correct
603333
604−```sql
605−create table users (
606− id bigint generated always as identity primary key, -- 9 quintillion max
607− email text, -- No artificial limit, same performance as varchar
608− created_at timestamptz, -- Always store timezone-aware timestamps
609− is_active boolean default true, -- 1 byte vs variable string length
610− price numeric(10,2) -- Exact decimal arithmetic
611−);
612−-- IDs: use bigint, not int (future-proofing)
613−-- Strings: use text, not varchar(n) unless constraint needed
614−-- Time: use timestamptz, not timestamp
615−-- Money: use numeric, not float (precision matters)
616−-- Enums: use text with check constraint or create enum type
334+```python
335+def process_user_data(
336+ data: Dict[str, Any],
337+ config: ProcessConfig
338+) -> ProcessResult:
339+ """Process user data according to the provided configuration.
340+
341+ Takes raw user data and applies transformations, validation,
342+ and enrichment based on the configuration settings.
343+
344+ Args:
345+ data: Raw user data as a dictionary containing at minimum
346+ 'user_id' and 'email' keys.
347+ config: Processing configuration specifying transformations
348+ to apply and validation rules.
349+
350+ Returns:
351+ ProcessResult containing the transformed data and any
352+ validation warnings encountered.
353+
354+ Raises:
355+ ValidationError: If required fields are missing from data.
356+ ConfigError: If config contains invalid transformation rules.
357+
358+ Example:
359+ >>> config = ProcessConfig(normalize_email=True)
360+ >>> result = process_user_data({'user_id': 1, 'email': 'TEST@Example.com'}, config)
361+ >>> result.data['email']
362+ 'test@example.com'
363+ """
364+ ...
617365 ```
618366
619−Key guidelines:
367+[➡️ Full details: style-docstrings.md](rules/style-docstrings.md)
620368
621−Reference: https://www.postgresql.org/docs/current/datatype.html
622−
623369 ---
624370
625−### 4.2 Index Foreign Key Columns
371+## Quick Reference
626372
627−**Impact: HIGH (10-100x faster JOINs and CASCADE operations)**
373+### Python Code Checklist
628374
629−Postgres does not automatically index foreign key columns. Missing indexes cause slow JOINs and CASCADE operations.
375+**Correctness (CRITICAL - address first)**
376+- [ ] No mutable default arguments
377+- [ ] Specific exception handling (no bare `except:`)
378+- [ ] Edge cases handled
379+- [ ] Input validation present
630380
631−**Incorrect (unindexed foreign key):**
381+**Type Safety (HIGH)**
382+- [ ] Type hints on all functions
383+- [ ] Return types specified
384+- [ ] Using dataclasses for data containers
385+- [ ] Generic types where appropriate
632386
633−```sql
634−create table orders (
635− id bigint generated always as identity primary key,
636− customer_id bigint references customers(id) on delete cascade,
637− total numeric(10,2)
638−);
387+**Performance (HIGH)**
388+- [ ] List comprehensions over loops where readable
389+- [ ] Context managers for all resources
390+- [ ] Generators for large data
391+- [ ] Built-in functions leveraged
639392
640−-- No index on customer_id!
641−-- JOINs and ON DELETE CASCADE both require full table scan
642−select * from orders where customer_id = 123; -- Seq Scan
643−delete from customers where id = 123; -- Locks table, scans all orders
644−```
393+**Style (MEDIUM)**
394+- [ ] PEP 8 compliant
395+- [ ] Docstrings on public functions
396+- [ ] Meaningful variable names
397+- [ ] 88-100 character line limit
645398
646−**Correct (indexed foreign key):**
647−
648−```sql
649−create table orders (
650− id bigint generated always as identity primary key,
651− customer_id bigint references customers(id) on delete cascade,
652− total numeric(10,2)
653−);
654−
655−-- Always index the FK column
656−create index orders_customer_id_idx on orders (customer_id);
657−
658−-- Now JOINs and cascades are fast
659−select * from orders where customer_id = 123; -- Index Scan
660−delete from customers where id = 123; -- Uses index, fast cascade
661−select
662− conrelid::regclass as table_name,
663− a.attname as fk_column
664−from pg_constraint c
665−join pg_attribute a on a.attrelid = c.conrelid and a.attnum = any(c.conkey)
666−where c.contype = 'f'
667− and not exists (
668− select 1 from pg_index i
669− where i.indrelid = c.conrelid and a.attnum = any(i.indkey)
670− );
671−```
672−
673−Find missing FK indexes:
674−
675−Reference: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK
676−
677399 ---
678400
679−### 4.3 Partition Large Tables for Better Performance
401+## Severity Levels
680402
681−**Impact: MEDIUM-HIGH (5-20x faster queries and maintenance on large tables)**
403+| Level | Description | Examples | Action |
404+|-------|-------------|----------|--------|
405+| **CRITICAL** | Bugs, data corruption, security issues | Mutable defaults, bare except | Fix immediately |
406+| **HIGH** | Correctness risks, maintainability issues | Missing types, resource leaks | Fix before merge |
407+| **MEDIUM** | Code quality, readability | Style violations, missing docs | Fix or accept with TODO |
408+| **LOW** | Minor improvements, preferences | Minor formatting | Optional |
682409
683−Partitioning splits a large table into smaller pieces, improving query performance and maintenance operations.
684−
685−**Incorrect (single large table):**
686−
687−```sql
688−create table events (
689− id bigint generated always as identity,
690− created_at timestamptz,
691− data jsonb
692−);
693−
694−-- 500M rows, queries scan everything
695−select * from events where created_at > '2024-01-01'; -- Slow
696−vacuum events; -- Takes hours, locks table
697−```
698−
699−**Correct (partitioned by time range):**
700−
701−```sql
702−create table events (
703− id bigint generated always as identity,
704− created_at timestamptz not null,
705− data jsonb
706−) partition by range (created_at);
707−
708−-- Create partitions for each month
709−create table events_2024_01 partition of events
710− for values from ('2024-01-01') to ('2024-02-01');
711−
712−create table events_2024_02 partition of events
713− for values from ('2024-02-01') to ('2024-03-01');
714−
715−-- Queries only scan relevant partitions
716−select * from events where created_at > '2024-01-15'; -- Only scans events_2024_01+
717−
718−-- Drop old data instantly
719−drop table events_2023_01; -- Instant vs DELETE taking hours
720−```
721−
722−When to partition:
723−- Tables > 100M rows
724−- Time-series data with date-based queries
725−- Need to efficiently drop old data
726−
727−Reference: https://www.postgresql.org/docs/current/ddl-partitioning.html
728−
729410 ---
730411
731−### 4.4 Select Optimal Primary Key Strategy
412+## Code Review Output Format
732413
733−**Impact: HIGH (Better index locality, reduced fragmentation)**
414+When reviewing Python code, structure your output as:
734415
735−Primary key choice affects insert performance, index size, and replication
736−efficiency.
416+```markdown
417+## Summary
418+[Brief overview of the code and main issues found]
737419
738−**Incorrect (problematic PK choices):**
420+## Critical Issues 🔴
739421
740−```sql
741−-- identity is the SQL-standard approach
742−create table users (
743− id serial primary key -- Works, but IDENTITY is recommended
744−);
745−
746−-- Random UUIDs (v4) cause index fragmentation
747−create table orders (
748− id uuid default gen_random_uuid() primary key -- UUIDv4 = random = scattered inserts
749−);
422+### 1. [Issue Title]
423+**File:** `path/to/file.py:line`
424+**Issue:** [Description of the problem]
425+**Impact:** [Why this matters]
426+**Fix:**
427+```python
428+# Corrected code
750429 ```
751430
752−**Correct (optimal PK strategies):**
431+## High Priority 🟠
753432
754−```sql
755−-- Use IDENTITY for sequential IDs (SQL-standard, best for most cases)
756−create table users (
757− id bigint generated always as identity primary key
758−);
433+### 1. [Issue Title]
434+[Continue pattern...]
759435
760−-- For distributed systems needing UUIDs, use UUIDv7 (time-ordered)
761−-- Requires pg_uuidv7 extension: create extension pg_uuidv7;
762−create table orders (
763− id uuid default uuid_generate_v7() primary key -- Time-ordered, no fragmentation
764−);
436+## Medium Priority 🟡
765437
766−-- Alternative: time-prefixed IDs for sortable, distributed IDs (no extension needed)
767−create table events (
768− id text default concat(
769− to_char(now() at time zone 'utc', 'YYYYMMDDHH24MISSMS'),
770− gen_random_uuid()::text
771− ) primary key
772−);
773−```
438+[Continue pattern...]
774439
775−Guidelines:
776−- Single database: `bigint identity` (sequential, 8 bytes, SQL-standard)
777−- Distributed/exposed IDs: UUIDv7 (requires pg_uuidv7) or ULID (time-ordered, no
778− fragmentation)
779−- `serial` works but `identity` is SQL-standard and preferred for new
780− applications
781−- Avoid random UUIDs (v4) as primary keys on large tables (causes index
782− fragmentation)
783−[Identity Columns](https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-PARMS-GENERATED-IDENTITY)
440+## Recommendations
441+- [General improvement suggestion]
442+- [Best practice to adopt]
784443
785−---
444+## Summary
445+- 🔴 CRITICAL: X
446+- 🟠 HIGH: X
447+- 🟡 MEDIUM: X
786448
787−### 4.5 Use Lowercase Identifiers for Compatibility
788−
789−**Impact: MEDIUM (Avoid case-sensitivity bugs with tools, ORMs, and AI assistants)**
790−
791−PostgreSQL folds unquoted identifiers to lowercase. Quoted mixed-case identifiers require quotes forever and cause issues with tools, ORMs, and AI assistants that may not recognize them.
792−
793−**Incorrect (mixed-case identifiers):**
794−
795−```sql
796−-- Quoted identifiers preserve case but require quotes everywhere
797−CREATE TABLE "Users" (
798− "userId" bigint PRIMARY KEY,
799− "firstName" text,
800− "lastName" text
801−);
802−
803−-- Must always quote or queries fail
804−SELECT "firstName" FROM "Users" WHERE "userId" = 1;
805−
806−-- This fails - Users becomes users without quotes
807−SELECT firstName FROM Users;
808−-- ERROR: relation "users" does not exist
449+**Recommendation:** [Overall assessment and next steps]
809450 ```
810451
811−**Correct (lowercase snake_case):**
812−
813−```sql
814−-- Unquoted lowercase identifiers are portable and tool-friendly
815−CREATE TABLE users (
816− user_id bigint PRIMARY KEY,
817− first_name text,
818− last_name text
819−);
820−
821−-- Works without quotes, recognized by all tools
822−SELECT first_name FROM users WHERE user_id = 1;
823−-- ORMs often generate quoted camelCase - configure them to use snake_case
824−-- Migrations from other databases may preserve original casing
825−-- Some GUI tools quote identifiers by default - disable this
826−
827−-- If stuck with mixed-case, create views as a compatibility layer
828−CREATE VIEW users AS SELECT "userId" AS user_id, "firstName" AS first_name FROM "Users";
829−```
830−
831−Common sources of mixed-case identifiers:
832−
833−Reference: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
834−
835452 ---
836453
837−## 5. Concurrency & Locking
838−
839−**Impact: MEDIUM-HIGH**
840−
841−Transaction management, isolation levels, deadlock prevention, and lock contention patterns.
842−
843−### 5.1 Keep Transactions Short to Reduce Lock Contention
844−
845−**Impact: MEDIUM-HIGH (3-5x throughput improvement, fewer deadlocks)**
846−
847−Long-running transactions hold locks that block other queries. Keep transactions as short as possible.
848−
849−**Incorrect (long transaction with external calls):**
850−
851−```sql
852−begin;
853−select * from orders where id = 1 for update; -- Lock acquired
854−
855−-- Application makes HTTP call to payment API (2-5 seconds)
856−-- Other queries on this row are blocked!
857−
858−update orders set status = 'paid' where id = 1;
859−commit; -- Lock held for entire duration
860−```
861−
862−**Correct (minimal transaction scope):**
863−
864−```sql
865−-- Validate data and call APIs outside transaction
866−-- Application: response = await paymentAPI.charge(...)
867−
868−-- Only hold lock for the actual update
869−begin;
870−update orders
871−set status = 'paid', payment_id = $1
872−where id = $2 and status = 'pending'
873−returning *;
874−commit; -- Lock held for milliseconds
875−-- Abort queries running longer than 30 seconds
876−set statement_timeout = '30s';
877−
878−-- Or per-session
879−set local statement_timeout = '5s';
880−```
881−
882−Use `statement_timeout` to prevent runaway transactions:
883−
884−Reference: https://www.postgresql.org/docs/current/tutorial-transactions.html
885−
886−---
887−
888−### 5.2 Prevent Deadlocks with Consistent Lock Ordering
889−
890−**Impact: MEDIUM-HIGH (Eliminate deadlock errors, improve reliability)**
891−
892−Deadlocks occur when transactions lock resources in different orders. Always
893−acquire locks in a consistent order.
894−
895−**Incorrect (inconsistent lock ordering):**
896−
897−```sql
898−-- Transaction A -- Transaction B
899−begin; begin;
900−update accounts update accounts
901−set balance = balance - 100 set balance = balance - 50
902−where id = 1; where id = 2; -- B locks row 2
903−
904−update accounts update accounts
905−set balance = balance + 100 set balance = balance + 50
906−where id = 2; -- A waits for B where id = 1; -- B waits for A
907−
908−-- DEADLOCK! Both waiting for each other
909−```
910−
911−**Correct (lock rows in consistent order first):**
912−
913−```sql
914−-- Explicitly acquire locks in ID order before updating
915−begin;
916−select * from accounts where id in (1, 2) order by id for update;
917−
918−-- Now perform updates in any order - locks already held
919−update accounts set balance = balance - 100 where id = 1;
920−update accounts set balance = balance + 100 where id = 2;
921−commit;
922−-- Single statement acquires all locks atomically
923−begin;
924−update accounts
925−set balance = balance + case id
926− when 1 then -100
927− when 2 then 100
928−end
929−where id in (1, 2);
930−commit;
931−-- Check for recent deadlocks
932−select * from pg_stat_database where deadlocks > 0;
933−
934−-- Enable deadlock logging
935−set log_lock_waits = on;
936−set deadlock_timeout = '1s';
937−```
938−
939−Alternative: use a single statement to update atomically:
940−Detect deadlocks in logs:
941−[Deadlocks](https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-DEADLOCKS)
942−
943−---
944−
945−### 5.3 Use Advisory Locks for Application-Level Locking
946−
947−**Impact: MEDIUM (Efficient coordination without row-level lock overhead)**
948−
949−Advisory locks provide application-level coordination without requiring database rows to lock.
950−
951−**Incorrect (creating rows just for locking):**
952−
953−```sql
954−-- Creating dummy rows to lock on
955−create table resource_locks (
956− resource_name text primary key
957−);
958−
959−insert into resource_locks values ('report_generator');
960−
961−-- Lock by selecting the row
962−select * from resource_locks where resource_name = 'report_generator' for update;
963−```
964−
965−**Correct (advisory locks):**
966−
967−```sql
968−-- Session-level advisory lock (released on disconnect or unlock)
969−select pg_advisory_lock(hashtext('report_generator'));
970−-- ... do exclusive work ...
971−select pg_advisory_unlock(hashtext('report_generator'));
972−
973−-- Transaction-level lock (released on commit/rollback)
974−begin;
975−select pg_advisory_xact_lock(hashtext('daily_report'));
976−-- ... do work ...
977−commit; -- Lock automatically released
978−-- Returns immediately with true/false instead of waiting
979−select pg_try_advisory_lock(hashtext('resource_name'));
980−
981−-- Use in application
982−if (acquired) {
983− -- Do work
984− select pg_advisory_unlock(hashtext('resource_name'));
985−} else {
986− -- Skip or retry later
987−}
988−```
989−
990−Try-lock for non-blocking operations:
991−
992−Reference: https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS
993−
994−---
995−
996−### 5.4 Use SKIP LOCKED for Non-Blocking Queue Processing
997−
998−**Impact: MEDIUM-HIGH (10x throughput for worker queues)**
999−
1000−When multiple workers process a queue, SKIP LOCKED allows workers to process different rows without waiting.
1001−
1002−**Incorrect (workers block each other):**
1003−
1004−```sql
1005−-- Worker 1 and Worker 2 both try to get next job
1006−begin;
1007−select * from jobs where status = 'pending' order by created_at limit 1 for update;
1008−-- Worker 2 waits for Worker 1's lock to release!
1009−```
1010−
1011−**Correct (SKIP LOCKED for parallel processing):**
1012−
1013−```sql
1014−-- Each worker skips locked rows and gets the next available
1015−begin;
1016−select * from jobs
1017−where status = 'pending'
1018−order by created_at
1019−limit 1
1020−for update skip locked;
1021−
1022−-- Worker 1 gets job 1, Worker 2 gets job 2 (no waiting)
1023−
1024−update jobs set status = 'processing' where id = $1;
1025−commit;
1026−-- Atomic claim-and-update in one statement
1027−update jobs
1028−set status = 'processing', worker_id = $1, started_at = now()
1029−where id = (
1030− select id from jobs
1031− where status = 'pending'
1032− order by created_at
1033− limit 1
1034− for update skip locked
1035−)
1036−returning *;
1037−```
1038−
1039−Complete queue pattern:
1040−
1041−Reference: https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE
1042−
1043−---
1044−
1045−## 6. Data Access Patterns
1046−
1047−**Impact: MEDIUM**
1048−
1049−N+1 query elimination, batch operations, cursor-based pagination, and efficient data fetching.
1050−
1051−### 6.1 Batch INSERT Statements for Bulk Data
1052−
1053−**Impact: MEDIUM (10-50x faster bulk inserts)**
1054−
1055−Individual INSERT statements have high overhead. Batch multiple rows in single statements or use COPY.
1056−
1057−**Incorrect (individual inserts):**
1058−
1059−```sql
1060−-- Each insert is a separate transaction and round trip
1061−insert into events (user_id, action) values (1, 'click');
1062−insert into events (user_id, action) values (1, 'view');
1063−insert into events (user_id, action) values (2, 'click');
1064−-- ... 1000 more individual inserts
1065−
1066−-- 1000 inserts = 1000 round trips = slow
1067−```
1068−
1069−**Correct (batch insert):**
1070−
1071−```sql
1072−-- Multiple rows in single statement
1073−insert into events (user_id, action) values
1074− (1, 'click'),
1075− (1, 'view'),
1076− (2, 'click'),
1077− -- ... up to ~1000 rows per batch
1078− (999, 'view');
1079−
1080−-- One round trip for 1000 rows
1081−-- COPY is fastest for bulk loading
1082−copy events (user_id, action, created_at)
1083−from '/path/to/data.csv'
1084−with (format csv, header true);
1085−
1086−-- Or from stdin in application
1087−copy events (user_id, action) from stdin with (format csv);
1088−1,click
1089−1,view
1090−2,click
1091−\.
1092−```
1093−
1094−For large imports, use COPY:
1095−
1096−Reference: https://www.postgresql.org/docs/current/sql-copy.html
1097−
1098−---
1099−
1100−### 6.2 Eliminate N+1 Queries with Batch Loading
1101−
1102−**Impact: MEDIUM-HIGH (10-100x fewer database round trips)**
1103−
1104−N+1 queries execute one query per item in a loop. Batch them into a single query using arrays or JOINs.
1105−
1106−**Incorrect (N+1 queries):**
1107−
1108−```sql
1109−-- First query: get all users
1110−select id from users where active = true; -- Returns 100 IDs
1111−
1112−-- Then N queries, one per user
1113−select * from orders where user_id = 1;
1114−select * from orders where user_id = 2;
1115−select * from orders where user_id = 3;
1116−-- ... 97 more queries!
1117−
1118−-- Total: 101 round trips to database
1119−```
1120−
1121−**Correct (single batch query):**
1122−
1123−```sql
1124−-- Collect IDs and query once with ANY
1125−select * from orders where user_id = any(array[1, 2, 3, ...]);
1126−
1127−-- Or use JOIN instead of loop
1128−select u.id, u.name, o.*
1129−from users u
1130−left join orders o on o.user_id = u.id
1131−where u.active = true;
1132−
1133−-- Total: 1 round trip
1134−-- Instead of looping in application code:
1135−-- for user in users: db.query("SELECT * FROM orders WHERE user_id = $1", user.id)
1136−
1137−-- Pass array parameter:
1138−select * from orders where user_id = any($1::bigint[]);
1139−-- Application passes: [1, 2, 3, 4, 5, ...]
1140−```
1141−
1142−Application pattern:
1143−
1144−Reference: https://supabase.com/docs/guides/database/query-optimization
1145−
1146−---
1147−
1148−### 6.3 Use Cursor-Based Pagination Instead of OFFSET
1149−
1150−**Impact: MEDIUM-HIGH (Consistent O(1) performance regardless of page depth)**
1151−
1152−OFFSET-based pagination scans all skipped rows, getting slower on deeper pages. Cursor pagination is O(1).
1153−
1154−**Incorrect (OFFSET pagination):**
1155−
1156−```sql
1157−-- Page 1: scans 20 rows
1158−select * from products order by id limit 20 offset 0;
1159−
1160−-- Page 100: scans 2000 rows to skip 1980
1161−select * from products order by id limit 20 offset 1980;
1162−
1163−-- Page 10000: scans 200,000 rows!
1164−select * from products order by id limit 20 offset 199980;
1165−```
1166−
1167−**Correct (cursor/keyset pagination):**
1168−
1169−```sql
1170−-- Page 1: get first 20
1171−select * from products order by id limit 20;
1172−-- Application stores last_id = 20
1173−
1174−-- Page 2: start after last ID
1175−select * from products where id > 20 order by id limit 20;
1176−-- Uses index, always fast regardless of page depth
1177−
1178−-- Page 10000: same speed as page 1
1179−select * from products where id > 199980 order by id limit 20;
1180−-- Cursor must include all sort columns
1181−select * from products
1182−where (created_at, id) > ('2024-01-15 10:00:00', 12345)
1183−order by created_at, id
1184−limit 20;
1185−```
1186−
1187−For multi-column sorting:
1188−
1189−Reference: https://supabase.com/docs/guides/database/pagination
1190−
1191−---
1192−
1193−### 6.4 Use UPSERT for Insert-or-Update Operations
1194−
1195−**Impact: MEDIUM (Atomic operation, eliminates race conditions)**
1196−
1197−Using separate SELECT-then-INSERT/UPDATE creates race conditions. Use INSERT ... ON CONFLICT for atomic upserts.
1198−
1199−**Incorrect (check-then-insert race condition):**
1200−
1201−```sql
1202−-- Race condition: two requests check simultaneously
1203−select * from settings where user_id = 123 and key = 'theme';
1204−-- Both find nothing
1205−
1206−-- Both try to insert
1207−insert into settings (user_id, key, value) values (123, 'theme', 'dark');
1208−-- One succeeds, one fails with duplicate key error!
1209−```
1210−
1211−**Correct (atomic UPSERT):**
1212−
1213−```sql
1214−-- Single atomic operation
1215−insert into settings (user_id, key, value)
1216−values (123, 'theme', 'dark')
1217−on conflict (user_id, key)
1218−do update set value = excluded.value, updated_at = now();
1219−
1220−-- Returns the inserted/updated row
1221−insert into settings (user_id, key, value)
1222−values (123, 'theme', 'dark')
1223−on conflict (user_id, key)
1224−do update set value = excluded.value
1225−returning *;
1226−-- Insert only if not exists (no update)
1227−insert into page_views (page_id, user_id)
1228−values (1, 123)
1229−on conflict (page_id, user_id) do nothing;
1230−```
1231−
1232−Insert-or-ignore pattern:
1233−
1234−Reference: https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT
1235−
1236−---
1237−
1238−## 7. Monitoring & Diagnostics
1239−
1240−**Impact: LOW-MEDIUM**
1241−
1242−Using pg_stat_statements, EXPLAIN ANALYZE, metrics collection, and performance diagnostics.
1243−
1244−### 7.1 Enable pg_stat_statements for Query Analysis
1245−
1246−**Impact: LOW-MEDIUM (Identify top resource-consuming queries)**
1247−
1248−pg_stat_statements tracks execution statistics for all queries, helping identify slow and frequent queries.
1249−
1250−**Incorrect (no visibility into query patterns):**
1251−
1252−```sql
1253−-- Database is slow, but which queries are the problem?
1254−-- No way to know without pg_stat_statements
1255−```
1256−
1257−**Correct (enable and query pg_stat_statements):**
1258−
1259−```sql
1260−-- Enable the extension
1261−create extension if not exists pg_stat_statements;
1262−
1263−-- Find slowest queries by total time
1264−select
1265− calls,
1266− round(total_exec_time::numeric, 2) as total_time_ms,
1267− round(mean_exec_time::numeric, 2) as mean_time_ms,
1268− query
1269−from pg_stat_statements
1270−order by total_exec_time desc
1271−limit 10;
1272−
1273−-- Find most frequent queries
1274−select calls, query
1275−from pg_stat_statements
1276−order by calls desc
1277−limit 10;
1278−
1279−-- Reset statistics after optimization
1280−select pg_stat_statements_reset();
1281−-- Queries with high mean time (candidates for optimization)
1282−select query, mean_exec_time, calls
1283−from pg_stat_statements
1284−where mean_exec_time > 100 -- > 100ms average
1285−order by mean_exec_time desc;
1286−```
1287−
1288−Key metrics to monitor:
1289−
1290−Reference: https://supabase.com/docs/guides/database/extensions/pg_stat_statements
1291−
1292−---
1293−
1294−### 7.2 Maintain Table Statistics with VACUUM and ANALYZE
1295−
1296−**Impact: MEDIUM (2-10x better query plans with accurate statistics)**
1297−
1298−Outdated statistics cause the query planner to make poor decisions. VACUUM reclaims space, ANALYZE updates statistics.
1299−
1300−**Incorrect (stale statistics):**
1301−
1302−```sql
1303−-- Table has 1M rows but stats say 1000
1304−-- Query planner chooses wrong strategy
1305−explain select * from orders where status = 'pending';
1306−-- Shows: Seq Scan (because stats show small table)
1307−-- Actually: Index Scan would be much faster
1308−```
1309−
1310−**Correct (maintain fresh statistics):**
1311−
1312−```sql
1313−-- Manually analyze after large data changes
1314−analyze orders;
1315−
1316−-- Analyze specific columns used in WHERE clauses
1317−analyze orders (status, created_at);
1318−
1319−-- Check when tables were last analyzed
1320−select
1321− relname,
1322− last_vacuum,
1323− last_autovacuum,
1324− last_analyze,
1325− last_autoanalyze
1326−from pg_stat_user_tables
1327−order by last_analyze nulls first;
1328−-- Increase frequency for high-churn tables
1329−alter table orders set (
1330− autovacuum_vacuum_scale_factor = 0.05, -- Vacuum at 5% dead tuples (default 20%)
1331− autovacuum_analyze_scale_factor = 0.02 -- Analyze at 2% changes (default 10%)
1332−);
1333−
1334−-- Check autovacuum status
1335−select * from pg_stat_progress_vacuum;
1336−```
1337−
1338−Autovacuum tuning for busy tables:
1339−
1340−Reference: https://supabase.com/docs/guides/database/database-size#vacuum-operations
1341−
1342−---
1343−
1344−### 7.3 Use EXPLAIN ANALYZE to Diagnose Slow Queries
1345−
1346−**Impact: LOW-MEDIUM (Identify exact bottlenecks in query execution)**
1347−
1348−EXPLAIN ANALYZE executes the query and shows actual timings, revealing the true performance bottlenecks.
1349−
1350−**Incorrect (guessing at performance issues):**
1351−
1352−```sql
1353−-- Query is slow, but why?
1354−select * from orders where customer_id = 123 and status = 'pending';
1355−-- "It must be missing an index" - but which one?
1356−```
1357−
1358−**Correct (use EXPLAIN ANALYZE):**
1359−
1360−```sql
1361−explain (analyze, buffers, format text)
1362−select * from orders where customer_id = 123 and status = 'pending';
1363−
1364−-- Output reveals the issue:
1365−-- Seq Scan on orders (cost=0.00..25000.00 rows=50 width=100) (actual time=0.015..450.123 rows=50 loops=1)
1366−-- Filter: ((customer_id = 123) AND (status = 'pending'::text))
1367−-- Rows Removed by Filter: 999950
1368−-- Buffers: shared hit=5000 read=15000
1369−-- Planning Time: 0.150 ms
1370−-- Execution Time: 450.500 ms
1371−-- Seq Scan on large tables = missing index
1372−-- Rows Removed by Filter = poor selectivity or missing index
1373−-- Buffers: read >> hit = data not cached, needs more memory
1374−-- Nested Loop with high loops = consider different join strategy
1375−-- Sort Method: external merge = work_mem too low
1376−```
1377−
1378−Key things to look for:
1379−
1380−Reference: https://supabase.com/docs/guides/database/inspect
1381−
1382−---
1383−
1384−## 8. Advanced Features
1385−
1386−**Impact: LOW**
1387−
1388−Full-text search, JSONB optimization, PostGIS, extensions, and advanced Postgres features.
1389−
1390−### 8.1 Index JSONB Columns for Efficient Querying
1391−
1392−**Impact: MEDIUM (10-100x faster JSONB queries with proper indexing)**
1393−
1394−JSONB queries without indexes scan the entire table. Use GIN indexes for containment queries.
1395−
1396−**Incorrect (no index on JSONB):**
1397−
1398−```sql
1399−create table products (
1400− id bigint primary key,
1401− attributes jsonb
1402−);
1403−
1404−-- Full table scan for every query
1405−select * from products where attributes @> '{"color": "red"}';
1406−select * from products where attributes->>'brand' = 'Nike';
1407−```
1408−
1409−**Correct (GIN index for JSONB):**
1410−
1411−```sql
1412−-- GIN index for containment operators (@>, ?, ?&, ?|)
1413−create index products_attrs_gin on products using gin (attributes);
1414−
1415−-- Now containment queries use the index
1416−select * from products where attributes @> '{"color": "red"}';
1417−
1418−-- For specific key lookups, use expression index
1419−create index products_brand_idx on products ((attributes->>'brand'));
1420−select * from products where attributes->>'brand' = 'Nike';
1421−-- jsonb_ops (default): supports all operators, larger index
1422−create index idx1 on products using gin (attributes);
1423−
1424−-- jsonb_path_ops: only @> operator, but 2-3x smaller index
1425−create index idx2 on products using gin (attributes jsonb_path_ops);
1426−```
1427−
1428−Choose the right operator class:
1429−
1430−Reference: https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING
1431−
1432−---
1433−
1434−### 8.2 Use tsvector for Full-Text Search
1435−
1436−**Impact: MEDIUM (100x faster than LIKE, with ranking support)**
1437−
1438−LIKE with wildcards can't use indexes. Full-text search with tsvector is orders of magnitude faster.
1439−
1440−**Incorrect (LIKE pattern matching):**
1441−
1442−```sql
1443−-- Cannot use index, scans all rows
1444−select * from articles where content like '%postgresql%';
1445−
1446−-- Case-insensitive makes it worse
1447−select * from articles where lower(content) like '%postgresql%';
1448−```
1449−
1450−**Correct (full-text search with tsvector):**
1451−
1452−```sql
1453−-- Add tsvector column and index
1454−alter table articles add column search_vector tsvector
1455− generated always as (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(content,''))) stored;
1456−
1457−create index articles_search_idx on articles using gin (search_vector);
1458−
1459−-- Fast full-text search
1460−select * from articles
1461−where search_vector @@ to_tsquery('english', 'postgresql & performance');
1462−
1463−-- With ranking
1464−select *, ts_rank(search_vector, query) as rank
1465−from articles, to_tsquery('english', 'postgresql') query
1466−where search_vector @@ query
1467−order by rank desc;
1468−-- AND: both terms required
1469−to_tsquery('postgresql & performance')
1470−
1471−-- OR: either term
1472−to_tsquery('postgresql | mysql')
1473−
1474−-- Prefix matching
1475−to_tsquery('post:*')
1476−```
1477−
1478−Search multiple terms:
1479−
1480−Reference: https://supabase.com/docs/guides/database/full-text-search
1481−
1482−---
1483−
1484454 ## References
1485455
1486−- https://www.postgresql.org/docs/current/
1487−- https://supabase.com/docs
1488−- https://wiki.postgresql.org/wiki/Performance_Optimization
1489−- https://supabase.com/docs/guides/database/overview
1490−- https://supabase.com/docs/guides/auth/row-level-security
456+- Individual rule files in `rules/` directory
457+- [PEP 8 - Style Guide for Python Code](https://peps.python.org/pep-0008/)
458+- [PEP 257 - Docstring Conventions](https://peps.python.org/pep-0257/)
459+- [PEP 484 - Type Hints](https://peps.python.org/pep-0484/)
460+- [Python typing module documentation](https://docs.python.org/3/library/typing.html)
1491461
