AGENTS.md

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