davila7/claude-code-templates / cli-tool/components/skills/development/postgres-best-practices
postgres-best-practices
VerifiedPostgres performance optimization and best practices from Supabase. Use this skill when writing, reviewing, or optimizing Postgres queries, schema designs, or database configurations.
rules/advanced-full-text-search.md
View on GitHub---
title: Use tsvector for Full-Text Search
impact: MEDIUM
impactDescription: 100x faster than LIKE, with ranking support
tags: full-text-search, tsvector, gin, search
---
## Use tsvector for Full-Text Search
LIKE with wildcards can't use indexes. Full-text search with tsvector is orders of magnitude faster.
**Incorrect (LIKE pattern matching):**
```sql
-- Cannot use index, scans all rows
select * from articles where content like '%postgresql%';
-- Case-insensitive makes it worse
select * from articles where lower(content) like '%postgresql%';
```
**Correct (full-text search with tsvector):**
```sql
-- Add tsvector column and index
alter table articles add column search_vector tsvector
generated always as (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(content,''))) stored;
create index articles_search_idx on articles using gin (search_vector);
-- Fast full-text search
select * from articles
where search_vector @@ to_tsquery('english', 'postgresql & performance');
-- With ranking
select *, ts_rank(search_vector, query) as rank
from articles, to_tsquery('english', 'postgresql') query
where search_vector @@ query
order by rank desc;
```
Search multiple terms:
```sql
-- AND: both terms required
to_tsquery('postgresql & performance')
-- OR: either term
to_tsquery('postgresql | mysql')
-- Prefix matching
to_tsquery('post:*')
```
Reference: [Full Text Search](https://supabase.com/docs/guides/database/full-text-search)
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |