{"items":[{"id":"cmuguczl600o8qu06ul2geh5y","slug":"yusufkaraaslan-skill-seekers-skill-seekers","name":"skill-seekers","description":"Convert documentation websites, GitHub repositories, and PDFs into Claude AI skills with automatic conflict detection","authorId":"gh:yusufkaraaslan","authorName":"yusufkaraaslan","version":"0.1.0","category":"MCP","securityLevel":"Sandbox","downloadsCount":0,"githubStars":15027,"pricePerCall":0,"manifest":{"name":"skill-seekers","tools":[],"category":"MCP","entrypoint":{"args":["-m","skill_seekers.mcp.server_fastmcp"],"type":"mcp-stdio","command":"python"},"description":"","permissions":["shell","network"],"requiredEnv":[],"schemaVersion":1},"repoUrl":"https://github.com/yusufkaraaslan/Skill_Seekers","tags":["ai-tools","ast-parser","automation","claude-ai","claude-skills","code-analysis","conflict-detection","documentation","documentation-generator","github","github-scraper","mcp"],"stats":{"installVelocity7d":0,"retentionRate":0,"executions":0,"rating":null},"origin":"github","source":{"repo":"Skill_Seekers","audit":{"files":["pyproject.toml","requirements.txt","uv.lock"],"binaries":[],"findings":[{"kind":"dependency","rule":"DP-03","message":"`GitPython` is one or two edits away from the popular `ipython`.","surface":"pyproject.toml","evidence":"GitPython","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"anyio@4.11.0 has a known vulnerability: AnyIO process-pool workers can block indefinitely on undrained stderr.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-5p39-cfhj-2xmp · PyPI:anyio@4.11.0","severity":"medium"},{"kind":"dependency","rule":"DP-01","message":"anyio@4.11.0 has a known vulnerability: AnyIO: TLSStream IDNA 2003 host name encoding enables potential TLS certificate spoofing.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-82r6-8w77-94w6 · PyPI:anyio@4.11.0","severity":"critical"},{"kind":"dependency","rule":"DP-01","message":"click@8.3.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2132 · PyPI:click@8.3.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"idna@3.11 has a known vulnerability: Internationalized Domain Names in Applications (IDNA): Specially crafted inputs to idna.encode() can bypass CVE-2024-3651 fix.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-65pc-fj4g-8rjx · PyPI:idna@3.11","severity":"medium"},{"kind":"dependency","rule":"DP-01","message":"idna@3.11 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-215 · PyPI:idna@3.11","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pygments@2.19.2 has a known vulnerability: Pygments has Regular Expression Denial of Service (ReDoS) due to Inefficient Regex for GUID Matching.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-5239-wwwm-4pmq · PyPI:Pygments@2.19.2","severity":"low"},{"kind":"dependency","rule":"DP-01","message":"Pygments@2.19.2 has a known vulnerability: Pygments has Regular Expression Denial of Service (ReDoS) due to Inefficient Regex for GUID Matching.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2987 · PyPI:Pygments@2.19.2","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability: Pillow `BdfFontFile`: `Image.new()` called without `_decompression_bomb_check()` — bomb protection bypass via font loading.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-45hq-cxwh-f6vc · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability: Pillow: WindowsViewer.get_command() OS command injection via unescaped shell path.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-4x4j-2g7c-83w6 · PyPI:Pillow@11.0.0","severity":"medium"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability: Pillow: `FontFile.compile()`: `Image.new()` called without `_decompression_bomb_check()`.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-5x94-69rx-g8h2 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-62p4-gmf7-7g93 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-6r8x-57c9-28j4 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-8v84-f9pq-wr9x · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-9hw9-ch79-4vh6 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-cfh3-3jmp-rvhc · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-fj7v-r99m-22gq · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-jjj6-mw9f-p565 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-phj9-mv4w-65pm · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-pwv6-vv43-88gr · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-r73j-pqj5-w3x7 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-vjc4-5qp5-m44j · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-whj4-6x5x-4v2j · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-wjx4-4jcj-g98j · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-xj96-63gp-2gmr · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-165 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2249 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2250 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2252 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2253 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2254 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2255 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2256 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2257 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2874 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3451 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3453 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3454 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3493 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3494 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3495 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3496 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"pytest@8.4.2 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-6w46-j5rx-g56g · PyPI:pytest@8.4.2","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"pytest@8.4.2 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-1845 · PyPI:pytest@8.4.2","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"python-dotenv@1.1.1 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-mf9w-mj56-hr94 · PyPI:python-dotenv@1.1.1","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"python-dotenv@1.1.1 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2270 · PyPI:python-dotenv@1.1.1","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"requests@2.32.5 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-gc5v-m9x4-r6x2 · PyPI:requests@2.32.5","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"requests@2.32.5 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2275 · PyPI:requests@2.32.5","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"soupsieve@2.8 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-2wc2-fm75-p42x · PyPI:soupsieve@2.8","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"soupsieve@2.8 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-836r-79rf-4m37 · PyPI:soupsieve@2.8","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"soupsieve@2.8 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-gjv8-xp57-g29c · PyPI:soupsieve@2.8","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"soupsieve@2.8 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-j934-xhv5-fg8f · PyPI:soupsieve@2.8","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"soupsieve@2.8 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3071 · PyPI:soupsieve@2.8","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"soupsieve@2.8 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3072 · PyPI:soupsieve@2.8","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-2xpw-w6gg-jr37 · PyPI:urllib3@2.5.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-38jv-5279-wg99 · PyPI:urllib3@2.5.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-gm62-xv2j-4w53 · PyPI:urllib3@2.5.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-qccp-gfcp-xxvc · PyPI:urllib3@2.5.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-141 · PyPI:urllib3@2.5.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-1994 · PyPI:urllib3@2.5.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-1996 · PyPI:urllib3@2.5.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-1998 · PyPI:urllib3@2.5.0","severity":"high"}],"packages":63,"auditedAt":"2026-09-25T10:52:13.469Z","lockfiles":["uv.lock"]},"forks":1533,"owner":"yusufkaraaslan","stars":15027,"topics":["ai-tools","ast-parser","automation","claude-ai","claude-skills","code-analysis","conflict-detection","documentation","documentation-generator","github","github-scraper","mcp","mcp-server","multi-source","ocr","pdf","python","web-scraping"],"license":"MIT","fullName":"yusufkaraaslan/Skill_Seekers","homepage":"https://skillseekersweb.com/","language":"Python","pushedAt":"2026-09-20T16:14:08Z","avatarUrl":"https://avatars.githubusercontent.com/u/11597362?v=4","crawledAt":"2026-09-25T10:52:08.347Z","openIssues":47,"manifestFile":".mcp.json","manifestPath":".mcp.json","defaultBranch":"development"},"readme":"
\n \n
\n\n# Skill Seekers\n\nEnglish | [简体中文](README.zh-CN.md) | [日本語](README.ja.md) | [한국어](README.ko.md) | [Español](README.es.md) | [Français](README.fr.md) | [Deutsch](README.de.md) | [Português](README.pt-BR.md) | [Türkçe](README.tr.md) | [العربية](README.ar.md) | [हिन्दी](README.hi.md) | [Русский](README.ru.md)\n\n[](https://github.com/yusufkaraaslan/Skill_Seekers/releases)\n[](https://opensource.org/licenses/MIT)\n[](https://www.python.org/downloads/)\n[](https://modelcontextprotocol.io)\n[](tests/)\n[](https://pypi.org/project/skill-seekers/)\n[](https://pypi.org/project/skill-seekers/)\n[](https://skillseekersweb.com/)\n[](https://github.com/yusufkaraaslan/Skill_Seekers)\n[](https://pepy.tech/projects/skill-seekers)\n\n\n\n**🧠 The data layer for AI systems.** Skill Seekers turns documentation sites, GitHub repos, PDFs, videos, notebooks, wikis, and more — **18 source types** — into structured knowledge assets, ready to power AI Skills (Claude, Gemini, OpenAI), RAG pipelines (LangChain, LlamaIndex, Pinecone), and AI coding assistants (Cursor, Windsurf, Cline). Prepare once, export to **22 targets**.\n\n## 💛 Sponsors\n\n\n### Bronze Sponsors\n\n
\n Sponsor — Bronze\n
\n\n\n**[Become a sponsor](SPONSORSHIP.md)** · [GitHub Sponsors](https://github.com/sponsors/yusufkaraaslan)\n\n---\n\n## 🚀 Quick Start\n\n```bash\n# 1. Install\npip install skill-seekers\n\n# 2. Create a skill from any source\nskill-seekers create https://docs.djangoproject.com/\n\n# Optional: preview how a source will be detected without creating anything\nskill-seekers detect https://docs.djangoproject.com/ --json\n\n# 3. Package it for your AI platform\nskill-seekers package output/django --target claude\n```\n\nYou now have `output/django-claude.zip`, ready to use.\n\n```bash\n# Pick a different AI agent for enhancement (default: claude)\nskill-seekers create https://docs.djangoproject.com/ --agent kimi\nskill-seekers create https://docs.djangoproject.com/ --agent-cmd \"my-custom-agent run\"\n```\n\n### 🛰️ AI-driven project scan\n\nPoint `scan` at a project and an AI agent reads its manifests, README, Dockerfile/CI and sampled source imports — then emits one config per detected framework, plus a `-codebase.json` for your own code:\n\n```bash\nskill-seekers scan ./my-react-app --out ./configs/scanned/\n# → react.json, vite.json, tailwind.json, jest.json, my-react-app-codebase.json\n\nskill-seekers create ./configs/scanned/react.json\n```\n\nIf a detection has no existing preset, the AI generates a fresh config; on exit you can optionally publish it back to the [community registry](https://github.com/yusufkaraaslan","createdAt":"2026-09-25T10:52:13.482Z","updatedAt":"2026-09-25T10:52:13.482Z"},{"id":"cmuguczll00obqu067q4c4nfv","slug":"yusufkaraaslan-skill-seekers-skill-seekers-2","name":"skill-builder","description":"Automatically detect source types and build AI skills using Skill Seekers. Use when the user wants to create skills from documentation, repos, PDFs, videos, or other knowledge sources.","authorId":"gh:yusufkaraaslan","authorName":"yusufkaraaslan","version":"0.1.0","category":"Prompt","securityLevel":"Sandbox","downloadsCount":0,"githubStars":15027,"pricePerCall":0,"manifest":{"name":"skill-builder","tools":[],"category":"Prompt","entrypoint":{"type":"prompt"},"description":"Automatically detect source types and build AI skills using Skill Seekers. Use when the user wants to create skills from documentation, repos, PDFs, videos, or other knowledge sources.","permissions":[],"systemPrompt":"# Skill Builder\n\nYou have access to the Skill Seekers MCP server which provides 40 tools for converting knowledge sources into AI-ready skills.\n\n## When to Use This Skill\n\nUse this skill when the user:\n- Wants to create an AI skill from a documentation site, GitHub repo, PDF, video, or other source\n- Needs to convert documentation into a format suitable for LLM consumption\n- Wants to update or sync existing skills with their source documentation\n- Needs to export skills to vector databases (Weaviate, Chroma, FAISS, Qdrant)\n- Asks about scraping, converting, or packaging documentation for AI\n\n## Source Type Detection\n\nAutomatically detect the source type from user input:\n\n| Input Pattern | Source Type | Tool to Use |\n|---------------|-------------|-------------|\n| `https://...` (not GitHub/YouTube) | Documentation | `scrape_docs` |\n| `owner/repo` or `github.com/...` | GitHub | `scrape_github` |\n| `*.pdf` | PDF | `scrape_pdf` |\n| YouTube/Vimeo URL or video file | Video | `scrape_video` |\n| Local directory path | Codebase | `scrape_codebase` |\n| `*.ipynb`, `*.html`, `*.yaml` (OpenAPI), `*.adoc`, `*.pptx`, `*.rss`, `*.1`-`.8` | Various | `scrape_generic` |\n| JSON config file | Unified | Use config with `scrape_docs` |\n\n## Recommended Workflow\n\n1. **Detect source type** from the user's input\n2. **Generate or fetch config** using `generate_config` or `fetch_config` if needed\n3. **Estimate scope** with `estimate_pages` for documentation sites\n4. **Scrape the source** using the appropriate scraping tool\n5. **Enhance** with `enhance_skill` if the user wants AI-powered improvements\n6. **Package** with `package_skill` for the target platform\n7. **Export to vector DB** if requested using `export_to_*` tools\n\n## Available MCP Tools\n\n### Config Management\n- `generate_config` — Generate a scraping config from a URL\n- `list_configs` — List available preset configs\n- `validate_config` — Validate a config file\n\n### Scraping (use based on source type)\n- `scrape_docs` — Documentation sites\n- `scrape_github` — GitHub repositories\n- `scrape_pdf` — PDF files\n- `scrape_video` — Video transcripts\n- `scrape_codebase` — Local code analysis\n- `scrape_generic` — Jupyter, HTML, OpenAPI, AsciiDoc, PPTX, RSS, manpage, Confluence, Notion, chat\n\n### Post-processing\n- `enhance_skill` — AI-powered skill enhancement\n- `package_skill` — Package for target platform\n- `upload_skill` — Upload to platform API\n- `install_skill` — End-to-end install workflow\n\n### Advanced\n- `detect_patterns` — Design pattern detection in code\n- `extract_test_examples` — Extract usage examples from tests\n- `build_how_to_guides` — Generate how-to guides from tests\n- `split_config` — Split large configs into focused skills\n- `export_to_weaviate`, `export_to_chroma`, `export_to_faiss`, `export_to_qdrant` — Vector DB export","schemaVersion":1},"repoUrl":"https://github.com/yusufkaraaslan/Skill_Seekers/tree/development/skills/skill-seekers","tags":["ai-tools","ast-parser","automation","claude-ai","claude-skills","code-analysis","conflict-detection","documentation","documentation-generator","github","github-scraper","mcp"],"stats":{"installVelocity7d":0,"retentionRate":0,"executions":0,"rating":null},"origin":"github","source":{"repo":"Skill_Seekers","audit":{"files":["pyproject.toml","requirements.txt","uv.lock"],"binaries":[],"findings":[{"kind":"dependency","rule":"DP-03","message":"`GitPython` is one or two edits away from the popular `ipython`.","surface":"pyproject.toml","evidence":"GitPython","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"anyio@4.11.0 has a known vulnerability: AnyIO process-pool workers can block indefinitely on undrained stderr.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-5p39-cfhj-2xmp · PyPI:anyio@4.11.0","severity":"medium"},{"kind":"dependency","rule":"DP-01","message":"anyio@4.11.0 has a known vulnerability: AnyIO: TLSStream IDNA 2003 host name encoding enables potential TLS certificate spoofing.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-82r6-8w77-94w6 · PyPI:anyio@4.11.0","severity":"critical"},{"kind":"dependency","rule":"DP-01","message":"click@8.3.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2132 · PyPI:click@8.3.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"idna@3.11 has a known vulnerability: Internationalized Domain Names in Applications (IDNA): Specially crafted inputs to idna.encode() can bypass CVE-2024-3651 fix.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-65pc-fj4g-8rjx · PyPI:idna@3.11","severity":"medium"},{"kind":"dependency","rule":"DP-01","message":"idna@3.11 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-215 · PyPI:idna@3.11","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pygments@2.19.2 has a known vulnerability: Pygments has Regular Expression Denial of Service (ReDoS) due to Inefficient Regex for GUID Matching.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-5239-wwwm-4pmq · PyPI:Pygments@2.19.2","severity":"low"},{"kind":"dependency","rule":"DP-01","message":"Pygments@2.19.2 has a known vulnerability: Pygments has Regular Expression Denial of Service (ReDoS) due to Inefficient Regex for GUID Matching.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2987 · PyPI:Pygments@2.19.2","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability: Pillow `BdfFontFile`: `Image.new()` called without `_decompression_bomb_check()` — bomb protection bypass via font loading.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-45hq-cxwh-f6vc · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability: Pillow: WindowsViewer.get_command() OS command injection via unescaped shell path.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-4x4j-2g7c-83w6 · PyPI:Pillow@11.0.0","severity":"medium"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability: Pillow: `FontFile.compile()`: `Image.new()` called without `_decompression_bomb_check()`.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-5x94-69rx-g8h2 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-62p4-gmf7-7g93 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-6r8x-57c9-28j4 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-8v84-f9pq-wr9x · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-9hw9-ch79-4vh6 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-cfh3-3jmp-rvhc · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-fj7v-r99m-22gq · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-jjj6-mw9f-p565 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-phj9-mv4w-65pm · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-pwv6-vv43-88gr · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-r73j-pqj5-w3x7 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-vjc4-5qp5-m44j · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-whj4-6x5x-4v2j · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-wjx4-4jcj-g98j · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-xj96-63gp-2gmr · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-165 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2249 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2250 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2252 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2253 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2254 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2255 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2256 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2257 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2874 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3451 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3453 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3454 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3493 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3494 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3495 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3496 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"pytest@8.4.2 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-6w46-j5rx-g56g · PyPI:pytest@8.4.2","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"pytest@8.4.2 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-1845 · PyPI:pytest@8.4.2","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"python-dotenv@1.1.1 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-mf9w-mj56-hr94 · PyPI:python-dotenv@1.1.1","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"python-dotenv@1.1.1 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2270 · PyPI:python-dotenv@1.1.1","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"requests@2.32.5 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-gc5v-m9x4-r6x2 · PyPI:requests@2.32.5","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"requests@2.32.5 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2275 · PyPI:requests@2.32.5","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"soupsieve@2.8 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-2wc2-fm75-p42x · PyPI:soupsieve@2.8","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"soupsieve@2.8 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-836r-79rf-4m37 · PyPI:soupsieve@2.8","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"soupsieve@2.8 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-gjv8-xp57-g29c · PyPI:soupsieve@2.8","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"soupsieve@2.8 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-j934-xhv5-fg8f · PyPI:soupsieve@2.8","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"soupsieve@2.8 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3071 · PyPI:soupsieve@2.8","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"soupsieve@2.8 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3072 · PyPI:soupsieve@2.8","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-2xpw-w6gg-jr37 · PyPI:urllib3@2.5.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-38jv-5279-wg99 · PyPI:urllib3@2.5.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-gm62-xv2j-4w53 · PyPI:urllib3@2.5.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-qccp-gfcp-xxvc · PyPI:urllib3@2.5.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-141 · PyPI:urllib3@2.5.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-1994 · PyPI:urllib3@2.5.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-1996 · PyPI:urllib3@2.5.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-1998 · PyPI:urllib3@2.5.0","severity":"high"}],"packages":63,"auditedAt":"2026-09-25T10:52:13.469Z","lockfiles":["uv.lock"]},"forks":1533,"owner":"yusufkaraaslan","stars":15027,"topics":["ai-tools","ast-parser","automation","claude-ai","claude-skills","code-analysis","conflict-detection","documentation","documentation-generator","github","github-scraper","mcp","mcp-server","multi-source","ocr","pdf","python","web-scraping"],"license":"MIT","fullName":"yusufkaraaslan/Skill_Seekers","homepage":"https://skillseekersweb.com/","language":"Python","pushedAt":"2026-09-20T16:14:08Z","avatarUrl":"https://avatars.githubusercontent.com/u/11597362?v=4","crawledAt":"2026-09-25T10:52:08.347Z","openIssues":47,"manifestFile":"SKILL.md","manifestPath":"skills/skill-seekers/SKILL.md","defaultBranch":"development"},"readme":"# Skill Builder\n\nYou have access to the Skill Seekers MCP server which provides 40 tools for converting knowledge sources into AI-ready skills.\n\n## When to Use This Skill\n\nUse this skill when the user:\n- Wants to create an AI skill from a documentation site, GitHub repo, PDF, video, or other source\n- Needs to convert documentation into a format suitable for LLM consumption\n- Wants to update or sync existing skills with their source documentation\n- Needs to export skills to vector databases (Weaviate, Chroma, FAISS, Qdrant)\n- Asks about scraping, converting, or packaging documentation for AI\n\n## Source Type Detection\n\nAutomatically detect the source type from user input:\n\n| Input Pattern | Source Type | Tool to Use |\n|---------------|-------------|-------------|\n| `https://...` (not GitHub/YouTube) | Documentation | `scrape_docs` |\n| `owner/repo` or `github.com/...` | GitHub | `scrape_github` |\n| `*.pdf` | PDF | `scrape_pdf` |\n| YouTube/Vimeo URL or video file | Video | `scrape_video` |\n| Local directory path | Codebase | `scrape_codebase` |\n| `*.ipynb`, `*.html`, `*.yaml` (OpenAPI), `*.adoc`, `*.pptx`, `*.rss`, `*.1`-`.8` | Various | `scrape_generic` |\n| JSON config file | Unified | Use config with `scrape_docs` |\n\n## Recommended Workflow\n\n1. **Detect source type** from the user's input\n2. **Generate or fetch config** using `generate_config` or `fetch_config` if needed\n3. **Estimate scope** with `estimate_pages` for documentation sites\n4. **Scrape the source** using the appropriate scraping tool\n5. **Enhance** with `enhance_skill` if the user wants AI-powered improvements\n6. **Package** with `package_skill` for the target platform\n7. **Export to vector DB** if requested using `export_to_*` tools\n\n## Available MCP Tools\n\n### Config Management\n- `generate_config` — Generate a scraping config from a URL\n- `list_configs` — List available preset configs\n- `validate_config` — Validate a config file\n\n### Scraping (use based on source type)\n- `scrape_docs` — Documentation sites\n- `scrape_github` — GitHub repositories\n- `scrape_pdf` — PDF files\n- `scrape_video` — Video transcripts\n- `scrape_codebase` — Local code analysis\n- `scrape_generic` — Jupyter, HTML, OpenAPI, AsciiDoc, PPTX, RSS, manpage, Confluence, Notion, chat\n\n### Post-processing\n- `enhance_skill` — AI-powered skill enhancement\n- `package_skill` — Package for target platform\n- `upload_skill` — Upload to platform API\n- `install_skill` — End-to-end install workflow\n\n### Advanced\n- `detect_patterns` — Design pattern detection in code\n- `extract_test_examples` — Extract usage examples from tests\n- `build_how_to_guides` — Generate how-to guides from tests\n- `split_config` — Split large configs into focused skills\n- `export_to_weaviate`, `export_to_chroma`, `export_to_faiss`, `export_to_qdrant` — Vector DB export","createdAt":"2026-09-25T10:52:13.497Z","updatedAt":"2026-09-25T10:52:13.497Z"},{"id":"cmuguczlz00oequ06v09cwt8l","slug":"yusufkaraaslan-skill-seekers-skill-builder","name":"skill-builder","description":"Automatically detect source types and build AI skills using Skill Seekers. Use when the user wants to create skills from documentation, repos, PDFs, videos, or other knowledge sources.","authorId":"gh:yusufkaraaslan","authorName":"yusufkaraaslan","version":"0.1.0","category":"Prompt","securityLevel":"Sandbox","downloadsCount":0,"githubStars":15027,"pricePerCall":0,"manifest":{"name":"skill-builder","tools":[],"category":"Prompt","entrypoint":{"type":"prompt"},"description":"Automatically detect source types and build AI skills using Skill Seekers. Use when the user wants to create skills from documentation, repos, PDFs, videos, or other knowledge sources.","permissions":[],"systemPrompt":"# Skill Builder\n\nThis skill uses the Skill Seekers MCP server, which provides 40 tools for converting knowledge sources into AI-ready skills. If the MCP tools are not available, use the CLI fallback at the bottom of this file instead — do not stop.\n\n## Prerequisites\n\nThe MCP tools below only work when the Skill Seekers MCP server is connected:\n\n1. Install the package: `pip install \"skill-seekers[mcp]\"`\n2. Connect the server:\n - Installed as the Skill Seekers plugin? Nothing to do — the plugin's bundled `.mcp.json` starts the server automatically (it still needs step 1).\n - Installed standalone (e.g. copied into `~/.claude/skills/`)? Register the server once: `claude mcp add skill-seekers -- python -m skill_seekers.mcp.server_fastmcp`\n\nIf tools like `scrape_docs` or `package_skill` are not in your tool list, the server is not connected. Tell the user about the two steps above, and use the CLI fallback in the meantime.\n\n## When to Use This Skill\n\nUse this skill when the user:\n- Wants to create an AI skill from a documentation site, GitHub repo, PDF, video, or other source\n- Needs to convert documentation into a format suitable for LLM consumption\n- Wants to update or sync existing skills with their source documentation\n- Needs to export skills to vector databases (Weaviate, Chroma, FAISS, Qdrant)\n- Asks about scraping, converting, or packaging documentation for AI\n\n## Source Type Detection\n\nAutomatically detect the source type from user input:\n\n| Input Pattern | Source Type | Tool to Use |\n|---------------|-------------|-------------|\n| `https://...` (not GitHub/YouTube) | Documentation | `scrape_docs` |\n| `owner/repo` or `github.com/...` | GitHub | `scrape_github` |\n| `*.pdf` | PDF | `scrape_pdf` |\n| YouTube/Vimeo URL or video file | Video | `scrape_video` |\n| Local directory path | Codebase | `scrape_codebase` |\n| `*.ipynb`, `*.html`, `*.yaml` (OpenAPI), `*.adoc`, `*.pptx`, `*.rss`, `*.1`-`.8` | Various | `scrape_generic` |\n| JSON config file | Unified | Use config with `scrape_docs` |\n\n## Recommended Workflow\n\n1. **Detect source type** from the user's input\n2. **Generate or fetch config** using `generate_config` or `fetch_config` if needed\n3. **Estimate scope** with `estimate_pages` for documentation sites\n4. **Scrape the source** using the appropriate scraping tool\n5. **Enhance** with `enhance_skill` if the user wants AI-powered improvements\n6. **Package** with `package_skill` for the target platform\n7. **Export to vector DB** if requested using `export_to_*` tools\n\n## Available MCP Tools\n\n### Config Management\n- `generate_config` — Generate a scraping config from a URL\n- `list_configs` — List available preset configs\n- `validate_config` — Validate a config file\n\n### Scraping (use based on source type)\n- `scrape_docs` — Documentation sites\n- `scrape_github` — GitHub repositories\n- `scrape_pdf` — PDF files\n- `scrape_video` — Video transcripts\n- `scrape_codebase` — Local code analysis\n- `scrape_generic` — Jupyter, HTML, OpenAPI, AsciiDoc, PPTX, RSS, manpage, Confluence, Notion, chat\n\n### Post-processing\n- `enhance_skill` — AI-powered skill enhancement\n- `package_skill` — Package for target platform\n- `upload_skill` — Upload to platform API\n- `install_skill` — End-to-end install workflow\n\n### Advanced\n- `detect_patterns` — Design pattern detection in code\n- `extract_test_examples` — Extract usage examples from tests\n- `build_how_to_guides` — Generate how-to guides from tests\n- `split_config` — Split large configs into focused skills\n- `export_to_weaviate`, `export_to_chroma`, `export_to_faiss`, `export_to_qdrant` — Vector DB export\n\n## CLI Fallback (MCP server not connected)\n\nThe same pipeline is available from the command line (requires `pip install skill-seekers`). Run it with the Bash tool:\n\n```bash\nskill-seekers create # auto-detects: URL, owner/repo, ./path, file.pdf, video URL, ...\nskill-seekers package --target claude # or gemini/openai/langchain/chroma/...\n```\n\n`create` covers detection, scraping, and building in one step; add `--enhance-level 0` to skip AI enhancement. After it finishes, read the generated `SKILL.md` and summarize what was created.","schemaVersion":1},"repoUrl":"https://github.com/yusufkaraaslan/Skill_Seekers/tree/development/distribution/claude-plugin/skills/skill-builder","tags":["ai-tools","ast-parser","automation","claude-ai","claude-skills","code-analysis","conflict-detection","documentation","documentation-generator","github","github-scraper","mcp"],"stats":{"installVelocity7d":0,"retentionRate":0,"executions":0,"rating":null},"origin":"github","source":{"repo":"Skill_Seekers","audit":{"files":["pyproject.toml","requirements.txt","uv.lock"],"binaries":[],"findings":[{"kind":"dependency","rule":"DP-03","message":"`GitPython` is one or two edits away from the popular `ipython`.","surface":"pyproject.toml","evidence":"GitPython","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"anyio@4.11.0 has a known vulnerability: AnyIO process-pool workers can block indefinitely on undrained stderr.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-5p39-cfhj-2xmp · PyPI:anyio@4.11.0","severity":"medium"},{"kind":"dependency","rule":"DP-01","message":"anyio@4.11.0 has a known vulnerability: AnyIO: TLSStream IDNA 2003 host name encoding enables potential TLS certificate spoofing.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-82r6-8w77-94w6 · PyPI:anyio@4.11.0","severity":"critical"},{"kind":"dependency","rule":"DP-01","message":"click@8.3.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2132 · PyPI:click@8.3.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"idna@3.11 has a known vulnerability: Internationalized Domain Names in Applications (IDNA): Specially crafted inputs to idna.encode() can bypass CVE-2024-3651 fix.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-65pc-fj4g-8rjx · PyPI:idna@3.11","severity":"medium"},{"kind":"dependency","rule":"DP-01","message":"idna@3.11 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-215 · PyPI:idna@3.11","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pygments@2.19.2 has a known vulnerability: Pygments has Regular Expression Denial of Service (ReDoS) due to Inefficient Regex for GUID Matching.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-5239-wwwm-4pmq · PyPI:Pygments@2.19.2","severity":"low"},{"kind":"dependency","rule":"DP-01","message":"Pygments@2.19.2 has a known vulnerability: Pygments has Regular Expression Denial of Service (ReDoS) due to Inefficient Regex for GUID Matching.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2987 · PyPI:Pygments@2.19.2","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability: Pillow `BdfFontFile`: `Image.new()` called without `_decompression_bomb_check()` — bomb protection bypass via font loading.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-45hq-cxwh-f6vc · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability: Pillow: WindowsViewer.get_command() OS command injection via unescaped shell path.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-4x4j-2g7c-83w6 · PyPI:Pillow@11.0.0","severity":"medium"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability: Pillow: `FontFile.compile()`: `Image.new()` called without `_decompression_bomb_check()`.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-5x94-69rx-g8h2 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-62p4-gmf7-7g93 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-6r8x-57c9-28j4 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-8v84-f9pq-wr9x · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-9hw9-ch79-4vh6 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-cfh3-3jmp-rvhc · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-fj7v-r99m-22gq · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-jjj6-mw9f-p565 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-phj9-mv4w-65pm · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-pwv6-vv43-88gr · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-r73j-pqj5-w3x7 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-vjc4-5qp5-m44j · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-whj4-6x5x-4v2j · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-wjx4-4jcj-g98j · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-xj96-63gp-2gmr · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-165 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2249 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2250 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2252 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2253 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2254 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2255 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2256 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2257 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2874 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3451 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3453 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3454 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3493 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3494 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3495 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"Pillow@11.0.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3496 · PyPI:Pillow@11.0.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"pytest@8.4.2 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-6w46-j5rx-g56g · PyPI:pytest@8.4.2","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"pytest@8.4.2 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-1845 · PyPI:pytest@8.4.2","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"python-dotenv@1.1.1 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-mf9w-mj56-hr94 · PyPI:python-dotenv@1.1.1","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"python-dotenv@1.1.1 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2270 · PyPI:python-dotenv@1.1.1","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"requests@2.32.5 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-gc5v-m9x4-r6x2 · PyPI:requests@2.32.5","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"requests@2.32.5 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-2275 · PyPI:requests@2.32.5","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"soupsieve@2.8 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-2wc2-fm75-p42x · PyPI:soupsieve@2.8","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"soupsieve@2.8 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-836r-79rf-4m37 · PyPI:soupsieve@2.8","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"soupsieve@2.8 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-gjv8-xp57-g29c · PyPI:soupsieve@2.8","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"soupsieve@2.8 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-j934-xhv5-fg8f · PyPI:soupsieve@2.8","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"soupsieve@2.8 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3071 · PyPI:soupsieve@2.8","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"soupsieve@2.8 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-3072 · PyPI:soupsieve@2.8","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-2xpw-w6gg-jr37 · PyPI:urllib3@2.5.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-38jv-5279-wg99 · PyPI:urllib3@2.5.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-gm62-xv2j-4w53 · PyPI:urllib3@2.5.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"GHSA-qccp-gfcp-xxvc · PyPI:urllib3@2.5.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-141 · PyPI:urllib3@2.5.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-1994 · PyPI:urllib3@2.5.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-1996 · PyPI:urllib3@2.5.0","severity":"high"},{"kind":"dependency","rule":"DP-01","message":"urllib3@2.5.0 has a known vulnerability.","surface":"pyproject.toml, requirements.txt, uv.lock","evidence":"PYSEC-2026-1998 · PyPI:urllib3@2.5.0","severity":"high"}],"packages":63,"auditedAt":"2026-09-25T10:52:13.469Z","lockfiles":["uv.lock"]},"forks":1533,"owner":"yusufkaraaslan","stars":15027,"topics":["ai-tools","ast-parser","automation","claude-ai","claude-skills","code-analysis","conflict-detection","documentation","documentation-generator","github","github-scraper","mcp","mcp-server","multi-source","ocr","pdf","python","web-scraping"],"license":"MIT","fullName":"yusufkaraaslan/Skill_Seekers","homepage":"https://skillseekersweb.com/","language":"Python","pushedAt":"2026-09-20T16:14:08Z","avatarUrl":"https://avatars.githubusercontent.com/u/11597362?v=4","crawledAt":"2026-09-25T10:52:08.347Z","openIssues":47,"manifestFile":"SKILL.md","manifestPath":"distribution/claude-plugin/skills/skill-builder/SKILL.md","defaultBranch":"development"},"readme":"# Skill Builder\n\nThis skill uses the Skill Seekers MCP server, which provides 40 tools for converting knowledge sources into AI-ready skills. If the MCP tools are not available, use the CLI fallback at the bottom of this file instead — do not stop.\n\n## Prerequisites\n\nThe MCP tools below only work when the Skill Seekers MCP server is connected:\n\n1. Install the package: `pip install \"skill-seekers[mcp]\"`\n2. Connect the server:\n - Installed as the Skill Seekers plugin? Nothing to do — the plugin's bundled `.mcp.json` starts the server automatically (it still needs step 1).\n - Installed standalone (e.g. copied into `~/.claude/skills/`)? Register the server once: `claude mcp add skill-seekers -- python -m skill_seekers.mcp.server_fastmcp`\n\nIf tools like `scrape_docs` or `package_skill` are not in your tool list, the server is not connected. Tell the user about the two steps above, and use the CLI fallback in the meantime.\n\n## When to Use This Skill\n\nUse this skill when the user:\n- Wants to create an AI skill from a documentation site, GitHub repo, PDF, video, or other source\n- Needs to convert documentation into a format suitable for LLM consumption\n- Wants to update or sync existing skills with their source documentation\n- Needs to export skills to vector databases (Weaviate, Chroma, FAISS, Qdrant)\n- Asks about scraping, converting, or packaging documentation for AI\n\n## Source Type Detection\n\nAutomatically detect the source type from user input:\n\n| Input Pattern | Source Type | Tool to Use |\n|---------------|-------------|-------------|\n| `https://...` (not GitHub/YouTube) | Documentation | `scrape_docs` |\n| `owner/repo` or `github.com/...` | GitHub | `scrape_github` |\n| `*.pdf` | PDF | `scrape_pdf` |\n| YouTube/Vimeo URL or video file | Video | `scrape_video` |\n| Local directory path | Codebase | `scrape_codebase` |\n| `*.ipynb`, `*.html`, `*.yaml` (OpenAPI), `*.adoc`, `*.pptx`, `*.rss`, `*.1`-`.8` | Various | `scrape_generic` |\n| JSON config file | Unified | Use config with `scrape_docs` |\n\n## Recommended Workflow\n\n1. **Detect source type** from the user's input\n2. **Generate or fetch config** using `generate_config` or `fetch_config` if needed\n3. **Estimate scope** with `estimate_pages` for documentation sites\n4. **Scrape the source** using the appropriate scraping tool\n5. **Enhance** with `enhance_skill` if the user wants AI-powered improvements\n6. **Package** with `package_skill` for the target platform\n7. **Export to vector DB** if requested using `export_to_*` tools\n\n## Available MCP Tools\n\n### Config Management\n- `generate_config` — Generate a scraping config from a URL\n- `list_configs` — List available preset configs\n- `validate_config` — Validate a config file\n\n### Scraping (use based on source type)\n- `scrape_docs` — Documentation sites\n- `scrape_github` — GitHub repositories\n- `scrape_pdf` — PDF files\n- `scrape_video` — Video transcripts\n- `scrape_codebase` — Local code analysis\n- `scrape_generic` — Jupyter, HTML, OpenAPI, AsciiDoc, PPTX, RSS, manpage, Confluence, Notion, chat\n\n### Post-processing\n- `enhance_skill` — AI-powered skill enhancement\n- `package_skill` — Package for target platform\n- `upload_skill` — Upload to platform API\n- `install_skill` — End-to-end install workflow\n\n### Advanced\n- `detect_patterns` — Design pattern detection in code\n- `extract_test_examples` — Extract usage examples from tests\n- `build_how_to_guides` — Generate how-to guides from tests\n- `split_config` — Split large configs into focused skills\n- `export_to_weaviate`, `export_to_chroma`, `export_to_faiss`, `export_to_qdrant` — Vector DB export\n\n## CLI Fallback (MCP server not connected)\n\nThe same pipeline is available from the command line (requires `pip install skill-seekers`). Run it with the Bash tool:\n\n```bash\nskill-seekers create # auto-detects: URL, owner/repo, ./path, file.pdf, video URL, ...\nskill-seekers package --target claude # or gemini/openai/langchain/chroma/...\n```\n\n`create` cov","createdAt":"2026-09-25T10:52:13.511Z","updatedAt":"2026-09-25T10:52:13.511Z"},{"id":"cmugwiav901ttqu066j1nrurv","slug":"timescale-pg-aiguide-design-postgis-tables","name":"design-postgis-tables","description":"Comprehensive PostGIS spatial table design reference covering geometry types, coordinate systems, spatial indexing, and performance patterns for location-based applications","authorId":"gh:timescale","authorName":"timescale","version":"0.1.0","category":"Prompt","securityLevel":"Community","downloadsCount":0,"githubStars":1847,"pricePerCall":0,"manifest":{"name":"design-postgis-tables","tools":[],"category":"Prompt","entrypoint":{"type":"prompt"},"description":"Comprehensive PostGIS spatial table design reference covering geometry types, coordinate systems, spatial indexing, and performance patterns for location-based applications","permissions":[],"systemPrompt":"# PostGIS Spatial Table Design\n\n## Before You Start (5 Questions)\n\n1. What is the geographic scope (single city/region vs global)?\n2. What are your primary query patterns (within-radius, bbox, intersects, nearest-neighbor)?\n3. What units do you need for distance/area (meters vs CRS units), and how accurate must they be?\n4. What is the expected scale (rows, write rate), and is the data mostly append-only?\n5. Do you need 3D (Z) or measures (M), or is 2D enough?\n\n**SQL injection note:** When turning these patterns into application code, use parameterized queries for user-provided values (WKT/WKB, coordinates, IDs, radii). Avoid string-concatenating untrusted input into SQL; for dynamic identifiers, use safe identifier quoting/whitelisting.\n\n## Core Rules\n\n- **Always use PostGIS geometry/geography types** instead of PostgreSQL's built-in geometric types (`POINT`, `LINE`, `POLYGON`, `CIRCLE`). PostGIS types provide true spatial capabilities.\n- **Choose between GEOMETRY and GEOGRAPHY** based on your use case: GEOMETRY for projected/local data with Cartesian math; GEOGRAPHY for global data requiring accurate spherical calculations.\n- **Always specify SRID** (Spatial Reference Identifier) when creating geometry columns. Use `4326` (WGS84) for GPS/global data, appropriate local projections for regional data.\n- **Create spatial indexes** on all geometry/geography columns using GiST (default). Consider BRIN only for very large **GEOMETRY** tables where rows are naturally ordered on disk and you can tolerate coarser filtering.\n- **Use constraint-based type enforcement** with `GEOMETRY(type, SRID)` syntax to ensure data integrity.\n\n## Geometry vs Geography\n\n### When to Use GEOMETRY\n\n- **Local/regional data** within a single coordinate system\n- **Projected coordinates** (meters, feet) for accurate area/distance calculations\n- **Complex spatial operations** (buffering, unions, intersections)\n- **Performance-critical queries** (Cartesian math is faster)\n- **Data already in a projected CRS** (UTM, State Plane, etc.)\n\n```sql\n-- Regional data with projected coordinates (UTM Zone 10N for California)\nCREATE TABLE local_parcels (\n id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n parcel_number TEXT NOT NULL,\n boundary GEOMETRY(POLYGON, 26910), -- UTM Zone 10N (meters)\n area_sqm DOUBLE PRECISION GENERATED ALWAYS AS (ST_Area(boundary)) STORED\n);\n```\n\n### When to Use GEOGRAPHY\n\n- **Global data** spanning multiple continents/hemispheres\n- **GPS coordinates** (latitude/longitude in decimal degrees)\n- **Accurate distance calculations** on Earth's surface (great circle)\n- **Simple spatial operations** (distance, containment)\n- **Data from GPS devices, geocoding services, or web maps**\n\n```sql\n-- Global data with geodetic calculations\nCREATE TABLE global_offices (\n id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n name TEXT NOT NULL,\n city TEXT NOT NULL,\n location GEOGRAPHY(POINT, 4326) -- WGS84 (lat/lon)\n);\n\n-- Distance in meters (accurate spherical calculation)\nSELECT\n a.name AS office_a,\n b.name AS office_b,\n ST_Distance(a.location, b.location) / 1000 AS distance_km\nFROM global_offices a\nCROSS JOIN global_offices b\nWHERE a.id < b.id;\n```\n\n### Comparison Table\n\n| Aspect | GEOMETRY | GEOGRAPHY |\n| ----------------- | ------------------------------------- | ------------------------- |\n| Coordinate system | Any SRID (projected or geodetic) | WGS84 (SRID 4326) only |\n| Distance units | CRS units (degrees, meters, feet) | Meters (always) |\n| Distance accuracy | Depends on projection | True spheroidal distance |\n| Area accuracy | Accurate in projected CRS | Accurate on sphere |\n| Function support | Full (300+ functions) | Limited (~40 functions) |\n| Performance | Faster (Cartesian math) | Slower (spherical math) |\n| Index type | GiST, BRIN, SP-GiST | GiST only |\n| Best for | Regional/local data, complex analysis | Global data, GPS tracking |\n\n## Geometry Types\n\n### Point Types\n\n```sql\n-- Single location (stores, sensors, events)\nlocation GEOMETRY(POINT, 4326)\n\n-- Multiple discrete locations (multi-branch business)\nlocations GEOMETRY(MULTIPOINT, 4326)\n\n-- 3D point with elevation\nlocation_3d GEOMETRY(POINTZ, 4326)\n\n-- Point with measure value (linear referencing)\nlocation_m GEOMETRY(POINTM, 4326)\n```\n\n**Use POINT for:** Store locations, sensor positions, event coordinates, addresses, POIs\n**Use MULTIPOINT for:** Multiple related locations stored as single feature\n\n### Line Types\n\n```sql\n-- Single path (road segment, river, route)\npath GEOMETRY(LINESTRING, 4326)\n\n-- Multiple paths (road network, transit lines)\nnetwork GEOMETRY(MULTILINESTRING, 4326)\n\n-- 3D line with elevation profile\ntrail_3d GEOMETRY(LINESTRINGZ, 4326)\n```\n\n**Use LINESTRING for:** Roads, rivers, pipelines, GPS tracks, routes\n**Use MULTILINESTRING for:** Disconnected road segments, river systems\n\n### Polygon Types\n\n```sql\n-- Single area (parcel, building footprint, zone)\nboundary GEOMETRY(POLYGON, 4326)\n\n-- Multiple areas (archipelago, fragmented habitat)\nterritories GEOMETRY(MULTIPOLYGON, 4326)\n\n-- 3D polygon (building with height)\nfootprint_3d GEOMETRY(POLYGONZ, 4326)\n```\n\n**Use POLYGON for:** Property boundaries, administrative areas, service zones\n**Use MULTIPOLYGON for:** Countries with islands, fragmented regions\n\n### Generic Types\n\n```sql\n-- Any geometry type (flexible schema)\ngeom GEOMETRY(GEOMETRY, 4326)\n\n-- Collection of mixed types\nfeatures GEOMETRY(GEOMETRYCOLLECTION, 4326)\n```\n\n**Use GEOMETRY for:** Flexible schemas accepting multiple types\n**Avoid GEOMETRYCOLLECTION:** Prefer homogeneous types for better indexing\n\n## Coordinate Systems (SRID)\n\n### Common SRIDs\n\n| SRID | Name | Use Case | Units |\n| ----------- | ----------------- | ---------------------------- | ------- |\n| 4326 | WGS84 | GPS, global data, web maps | Degrees |\n| 3857 | Web Mercator | Web map tiles (display only) | Meters |\n| 26910-26919 | UTM Zones (US) | Regional analysis | Meters |\n| 32601-32660 | UTM Zones (North) | Regional analysis | Meters |\n| 32701-32760 | UTM Zones (South) | Regional analysis | Meters |\n\n### SRID Best Practices\n\n- **Store in WGS84 (4326)** for interoperability and GPS data\n- **Transform to projected CRS** for accurate measurements\n- **Never mix SRIDs** in spatial operations without explicit transformation\n- **Use appropriate local CRS** for area/distance calculations requiring high precision\n\n```sql\n-- Store in WGS84, calculate in UTM\nCREATE TABLE survey_points (\n id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n location GEOMETRY(POINT, 4326), -- Storage: WGS84\n CONSTRAINT valid_location CHECK (ST_IsValid(location))\n);\n\n-- Calculate distance in meters using UTM projection\nSELECT\n a.id AS point_a,\n b.id AS point_b,\n ST_Distance(\n ST_Transform(a.location, 26910), -- Transform to UTM\n ST_Transform(b.location, 26910)\n ) AS distance_meters\nFROM survey_points a\nCROSS JOIN survey_points b\nWHERE a.id < b.id;\n```\n\n## Spatial Indexing\n\n### GiST Index (Default)\n\nMost versatile spatial index. Use for all geometry/geography columns.\n\n```sql\n-- Geometry (most common)\nCREATE INDEX idx_your_table_geom_gist ON your_table_name USING GIST (geom);\n\n-- Geography (GiST is the supported option)\nCREATE INDEX idx_your_table_geog_gist ON your_table_name USING GIST (geog);\n\n-- Analyze after index creation\nVACUUM ANALYZE your_table_name;\n```\n\n**Supports:** All spatial operators (`&&`, `@>`, `<@`, `~=`, `<->`)\n**Best for:** General-purpose spatial queries, mixed query patterns\n\n### BRIN Index\n\nBlock Range Index for very large, naturally ordered datasets.\n\n```sql\n-- BRIN for very large, append-only GEOMETRY tables (geography uses GiST)\nCREATE INDEX idx_your_table_geom_brin\n ON your_table_name\n USING BRIN (geom)\n WITH (pages_per_range = 128);\n```\n\n**Supports:** Bounding box operators (`&&`, `@>`, `<@`)\n**Best for:** Append-only tables, time-series spatial data, very large datasets (>100M rows)\n**Trade-off:** Much smaller than GiST, but less precise filtering\n\n### SP-GiST Index\n\nSpace-partitioned GiST for point data with specific distributions.\n\n```sql\n-- SP-GiST for GEOMETRY(POINT, ...) only\nCREATE INDEX idx_sensors_location_spgist\n ON sensors\n USING SPGIST (location);\n```\n\n**Best for:** Point-only data, quadtree-friendly distributions\n**Not for:** Complex geometries, mixed types\n\n### Index Selection Guide\n\n| Scenario | Index Type | Reasoning |\n| -------------------------------- | ------------- | ------------------------------------------ |\n| General spatial queries | GiST | Most versatile, supports all operators |\n| Very large, append-only | BRIN | Tiny footprint, good for time-ordered data |\n| Point-only, uniform distribution | SP-GiST | Efficient for point lookups |\n| Geography columns | GiST | Only supported option |\n| Composite spatial + attribute | GiST + B-tree | Separate indexes or expression index |\n\n## Table Design Examples\n\n### Points of Interest (POI)\n\n```sql\nCREATE TABLE pois (\n id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n name TEXT NOT NULL,\n category TEXT NOT NULL,\n location GEOGRAPHY(POINT, 4326) NOT NULL,\n address TEXT,\n metadata JSONB DEFAULT '{}',\n created_at TIMESTAMPTZ NOT NULL DEFAULT now(),\n CONSTRAINT valid_category CHECK (category IN (\n 'restaurant', 'hotel', 'gas_station', 'hospital', 'school'\n ))\n);\n\n-- Spatial index\nCREATE INDEX idx_pois_location ON pois USING GIST (location);\n\n-- Category + location for filtered spatial queries\nCREATE INDEX idx_pois_category ON pois (category);\n\n-- Find restaurants within 1km\nSELECT name, address,\n ST_Distance(\n location,\n ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326)::GEOGRAPHY\n ) AS distance_m\nFROM pois\nWHERE category = 'restaurant'\n AND ST_DWithin(\n location,\n ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326)::GEOGRAPHY,\n 1000\n )\nORDER BY distance_m;\n```\n\n### Property Parcels\n\n```sql\nCREATE TABLE parcels (\n id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n parcel_id TEXT NOT NULL UNIQUE,\n owner_name TEXT,\n boundary GEOMETRY(MULTIPOLYGON, 4326) NOT NULL,\n centroid GEOMETRY(POINT, 4326) GENERATED ALWAYS AS (ST_Centroid(boundary)) STORED,\n area_sqm DOUBLE PRECISION GENERATED ALWAYS AS (\n ST_Area(boundary::GEOGRAPHY)\n ) STORED,\n perimeter_m DOUBLE PRECISION GENERATED ALWAYS AS (\n ST_Perimeter(boundary::GEOGRAPHY)\n ) STORED,\n CONSTRAINT valid_boundary CHECK (ST_IsValid(boundary)),\n CONSTRAINT closed_boundary CHECK (ST_IsClosed(ST_ExteriorRing(ST_GeometryN(boundary, 1))))\n);\n\nCREATE INDEX idx_parcels_boundary ON parcels USING GIST (boundary);\nCREATE INDEX idx_parcels_centroid ON parcels USING GIST (centroid);\n\n-- Find parcels intersecting a search area\nSELECT parcel_id, owner_name, area_sqm\nFROM parcels\nWHERE ST_Intersects(boundary, ST_MakeEnvelope(-122.5, 37.7, -122.4, 37.8, 4326));\n```\n\n### GPS Tracking\n\n```sql\nCREATE TABLE gps_tracks (\n id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n device_id TEXT NOT NULL,\n recorded_at TIMESTAMPTZ NOT NULL,\n location GEOGRAPHY(POINT, 4326) NOT NULL,\n speed_kmh DOUBLE PRECISION,\n heading DOUBLE PRECISION,\n accuracy_m DOUBLE PRECISION\n);\n\n-- Composite index for device + time queries\nCREATE INDEX idx_gps_device_time ON gps_tracks (device_id, recorded_at DESC);\n\n-- Spatial index for location queries\nCREATE INDEX idx_gps_location ON gps_tracks USING GIST (location);\n\n-- Note: GEOGRAPHY supports GiST; BRIN is for GEOMETRY (when appropriate).\n\n-- Create linestring from track points\nSELECT\n device_id,\n ST_MakeLine(location::GEOMETRY ORDER BY recorded_at) AS track_line,\n MIN(recorded_at) AS start_time,\n MAX(recorded_at) AS end_time\nFROM gps_tracks\nWHERE device_id = 'device_001'\n AND recorded_at >= '2024-01-01'\nGROUP BY device_id;\n```\n\n### Service Areas / Coverage Zones\n\n```sql\nCREATE TABLE service_zones (\n id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n zone_name TEXT NOT NULL,\n zone_type TEXT NOT NULL,\n boundary GEOMETRY(POLYGON, 4326) NOT NULL,\n population INTEGER,\n active BOOLEAN NOT NULL DEFAULT true,\n CONSTRAINT valid_zone_type CHECK (zone_type IN ('delivery', 'service', 'coverage')),\n CONSTRAINT valid_boundary CHECK (ST_IsValid(boundary))\n);\n\nCREATE INDEX idx_zones_boundary ON service_zones USING GIST (boundary);\nCREATE INDEX idx_zones_active ON service_zones (active) WHERE active = true;\n\n-- Check if location is within any active service zone\nSELECT zone_name, zone_type\nFROM service_zones\nWHERE active = true\n AND ST_Contains(boundary, ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326));\n```\n\n## Performance Patterns\n\n### Use ST_DWithin Instead of ST_Distance\n\n```sql\n-- SLOW: calculates distance for all rows\nSELECT * FROM pois\nWHERE ST_Distance(location, ref_point) < 1000;\n\n-- FAST: uses spatial index\nSELECT * FROM pois\nWHERE ST_DWithin(location, ref_point, 1000);\n```\n\n### Use && for Bounding Box Pre-filtering\n\n```sql\n-- Bounding box operator leverages spatial index\nSELECT * FROM parcels\nWHERE boundary && ST_MakeEnvelope(-122.5, 37.7, -122.4, 37.8, 4326)\n AND ST_Intersects(boundary, search_polygon);\n```\n\n### Avoid Functions on Indexed Columns\n\n```sql\n-- SLOW: function prevents index usage\nSELECT * FROM parcels WHERE ST_Area(boundary) > 10000;\n\n-- FAST: use generated column with regular index\nALTER TABLE parcels ADD COLUMN area_sqm DOUBLE PRECISION\n GENERATED ALWAYS AS (ST_Area(boundary::GEOGRAPHY)) STORED;\nCREATE INDEX idx_parcels_area ON parcels (area_sqm);\nSELECT * FROM parcels WHERE area_sqm > 10000;\n```\n\n### Simplify Geometries for Display\n\n```sql\n-- Reduce complexity for web display (tolerance in CRS units)\nSELECT\n id,\n name,\n ST_AsGeoJSON(ST_Simplify(boundary, 0.0001)) AS geojson\nFROM parcels;\n```\n\n### Use Appropriate Precision\n\n```sql\n-- Reduce coordinate precision for storage efficiency\nUPDATE locations SET geom = ST_ReducePrecision(geom, 0.000001);\n\n-- GeoJSON with limited decimal places\nSELECT ST_AsGeoJSON(location, 6) AS geojson FROM pois;\n```\n\n## Data Validation\n\n### Geometry Validity Checks\n\n```sql\n-- Add validity constraint\nALTER TABLE parcels ADD CONSTRAINT valid_geom CHECK (ST_IsValid(boundary));\n\n-- Find and fix invalid geometries\nSELECT id, ST_IsValidReason(boundary) AS reason\nFROM parcels\nWHERE NOT ST_IsValid(boundary);\n\n-- Attempt to fix invalid geometries\nUPDATE parcels\nSET boundary = ST_MakeValid(boundary)\nWHERE NOT ST_IsValid(boundary);\n```\n\n### SRID Consistency\n\n```sql\n-- Verify SRID consistency\nSELECT DISTINCT ST_SRID(geom) FROM spatial_table;\n\n-- Enforce SRID with constraint\nALTER TABLE locations ADD CONSTRAINT enforce_srid\n CHECK (ST_SRID(location) = 4326);\n```\n\n### Coordinate Range Validation\n\n```sql\n-- Ensure coordinates are within valid WGS84 bounds\nALTER TABLE global_locations ADD CONSTRAINT valid_coords CHECK (\n ST_X(location::GEOMETRY) BETWEEN -180 AND 180 AND\n ST_Y(location::GEOMETRY) BETWEEN -90 AND 90\n);\n```\n\n## Do Not Use\n\n- **PostgreSQL built-in types** (`POINT`, `LINE`, `POLYGON`, `CIRCLE`) - use PostGIS types instead\n- **SRID 0** (undefined) - always specify the correct SRID\n- **ST_Distance for filtering** - use ST_DWithin for index-supported distance queries\n- **Mixed SRIDs** in operations - always transform to common SRID first\n- **GEOGRAPHY for complex analysis** - use GEOMETRY with appropriate projection\n- **Over-precise coordinates** - GPS accuracy is ~3-5m, 6 decimal places (0.1m) is sufficient\n\n## Common Pitfalls\n\n1. **Longitude/Latitude order**: PostGIS uses `(longitude, latitude)` = `(X, Y)`, not `(lat, lon)`\n2. **GEOGRAPHY distance units**: Always in meters, regardless of display\n3. **Index not used**: Run `EXPLAIN ANALYZE` to verify spatial index usage\n4. **Transform performance**: Cache transformed geometries for repeated queries\n5. **Large geometries**: Consider ST_Subdivide for very complex polygons\n6. **SQL injection / unsafe dynamic SQL**: Don't concatenate untrusted input into SQL. Parameterize values; for dynamic identifiers use safe quoting (`quote_ident`, `format('%I', ...)`) or strict allowlists.","schemaVersion":1},"repoUrl":"https://github.com/timescale/pg-aiguide/tree/main/skills/design-postgis-tables","tags":["ai","ai-agents","ai-coding","claude-code-plugin","claude-code-plugins","claude-code-plugins-marketplace","claude-marketplace","claude-plugin","claude-skills","docs","documentation","mcp"],"stats":{"installVelocity7d":0,"retentionRate":0,"executions":0,"rating":null},"origin":"github","source":{"repo":"pg-aiguide","audit":{"files":["bun.lock","package.json"],"binaries":[],"findings":[],"packages":9,"auditedAt":"2026-09-25T11:52:20.587Z","lockfiles":["bun.lock"]},"forks":110,"owner":"timescale","stars":1847,"topics":["ai","ai-agents","ai-coding","claude-code-plugin","claude-code-plugins","claude-code-plugins-marketplace","claude-marketplace","claude-plugin","claude-skills","docs","documentation","mcp","mcp-server","postgres","postgresql","skills"],"license":"Apache-2.0","fullName":"timescale/pg-aiguide","homepage":null,"language":"Python","pushedAt":"2026-09-24T20:55:47Z","avatarUrl":"https://avatars.githubusercontent.com/u/8986001?v=4","crawledAt":"2026-09-25T11:52:15.055Z","openIssues":11,"manifestFile":"SKILL.md","manifestPath":"skills/design-postgis-tables/SKILL.md","defaultBranch":"main"},"readme":"# PostGIS Spatial Table Design\n\n## Before You Start (5 Questions)\n\n1. What is the geographic scope (single city/region vs global)?\n2. What are your primary query patterns (within-radius, bbox, intersects, nearest-neighbor)?\n3. What units do you need for distance/area (meters vs CRS units), and how accurate must they be?\n4. What is the expected scale (rows, write rate), and is the data mostly append-only?\n5. Do you need 3D (Z) or measures (M), or is 2D enough?\n\n**SQL injection note:** When turning these patterns into application code, use parameterized queries for user-provided values (WKT/WKB, coordinates, IDs, radii). Avoid string-concatenating untrusted input into SQL; for dynamic identifiers, use safe identifier quoting/whitelisting.\n\n## Core Rules\n\n- **Always use PostGIS geometry/geography types** instead of PostgreSQL's built-in geometric types (`POINT`, `LINE`, `POLYGON`, `CIRCLE`). PostGIS types provide true spatial capabilities.\n- **Choose between GEOMETRY and GEOGRAPHY** based on your use case: GEOMETRY for projected/local data with Cartesian math; GEOGRAPHY for global data requiring accurate spherical calculations.\n- **Always specify SRID** (Spatial Reference Identifier) when creating geometry columns. Use `4326` (WGS84) for GPS/global data, appropriate local projections for regional data.\n- **Create spatial indexes** on all geometry/geography columns using GiST (default). Consider BRIN only for very large **GEOMETRY** tables where rows are naturally ordered on disk and you can tolerate coarser filtering.\n- **Use constraint-based type enforcement** with `GEOMETRY(type, SRID)` syntax to ensure data integrity.\n\n## Geometry vs Geography\n\n### When to Use GEOMETRY\n\n- **Local/regional data** within a single coordinate system\n- **Projected coordinates** (meters, feet) for accurate area/distance calculations\n- **Complex spatial operations** (buffering, unions, intersections)\n- **Performance-critical queries** (Cartesian math is faster)\n- **Data already in a projected CRS** (UTM, State Plane, etc.)\n\n```sql\n-- Regional data with projected coordinates (UTM Zone 10N for California)\nCREATE TABLE local_parcels (\n id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n parcel_number TEXT NOT NULL,\n boundary GEOMETRY(POLYGON, 26910), -- UTM Zone 10N (meters)\n area_sqm DOUBLE PRECISION GENERATED ALWAYS AS (ST_Area(boundary)) STORED\n);\n```\n\n### When to Use GEOGRAPHY\n\n- **Global data** spanning multiple continents/hemispheres\n- **GPS coordinates** (latitude/longitude in decimal degrees)\n- **Accurate distance calculations** on Earth's surface (great circle)\n- **Simple spatial operations** (distance, containment)\n- **Data from GPS devices, geocoding services, or web maps**\n\n```sql\n-- Global data with geodetic calculations\nCREATE TABLE global_offices (\n id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n name TEXT NOT NULL,\n city TEXT NOT NULL,\n location GEOGRAPHY(POINT, 4326) -- WGS84 (lat/lon)\n);\n\n-- Distance in meters (accurate spherical calculation)\nSELECT\n a.name AS office_a,\n b.name AS office_b,\n ST_Distance(a.location, b.location) / 1000 AS distance_km\nFROM global_offices a\nCROSS JOIN global_offices b\nWHERE a.id < b.id;\n```\n\n### Comparison Table\n\n| Aspect | GEOMETRY | GEOGRAPHY |\n| ----------------- | ------------------------------------- | ------------------------- |\n| Coordinate system | Any SRID (projected or geodetic) | WGS84 (SRID 4326) only |\n| Distance units | CRS units (degrees, meters, feet) | Meters (always) |\n| Distance accuracy | Depends on projection | True spheroidal distance |\n| Area accuracy | Accurate in projected CRS | Accurate on sphere |\n| Function support | Full (300+ functions) | Limited (~40 functions) |\n| Performance | Faster (Cartesian math) | Slower (spherical math) |\n| Index type | GiST, BRIN, SP-GiST ","createdAt":"2026-09-25T11:52:20.613Z","updatedAt":"2026-09-25T11:52:20.613Z"},{"id":"cmugwiavl01twqu06944pnltj","slug":"timescale-pg-aiguide-design-postgres-tables","name":"design-postgres-tables","description":"Use this skill for general PostgreSQL table design. **Trigger when user asks to:** - Design PostgreSQL tables, schemas, or data models when creating new tables and when modifying existing ones. - Choose data types, constraints, or indexes for PostgreSQL - Create user tables, order tables, reference tables, or JSONB schemas - Understand PostgreSQL best practices for normalization, constraints, or indexing - Design update-heavy, upsert-heavy, or OLTP-style tables **Keywords:** PostgreSQL schema, table design, data types, PRIMARY KEY, FOREIGN KEY, indexes, B-tree, GIN, JSONB, constraints, normalization, identity columns, partitioning, row-level security Comprehensive reference covering data types, indexing strategies, constraints, JSONB patterns, partitioning, and PostgreSQL-specific best practices.","authorId":"gh:timescale","authorName":"timescale","version":"0.1.0","category":"Prompt","securityLevel":"Community","downloadsCount":0,"githubStars":1847,"pricePerCall":0,"manifest":{"name":"design-postgres-tables","tools":[],"category":"Prompt","entrypoint":{"type":"prompt"},"description":"Use this skill for general PostgreSQL table design. **Trigger when user asks to:** - Design PostgreSQL tables, schemas, or data models when creating new tables and when modifying existing ones. - Choose data types, constraints, or indexes for PostgreSQL - Create user tables, order tables, reference tables, or JSONB schemas - Understand PostgreSQL best practices for normalization, constraints, or indexing - Design update-heavy, upsert-heavy, or OLTP-style tables **Keywords:** PostgreSQL schema, table design, data types, PRIMARY KEY, FOREIGN KEY, indexes, B-tree, GIN, JSONB, constraints, normalization, identity columns, partitioning, row-level security Comprehensive reference covering data types, indexing strategies, constraints, JSONB patterns, partitioning, and PostgreSQL-specific best practices.","permissions":[],"systemPrompt":"# PostgreSQL Table Design\n\n## Core Rules\n\n- Define a **PRIMARY KEY** for reference tables (users, orders, etc.). Not always needed for time-series/event/log data. When used, prefer `BIGINT GENERATED ALWAYS AS IDENTITY`; use `UUID` only when global uniqueness/opacity is needed.\n- **Normalize first (to 3NF)** to eliminate data redundancy and update anomalies; denormalize **only** for measured, high-ROI reads where join performance is proven problematic. Premature denormalization creates maintenance burden.\n- Add **NOT NULL** everywhere it’s semantically required; use **DEFAULT**s for common values.\n- Create **indexes for access paths you actually query**: PK/unique (auto), **FK columns (manual!)**, frequent filters/sorts, and join keys.\n- Prefer **TIMESTAMPTZ** for event time; **NUMERIC** for money; **TEXT** for strings; **BIGINT** for integer values, **DOUBLE PRECISION** for floats (or `NUMERIC` for exact decimal arithmetic).\n\n## PostgreSQL “Gotchas”\n\n- **Identifiers**: unquoted → lowercased. Avoid quoted/mixed-case names. Convention: use `snake_case` for table/column names.\n- **Unique + NULLs**: UNIQUE allows multiple NULLs. Use `UNIQUE (...) NULLS NOT DISTINCT` (PG15+) to restrict to one NULL.\n- **FK indexes**: PostgreSQL **does not** auto-index FK columns. Add them.\n- **No silent coercions**: length/precision overflows error out (no truncation). Example: inserting 999 into `NUMERIC(2,0)` fails with error, unlike some databases that silently truncate or round.\n- **Sequences/identity have gaps** (normal; don't \"fix\"). Rollbacks, crashes, and concurrent transactions create gaps in ID sequences (1, 2, 5, 6...). This is expected behavior—don't try to make IDs consecutive.\n- **Heap storage**: no clustered PK by default (unlike SQL Server/MySQL InnoDB); `CLUSTER` is one-off reorganization, not maintained on subsequent inserts. Row order on disk is insertion order unless explicitly clustered.\n- **MVCC**: updates/deletes leave dead tuples; vacuum handles them—design to avoid hot wide-row churn.\n\n## Data Types\n\n- **IDs**: `BIGINT GENERATED ALWAYS AS IDENTITY` preferred (`GENERATED BY DEFAULT` also fine); `UUID` when merging/federating/used in a distributed system or for opaque IDs. Generate with `uuidv7()` (preferred if using PG18+) or `gen_random_uuid()` (if using an older PG version).\n- **Integers**: prefer `BIGINT` unless storage space is critical; `INTEGER` for smaller ranges; avoid `SMALLINT` unless constrained.\n- **Floats**: prefer `DOUBLE PRECISION` over `REAL` unless storage space is critical. Use `NUMERIC` for exact decimal arithmetic.\n- **Strings**: prefer `TEXT`; if length limits needed, use `CHECK (LENGTH(col) <= n)` instead of `VARCHAR(n)`; avoid `CHAR(n)`. Use `BYTEA` for binary data. Large strings/binary (>2KB default threshold) automatically stored in TOAST with compression. TOAST storage: `PLAIN` (no TOAST), `EXTENDED` (compress + out-of-line), `EXTERNAL` (out-of-line, no compress), `MAIN` (compress, keep in-line if possible). Default `EXTENDED` usually optimal. Control with `ALTER TABLE tbl ALTER COLUMN col SET STORAGE strategy` and `ALTER TABLE tbl SET (toast_tuple_target = 4096)` for threshold. Case-insensitive: for locale/accent handling use non-deterministic collations; for plain ASCII use expression indexes on `LOWER(col)` (preferred unless column needs case-insensitive PK/FK/UNIQUE) or `CITEXT`.\n- **Money**: `NUMERIC(p,s)` (never float).\n- **Time**: `TIMESTAMPTZ` for timestamps; `DATE` for date-only; `INTERVAL` for durations. Avoid `TIMESTAMP` (without timezone). Use `now()` for transaction start time, `clock_timestamp()` for current wall-clock time.\n- **Booleans**: `BOOLEAN` with `NOT NULL` constraint unless tri-state values are required.\n- **Enums**: `CREATE TYPE ... AS ENUM` for small, stable sets (e.g. US states, days of week). For business-logic-driven and evolving values (e.g. order statuses) → use TEXT (or INT) + CHECK or lookup table.\n- **Arrays**: `TEXT[]`, `INTEGER[]`, etc. Use for ordered lists where you query elements. Index with **GIN** for containment (`@>`, `<@`) and overlap (`&&`) queries. Access: `arr[1]` (1-indexed), `arr[1:3]` (slicing). Good for tags, categories; avoid for relations—use junction tables instead. Literal syntax: `'{val1,val2}'` or `ARRAY[val1,val2]`.\n- **Range types**: `daterange`, `numrange`, `tstzrange` for intervals. Support overlap (`&&`), containment (`@>`), operators. Index with **GiST**. Good for scheduling, versioning, numeric ranges. Pick a bounds scheme and use it consistently; prefer `[)` (inclusive/exclusive) by default.\n- **Network types**: `INET` for IP addresses, `CIDR` for network ranges, `MACADDR` for MAC addresses. Support network operators (`<<`, `>>`, `&&`).\n- **Geometric types**: avoid `POINT`, `LINE`, `POLYGON`, `CIRCLE`. Index with **GiST**. Consider **PostGIS** for spatial features.\n- **Text search**: `TSVECTOR` for full-text search documents, `TSQUERY` for search queries. Index `tsvector` with **GIN**. Always specify language: `to_tsvector('english', col)` and `to_tsquery('english', 'query')`. Never use single-argument versions. This applies to both index expressions and queries.\n- **Domain types**: `CREATE DOMAIN email AS TEXT CHECK (VALUE ~ '^[^@]+@[^@]+$')` for reusable custom types with validation. Enforces constraints across tables.\n- **Composite types**: `CREATE TYPE address AS (street TEXT, city TEXT, zip TEXT)` for structured data within columns. Access with `(col).field` syntax.\n- **JSONB**: preferred over JSON; index with **GIN**. Use only for optional/semi-structured attrs. ONLY use JSON if the original ordering of the contents MUST be preserved.\n- **Vector types**: `vector` type by `pgvector` for vector similarity search for embeddings.\n\n### Do not use the following data types\n\n- DO NOT use `timestamp` (without time zone); DO use `timestamptz` instead.\n- DO NOT use `char(n)` or `varchar(n)`; DO use `text` instead.\n- DO NOT use `money` type; DO use `numeric` instead.\n- DO NOT use `timetz` type; DO use `timestamptz` instead.\n- DO NOT use `timestamptz(0)` or any other precision specification; DO use `timestamptz` instead\n- DO NOT use `serial` type; DO use `generated always as identity` instead.\n- DO NOT use `POINT`, `LINE`, `POLYGON`, `CIRCLE` built-in types, DO use `geometry` from postgis extension instead.\n\n## Table Types\n\n- **Regular**: default; fully durable, logged.\n- **TEMPORARY**: session-scoped, auto-dropped, not logged. Faster for scratch work.\n- **UNLOGGED**: persistent but not crash-safe. Faster writes; good for caches/staging.\n\n## Row-Level Security\n\nEnable with `ALTER TABLE tbl ENABLE ROW LEVEL SECURITY`. Create policies: `CREATE POLICY user_access ON orders FOR SELECT TO app_users USING (user_id = current_user_id())`. Built-in user-based access control at the row level.\n\n## Constraints\n\n- **PK**: implicit UNIQUE + NOT NULL; creates a B-tree index.\n- **FK**: specify `ON DELETE/UPDATE` action (`CASCADE`, `RESTRICT`, `SET NULL`, `SET DEFAULT`). Add explicit index on referencing column—speeds up joins and prevents locking issues on parent deletes/updates. Use `DEFERRABLE INITIALLY DEFERRED` for circular FK dependencies checked at transaction end.\n- **UNIQUE**: creates a B-tree index; allows multiple NULLs unless `NULLS NOT DISTINCT` (PG15+). Standard behavior: `(1, NULL)` and `(1, NULL)` are allowed. With `NULLS NOT DISTINCT`: only one `(1, NULL)` allowed. Prefer `NULLS NOT DISTINCT` unless you specifically need duplicate NULLs.\n- **CHECK**: row-local constraints; NULL values pass the check (three-valued logic). Example: `CHECK (price > 0)` allows NULL prices. Combine with `NOT NULL` to enforce: `price NUMERIC NOT NULL CHECK (price > 0)`.\n- **EXCLUDE**: prevents overlapping values using operators. `EXCLUDE USING gist (room_id WITH =, booking_period WITH &&)` prevents double-booking rooms. Requires appropriate index type (often GiST).\n\n## Indexing\n\n- **B-tree**: default for equality/range queries (`=`, `<`, `>`, `BETWEEN`, `ORDER BY`)\n- **Composite**: order matters—index used if equality on leftmost prefix (`WHERE a = ? AND b > ?` uses index on `(a,b)`, but `WHERE b = ?` does not). Put most selective/frequently filtered columns first.\n- **Covering**: `CREATE INDEX ON tbl (id) INCLUDE (name, email)` - includes non-key columns for index-only scans without visiting table.\n- **Partial**: for hot subsets (`WHERE status = 'active'` → `CREATE INDEX ON tbl (user_id) WHERE status = 'active'`). Any query with `status = 'active'` can use this index.\n- **Expression**: for computed search keys (`CREATE INDEX ON tbl (LOWER(email))`). Expression must match exactly in WHERE clause: `WHERE LOWER(email) = 'user@example.com'`.\n- **GIN**: JSONB containment/existence, arrays (`@>`, `?`), full-text search (`@@`)\n- **GiST**: ranges, geometry, exclusion constraints\n- **BRIN**: very large, naturally ordered data (time-series)—minimal storage overhead. Effective when row order on disk correlates with indexed column (insertion order or after `CLUSTER`).\n\n## Partitioning\n\n- Use for very large tables (>100M rows) where queries consistently filter on partition key (often time/date).\n- Alternate use: use for tables where data maintenance tasks dictates e.g. data pruned or bulk replaced periodically\n- **RANGE**: common for time-series (`PARTITION BY RANGE (created_at)`). Create partitions: `CREATE TABLE logs_2024_01 PARTITION OF logs FOR VALUES FROM ('2024-01-01') TO ('2024-02-01')`. **TimescaleDB** automates time-based or ID-based partitioning with retention policies and compression.\n- **LIST**: for discrete values (`PARTITION BY LIST (region)`). Example: `FOR VALUES IN ('us-east', 'us-west')`.\n- **HASH**: for even distribution when no natural key (`PARTITION BY HASH (user_id)`). Creates N partitions with modulus.\n- **Constraint exclusion**: requires `CHECK` constraints on partitions for query planner to prune. Auto-created for declarative partitioning (PG10+).\n- Prefer declarative partitioning or hypertables. Do NOT use table inheritance.\n- **Limitations**: no global UNIQUE constraints—include partition key in PK/UNIQUE. FKs from partitioned tables not supported; use triggers.\n\n## Special Considerations\n\n### Update-Heavy Tables\n\n- **Separate hot/cold columns**—put frequently updated columns in separate table to minimize bloat.\n- **Use `fillfactor=90`** to leave space for HOT updates that avoid index maintenance.\n- **Avoid updating indexed columns**—prevents beneficial HOT updates.\n- **Partition by update patterns**—separate frequently updated rows in a different partition from stable data.\n\n### Insert-Heavy Workloads\n\n- **Minimize indexes**—only create what you query; every index slows inserts.\n- **Use `COPY` or multi-row `INSERT`** instead of single-row inserts.\n- **UNLOGGED tables** for rebuildable staging data—much faster writes.\n- **Defer index creation** for bulk loads—>drop index, load data, recreate indexes.\n- **Partition by time/hash** to distribute load. **TimescaleDB** automates partitioning and compression of insert-heavy data.\n- **Use a natural key for primary key** such as a (timestamp, device_id) if enforcing global uniqueness is important many insert-heavy tables don't need a primary key at all.\n- If you do need a surrogate key, **Prefer `BIGINT GENERATED ALWAYS AS IDENTITY` over `UUID`**.\n\n### Upsert-Friendly Design\n\n- **Requires UNIQUE index** on conflict target columns—`ON CONFLICT (col1, col2)` needs exact matching unique index (partial indexes don't work).\n- **Use `EXCLUDED.column`** to reference would-be-inserted values; only update columns that actually changed to reduce write overhead.\n- **`DO NOTHING` faster** than `DO UPDATE` when no actual update needed.\n\n### Safe Schema Evolution\n\n- **Transactional DDL**: most DDL operations can run in transactions and be rolled back—`BEGIN; ALTER TABLE...; ROLLBACK;` for safe testing.\n- **Concurrent index creation**: `CREATE INDEX CONCURRENTLY` avoids blocking writes but can't run in transactions.\n- **Volatile defaults cause rewrites**: adding `NOT NULL` columns with volatile defaults (e.g., `now()`, `gen_random_uuid()`) rewrites entire table. Non-volatile defaults are fast.\n- **Drop constraints before columns**: `ALTER TABLE DROP CONSTRAINT` then `DROP COLUMN` to avoid dependency issues.\n- **Function signature changes**: `CREATE OR REPLACE` with different arguments creates overloads, not replacements. DROP old version if no overload desired.\n\n## Generated Columns\n\n- `... GENERATED ALWAYS AS () STORED` for computed, indexable fields. PG18+ adds `VIRTUAL` columns (computed on read, not stored).\n\n## Extensions\n\n- **`pgcrypto`**: `crypt()` for password hashing.\n- **`uuid-ossp`**: alternative UUID functions; prefer `pgcrypto` for new projects.\n- **`pg_trgm`**: fuzzy text search with `%` operator, `similarity()` function. Index with GIN for `LIKE '%pattern%'` acceleration.\n- **`citext`**: case-insensitive text type. Prefer expression indexes on `LOWER(col)` unless you need case-insensitive constraints.\n- **`btree_gin`/`btree_gist`**: enable mixed-type indexes (e.g., GIN index on both JSONB and text columns).\n- **`hstore`**: key-value pairs; mostly superseded by JSONB but useful for simple string mappings.\n- **`timescaledb`**: essential for time-series—automated partitioning, retention, compression, continuous aggregates.\n- **`postgis`**: comprehensive geospatial support beyond basic geometric types—essential for location-based applications.\n- **`pgvector`**: vector similarity search for embeddings.\n- **`pgaudit`**: audit logging for all database activity.\n\n## JSONB Guidance\n\n- Prefer `JSONB` with **GIN** index.\n- Default: `CREATE INDEX ON tbl USING GIN (jsonb_col);` → accelerates:\n - **Containment** `jsonb_col @> '{\"k\":\"v\"}'`\n - **Key existence** `jsonb_col ? 'k'`, **any/all keys** `?\\|`, `?&`\n - **Path containment** on nested docs\n - **Disjunction** `jsonb_col @> ANY(ARRAY['{\"status\":\"active\"}', '{\"status\":\"pending\"}'])`\n- Heavy `@>` workloads: consider opclass `jsonb_path_ops` for smaller/faster containment-only indexes:\n - `CREATE INDEX ON tbl USING GIN (jsonb_col jsonb_path_ops);`\n - **Trade-off**: loses support for key existence (`?`, `?|`, `?&`) queries—only supports containment (`@>`)\n- Equality/range on a specific scalar field: extract and index with B-tree (generated column or expression):\n - `ALTER TABLE tbl ADD COLUMN price INT GENERATED ALWAYS AS ((jsonb_col->>'price')::INT) STORED;`\n - `CREATE INDEX ON tbl (price);`\n - Prefer queries like `WHERE price BETWEEN 100 AND 500` (uses B-tree) over `WHERE (jsonb_col->>'price')::INT BETWEEN 100 AND 500` without index.\n- Arrays inside JSONB: use GIN + `@>` for containment (e.g., tags). Consider `jsonb_path_ops` if only doing containment.\n- Keep core relations in tables; use JSONB for optional/variable attributes.\n- Use constraints to limit allowed JSONB values in a column e.g. `config JSONB NOT NULL CHECK(jsonb_typeof(config) = 'object')`\n\n## Examples\n\n### Users\n\n```sql\nCREATE TABLE users (\n user_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n email TEXT NOT NULL UNIQUE,\n name TEXT NOT NULL,\n created_at TIMESTAMPTZ NOT NULL DEFAULT now()\n);\nCREATE UNIQUE INDEX ON users (LOWER(email));\nCREATE INDEX ON users (created_at);\n```\n\n### Orders\n\n```sql\nCREATE TABLE orders (\n order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n user_id BIGINT NOT NULL REFERENCES users(user_id),\n status TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING','PAID','CANCELED')),\n total NUMERIC(10,2) NOT NULL CHECK (total > 0),\n created_at TIMESTAMPTZ NOT NULL DEFAULT now()\n);\nCREATE INDEX ON orders (user_id);\nCREATE INDEX ON orders (created_at);\n```\n\n### JSONB\n\n```sql\nCREATE TABLE profiles (\n user_id BIGINT PRIMARY KEY REFERENCES users(user_id),\n attrs JSONB NOT NULL DEFAULT '{}',\n theme TEXT GENERATED ALWAYS AS (attrs->>'theme') STORED\n);\nCREATE INDEX profiles_attrs_gin ON profiles USING GIN (attrs);\n```","schemaVersion":1},"repoUrl":"https://github.com/timescale/pg-aiguide/tree/main/skills/design-postgres-tables","tags":["ai","ai-agents","ai-coding","claude-code-plugin","claude-code-plugins","claude-code-plugins-marketplace","claude-marketplace","claude-plugin","claude-skills","docs","documentation","mcp"],"stats":{"installVelocity7d":0,"retentionRate":0,"executions":0,"rating":null},"origin":"github","source":{"repo":"pg-aiguide","audit":{"files":["bun.lock","package.json"],"binaries":[],"findings":[],"packages":9,"auditedAt":"2026-09-25T11:52:20.587Z","lockfiles":["bun.lock"]},"forks":110,"owner":"timescale","stars":1847,"topics":["ai","ai-agents","ai-coding","claude-code-plugin","claude-code-plugins","claude-code-plugins-marketplace","claude-marketplace","claude-plugin","claude-skills","docs","documentation","mcp","mcp-server","postgres","postgresql","skills"],"license":"Apache-2.0","fullName":"timescale/pg-aiguide","homepage":null,"language":"Python","pushedAt":"2026-09-24T20:55:47Z","avatarUrl":"https://avatars.githubusercontent.com/u/8986001?v=4","crawledAt":"2026-09-25T11:52:15.055Z","openIssues":11,"manifestFile":"SKILL.md","manifestPath":"skills/design-postgres-tables/SKILL.md","defaultBranch":"main"},"readme":"# PostgreSQL Table Design\n\n## Core Rules\n\n- Define a **PRIMARY KEY** for reference tables (users, orders, etc.). Not always needed for time-series/event/log data. When used, prefer `BIGINT GENERATED ALWAYS AS IDENTITY`; use `UUID` only when global uniqueness/opacity is needed.\n- **Normalize first (to 3NF)** to eliminate data redundancy and update anomalies; denormalize **only** for measured, high-ROI reads where join performance is proven problematic. Premature denormalization creates maintenance burden.\n- Add **NOT NULL** everywhere it’s semantically required; use **DEFAULT**s for common values.\n- Create **indexes for access paths you actually query**: PK/unique (auto), **FK columns (manual!)**, frequent filters/sorts, and join keys.\n- Prefer **TIMESTAMPTZ** for event time; **NUMERIC** for money; **TEXT** for strings; **BIGINT** for integer values, **DOUBLE PRECISION** for floats (or `NUMERIC` for exact decimal arithmetic).\n\n## PostgreSQL “Gotchas”\n\n- **Identifiers**: unquoted → lowercased. Avoid quoted/mixed-case names. Convention: use `snake_case` for table/column names.\n- **Unique + NULLs**: UNIQUE allows multiple NULLs. Use `UNIQUE (...) NULLS NOT DISTINCT` (PG15+) to restrict to one NULL.\n- **FK indexes**: PostgreSQL **does not** auto-index FK columns. Add them.\n- **No silent coercions**: length/precision overflows error out (no truncation). Example: inserting 999 into `NUMERIC(2,0)` fails with error, unlike some databases that silently truncate or round.\n- **Sequences/identity have gaps** (normal; don't \"fix\"). Rollbacks, crashes, and concurrent transactions create gaps in ID sequences (1, 2, 5, 6...). This is expected behavior—don't try to make IDs consecutive.\n- **Heap storage**: no clustered PK by default (unlike SQL Server/MySQL InnoDB); `CLUSTER` is one-off reorganization, not maintained on subsequent inserts. Row order on disk is insertion order unless explicitly clustered.\n- **MVCC**: updates/deletes leave dead tuples; vacuum handles them—design to avoid hot wide-row churn.\n\n## Data Types\n\n- **IDs**: `BIGINT GENERATED ALWAYS AS IDENTITY` preferred (`GENERATED BY DEFAULT` also fine); `UUID` when merging/federating/used in a distributed system or for opaque IDs. Generate with `uuidv7()` (preferred if using PG18+) or `gen_random_uuid()` (if using an older PG version).\n- **Integers**: prefer `BIGINT` unless storage space is critical; `INTEGER` for smaller ranges; avoid `SMALLINT` unless constrained.\n- **Floats**: prefer `DOUBLE PRECISION` over `REAL` unless storage space is critical. Use `NUMERIC` for exact decimal arithmetic.\n- **Strings**: prefer `TEXT`; if length limits needed, use `CHECK (LENGTH(col) <= n)` instead of `VARCHAR(n)`; avoid `CHAR(n)`. Use `BYTEA` for binary data. Large strings/binary (>2KB default threshold) automatically stored in TOAST with compression. TOAST storage: `PLAIN` (no TOAST), `EXTENDED` (compress + out-of-line), `EXTERNAL` (out-of-line, no compress), `MAIN` (compress, keep in-line if possible). Default `EXTENDED` usually optimal. Control with `ALTER TABLE tbl ALTER COLUMN col SET STORAGE strategy` and `ALTER TABLE tbl SET (toast_tuple_target = 4096)` for threshold. Case-insensitive: for locale/accent handling use non-deterministic collations; for plain ASCII use expression indexes on `LOWER(col)` (preferred unless column needs case-insensitive PK/FK/UNIQUE) or `CITEXT`.\n- **Money**: `NUMERIC(p,s)` (never float).\n- **Time**: `TIMESTAMPTZ` for timestamps; `DATE` for date-only; `INTERVAL` for durations. Avoid `TIMESTAMP` (without timezone). Use `now()` for transaction start time, `clock_timestamp()` for current wall-clock time.\n- **Booleans**: `BOOLEAN` with `NOT NULL` constraint unless tri-state values are required.\n- **Enums**: `CREATE TYPE ... AS ENUM` for small, stable sets (e.g. US states, days of week). For business-logic-driven and evolving values (e.g. order statuses) → use TEXT (or INT) + CHECK or lookup table.\n- **Arrays**: `TEXT[]`, `INTEGER[]`, etc. Use for ordered lists where","createdAt":"2026-09-25T11:52:20.625Z","updatedAt":"2026-09-25T11:52:20.625Z"},{"id":"cmugwiavy01tzqu06enkh8k7i","slug":"timescale-pg-aiguide-find-hypertable-candidates","name":"find-hypertable-candidates","description":"Use this skill to analyze an existing PostgreSQL database and identify which tables should be converted to Timescale/TimescaleDB hypertables. **Trigger when user asks to:** - Analyze database tables for hypertable conversion potential - Identify time-series or event tables in an existing schema - Evaluate if a table would benefit from Timescale/TimescaleDB - Audit PostgreSQL tables for migration to Timescale/TimescaleDB/TigerData - Score or rank tables for hypertable candidacy **Keywords:** hypertable candidate, table analysis, migration assessment, Timescale, TimescaleDB, time-series detection, insert-heavy tables, event logs, audit tables Provides SQL queries to analyze table statistics, index patterns, and query patterns. Includes scoring criteria (8+ points = good candidate) and pattern recognition for IoT, events, transactions, and sequential data.","authorId":"gh:timescale","authorName":"timescale","version":"0.1.0","category":"Prompt","securityLevel":"Community","downloadsCount":0,"githubStars":1847,"pricePerCall":0,"manifest":{"name":"find-hypertable-candidates","tools":[],"category":"Prompt","entrypoint":{"type":"prompt"},"description":"Use this skill to analyze an existing PostgreSQL database and identify which tables should be converted to Timescale/TimescaleDB hypertables. **Trigger when user asks to:** - Analyze database tables for hypertable conversion potential - Identify time-series or event tables in an existing schema - Evaluate if a table would benefit from Timescale/TimescaleDB - Audit PostgreSQL tables for migration to Timescale/TimescaleDB/TigerData - Score or rank tables for hypertable candidacy **Keywords:** hypertable candidate, table analysis, migration assessment, Timescale, TimescaleDB, time-series detection, insert-heavy tables, event logs, audit tables Provides SQL queries to analyze table statistics, index patterns, and query patterns. Includes scoring criteria (8+ points = good candidate) and pattern recognition for IoT, events, transactions, and sequential data.","permissions":[],"systemPrompt":"# PostgreSQL Hypertable Candidate Analysis\n\nIdentify tables that would benefit from TimescaleDB hypertable conversion. After identification, use the companion \"migrate-postgres-tables-to-hypertables\" skill for configuration and migration.\n\n## TimescaleDB Benefits\n\n**Performance gains:** 90%+ compression, fast time-based queries, improved insert performance, efficient aggregations, continuous aggregates for materialization (dashboards, reports, analytics), automatic data management (retention, compression).\n\n**Best for insert-heavy patterns:**\n\n- Time-series data (sensors, metrics, monitoring)\n- Event logs (user events, audit trails, application logs)\n- Transaction records (orders, payments, financial)\n- Sequential data (auto-incrementing IDs with timestamps)\n- Append-only datasets (immutable records, historical)\n\n**Requirements:** Large volumes (1M+ rows), time-based queries, infrequent updates\n\n## Step 1: Database Schema Analysis\n\n### Option A: From Database Connection\n\n#### Table statistics and size\n\n```sql\n-- Get all tables with row counts and insert/update patterns\nWITH table_stats AS (\n SELECT\n schemaname, tablename,\n n_tup_ins as total_inserts,\n n_tup_upd as total_updates,\n n_tup_del as total_deletes,\n n_live_tup as live_rows,\n n_dead_tup as dead_rows\n FROM pg_stat_user_tables\n),\ntable_sizes AS (\n SELECT\n schemaname, tablename,\n pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,\n pg_total_relation_size(schemaname||'.'||tablename) as total_size_bytes\n FROM pg_tables\n WHERE schemaname NOT IN ('information_schema', 'pg_catalog')\n)\nSELECT\n ts.schemaname, ts.tablename, ts.live_rows,\n tsize.total_size, tsize.total_size_bytes,\n ts.total_inserts, ts.total_updates, ts.total_deletes,\n ROUND(CASE WHEN ts.live_rows > 0\n THEN (ts.total_inserts::float / ts.live_rows) * 100\n ELSE 0 END, 2) as insert_ratio_pct\nFROM table_stats ts\nJOIN table_sizes tsize ON ts.schemaname = tsize.schemaname AND ts.tablename = tsize.tablename\nORDER BY tsize.total_size_bytes DESC;\n```\n\n**Look for:**\n\n- mostly insert-heavy patterns (less updates/deletes)\n- big tables (1M+ rows or 100MB+)\n\n#### Index patterns\n\n```sql\n-- Identify common query dimensions\nSELECT schemaname, tablename, indexname, indexdef\nFROM pg_indexes\nWHERE schemaname NOT IN ('information_schema', 'pg_catalog')\nORDER BY tablename, indexname;\n```\n\n**Look for:**\n\n- Multiple indexes with timestamp/created_at columns → time-based queries\n- Composite (entity_id, timestamp) indexes → good candidates\n- Time-only indexes → time range filtering common\n\n#### Query patterns (if pg_stat_statements available)\n\n```sql\n-- Check availability\nSELECT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_stat_statements');\n\n-- Analyze expensive queries for candidate tables\nSELECT query, calls, mean_exec_time, total_exec_time\nFROM pg_stat_statements\nWHERE query ILIKE '%your_table_name%'\nORDER BY total_exec_time DESC LIMIT 20;\n```\n\n**✅ Good patterns:** Time-based WHERE, entity filtering combined with time-based qualifiers, GROUP BY time_bucket, range queries over time\n**❌ Poor patterns:** Non-time lookups with no time-based qualifiers in same query (WHERE email = ...)\n\n#### Constraints\n\n```sql\n-- Check migration compatibility\nSELECT conname, contype, pg_get_constraintdef(oid) as definition\nFROM pg_constraint\nWHERE conrelid = 'your_table_name'::regclass;\n```\n\n**Compatibility:**\n\n- Primary keys (p): Must include partition column or ask user if can be modified\n- Foreign keys (f): Plain→Hypertable and Hypertable→Plain OK, Hypertable→Hypertable NOT supported\n- Unique constraints (u): Must include partition column or ask user if can be modified\n- Check constraints (c): Usually OK\n\n### Option B: From Code Analysis\n\n#### ✅ GOOD Patterns\n\n```python\n# Append-only logging\nINSERT INTO events (user_id, event_time, data) VALUES (...);\n# Time-series collection\nINSERT INTO metrics (device_id, timestamp, value) VALUES (...);\n# Time-based queries\nSELECT * FROM metrics WHERE timestamp >= NOW() - INTERVAL '24 hours';\n# Time aggregations\nSELECT DATE_TRUNC('day', timestamp), COUNT(*) GROUP BY 1;\n```\n\n#### ❌ POOR Patterns\n\n```python\n# Frequent updates to historical records\nUPDATE users SET email = ..., updated_at = NOW() WHERE id = ...;\n# Non-time lookups\nSELECT * FROM users WHERE email = ...;\n# Small reference tables\nSELECT * FROM countries ORDER BY name;\n```\n\n#### Schema Indicators\n\n**✅ GOOD:**\n\n- Has timestamp/timestamptz column\n- Multiple indexes with timestamp-based columns\n- Composite (entity_id, timestamp) indexes\n\n**❌ POOR:**\n\n- Mostly indexes with non-time-based columns (on columns like email, name, status, etc.)\n- Columns that you expect to be updated over time (updated_at, updated_by, status, etc.)\n- Unique constraints on non-time fields\n- Frequent updated_at modifications\n- Small static tables\n\n#### Special Case: ID-Based Tables\n\nSequential ID tables can be candidates if:\n\n- Insert-mostly pattern / updates are either infrequent or only on recent records.\n- If updates do happen, they occur on recent records (such as an order status being updated orderered->processing->delivered. Note once an order is delivered, it is unlikely to be updated again.)\n- IDs correlate with time (as is the case for serial/auto-incrementing IDs/GENERATED ALWAYS AS IDENTITY)\n- ID is the primary query dimension\n- Recent data accessed more often (frequently the case in ecommerce, finance, etc.)\n- Time-based reporting common (e.g. monthly, daily summaries/analytics)\n\n```sql\nCREATE TABLE orders (\n id BIGSERIAL PRIMARY KEY, -- Can partition by ID\n user_id BIGINT,\n created_at TIMESTAMPTZ DEFAULT NOW() -- For sparse indexes\n);\n```\n\nNote: For ID-based tables where there is also a time column (created_at, ordered_at, etc.),\nyou can partition by ID and use sparse indexes on the time column.\nSee the `migrate-postgres-tables-to-hypertables` skill for details.\n\n## Step 2: Candidacy Scoring (8+ points = good candidate)\n\n### Time-Series Characteristics (5+ points needed)\n\n- Has timestamp/timestamptz column: **3 points**\n- Data inserted chronologically: **2 points**\n- Queries filter by time: **2 points**\n- Time aggregations common: **2 points**\n\n### Scale & Performance (3+ points recommended)\n\n- Large table (1M+ rows or 100MB+): **2 points**\n- High insert volume: **1 point**\n- Infrequent updates to historical: **1 point**\n- Range queries common: **1 point**\n- Aggregation queries: **2 points**\n\n### Data Patterns (bonus)\n\n- Contains entity ID for segmentation (device_id, user_id, product_id, symbol, etc.): **1 point**\n- Numeric measurements: **1 point**\n- Log/event structure: **1 point**\n\n## Common Patterns\n\n### ✅ GOOD Candidates\n\n**✅ Event/Log Tables** (user_events, audit_logs)\n\n```sql\nCREATE TABLE user_events (\n id BIGSERIAL PRIMARY KEY,\n user_id BIGINT,\n event_type TEXT,\n event_time TIMESTAMPTZ DEFAULT NOW(),\n metadata JSONB\n);\n-- Partition by id, segment by user_id, enable minmax sparse_index on event_time\n```\n\n**✅ Sensor/IoT Data** (sensor_readings, telemetry)\n\n```sql\nCREATE TABLE sensor_readings (\n device_id TEXT,\n timestamp TIMESTAMPTZ,\n temperature DOUBLE PRECISION,\n humidity DOUBLE PRECISION\n);\n-- Partition by timestamp, segment by device_id, minmax sparse indexes on temperature and humidity\n```\n\n**✅ Financial/Trading** (stock_prices, transactions)\n\n```sql\nCREATE TABLE stock_prices (\n symbol VARCHAR(10),\n price_time TIMESTAMPTZ,\n open_price DECIMAL,\n close_price DECIMAL,\n volume BIGINT\n);\n-- Partition by price_time, segment by symbol, minmax sparse indexes on open_price and close_price and volume\n```\n\n**✅ System Metrics** (monitoring_data)\n\n```sql\nCREATE TABLE system_metrics (\n hostname TEXT,\n metric_time TIMESTAMPTZ,\n cpu_usage DOUBLE PRECISION,\n memory_usage BIGINT\n);\n-- Partition by metric_time, segment by hostname, minmax sparse indexes on cpu_usage and memory_usage\n```\n\n### ❌ POOR Candidates\n\n**❌ Reference Tables** (countries, categories)\n\n```sql\nCREATE TABLE countries (\n id SERIAL PRIMARY KEY,\n name VARCHAR(100),\n code CHAR(2)\n);\n-- Static data, no time component\n```\n\n**❌ User Profiles** (users, accounts)\n\n```sql\nCREATE TABLE users (\n id BIGSERIAL PRIMARY KEY,\n email VARCHAR(255),\n created_at TIMESTAMPTZ,\n updated_at TIMESTAMPTZ\n);\n-- Accessed by ID, frequently updated, has timestamp but it's not the primary query dimension (the primary query dimension is id or email)\n```\n\n**❌ Settings/Config** (user_settings)\n\n```sql\nCREATE TABLE user_settings (\n user_id BIGINT PRIMARY KEY,\n theme VARCHAR(20), -- Changes: light -> dark -> auto\n language VARCHAR(10), -- Changes: en -> es -> fr\n notifications JSONB, -- Frequent preference updates\n updated_at TIMESTAMPTZ\n);\n-- Accessed by user_id, frequently updated, has timestamp but it's not the primary query dimension (the primary query dimension is user_id)\n```\n\n## Analysis Output Requirements\n\nFor each candidate table provide:\n\n- **Score:** Based on criteria (8+ = strong candidate)\n- **Pattern:** Insert vs update ratio\n- **Access:** Time-based vs entity lookups\n- **Size:** Current size and growth rate\n- **Queries:** Time-range, aggregations, point lookups\n\nFocus on insert-heavy patterns with time-based or sequential access. Tables scoring 8+ points are strong candidates for conversion.","schemaVersion":1},"repoUrl":"https://github.com/timescale/pg-aiguide/tree/main/skills/find-hypertable-candidates","tags":["ai","ai-agents","ai-coding","claude-code-plugin","claude-code-plugins","claude-code-plugins-marketplace","claude-marketplace","claude-plugin","claude-skills","docs","documentation","mcp"],"stats":{"installVelocity7d":0,"retentionRate":0,"executions":0,"rating":null},"origin":"github","source":{"repo":"pg-aiguide","audit":{"files":["bun.lock","package.json"],"binaries":[],"findings":[],"packages":9,"auditedAt":"2026-09-25T11:52:20.587Z","lockfiles":["bun.lock"]},"forks":110,"owner":"timescale","stars":1847,"topics":["ai","ai-agents","ai-coding","claude-code-plugin","claude-code-plugins","claude-code-plugins-marketplace","claude-marketplace","claude-plugin","claude-skills","docs","documentation","mcp","mcp-server","postgres","postgresql","skills"],"license":"Apache-2.0","fullName":"timescale/pg-aiguide","homepage":null,"language":"Python","pushedAt":"2026-09-24T20:55:47Z","avatarUrl":"https://avatars.githubusercontent.com/u/8986001?v=4","crawledAt":"2026-09-25T11:52:15.055Z","openIssues":11,"manifestFile":"SKILL.md","manifestPath":"skills/find-hypertable-candidates/SKILL.md","defaultBranch":"main"},"readme":"# PostgreSQL Hypertable Candidate Analysis\n\nIdentify tables that would benefit from TimescaleDB hypertable conversion. After identification, use the companion \"migrate-postgres-tables-to-hypertables\" skill for configuration and migration.\n\n## TimescaleDB Benefits\n\n**Performance gains:** 90%+ compression, fast time-based queries, improved insert performance, efficient aggregations, continuous aggregates for materialization (dashboards, reports, analytics), automatic data management (retention, compression).\n\n**Best for insert-heavy patterns:**\n\n- Time-series data (sensors, metrics, monitoring)\n- Event logs (user events, audit trails, application logs)\n- Transaction records (orders, payments, financial)\n- Sequential data (auto-incrementing IDs with timestamps)\n- Append-only datasets (immutable records, historical)\n\n**Requirements:** Large volumes (1M+ rows), time-based queries, infrequent updates\n\n## Step 1: Database Schema Analysis\n\n### Option A: From Database Connection\n\n#### Table statistics and size\n\n```sql\n-- Get all tables with row counts and insert/update patterns\nWITH table_stats AS (\n SELECT\n schemaname, tablename,\n n_tup_ins as total_inserts,\n n_tup_upd as total_updates,\n n_tup_del as total_deletes,\n n_live_tup as live_rows,\n n_dead_tup as dead_rows\n FROM pg_stat_user_tables\n),\ntable_sizes AS (\n SELECT\n schemaname, tablename,\n pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,\n pg_total_relation_size(schemaname||'.'||tablename) as total_size_bytes\n FROM pg_tables\n WHERE schemaname NOT IN ('information_schema', 'pg_catalog')\n)\nSELECT\n ts.schemaname, ts.tablename, ts.live_rows,\n tsize.total_size, tsize.total_size_bytes,\n ts.total_inserts, ts.total_updates, ts.total_deletes,\n ROUND(CASE WHEN ts.live_rows > 0\n THEN (ts.total_inserts::float / ts.live_rows) * 100\n ELSE 0 END, 2) as insert_ratio_pct\nFROM table_stats ts\nJOIN table_sizes tsize ON ts.schemaname = tsize.schemaname AND ts.tablename = tsize.tablename\nORDER BY tsize.total_size_bytes DESC;\n```\n\n**Look for:**\n\n- mostly insert-heavy patterns (less updates/deletes)\n- big tables (1M+ rows or 100MB+)\n\n#### Index patterns\n\n```sql\n-- Identify common query dimensions\nSELECT schemaname, tablename, indexname, indexdef\nFROM pg_indexes\nWHERE schemaname NOT IN ('information_schema', 'pg_catalog')\nORDER BY tablename, indexname;\n```\n\n**Look for:**\n\n- Multiple indexes with timestamp/created_at columns → time-based queries\n- Composite (entity_id, timestamp) indexes → good candidates\n- Time-only indexes → time range filtering common\n\n#### Query patterns (if pg_stat_statements available)\n\n```sql\n-- Check availability\nSELECT EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_stat_statements');\n\n-- Analyze expensive queries for candidate tables\nSELECT query, calls, mean_exec_time, total_exec_time\nFROM pg_stat_statements\nWHERE query ILIKE '%your_table_name%'\nORDER BY total_exec_time DESC LIMIT 20;\n```\n\n**✅ Good patterns:** Time-based WHERE, entity filtering combined with time-based qualifiers, GROUP BY time_bucket, range queries over time\n**❌ Poor patterns:** Non-time lookups with no time-based qualifiers in same query (WHERE email = ...)\n\n#### Constraints\n\n```sql\n-- Check migration compatibility\nSELECT conname, contype, pg_get_constraintdef(oid) as definition\nFROM pg_constraint\nWHERE conrelid = 'your_table_name'::regclass;\n```\n\n**Compatibility:**\n\n- Primary keys (p): Must include partition column or ask user if can be modified\n- Foreign keys (f): Plain→Hypertable and Hypertable→Plain OK, Hypertable→Hypertable NOT supported\n- Unique constraints (u): Must include partition column or ask user if can be modified\n- Check constraints (c): Usually OK\n\n### Option B: From Code Analysis\n\n#### ✅ GOOD Patterns\n\n```python\n# Append-only logging\nINSERT INTO events (user_id, event_time, data) VALUES (...);\n# Time-series collection\nINSERT INTO metrics (device_id, ","createdAt":"2026-09-25T11:52:20.638Z","updatedAt":"2026-09-25T11:52:20.638Z"},{"id":"cmugwiawb01u2qu06jggobihw","slug":"timescale-pg-aiguide-migrate-postgres-tables-to-hypertables","name":"migrate-postgres-tables-to-hypertables","description":"Use this skill to migrate identified PostgreSQL tables to Timescale/TimescaleDB hypertables with optimal configuration and validation. **Trigger when user asks to:** - Migrate or convert PostgreSQL tables to hypertables - Execute hypertable migration with minimal downtime - Plan blue-green migration for large tables - Validate hypertable migration success - Configure compression after migration **Prerequisites:** Tables already identified as candidates (use find-hypertable-candidates first if needed) **Keywords:** migrate to hypertable, convert table, Timescale, TimescaleDB, blue-green migration, in-place conversion, create_hypertable, migration validation, compression setup Step-by-step migration planning including: partition column selection, chunk interval calculation, PK/constraint handling, migration execution (in-place vs blue-green), and performance validation queries.","authorId":"gh:timescale","authorName":"timescale","version":"0.1.0","category":"Prompt","securityLevel":"Community","downloadsCount":0,"githubStars":1847,"pricePerCall":0,"manifest":{"name":"migrate-postgres-tables-to-hypertables","tools":[],"category":"Prompt","entrypoint":{"type":"prompt"},"description":"Use this skill to migrate identified PostgreSQL tables to Timescale/TimescaleDB hypertables with optimal configuration and validation. **Trigger when user asks to:** - Migrate or convert PostgreSQL tables to hypertables - Execute hypertable migration with minimal downtime - Plan blue-green migration for large tables - Validate hypertable migration success - Configure compression after migration **Prerequisites:** Tables already identified as candidates (use find-hypertable-candidates first if needed) **Keywords:** migrate to hypertable, convert table, Timescale, TimescaleDB, blue-green migration, in-place conversion, create_hypertable, migration validation, compression setup Step-by-step migration planning including: partition column selection, chunk interval calculation, PK/constraint handling, migration execution (in-place vs blue-green), and performance validation queries.","permissions":[],"systemPrompt":"# PostgreSQL to TimescaleDB Hypertable Migration\n\nMigrate identified PostgreSQL tables to TimescaleDB hypertables with optimal configuration, migration planning and validation.\n\n**Prerequisites**: Tables already identified as hypertable candidates (use companion \"find-hypertable-candidates\" skill if needed).\n\n## Step 1: Optimal Configuration\n\n### Partition Column Selection\n\n```sql\n-- Find potential partition columns\nSELECT column_name, data_type, is_nullable\nFROM information_schema.columns\nWHERE table_name = 'your_table_name'\n AND data_type IN ('timestamp', 'timestamptz', 'bigint', 'integer', 'date')\nORDER BY ordinal_position;\n```\n\n**Requirements:** Time-based (TIMESTAMP/TIMESTAMPTZ/DATE) or sequential integer (INT/BIGINT)\n\nShould represent when the event actually occurred or sequential ordering.\n\n**Common choices:**\n\n- `timestamp`, `created_at`, `event_time` - when event occurred\n- `id`, `sequence_number` - auto-increment (for sequential data without timestamps)\n- `ingested_at` - less ideal, only if primary query dimension\n- `updated_at` - AVOID (records updated out of order, breaks chunk distribution) unless primary query dimension\n\n#### Special Case: table with BOTH ID AND Timestamp\n\nWhen table has sequential ID (PK) AND timestamp that correlate:\n\n```sql\n-- Partition by ID, enable minmax sparse indexes on timestamp\nSELECT create_hypertable('orders', 'id', chunk_time_interval => 1000000);\nALTER TABLE orders SET (\n timescaledb.sparse_index = 'minmax(created_at),...'\n);\n```\n\nSparse indexes on time column enable skipping compressed blocks outside queried time ranges.\n\nUse when: ID correlates with time (newer records have higher IDs), need ID-based lookups, time queries also common\n\n### Chunk Interval Selection\n\n```sql\n-- Ensure statistics are current\nANALYZE your_table_name;\n\n-- Estimate index size per time unit\nWITH time_range AS (\n SELECT\n MIN(timestamp_column) as min_time,\n MAX(timestamp_column) as max_time,\n EXTRACT(EPOCH FROM (MAX(timestamp_column) - MIN(timestamp_column)))/3600 as total_hours\n FROM your_table_name\n),\ntotal_index_size AS (\n SELECT SUM(pg_relation_size(indexname::regclass)) as total_index_bytes\n FROM pg_stat_user_indexes\n WHERE schemaname||'.'||tablename = 'your_schema.your_table_name'\n)\nSELECT\n pg_size_pretty(tis.total_index_bytes / tr.total_hours) as index_size_per_hour\nFROM time_range tr, total_index_size tis;\n```\n\n**Target:** Indexes of recent chunks < 25% of RAM\n**Default:** IMPORTANT: Keep default of 7 days if unsure\n**Range:** 1 hour minimum, 30 days maximum\n\n**Example:** 32GB RAM → target 8GB for recent indexes. If index_size_per_hour = 200MB:\n\n- 1 hour chunks: 200MB chunk index size × 40 recent = 8GB ✓\n- 6 hour chunks: 1.2GB chunk index size × 7 recent = 8.4GB ✓\n- 1 day chunks: 4.8GB chunk index size × 2 recent = 9.6GB ⚠️\n Choose largest interval keeping 2+ recent chunk indexes under target.\n\n### Primary Key/ Unique Constraints Compatibility\n\n```sql\n-- Check existing primary key/ unique constraints\nSELECT conname, pg_get_constraintdef(oid) as definition\nFROM pg_constraint\nWHERE conrelid = 'your_table_name'::regclass AND contype = 'p' OR contype = 'u';\n```\n\n**Rules:** PK/UNIQUE must include partition column\n\n**Actions:**\n\n1. **No PK/UNIQUE:** No changes needed\n2. **PK/UNIQUE includes partition column:** No changes needed\n3. **PK/UNIQUE excludes partition column:** ⚠️ **ASK USER PERMISSION** to modify PK/UNIQUE\n\n**Example: user prompt if needed:**\n\n> \"Primary key (id) doesn't include partition column (timestamp). Must modify to PRIMARY KEY (id, timestamp) to convert to hypertable. This may break application code. Is this acceptable?\"\n> \"Unique constraint (id) doesn't include partition column (timestamp). Must modify to UNIQUE (id, timestamp) to convert to hypertable. This may break application code. Is this acceptable?\"\n\nIf the user accepts, modify the constraint:\n\n```sql\nBEGIN;\nALTER TABLE your_table_name DROP CONSTRAINT existing_pk_name;\nALTER TABLE your_table_name ADD PRIMARY KEY (existing_columns, partition_column);\nCOMMIT;\n```\n\nIf the user does not accept, you should NOT migrate the table.\n\nIMPORTANT: DO NOT modify the primary key/unique constraint without user permission.\n\n### Compression Configuration\n\nFor detailed segment_by and order_by selection, see \"setup-timescaledb-hypertables\" skill. Quick reference:\n\n**segment_by:** Most common WHERE filter with >100 rows per value per chunk\n\n- IoT: `device_id`\n- Finance: `symbol`\n- Analytics: `user_id` or `session_id`\n\n```sql\n-- Analyze cardinality for segment_by selection\nSELECT column_name, COUNT(DISTINCT column_name) as unique_values,\n ROUND(COUNT(*)::float / COUNT(DISTINCT column_name), 2) as avg_rows_per_value\nFROM your_table_name GROUP BY column_name;\n```\n\n**order_by:** Usually `timestamp DESC`. The (segment_by, order_by) combination should form a natural time-series progression.\n\n- If column has <100 rows/chunk (too low for segment_by), prepend to order_by: `order_by='low_density_col, timestamp DESC'`\n\n**sparse indexes:** add minmax on the columns that are used in the WHERE clauses but are not in the segment_by or order_by. Use minmax for columns used in range queries.\n\n```sql\nALTER TABLE your_table_name SET (\n timescaledb.enable_columnstore,\n timescaledb.segmentby = 'entity_id',\n timescaledb.orderby = 'timestamp DESC'\n timescaledb.sparse_index = 'minmax(value_1),...'\n);\n\n-- Compress after data unlikely to change (adjust `after` parameter based on update patterns)\nCALL add_columnstore_policy('your_table_name', after => INTERVAL '7 days');\n```\n\n## Step 2: Migration Planning\n\n### Pre-Migration Checklist\n\n- [ ] Partition column selected\n- [ ] Chunk interval calculated (or using default)\n- [ ] PK includes partition column OR user approved modification\n- [ ] No Hypertable→Hypertable foreign keys\n- [ ] Unique constraints include partition column\n- [ ] Created compression configuration (segment_by, order_by, sparse indexes, compression policy)\n- [ ] Maintenance window scheduled / backup created.\n\n### Migration Options\n\n#### Option 1: In-Place (Tables < 1GB)\n\n```sql\n-- Enable extension\nCREATE EXTENSION IF NOT EXISTS timescaledb;\n\n-- Convert to hypertable (locks table)\nSELECT create_hypertable(\n 'your_table_name',\n 'timestamp_column',\n chunk_time_interval => INTERVAL '7 days',\n if_not_exists => TRUE\n);\n\n-- Configure compression\nALTER TABLE your_table_name SET (\n timescaledb.enable_columnstore,\n timescaledb.segmentby = 'entity_id',\n timescaledb.orderby = 'timestamp DESC',\n timescaledb.sparse_index = 'minmax(value_1),...'\n);\n\n-- Adjust `after` parameter based on update patterns\nCALL add_columnstore_policy('your_table_name', after => INTERVAL '7 days');\n```\n\n#### Option 2: Blue-Green (Tables > 1GB)\n\n```sql\n-- 1. Create new hypertable\nCREATE TABLE your_table_name_new (LIKE your_table_name INCLUDING ALL);\n\n-- 2. Convert to hypertable\nSELECT create_hypertable('your_table_name_new', 'timestamp_column');\n\n-- 3. Configure compression\nALTER TABLE your_table_name_new SET (\n timescaledb.enable_columnstore,\n timescaledb.segmentby = 'entity_id',\n timescaledb.orderby = 'timestamp DESC'\n);\n\n-- 4. Migrate data in batches\nINSERT INTO your_table_name_new\nSELECT * FROM your_table_name\nWHERE timestamp_column >= '2024-01-01' AND timestamp_column < '2024-02-01';\n-- Repeat for each time range\n\n-- 4. Enter maintenance window and do the following:\n\n-- 5. Pause modification of the old table.\n\n-- 6. Copy over the most recent data from the old table to the new table.\n\n-- 7. Swap tables\nBEGIN;\nALTER TABLE your_table_name RENAME TO your_table_name_old;\nALTER TABLE your_table_name_new RENAME TO your_table_name;\nCOMMIT;\n\n-- 8. Exit maintenance window.\n\n-- 9. (sometime much later) Drop old table after validation\n-- DROP TABLE your_table_name_old;\n```\n\n### Common Issues\n\n#### Foreign Keys\n\n```sql\n-- Check foreign keys\nSELECT conname, confrelid::regclass as referenced_table\nFROM pg_constraint\nWHERE (conrelid = 'your_table_name'::regclass\n OR confrelid = 'your_table_name'::regclass)\n AND contype = 'f';\n```\n\n**Supported:** Plain→Hypertable, Hypertable→Plain\n**NOT supported:** Hypertable→Hypertable\n\n⚠️ **CRITICAL:** Hypertable→Hypertable FKs must be dropped (enforce in application). **ASK USER PERMISSION**. If no, **STOP MIGRATION**.\n\n#### Large Table Migration Time\n\n```sql\n-- Rough estimate: ~75k rows/second\nSELECT\n pg_size_pretty(pg_total_relation_size(tablename)) as size,\n n_live_tup as rows,\n ROUND(n_live_tup / 75000.0 / 60, 1) as estimated_minutes\nFROM pg_stat_user_tables\nWHERE tablename = 'your_table_name';\n```\n\n**Solutions for large tables (>1GB/10M rows):** Use blue-green migration, migrate during off-peak, test on subset first\n\n## Step 3: Performance Validation\n\n### Chunk & Compression Analysis\n\n```sql\n-- View chunks and compression\nSELECT\n chunk_name,\n pg_size_pretty(total_bytes) as size,\n pg_size_pretty(compressed_total_bytes) as compressed_size,\n ROUND((total_bytes - compressed_total_bytes::numeric) / total_bytes * 100, 1) as compression_pct,\n range_start,\n range_end\nFROM timescaledb_information.chunks\nWHERE hypertable_name = 'your_table_name'\nORDER BY range_start DESC;\n```\n\n**Look for:**\n\n- Consistent chunk sizes (within 2x)\n- Compression >90% for time-series\n- Recent chunks uncompressed\n- Chunk indexes < 25% RAM\n\n### Query Performance Tests\n\n```sql\n-- 1. Time-range query (should show chunk exclusion)\nEXPLAIN (ANALYZE, BUFFERS)\nSELECT COUNT(*), AVG(value)\nFROM your_table_name\nWHERE timestamp >= NOW() - INTERVAL '1 day';\n\n-- 2. Entity + time query (benefits from segment_by)\nEXPLAIN (ANALYZE, BUFFERS)\nSELECT * FROM your_table_name\nWHERE entity_id = 'X' AND timestamp >= NOW() - INTERVAL '1 week';\n\n-- 3. Aggregation (benefits from columnstore)\nEXPLAIN (ANALYZE, BUFFERS)\nSELECT DATE_TRUNC('hour', timestamp), entity_id, COUNT(*), AVG(value)\nFROM your_table_name\nWHERE timestamp >= NOW() - INTERVAL '1 month'\nGROUP BY 1, 2;\n```\n\n**✅ Good signs:**\n\n- \"Chunks excluded during startup: X\" in EXPLAIN plan\n- \"Custom Scan (ColumnarScan)\" for compressed data\n- Lower \"Buffers: shared read\" in EXPLAIN ANALYZE plan than pre-migration\n- Faster execution times\n\n**❌ Bad signs:**\n\n- \"Seq Scan\" on large chunks\n- No chunk exclusion messages\n- Slower than before migration\n\n### Storage Metrics\n\n```sql\n-- Monitor compression effectiveness\nSELECT\n hypertable_name,\n pg_size_pretty(total_bytes) as total_size,\n pg_size_pretty(compressed_total_bytes) as compressed_size,\n ROUND(compressed_total_bytes::numeric / total_bytes * 100, 1) as compressed_pct_of_total,\n ROUND((uncompressed_total_bytes - compressed_total_bytes::numeric) /\n uncompressed_total_bytes * 100, 1) as compression_ratio_pct\nFROM timescaledb_information.hypertables\nWHERE hypertable_name = 'your_table_name';\n```\n\n**Monitor:**\n\n- compression_ratio_pct >90% (typical time-series)\n- compressed_pct_of_total growing as data ages\n- Size growth slowing significantly vs pre-hypertable\n- Decreasing compression_ratio_pct = poor segment_by\n\n### Troubleshooting\n\n#### Poor Chunk Exclusion\n\n```sql\n-- Verify chunks are being excluded\nEXPLAIN (ANALYZE, BUFFERS)\nSELECT * FROM your_table_name\nWHERE timestamp >= '2024-01-01' AND timestamp < '2024-01-02';\n-- Look for \"Chunks excluded during startup: X\"\n```\n\n#### Poor Compression\n\n```sql\n-- Get newest compressed chunk name\nSELECT chunk_name FROM timescaledb_information.chunks\nWHERE hypertable_name = 'your_table_name'\n AND compressed_total_bytes IS NOT NULL\nORDER BY range_start DESC LIMIT 1;\n\n-- Analyze segment distribution\nSELECT segment_by_column, COUNT(*) as rows_per_segment\nFROM _timescaledb_internal._hyper_X_Y_chunk -- Use actual chunk name\nGROUP BY 1 ORDER BY 2 DESC;\n```\n\n**Look for:** <20 rows per segment: Poor segment_by choice (should be >100) => Low compression potential.\n\n#### Poor insert performance\n\nCheck that you don't have too many indexes. Unused indexes hurt insert performance and should be dropped.\n\n```sql\nSELECT\n schemaname,\n tablename,\n indexname,\n idx_tup_read,\n idx_tup_fetch,\n idx_scan\nFROM pg_stat_user_indexes\nWHERE tablename LIKE '%your_table_name%'\nORDER BY idx_scan DESC;\n```\n\n**Look for:** Unused indexes via a low idx_scan value. Drop such indexes (but ask user permission).\n\n### Ongoing Monitoring\n\n```sql\n-- Monitor chunk compression status\nCREATE OR REPLACE VIEW hypertable_compression_status AS\nSELECT\n h.hypertable_name,\n COUNT(c.chunk_name) as total_chunks,\n COUNT(c.chunk_name) FILTER (WHERE c.compressed_total_bytes IS NOT NULL) as compressed_chunks,\n ROUND(\n COUNT(c.chunk_name) FILTER (WHERE c.compressed_total_bytes IS NOT NULL)::numeric /\n COUNT(c.chunk_name) * 100, 1\n ) as compression_coverage_pct,\n pg_size_pretty(SUM(c.total_bytes)) as total_size,\n pg_size_pretty(SUM(c.compressed_total_bytes)) as compressed_size\nFROM timescaledb_information.hypertables h\nLEFT JOIN timescaledb_information.chunks c ON h.hypertable_name = c.hypertable_name\nGROUP BY h.hypertable_name;\n\n-- Query this view regularly to monitor compression progress\nSELECT * FROM hypertable_compression_status\nWHERE hypertable_name = 'your_table_name';\n```\n\n**Look for:**\n\n- compression_coverage_pct should increase over time as data ages and gets compressed.\n- total_chunks should not grow too quickly (more than 10000 becomes a problem).\n- You should not see unexpected spikes in total_size or compressed_size.\n\n## Success Criteria\n\n**✅ Migration successful when:**\n\n- All queries return correct results\n- Query performance equal or better\n- Compression >90% for older data\n- Chunk exclusion working for time queries\n- Insert performance acceptable\n\n**❌ Investigate if:**\n\n- Query performance >20% worse\n- Compression <80%\n- No chunk exclusion\n- Insert performance degraded\n- Increased error rates\n\nFocus on high-volume, insert-heavy workloads with time-based access patterns for best ROI.","schemaVersion":1},"repoUrl":"https://github.com/timescale/pg-aiguide/tree/main/skills/migrate-postgres-tables-to-hypertables","tags":["ai","ai-agents","ai-coding","claude-code-plugin","claude-code-plugins","claude-code-plugins-marketplace","claude-marketplace","claude-plugin","claude-skills","docs","documentation","mcp"],"stats":{"installVelocity7d":0,"retentionRate":0,"executions":0,"rating":null},"origin":"github","source":{"repo":"pg-aiguide","audit":{"files":["bun.lock","package.json"],"binaries":[],"findings":[],"packages":9,"auditedAt":"2026-09-25T11:52:20.587Z","lockfiles":["bun.lock"]},"forks":110,"owner":"timescale","stars":1847,"topics":["ai","ai-agents","ai-coding","claude-code-plugin","claude-code-plugins","claude-code-plugins-marketplace","claude-marketplace","claude-plugin","claude-skills","docs","documentation","mcp","mcp-server","postgres","postgresql","skills"],"license":"Apache-2.0","fullName":"timescale/pg-aiguide","homepage":null,"language":"Python","pushedAt":"2026-09-24T20:55:47Z","avatarUrl":"https://avatars.githubusercontent.com/u/8986001?v=4","crawledAt":"2026-09-25T11:52:15.055Z","openIssues":11,"manifestFile":"SKILL.md","manifestPath":"skills/migrate-postgres-tables-to-hypertables/SKILL.md","defaultBranch":"main"},"readme":"# PostgreSQL to TimescaleDB Hypertable Migration\n\nMigrate identified PostgreSQL tables to TimescaleDB hypertables with optimal configuration, migration planning and validation.\n\n**Prerequisites**: Tables already identified as hypertable candidates (use companion \"find-hypertable-candidates\" skill if needed).\n\n## Step 1: Optimal Configuration\n\n### Partition Column Selection\n\n```sql\n-- Find potential partition columns\nSELECT column_name, data_type, is_nullable\nFROM information_schema.columns\nWHERE table_name = 'your_table_name'\n AND data_type IN ('timestamp', 'timestamptz', 'bigint', 'integer', 'date')\nORDER BY ordinal_position;\n```\n\n**Requirements:** Time-based (TIMESTAMP/TIMESTAMPTZ/DATE) or sequential integer (INT/BIGINT)\n\nShould represent when the event actually occurred or sequential ordering.\n\n**Common choices:**\n\n- `timestamp`, `created_at`, `event_time` - when event occurred\n- `id`, `sequence_number` - auto-increment (for sequential data without timestamps)\n- `ingested_at` - less ideal, only if primary query dimension\n- `updated_at` - AVOID (records updated out of order, breaks chunk distribution) unless primary query dimension\n\n#### Special Case: table with BOTH ID AND Timestamp\n\nWhen table has sequential ID (PK) AND timestamp that correlate:\n\n```sql\n-- Partition by ID, enable minmax sparse indexes on timestamp\nSELECT create_hypertable('orders', 'id', chunk_time_interval => 1000000);\nALTER TABLE orders SET (\n timescaledb.sparse_index = 'minmax(created_at),...'\n);\n```\n\nSparse indexes on time column enable skipping compressed blocks outside queried time ranges.\n\nUse when: ID correlates with time (newer records have higher IDs), need ID-based lookups, time queries also common\n\n### Chunk Interval Selection\n\n```sql\n-- Ensure statistics are current\nANALYZE your_table_name;\n\n-- Estimate index size per time unit\nWITH time_range AS (\n SELECT\n MIN(timestamp_column) as min_time,\n MAX(timestamp_column) as max_time,\n EXTRACT(EPOCH FROM (MAX(timestamp_column) - MIN(timestamp_column)))/3600 as total_hours\n FROM your_table_name\n),\ntotal_index_size AS (\n SELECT SUM(pg_relation_size(indexname::regclass)) as total_index_bytes\n FROM pg_stat_user_indexes\n WHERE schemaname||'.'||tablename = 'your_schema.your_table_name'\n)\nSELECT\n pg_size_pretty(tis.total_index_bytes / tr.total_hours) as index_size_per_hour\nFROM time_range tr, total_index_size tis;\n```\n\n**Target:** Indexes of recent chunks < 25% of RAM\n**Default:** IMPORTANT: Keep default of 7 days if unsure\n**Range:** 1 hour minimum, 30 days maximum\n\n**Example:** 32GB RAM → target 8GB for recent indexes. If index_size_per_hour = 200MB:\n\n- 1 hour chunks: 200MB chunk index size × 40 recent = 8GB ✓\n- 6 hour chunks: 1.2GB chunk index size × 7 recent = 8.4GB ✓\n- 1 day chunks: 4.8GB chunk index size × 2 recent = 9.6GB ⚠️\n Choose largest interval keeping 2+ recent chunk indexes under target.\n\n### Primary Key/ Unique Constraints Compatibility\n\n```sql\n-- Check existing primary key/ unique constraints\nSELECT conname, pg_get_constraintdef(oid) as definition\nFROM pg_constraint\nWHERE conrelid = 'your_table_name'::regclass AND contype = 'p' OR contype = 'u';\n```\n\n**Rules:** PK/UNIQUE must include partition column\n\n**Actions:**\n\n1. **No PK/UNIQUE:** No changes needed\n2. **PK/UNIQUE includes partition column:** No changes needed\n3. **PK/UNIQUE excludes partition column:** ⚠️ **ASK USER PERMISSION** to modify PK/UNIQUE\n\n**Example: user prompt if needed:**\n\n> \"Primary key (id) doesn't include partition column (timestamp). Must modify to PRIMARY KEY (id, timestamp) to convert to hypertable. This may break application code. Is this acceptable?\"\n> \"Unique constraint (id) doesn't include partition column (timestamp). Must modify to UNIQUE (id, timestamp) to convert to hypertable. This may break application code. Is this acceptable?\"\n\nIf the user accepts, modify the constraint:\n\n```sql\nBEGIN;\nALTER TABLE your_table_name DROP CONSTRAINT existing_pk_name;\nALTER TABLE your_","createdAt":"2026-09-25T11:52:20.651Z","updatedAt":"2026-09-25T11:52:20.651Z"},{"id":"cmugwiawn01u5qu064jy9m2lz","slug":"timescale-pg-aiguide-pgvector-semantic-search","name":"pgvector-semantic-search","description":"Use this skill for setting up vector similarity search with pgvector for AI/ML embeddings, RAG applications, or semantic search. **Trigger when user asks to:** - Store or search vector embeddings in PostgreSQL - Set up semantic search, similarity search, or nearest neighbor search - Create HNSW or IVFFlat indexes for vectors - Implement RAG (Retrieval Augmented Generation) with PostgreSQL - Optimize pgvector performance, recall, or memory usage - Use binary quantization for large vector datasets **Keywords:** pgvector, embeddings, semantic search, vector similarity, HNSW, IVFFlat, halfvec, cosine distance, nearest neighbor, RAG, LLM, AI search Covers: halfvec storage, HNSW index configuration (m, ef_construction, ef_search), quantization strategies, filtered search, bulk loading, and performance tuning.","authorId":"gh:timescale","authorName":"timescale","version":"0.1.0","category":"Prompt","securityLevel":"Community","downloadsCount":0,"githubStars":1847,"pricePerCall":0,"manifest":{"name":"pgvector-semantic-search","tools":[],"category":"Prompt","entrypoint":{"type":"prompt"},"description":"Use this skill for setting up vector similarity search with pgvector for AI/ML embeddings, RAG applications, or semantic search. **Trigger when user asks to:** - Store or search vector embeddings in PostgreSQL - Set up semantic search, similarity search, or nearest neighbor search - Create HNSW or IVFFlat indexes for vectors - Implement RAG (Retrieval Augmented Generation) with PostgreSQL - Optimize pgvector performance, recall, or memory usage - Use binary quantization for large vector datasets **Keywords:** pgvector, embeddings, semantic search, vector similarity, HNSW, IVFFlat, halfvec, cosine distance, nearest neighbor, RAG, LLM, AI search Covers: halfvec storage, HNSW index configuration (m, ef_construction, ef_search), quantization strategies, filtered search, bulk loading, and performance tuning.","permissions":[],"systemPrompt":"# pgvector for Semantic Search\n\nSemantic search finds content by meaning rather than exact keywords. An embedding model converts text into high-dimensional vectors, where similar meanings map to nearby points. pgvector stores these vectors in PostgreSQL and uses approximate nearest neighbor (ANN) indexes to find the closest matches quickly—scaling to millions of rows without leaving the database. Store your text alongside its embedding, then query by converting your search text to a vector and returning the rows with the smallest distance.\n\nThis guide covers pgvector setup and tuning—not embedding model selection or text chunking, which significantly affect search quality. Requires pgvector 0.8.0+ for all features (`halfvec`, `binary_quantize`, iterative scan).\n\n## Golden Path (Default Setup)\n\nUse this configuration unless you have a specific reason not to.\n- Embedding column data type: `halfvec(N)` where `N` is your embedding dimension (must match everywhere). Examples use 1536; replace with your dimension `N`.\n- Distance: cosine (`<=>`)\n- Index: HNSW (`m = 16`, `ef_construction = 64`). Use `halfvec_cosine_ops` and query with `<=>`.\n- Query-time recall: `SET hnsw.ef_search = 100` (good starting point from published benchmarks, increase for higher recall at higher latency)\n- Query pattern: `ORDER BY embedding <=> $1::halfvec(N) LIMIT k`\n\nThis setup provides a strong speed–recall tradeoff for most text-embedding workloads.\n\n## Core Rules\n\n- **Enable the extension** in each database: `CREATE EXTENSION IF NOT EXISTS vector;`\n- **Use HNSW indexes by default**—superior speed-recall tradeoff, can be created on empty tables, no training step required. Only consider IVFFlat for write-heavy or memory-bound workloads.\n- **Use `halfvec` by default**—store and index as `halfvec` for 50% smaller storage and indexes with minimal recall loss.\n- **Index after bulk loading** initial data for best build performance.\n- **Create indexes concurrently** in production: `CREATE INDEX CONCURRENTLY ...`\n- **Use cosine distance by default** (`<=>`): For non-normalized embeddings, use cosine. For unit-normalized embeddings, cosine and inner product yield identical rankings; default to cosine.\n- **Match query operator to index ops**: Index with `halfvec_cosine_ops` requires `<=>` in queries; `halfvec_l2_ops` requires `<->`; mismatched operators won't use the index.\n- **Always cast query vectors explicitly** (`$1::halfvec(N)`) to avoid implicit-cast failures in prepared statements.\n- **Always use the same embedding model for data and queries**. Similarity search only works when the model generating the vectors is the same.\n\n## Type Rules\n\n- Store embeddings as `halfvec(N)`\n- Cast query vectors to `halfvec(N)`\n- Store binary quantized vectors as `bit(N)` in a generated column\n- Do not mix `vector` / `halfvec` / `bit` without explicit casts\n- Never call `binary_quantize()` on table columns inside `ORDER BY`; store it instead\n- Dimensions must match: a `halfvec(1536)` column requires query vectors cast as `::halfvec(1536)`.\n\n## Standard Pattern\n\n```sql\n-- Store and index as halfvec\nCREATE TABLE items (\n id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n contents TEXT NOT NULL,\n embedding halfvec(1536) NOT NULL -- NOT NULL requires embeddings generated before insert, not async\n);\nCREATE INDEX ON items USING hnsw (embedding halfvec_cosine_ops);\n\n-- Query: returns 10 closest items. $1 is the embedding of your search text.\nSELECT id, contents FROM items ORDER BY embedding <=> $1::halfvec(1536) LIMIT 10;\n```\n\nFor other distance operators (L2, inner product, etc.), see the [pgvector README](https://github.com/pgvector/pgvector).\n\n## HNSW Index\n\nThe recommended index type. Creates a multilayer navigable graph with superior speed-recall tradeoff. Can be created on empty tables (no training step required).\n\n```sql\nCREATE INDEX ON items USING hnsw (embedding halfvec_cosine_ops);\n\n-- With tuning parameters\nCREATE INDEX ON items USING hnsw (embedding halfvec_cosine_ops) WITH (m = 16, ef_construction = 64);\n```\n\n### HNSW Parameters\n\n| Parameter | Default | Description |\n|-----------|---------|-------------|\n| `m` | 16 | Max connections per layer. Higher = better recall, more memory |\n| `ef_construction` | 64 | Build-time candidate list. Higher = better graph quality, slower build |\n| `hnsw.ef_search` | 40 | Query-time candidate list. Higher = better recall, slower queries. Should be ≥ LIMIT. |\n\n**ef_search tuning (rough guidelines—actual results vary by dataset):**\n\n| ef_search | Approx Recall | Relative Speed |\n|-----------|---------------|----------------|\n| 40 | lower (~95% on some benchmarks) | 1x (baseline) |\n| 100 | higher | ~2x slower |\n| 200 | very-high | ~4x slower |\n| 400 | near-exact | ~8x slower |\n\n```sql\n-- Set search parameter for session\nSET hnsw.ef_search = 100;\n\n-- Set for single query\nBEGIN;\nSET LOCAL hnsw.ef_search = 100;\nSELECT id, contents FROM items ORDER BY embedding <=> $1::halfvec(1536) LIMIT 10;\nCOMMIT;\n```\n\n## IVFFlat Index (Generally Not Recommended)\n\nDefault to HNSW. Use IVFFlat only when HNSW’s operational costs matter more than peak recall.\n\nChoose IVFFlat if:\n- Write-heavy or constantly changing data AND you're willing to rebuild the index frequently\n- You rebuild indexes often and want predictable build time and memory usage\n- Memory is tight and you cannot keep an HNSW graph mostly resident\n- Data is partitioned or tiered, and this index lives on colder partitions\n\nAvoid IVFFlat if you need:\n- highest recall at low latency\n- minimal tuning\n- a “set and forget” index\n\nNotes:\n- IVFFlat requires data to exist before index creation.\n- Recall depends on `lists` and `ivfflat.probes`; higher probes = better recall, slower queries.\n\nStarter config:\n```sql\nCREATE INDEX ON items\nUSING ivfflat (embedding halfvec_cosine_ops)\nWITH (lists = 1000);\n\nSET ivfflat.probes = 10;\n```\n\n## Quantization Strategies\n\n- Quantization is a memory decision, not a recall decision.\n- Use `halfvec` by default for storage and indexing.\n- Estimate HNSW index footprint as ~4–6 KB per 1536-dim `halfvec` (m=16) (order-of-magnitude); 3072-dim is ~2×; m=32 roughly doubles HNSW link/graph overhead.\n- If p95/p99 latency rises while CPU is mostly idle, the HNSW index is likely no longer resident in memory.\n- If `halfvec` doesn’t fit, use binary quantization + re-ranking.\n\n### Guidelines for 1536-dim vectors\n\nApproximate `halfvec` capacity at `m=16`, 1536-dim (assumes RAM mostly available for index caching):\n\n| RAM | Approx max halfvec vectors |\n|-----|----------------------------|\n| 16 GB | ~2–3M vectors |\n| 32 GB | ~4–6M vectors |\n| 64 GB | ~8–12M vectors |\n| 128 GB | ~16–25M vectors |\n\nFor 3072-dim embeddings, divide these numbers by ~2. \nFor `m=32`, also divide capacity by ~2.\n\nIf the index cannot fit in memory at this scale, use binary quantization.\n\nThese are ranges, not guarantees. Validate by monitoring cache residency and p95/p99 latency under load.\n\n### Binary Quantization (For Very Large Datasets)\n\n32× memory reduction. Use with re-ranking for acceptable recall.\n\n```sql\n-- Table with generated column for binary quantization\nCREATE TABLE items (\n id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n contents TEXT NOT NULL,\n embedding halfvec(1536) NOT NULL,\n embedding_bq bit(1536) GENERATED ALWAYS AS (binary_quantize(embedding)::bit(1536)) STORED\n);\n\nCREATE INDEX ON items USING hnsw (embedding_bq bit_hamming_ops);\n\n-- Query with re-ranking for better recall\n-- ef_search must be >= inner LIMIT to retrieve enough candidates\nSET hnsw.ef_search = 800;\nWITH q AS (\n SELECT binary_quantize($1::halfvec(1536))::bit(1536) AS qb\n)\nSELECT *\nFROM (\n SELECT i.id, i.contents, i.embedding\n FROM items i, q\n ORDER BY i.embedding_bq <~> q.qb -- computes binary distance using index\n LIMIT 800\n) candidates\nORDER BY candidates.embedding <=> $1::halfvec(1536) -- computes halfvec distance (no index), more accurate than binary\nLIMIT 10;\n```\n\nThe 80× oversampling ratio (800 candidates for 10 results) is a reasonable starting point. Binary quantization loses precision, so more candidates are needed to find true nearest neighbors during re-ranking. Increase if recall is insufficient; decrease if re-ranking latency is too high.\n\n## Performance by Dataset Size\n\n| Scale | Vectors | Config | Notes |\n|-------|---------|--------|-------|\n| Small | <100K | Defaults | Index optional but improves tail latency |\n| Medium | 100K–5M | Defaults | Monitor p95 latency; most common production range |\n| Large | 5M+ | `ef_construction=100+` | Memory residency critical |\n| Very Large | 10M+ | Binary quantization + re-ranking | Add RAM or partition first if possible |\n\nTune `ef_search` first for recall; only increase `m` if recall plateaus and memory allows. Under concurrency, tail latency spikes when the index doesn't fit in memory. Binary quantization is an escape hatch—prefer adding RAM or partitioning first.\n\n## Filtering Best Practices\n\nFiltered vector search requires care. Depending on filter selectivity and query shape, filters can cause early termination (too few rows, missing results) or increase work (latency).\n\n### Iterative scan (recommended when filters are selective)\n\nBy default, HNSW may stop early when a WHERE clause is present, which can lead to fewer results than expected. Iterative scan allows HNSW to continue searching until enough filtered rows are found.\n\nEnable iterative scan when filters materially reduce the result set.\n\n```sql\n-- Enable iterative scans for filtered queries\nSET hnsw.iterative_scan = relaxed_order;\n\nSELECT id, contents\nFROM items\nWHERE category_id = 123\nORDER BY embedding <=> $1::halfvec(1536)\nLIMIT 10;\n```\n\nIf results are still sparse, increase the scan budget:\n\n```sql\nSET hnsw.max_scan_tuples = 50000;\n```\n\nTrade-off: increasing `hnsw.max_scan_tuples` improves recall but can significantly increase latency.\n\n**When iterative scan is not needed:**\n- The filter matches a large portion of the table (low selectivity)\n- You are prefiltering via a B-tree index\n- You are querying a single partition or partial index\n\n### Choose the right filtering strategy\n\n**Highly selective filters (under ~10k rows)**\nUse a B-tree index on the filter column so Postgres can prefilter before ANN.\n\n```sql\nCREATE INDEX ON items (category_id);\n```\n\n**Low-cardinality filters (few distinct values)**\nUse partial HNSW indexes per filter value.\n\n```sql\nCREATE INDEX ON items\nUSING hnsw (embedding halfvec_cosine_ops)\nWHERE category_id = 11;\n```\n\n**Many filter values or large datasets**\nPartition by the filter key to keep each ANN index small.\n\n```sql\nCREATE TABLE items (\n embedding halfvec(1536),\n category_id int\n) PARTITION BY LIST (category_id);\n```\n\n### Key rules\n\n- Filters that match few rows require prefiltering, partitioning, or iterative scan.\n- Always validate filtered queries by measuring p95/p99 latency and tuples visited under realistic load.\n\n### Alternative: pgvectorscale for label-based filtering\n\nFor large datasets with label-based filters, [pgvectorscale](https://github.com/timescale/pgvectorscale)'s StreamingDiskANN index supports filtered indexes on `smallint[]` columns. Labels are indexed alongside vectors, enabling efficient filtered search without the accuracy tradeoffs of HNSW post-filtering. See the pgvectorscale documentation for setup details.\n\n## Bulk Loading\n\n```sql\n-- COPY is fastest; binary format is faster but requires proper encoding\n-- Text format: '[0.1, 0.2, ...]'\nCOPY items (contents, embedding) FROM STDIN;\n-- Binary format (if your client supports it):\nCOPY items (contents, embedding) FROM STDIN WITH (FORMAT BINARY);\n\n-- Add indexes AFTER loading\nSET maintenance_work_mem = '4GB';\nSET max_parallel_maintenance_workers = 7;\nCREATE INDEX ON items USING hnsw (embedding halfvec_cosine_ops);\n```\n\n## Maintenance\n\n- **VACUUM regularly** after updates/deletes—stale entries may persist until vacuumed\n- **REINDEX** if performance degrades after high churn (rebuilds the graph from scratch)\n- For write-heavy workloads with frequent deletes, consider IVFFlat or partitioning by time using hypertables\n\n## Monitoring & Debugging\n\n```sql\n-- Check index size\nSELECT pg_size_pretty(pg_relation_size('items_embedding_idx'));\n\n-- Debug query performance\nEXPLAIN (ANALYZE, BUFFERS) SELECT id, contents FROM items ORDER BY embedding <=> $1::halfvec(1536) LIMIT 10;\n\n-- Monitor index build progress\nSELECT phase, round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS \"%\" \nFROM pg_stat_progress_create_index;\n\n-- Compare approximate vs exact recall\nBEGIN;\nSET LOCAL enable_indexscan = off; -- Force exact search\nSELECT id, contents FROM items ORDER BY embedding <=> $1::halfvec(1536) LIMIT 10;\nCOMMIT;\n\n-- Force index use for debugging\nBEGIN;\nSET LOCAL enable_seqscan = off;\nSELECT id, contents FROM items ORDER BY embedding <=> $1::halfvec(1536) LIMIT 10;\nCOMMIT;\n```\n\n## Common Issues (Symptom → Fix)\n\n| Symptom | Likely Cause | Fix |\n|--------|--------------|-----|\n| Query does not use ANN index | Missing `ORDER BY` + `LIMIT`, operator mismatch, or implicit casts | Use `ORDER BY` with a distance operator that matches the index ops class; explicitly cast query vectors |\n| Fewer results than expected (filtered query) | HNSW stops early due to filter | Enable iterative scan; increase `hnsw.max_scan_tuples`; or prefilter (B-tree), use partial indexes, or partition |\n| Fewer results than expected (unfiltered query) | ANN recall too low | Increase `hnsw.ef_search` |\n| High latency with low CPU usage | HNSW index not resident in memory | Use `halfvec`, reduce `m`/`ef_construction`, add RAM, partition, or use binary quantization |\n| Slow index builds | Insufficient build memory or parallelism | Increase `maintenance_work_mem` and `max_parallel_maintenance_workers`; build after bulk load |\n| Out-of-memory errors | Index too large for available RAM | Use `halfvec`, reduce index parameters, or switch to binary quantization with re-ranking |\n| Zero or missing results | NULL or zero vectors | Avoid NULL embeddings; do not use zero vectors with cosine distance |","schemaVersion":1},"repoUrl":"https://github.com/timescale/pg-aiguide/tree/main/skills/pgvector-semantic-search","tags":["ai","ai-agents","ai-coding","claude-code-plugin","claude-code-plugins","claude-code-plugins-marketplace","claude-marketplace","claude-plugin","claude-skills","docs","documentation","mcp"],"stats":{"installVelocity7d":0,"retentionRate":0,"executions":0,"rating":null},"origin":"github","source":{"repo":"pg-aiguide","audit":{"files":["bun.lock","package.json"],"binaries":[],"findings":[],"packages":9,"auditedAt":"2026-09-25T11:52:20.587Z","lockfiles":["bun.lock"]},"forks":110,"owner":"timescale","stars":1847,"topics":["ai","ai-agents","ai-coding","claude-code-plugin","claude-code-plugins","claude-code-plugins-marketplace","claude-marketplace","claude-plugin","claude-skills","docs","documentation","mcp","mcp-server","postgres","postgresql","skills"],"license":"Apache-2.0","fullName":"timescale/pg-aiguide","homepage":null,"language":"Python","pushedAt":"2026-09-24T20:55:47Z","avatarUrl":"https://avatars.githubusercontent.com/u/8986001?v=4","crawledAt":"2026-09-25T11:52:15.055Z","openIssues":11,"manifestFile":"SKILL.md","manifestPath":"skills/pgvector-semantic-search/SKILL.md","defaultBranch":"main"},"readme":"# pgvector for Semantic Search\n\nSemantic search finds content by meaning rather than exact keywords. An embedding model converts text into high-dimensional vectors, where similar meanings map to nearby points. pgvector stores these vectors in PostgreSQL and uses approximate nearest neighbor (ANN) indexes to find the closest matches quickly—scaling to millions of rows without leaving the database. Store your text alongside its embedding, then query by converting your search text to a vector and returning the rows with the smallest distance.\n\nThis guide covers pgvector setup and tuning—not embedding model selection or text chunking, which significantly affect search quality. Requires pgvector 0.8.0+ for all features (`halfvec`, `binary_quantize`, iterative scan).\n\n## Golden Path (Default Setup)\n\nUse this configuration unless you have a specific reason not to.\n- Embedding column data type: `halfvec(N)` where `N` is your embedding dimension (must match everywhere). Examples use 1536; replace with your dimension `N`.\n- Distance: cosine (`<=>`)\n- Index: HNSW (`m = 16`, `ef_construction = 64`). Use `halfvec_cosine_ops` and query with `<=>`.\n- Query-time recall: `SET hnsw.ef_search = 100` (good starting point from published benchmarks, increase for higher recall at higher latency)\n- Query pattern: `ORDER BY embedding <=> $1::halfvec(N) LIMIT k`\n\nThis setup provides a strong speed–recall tradeoff for most text-embedding workloads.\n\n## Core Rules\n\n- **Enable the extension** in each database: `CREATE EXTENSION IF NOT EXISTS vector;`\n- **Use HNSW indexes by default**—superior speed-recall tradeoff, can be created on empty tables, no training step required. Only consider IVFFlat for write-heavy or memory-bound workloads.\n- **Use `halfvec` by default**—store and index as `halfvec` for 50% smaller storage and indexes with minimal recall loss.\n- **Index after bulk loading** initial data for best build performance.\n- **Create indexes concurrently** in production: `CREATE INDEX CONCURRENTLY ...`\n- **Use cosine distance by default** (`<=>`): For non-normalized embeddings, use cosine. For unit-normalized embeddings, cosine and inner product yield identical rankings; default to cosine.\n- **Match query operator to index ops**: Index with `halfvec_cosine_ops` requires `<=>` in queries; `halfvec_l2_ops` requires `<->`; mismatched operators won't use the index.\n- **Always cast query vectors explicitly** (`$1::halfvec(N)`) to avoid implicit-cast failures in prepared statements.\n- **Always use the same embedding model for data and queries**. Similarity search only works when the model generating the vectors is the same.\n\n## Type Rules\n\n- Store embeddings as `halfvec(N)`\n- Cast query vectors to `halfvec(N)`\n- Store binary quantized vectors as `bit(N)` in a generated column\n- Do not mix `vector` / `halfvec` / `bit` without explicit casts\n- Never call `binary_quantize()` on table columns inside `ORDER BY`; store it instead\n- Dimensions must match: a `halfvec(1536)` column requires query vectors cast as `::halfvec(1536)`.\n\n## Standard Pattern\n\n```sql\n-- Store and index as halfvec\nCREATE TABLE items (\n id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n contents TEXT NOT NULL,\n embedding halfvec(1536) NOT NULL -- NOT NULL requires embeddings generated before insert, not async\n);\nCREATE INDEX ON items USING hnsw (embedding halfvec_cosine_ops);\n\n-- Query: returns 10 closest items. $1 is the embedding of your search text.\nSELECT id, contents FROM items ORDER BY embedding <=> $1::halfvec(1536) LIMIT 10;\n```\n\nFor other distance operators (L2, inner product, etc.), see the [pgvector README](https://github.com/pgvector/pgvector).\n\n## HNSW Index\n\nThe recommended index type. Creates a multilayer navigable graph with superior speed-recall tradeoff. Can be created on empty tables (no training step required).\n\n```sql\nCREATE INDEX ON items USING hnsw (embedding halfvec_cosine_ops);\n\n-- With tuning parameters\nCREATE INDEX ON items USING hnsw (embedding halfvec_cosine","createdAt":"2026-09-25T11:52:20.663Z","updatedAt":"2026-09-25T11:52:20.663Z"},{"id":"cmugwiawz01u8qu06m5syr8fo","slug":"timescale-pg-aiguide-postgres-database-migration","name":"postgres-database-migration","description":"Use this skill for planning, testing, and safely executing PostgreSQL schema migrations — especially when working with production data or shared databases. **Trigger when user asks to:** - Test a schema migration before applying it to production - Add, remove, or rename columns safely on a live table - Change a column's data type without downtime - Add or drop indexes, constraints, or foreign keys on large tables - Understand which ALTER TABLE operations lock the table - Roll back a failed migration - Plan a zero-downtime migration strategy - Fork a database to test a migration safely **Keywords:** migration, schema change, ALTER TABLE, add column, drop column, rename column, change type, zero downtime, lock, AccessExclusiveLock, concurrent index, forking, rollback, backfill, deploy Covers: lock-level reference for every common DDL operation, safe migration patterns, fork-based testing, zero-downtime column changes, index creation, constraint addition, backfill strategies, pre/post-migration validation, and rollback planning.","authorId":"gh:timescale","authorName":"timescale","version":"0.1.0","category":"Prompt","securityLevel":"Community","downloadsCount":0,"githubStars":1847,"pricePerCall":0,"manifest":{"name":"postgres-database-migration","tools":[],"category":"Prompt","entrypoint":{"type":"prompt"},"description":"Use this skill for planning, testing, and safely executing PostgreSQL schema migrations — especially when working with production data or shared databases. **Trigger when user asks to:** - Test a schema migration before applying it to production - Add, remove, or rename columns safely on a live table - Change a column's data type without downtime - Add or drop indexes, constraints, or foreign keys on large tables - Understand which ALTER TABLE operations lock the table - Roll back a failed migration - Plan a zero-downtime migration strategy - Fork a database to test a migration safely **Keywords:** migration, schema change, ALTER TABLE, add column, drop column, rename column, change type, zero downtime, lock, AccessExclusiveLock, concurrent index, forking, rollback, backfill, deploy Covers: lock-level reference for every common DDL operation, safe migration patterns, fork-based testing, zero-downtime column changes, index creation, constraint addition, backfill strategies, pre/post-migration validation, and rollback planning.","permissions":[],"systemPrompt":"# PostgreSQL Database Migrations\n\nA schema migration that works on an empty dev database can fail, lock, or corrupt data on a production table with millions of rows. This guide covers how to assess risk, test against real data, and execute migrations safely.\n\n## DDL Lock Reference\n\nEvery schema change acquires a lock. The critical question is: **does it block reads and writes, and for how long?**\n\n### Fast, Non-Blocking Operations\n\nThese complete in milliseconds regardless of table size. They only hold a brief `AccessExclusiveLock` for the catalog update, not for data rewriting.\n\n| Operation | Lock Level | Notes |\n|-----------|-----------|-------|\n| `ADD COLUMN` (nullable, no default) | `AccessExclusiveLock` (brief) | **Fast.** No table rewrite. Metadata-only change. |\n| `ADD COLUMN ... DEFAULT x` (PG 11+) | `AccessExclusiveLock` (brief) | **Fast.** Non-volatile defaults stored in catalog, not backfilled. |\n| `DROP COLUMN` | `AccessExclusiveLock` (brief) | **Fast.** Column marked invisible; space reclaimed by VACUUM over time. |\n| `SET DEFAULT` / `DROP DEFAULT` | `AccessExclusiveLock` (brief) | Metadata change only. Does not touch existing rows. |\n| `CREATE INDEX CONCURRENTLY` | `ShareUpdateExclusiveLock` | **Non-blocking.** Allows reads and writes during build. Slower than regular index creation. |\n| `DROP INDEX CONCURRENTLY` | `ShareUpdateExclusiveLock` | **Non-blocking.** Waits for queries using the index to finish, then drops. No table-level exclusive lock. |\n| `RENAME COLUMN` | `AccessExclusiveLock` (brief) | Metadata change only. Fast. |\n| `RENAME TABLE` | `AccessExclusiveLock` (brief) | Metadata change only. Fast. |\n| `ADD CONSTRAINT ... NOT VALID` | `ShareUpdateExclusiveLock` | Adds constraint for new rows only. Does not scan existing data. |\n| `VALIDATE CONSTRAINT` | `ShareUpdateExclusiveLock` | Scans existing rows but allows concurrent reads and writes. |\n| `CREATE/DROP TRIGGER` | `ShareRowExclusiveLock` | Brief catalog update. |\n\n### Slow or Blocking Operations\n\nThese rewrite the table or scan all rows. On large tables, they can lock out all access for seconds to hours.\n\n| Operation | Lock Level | Why It's Slow |\n|-----------|-----------|---------------|\n| `ADD COLUMN ... DEFAULT x` (volatile, e.g. `now()`, `gen_random_uuid()`) | `AccessExclusiveLock` | Full table rewrite. Every row gets the computed value. |\n| `ALTER COLUMN TYPE` (most type changes) | `AccessExclusiveLock` | Full table rewrite to convert stored data. |\n| `SET NOT NULL` (PG < 12, or without existing CHECK) | `AccessExclusiveLock` | Full table scan to verify no NULLs. See safe pattern below. |\n| `ADD CONSTRAINT ... CHECK/UNIQUE/FK` (validated) | `AccessExclusiveLock` or `ShareRowExclusiveLock` | Scans all rows to verify, blocks writes. |\n| `CREATE INDEX` (without CONCURRENTLY) | `ShareLock` | Blocks writes for the entire build duration. |\n| `CLUSTER` | `AccessExclusiveLock` | Rewrites entire table in index order. |\n| `VACUUM FULL` | `AccessExclusiveLock` | Rewrites table to reclaim space. |\n\n**Key insight:** `AccessExclusiveLock` blocks everything — reads and writes. Even if the operation itself is fast (milliseconds), it must wait for all in-flight transactions to finish before acquiring the lock. A long-running query or idle transaction can cause an `ALTER TABLE` to hang and queue up all subsequent queries behind it.\n\n## Safe Migration Patterns\n\n### Add a Column\n\n```sql\n-- SAFE: nullable column, no default — instant\nALTER TABLE orders ADD COLUMN tracking_number TEXT;\n\n-- SAFE (PG 11+): column with non-volatile default — instant\nALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0;\n\n-- UNSAFE: column with volatile default — full table rewrite\n-- DON'T: ALTER TABLE orders ADD COLUMN created_at TIMESTAMPTZ DEFAULT now();\n-- DO: add nullable, then backfill, then set default + NOT NULL\nALTER TABLE orders ADD COLUMN created_at TIMESTAMPTZ;\n-- Backfill in batches (see Backfill section)\nALTER TABLE orders ALTER COLUMN created_at SET DEFAULT now();\nALTER TABLE orders ALTER COLUMN created_at SET NOT NULL; -- only if PG12+ or CHECK exists\n```\n\n### Drop a Column\n\n```sql\n-- SAFE: instant (column marked invisible, space reclaimed by VACUUM)\nALTER TABLE orders DROP COLUMN old_status;\n```\n\n**Application coordination:** Ensure your application no longer references the column before dropping it. For zero-downtime deploys, this requires two steps:\n1. Deploy code that doesn't read/write the column\n2. Then drop the column in a separate migration\n\n**Security caveat:** `DROP COLUMN` does not physically delete the data. The column is marked as dropped in `pg_attribute` but the values remain on disk until `VACUUM` reclaims the space — and even then, a superuser could recover them. If the column contains sensitive data, run `VACUUM FULL` on the table after dropping, or use dump/restore to ensure the data is truly gone.\n\n### Rename a Column\n\n```sql\n-- SAFE: instant metadata change\nALTER TABLE orders RENAME COLUMN status TO order_status;\n```\n\n**Warning:** This breaks any application code, views, or functions that reference the old column name. For zero-downtime deploys, use the column-swap pattern instead:\n1. Add the new column\n2. Deploy code that writes to both columns\n3. Backfill old rows\n4. Deploy code that reads from the new column\n5. Drop the old column\n\n### Change a Column Type\n\nMost type changes rewrite the entire table. Safe alternatives:\n\n```sql\n-- UNSAFE: full table rewrite, blocks everything\n-- DON'T: ALTER TABLE orders ALTER COLUMN amount TYPE NUMERIC(12,2);\n\n-- SAFE: use a new column + backfill\nALTER TABLE orders ADD COLUMN amount_new NUMERIC(12,2);\n\n-- Backfill in batches (see Backfill section below)\nUPDATE orders SET amount_new = amount WHERE id BETWEEN 1 AND 10000;\n-- ... continue in batches ...\n\n-- Swap columns\nALTER TABLE orders DROP COLUMN amount;\nALTER TABLE orders RENAME COLUMN amount_new TO amount;\n```\n\n**Exception:** Some casts don't require a rewrite and are fast:\n\n| From | To | Rewrite? |\n|------|----|----------|\n| `VARCHAR(n)` → `VARCHAR(m)` where m > n | No | Metadata only |\n| `VARCHAR(n)` → `TEXT` | No | Metadata only |\n| `NUMERIC(p,s)` → `NUMERIC(p2,s)` where p2 > p (same scale) | No | Metadata only |\n| `INTEGER` → `BIGINT` | **Yes** | Full rewrite |\n| `TIMESTAMP` → `TIMESTAMPTZ` | **Yes** | Full rewrite |\n\n### Add a NOT NULL Constraint\n\n```sql\n-- PG 18+: simplified two-step pattern\nALTER TABLE orders ALTER COLUMN order_status SET NOT NULL NOT VALID;\nALTER TABLE orders VALIDATE NOT NULL ON order_status;\n\n-- PG 12–17: fast if a valid CHECK constraint already exists\n-- Step 1: add CHECK (non-blocking scan)\nALTER TABLE orders ADD CONSTRAINT orders_status_nn CHECK (order_status IS NOT NULL) NOT VALID;\nALTER TABLE orders VALIDATE CONSTRAINT orders_status_nn;\n\n-- Step 2: add NOT NULL (PG12+ recognizes the CHECK and skips the scan)\nALTER TABLE orders ALTER COLUMN order_status SET NOT NULL;\n\n-- Step 3: drop the now-redundant CHECK\nALTER TABLE orders DROP CONSTRAINT orders_status_nn;\n\n-- PG < 12: SET NOT NULL always scans the full table.\n-- Ensure no NULLs exist first, then accept the brief lock.\n```\n\n### Add a Foreign Key\n\n```sql\n-- UNSAFE: validates all existing rows while holding a heavy lock\n-- DON'T: ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);\n\n-- SAFE: two-step approach\n-- Step 1: add without validation (blocks writes briefly, doesn't scan data)\nALTER TABLE orders ADD CONSTRAINT fk_user\n FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;\n\n-- Step 2: validate existing rows (allows concurrent reads and writes)\nALTER TABLE orders VALIDATE CONSTRAINT fk_user;\n```\n\n### Add an Index\n\n```sql\n-- UNSAFE on large tables: blocks all writes for the entire build\n-- DON'T: CREATE INDEX idx_orders_user ON orders (user_id);\n\n-- SAFE: concurrent index creation (allows reads and writes)\nCREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);\n\n-- IMPORTANT: if concurrent index creation fails (crashes, deadlock),\n-- it leaves an INVALID index behind. Check and clean up:\nSELECT indexrelname, idx_scan\nFROM pg_stat_user_indexes\nWHERE schemaname = 'public'\n AND indexrelname = 'idx_orders_user';\n\n-- Check for invalid indexes\nSELECT indexrelid::regclass AS index_name, indisvalid\nFROM pg_index\nWHERE NOT indisvalid;\n\n-- Drop and retry if invalid\nDROP INDEX CONCURRENTLY idx_orders_user;\nCREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);\n```\n\n### Add a Unique Constraint\n\n```sql\n-- A UNIQUE constraint creates an index. Use CONCURRENTLY to avoid blocking:\n\n-- Step 1: create a unique index concurrently\nCREATE UNIQUE INDEX CONCURRENTLY idx_orders_tracking_uniq ON orders (tracking_number);\n\n-- Step 2: attach it as a constraint (instant)\nALTER TABLE orders ADD CONSTRAINT orders_tracking_uniq UNIQUE USING INDEX idx_orders_tracking_uniq;\n```\n\n### Redefine a Primary Key\n\nRedefining a PK (e.g., switching from `id` to a composite key, or from `int` to `bigint`) requires both a UNIQUE constraint and NOT NULL — both of which can cause long-lasting locks if done naively. The zero-downtime approach builds each ingredient separately:\n\n```sql\n-- Step 1: add CHECK NOT NULL constraint without validation (brief lock)\nALTER TABLE orders ADD CONSTRAINT orders_new_id_nn\n CHECK (new_id IS NOT NULL) NOT VALID;\n\n-- Step 2: validate existing rows (allows concurrent reads and writes)\nALTER TABLE orders VALIDATE CONSTRAINT orders_new_id_nn;\n\n-- Step 3: build unique index concurrently (non-blocking)\nCREATE UNIQUE INDEX CONCURRENTLY idx_orders_new_pkey\n ON orders (new_id);\n\n-- Step 4: drop the old PK\nALTER TABLE orders DROP CONSTRAINT orders_pkey;\n\n-- Step 5: add new PK using the existing index (instant — also implicitly adds NOT NULL)\nALTER TABLE orders ADD CONSTRAINT orders_pkey\n PRIMARY KEY USING INDEX idx_orders_new_pkey;\n\n-- Step 6: drop the now-redundant CHECK constraint\nALTER TABLE orders DROP CONSTRAINT orders_new_id_nn;\n```\n\n**Why this works:** Step 5 is fast because Postgres reuses the already-built unique index and recognizes the existing CHECK constraint, skipping both the index build and the full-table NOT NULL scan (PG12+).\n\n### Drop a Constraint\n\n```sql\n-- SAFE: instant metadata change\nALTER TABLE orders DROP CONSTRAINT orders_tracking_uniq;\n\n-- If dropping a FK that has a supporting index you no longer need:\nALTER TABLE orders DROP CONSTRAINT fk_user;\nDROP INDEX idx_orders_user_id; -- only if no other queries use it\n```\n\n## Backfill Strategies\n\nAlways backfill in batches — never in a single UPDATE. See [backfill-strategies](references/backfill-strategies.md) for batch-by-PK patterns, resumable progress tracking, and tuning guidance.\n\n## Migration Validation\n\nRun validation queries before and after every migration. See [validation-queries](references/validation-queries.md) for the full set of checks: NULL detection, duplicate detection, orphan rows, cast failures, duration estimation, schema verification, data integrity, and query performance.\n\n## Rollback Planning\n\nEvery migration should have a rollback plan documented before execution.\n\n### Reversible Operations\n\n| Operation | Rollback |\n|-----------|----------|\n| `ADD COLUMN` | `DROP COLUMN` |\n| `ADD CONSTRAINT` | `DROP CONSTRAINT` |\n| `CREATE INDEX` | `DROP INDEX` |\n| `RENAME COLUMN x TO y` | `RENAME COLUMN y TO x` |\n| `SET DEFAULT x` | `SET DEFAULT old_value` or `DROP DEFAULT` |\n| `ADD COLUMN new + DROP COLUMN old` | Cannot directly undo — need to re-add old column and backfill from a backup |\n\n### Irreversible Operations\n\nThese require restoring from a backup or the database fork to undo:\n\n- **`DROP COLUMN`** — data is gone once VACUUM reclaims it\n- **`ALTER COLUMN TYPE`** with lossy cast (e.g., `NUMERIC` → `INTEGER`, `TEXT` → `VARCHAR(50)`)\n- **`DELETE` / `TRUNCATE`** during data cleanup\n- **`DROP TABLE`**\n\n**This is where a database fork is invaluable.** If you forked before the migration, the original database has the pre-migration state. If the migration went wrong, your production data is untouched — just delete the fork and start over.\n\n## Transaction Strategy\n\nThere are two approaches for executing multiple DDL statements. Each has tradeoffs:\n\n**Wrapped in one transaction** — all changes succeed or all roll back. Use this when atomicity matters more than lock duration, and all operations are fast (milliseconds).\n\n```sql\nBEGIN;\n\nALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0;\nALTER TABLE orders ADD COLUMN tags TEXT[] NOT NULL DEFAULT '{}';\nCREATE INDEX ON orders USING GIN (tags);\nALTER TABLE orders DROP COLUMN old_priority;\n\n-- Verify before committing\nSELECT column_name, data_type\nFROM information_schema.columns\nWHERE table_name = 'orders'\nORDER BY ordinal_position;\n\nCOMMIT;\n-- Or ROLLBACK; if something looks wrong\n```\n\n**Separate transactions** — each DDL runs and commits independently. Use this when lock duration matters more than atomicity. In a single transaction, all locks are held until `COMMIT` — so if you have 5 DDL statements, the `AccessExclusiveLock` from the first one blocks traffic for the entire duration of all 5. Separate transactions release locks between statements.\n\n```sql\n-- Each statement auto-commits\nALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0;\nALTER TABLE orders ADD COLUMN tags TEXT[] NOT NULL DEFAULT '{}';\nALTER TABLE orders DROP COLUMN old_priority;\n```\n\n**The tradeoff:** separate transactions can leave the schema in a partially migrated state if a later statement fails. You'll need a rollback plan for each step individually.\n\n**Cannot use transactions with:**\n- `CREATE INDEX CONCURRENTLY` (explicitly disallowed inside a transaction)\n- `DROP INDEX CONCURRENTLY`\n- Any statement that requires its own transaction context\n\n## Dealing with Long-Running Queries\n\nA fast `ALTER TABLE` can still hang if it's waiting to acquire `AccessExclusiveLock` behind a long-running query. Worse, the waiting DDL blocks all subsequent queries too — even simple SELECTs pile up behind it:\n\n```\nSession 1: SELECT COUNT(*) FROM orders; -- long query, holds AccessShareLock\nSession 2: ALTER TABLE orders ADD COLUMN ...; -- waits for Session 1 (needs AccessExclusiveLock)\nSession 3: SELECT * FROM orders WHERE id = 123; -- BLOCKED by Session 2's lock queue entry\nSession 4: INSERT INTO orders (...) VALUES (...); -- also BLOCKED\n-- All sessions freeze until Session 1 finishes and Session 2 completes or times out\n```\n\nThis is why `lock_timeout` is critical — without it, a single slow query can cascade into an application-wide outage.\n\n### Set Timeouts\n\n**`lock_timeout`** — How long to wait for a lock before giving up. Use this on every production DDL statement. Without it, an `ALTER TABLE` can queue behind a long-running query and block all subsequent queries behind it indefinitely.\n\n**`statement_timeout`** — How long the statement can run once it has the lock. This is a safety net against unexpectedly slow operations (e.g., a type change that triggers a table rewrite you didn't anticipate). The tradeoff: if the timeout fires mid-operation, the entire statement rolls back — which is safe for DDL (no partial changes), but means a long `CREATE INDEX CONCURRENTLY` could be killed near completion. For that reason, avoid setting `statement_timeout` on operations you know will be slow (like concurrent index builds on large tables) and instead monitor them manually.\n\n**Choosing timeout values:**\n\nThere are two schools of thought:\n\n- **Conservative (50-100ms lock_timeout, hundreds of retries):** Minimizes the window where a waiting DDL blocks other queries. Each attempt is nearly invisible to application traffic, but requires retry logic. Best for high-traffic OLTP systems where even a few seconds of blocked queries is unacceptable.\n- **Pragmatic (3-5s lock_timeout, few retries):** Gives the lock a reasonable chance to be acquired on each attempt, reducing the need for complex retry logic. Acceptable for most applications where brief pauses are tolerable.\n\nPick based on your traffic profile: the higher your query throughput, the shorter your `lock_timeout` should be — because even a brief queue-up affects more queries per second. For `statement_timeout`, set it to a generous multiple of what you expect the operation to take (e.g., 30s for metadata-only changes, minutes for VALIDATE CONSTRAINT on large tables, disabled for CREATE INDEX CONCURRENTLY).\n\n```sql\n-- Fail fast instead of blocking all queries behind you\nSET lock_timeout = '5s';\nSET statement_timeout = '30s';\n\nALTER TABLE orders ADD COLUMN tracking_number TEXT;\n\n-- If it fails with \"canceling statement due to lock timeout\":\n-- 1. Find what's blocking\nSELECT pid, state, query, now() - query_start AS duration\nFROM pg_stat_activity\nWHERE state != 'idle'\nORDER BY duration DESC;\n\n-- 2. Wait for the blocker to finish, or cancel it if appropriate\n-- SELECT pg_cancel_backend();\n\n-- 3. Retry the ALTER TABLE\nSET lock_timeout = '5s';\nALTER TABLE orders ADD COLUMN tracking_number TEXT;\n\n-- Reset timeouts when done\nRESET lock_timeout;\nRESET statement_timeout;\n```\n\n### The Retry-With-Timeout Pattern\n\nFor automated migration runners, wrap DDL in a retry loop with a short lock timeout:\n\n```sql\nDO $$\nDECLARE\n max_attempts INTEGER := 5;\n attempt INTEGER := 1;\n success BOOLEAN := FALSE;\nBEGIN\n WHILE attempt <= max_attempts AND NOT success LOOP\n BEGIN\n SET lock_timeout = '3s';\n -- Replace with your DDL statement\n ALTER TABLE orders ADD COLUMN tracking_number TEXT;\n success := TRUE;\n RAISE NOTICE 'DDL succeeded on attempt %', attempt;\n EXCEPTION\n WHEN lock_not_available THEN\n RAISE NOTICE 'Attempt % failed (lock not available), retrying...', attempt;\n PERFORM pg_sleep(2 * attempt); -- linear backoff\n attempt := attempt + 1;\n END;\n END LOOP;\n\n IF NOT success THEN\n RAISE EXCEPTION 'DDL failed after % attempts', max_attempts;\n END IF;\nEND $$;\n```\n\nThis prevents the migration from creating a pile-up of blocked queries behind it. Each attempt either succeeds quickly or gives up and lets normal traffic flow.\n\n**Alternative: `NOWAIT`** — For the highest-traffic systems, use `LOCK TABLE ... NOWAIT` to test lock availability before running DDL. Unlike `lock_timeout`, `NOWAIT` fails instantly without ever entering the lock queue, so there is zero risk of cascading blocked queries. The tradeoff is more retries:\n\n```sql\nBEGIN;\nLOCK TABLE orders IN ACCESS EXCLUSIVE MODE NOWAIT;\n-- If we get here, we have the lock — run DDL\nALTER TABLE orders ADD COLUMN tracking_number TEXT;\nCOMMIT;\n-- If LOCK fails with \"could not obtain lock\", retry after a short sleep\n```\n\n## Fork-Based Migration Testing\n\nThe safest way to test a migration is to run it against a copy of your actual database — same schema, same data, same edge cases. Providers such as [Neon](https://neon.tech) support fast database forking. Without database forking, you need to manually dump and restore your database, which can take a long time for large datasets.\n\n### With Forking\n\n1. **Fork your database** — create a full copy using your provider's fork feature (takes seconds)\n2. **Inspect the current schema** on the fork to confirm it matches production\n3. **Run your migration** on the fork\n4. **Validate** — run your checks (see Pre/Post-Migration Validation sections above)\n5. **If it worked:** apply the same migration to production\n6. **If it failed:** delete the fork — your production database is untouched\n\nThis catches problems that never show up in empty test databases:\n- Data that violates a new constraint\n- Type casts that fail on real values\n- Migrations that are fast on 100 rows but lock the table for minutes on 10 million\n- Index creation that runs out of memory or disk space\n\n**Limitation:** fork-based testing runs your migration in isolation — it won't catch issues caused by concurrent database traffic (e.g., lock contention under load, deadlocks with concurrent writes, or replication lag from heavy WAL generation). For most applications, fork-based testing is sufficient. For very high-uptime applications, use [PgDog](https://pgdog.dev)'s mirroring feature to replay production traffic against the fork — it reproduces queries byte-for-byte with realistic timing, and you can filter to DDL-only or DML-only and control exposure percentage to ramp up gradually.\n\n### Without Forking\n\nCreate a test database from a backup or dump:\n\n```bash\n# Dump your production database\npg_dump -Fc my_app_db > backup.dump\n\n# Restore into a test database\ncreatedb migration_test\npg_restore -d migration_test backup.dump\n\n# Or clone from a live database (requires downtime on source during copy)\ncreatedb migration_test -T my_app_db\n```\n\n## Complete Migration Example\n\nFor a full end-to-end walkthrough (plan, fork, run, validate, apply, clean up), see [complete-example](references/complete-example.md).\n\n## Advanced Considerations\n\n**Subtransactions in PL/pgSQL retry loops:** The `BEGIN/EXCEPTION WHEN/END` block in the retry-with-timeout pattern creates implicit subtransactions. Under high write throughput, this can trigger SubtransSLRU contention on replicas — especially if the retry loop runs as a long-lived transaction with many attempts. If you see replica lag during retries, move the retry logic to the application layer (separate transactions per attempt) instead of using PL/pgSQL exception handling.\n\n**Autovacuum can block VALIDATE CONSTRAINT:** `VALIDATE CONSTRAINT` acquires `ShareUpdateExclusiveLock`, which conflicts with autovacuum running in transaction ID wraparound prevention mode. If `VALIDATE` hangs unexpectedly, check `pg_stat_activity` for autovacuum processes on the same table. You may need to wait for wraparound-prevention autovacuum to finish — do not cancel it, as that can lead to data loss if the table approaches the XID wraparound limit.\n\n## Common Pitfalls\n\n1. **Testing migrations on empty tables** — a migration that runs in 1ms on an empty table can lock a 10M-row table for minutes. Always test against realistic data volumes.\n2. **Forgetting `CONCURRENTLY` on index creation** — `CREATE INDEX` (without `CONCURRENTLY`) blocks all writes. On a table with active traffic, this causes downtime.\n3. **Adding NOT NULL without the two-step pattern** — on large tables in PG < 12, `SET NOT NULL` scans every row while holding `AccessExclusiveLock`. Use the CHECK constraint pattern.\n4. **No lock timeout** — a fast ALTER TABLE can block behind a long-running query, and every subsequent query stacks up behind it. Always `SET lock_timeout` for production DDL.\n5. **Backfilling in one big transaction** — a single `UPDATE orders SET x = y` on 10M rows generates enormous WAL, bloats the table, and holds locks for the entire duration. Always batch.\n6. **Leaving invalid indexes behind** — if `CREATE INDEX CONCURRENTLY` fails, it leaves an invisible invalid index that consumes space and slows writes. Check `pg_index.indisvalid` after every concurrent index operation.\n7. **Dropping columns before updating application code** — in a running system, the old code still references the column. Deploy the code change first, then drop the column in a subsequent migration.\n8. **Not checking replication lag** — large backfills generate heavy WAL. If you have read replicas, monitor `pg_stat_replication` during and after the migration.\n9. **Assuming ALTER COLUMN TYPE is safe** — most type changes rewrite the entire table. Use the add-new-column + backfill + swap pattern for large tables.","schemaVersion":1},"repoUrl":"https://github.com/timescale/pg-aiguide/tree/main/skills/postgres-database-migration","tags":["ai","ai-agents","ai-coding","claude-code-plugin","claude-code-plugins","claude-code-plugins-marketplace","claude-marketplace","claude-plugin","claude-skills","docs","documentation","mcp"],"stats":{"installVelocity7d":0,"retentionRate":0,"executions":0,"rating":null},"origin":"github","source":{"repo":"pg-aiguide","audit":{"files":["bun.lock","package.json"],"binaries":[],"findings":[],"packages":9,"auditedAt":"2026-09-25T11:52:20.587Z","lockfiles":["bun.lock"]},"forks":110,"owner":"timescale","stars":1847,"topics":["ai","ai-agents","ai-coding","claude-code-plugin","claude-code-plugins","claude-code-plugins-marketplace","claude-marketplace","claude-plugin","claude-skills","docs","documentation","mcp","mcp-server","postgres","postgresql","skills"],"license":"Apache-2.0","fullName":"timescale/pg-aiguide","homepage":null,"language":"Python","pushedAt":"2026-09-24T20:55:47Z","avatarUrl":"https://avatars.githubusercontent.com/u/8986001?v=4","crawledAt":"2026-09-25T11:52:15.055Z","openIssues":11,"manifestFile":"SKILL.md","manifestPath":"skills/postgres-database-migration/SKILL.md","defaultBranch":"main"},"readme":"# PostgreSQL Database Migrations\n\nA schema migration that works on an empty dev database can fail, lock, or corrupt data on a production table with millions of rows. This guide covers how to assess risk, test against real data, and execute migrations safely.\n\n## DDL Lock Reference\n\nEvery schema change acquires a lock. The critical question is: **does it block reads and writes, and for how long?**\n\n### Fast, Non-Blocking Operations\n\nThese complete in milliseconds regardless of table size. They only hold a brief `AccessExclusiveLock` for the catalog update, not for data rewriting.\n\n| Operation | Lock Level | Notes |\n|-----------|-----------|-------|\n| `ADD COLUMN` (nullable, no default) | `AccessExclusiveLock` (brief) | **Fast.** No table rewrite. Metadata-only change. |\n| `ADD COLUMN ... DEFAULT x` (PG 11+) | `AccessExclusiveLock` (brief) | **Fast.** Non-volatile defaults stored in catalog, not backfilled. |\n| `DROP COLUMN` | `AccessExclusiveLock` (brief) | **Fast.** Column marked invisible; space reclaimed by VACUUM over time. |\n| `SET DEFAULT` / `DROP DEFAULT` | `AccessExclusiveLock` (brief) | Metadata change only. Does not touch existing rows. |\n| `CREATE INDEX CONCURRENTLY` | `ShareUpdateExclusiveLock` | **Non-blocking.** Allows reads and writes during build. Slower than regular index creation. |\n| `DROP INDEX CONCURRENTLY` | `ShareUpdateExclusiveLock` | **Non-blocking.** Waits for queries using the index to finish, then drops. No table-level exclusive lock. |\n| `RENAME COLUMN` | `AccessExclusiveLock` (brief) | Metadata change only. Fast. |\n| `RENAME TABLE` | `AccessExclusiveLock` (brief) | Metadata change only. Fast. |\n| `ADD CONSTRAINT ... NOT VALID` | `ShareUpdateExclusiveLock` | Adds constraint for new rows only. Does not scan existing data. |\n| `VALIDATE CONSTRAINT` | `ShareUpdateExclusiveLock` | Scans existing rows but allows concurrent reads and writes. |\n| `CREATE/DROP TRIGGER` | `ShareRowExclusiveLock` | Brief catalog update. |\n\n### Slow or Blocking Operations\n\nThese rewrite the table or scan all rows. On large tables, they can lock out all access for seconds to hours.\n\n| Operation | Lock Level | Why It's Slow |\n|-----------|-----------|---------------|\n| `ADD COLUMN ... DEFAULT x` (volatile, e.g. `now()`, `gen_random_uuid()`) | `AccessExclusiveLock` | Full table rewrite. Every row gets the computed value. |\n| `ALTER COLUMN TYPE` (most type changes) | `AccessExclusiveLock` | Full table rewrite to convert stored data. |\n| `SET NOT NULL` (PG < 12, or without existing CHECK) | `AccessExclusiveLock` | Full table scan to verify no NULLs. See safe pattern below. |\n| `ADD CONSTRAINT ... CHECK/UNIQUE/FK` (validated) | `AccessExclusiveLock` or `ShareRowExclusiveLock` | Scans all rows to verify, blocks writes. |\n| `CREATE INDEX` (without CONCURRENTLY) | `ShareLock` | Blocks writes for the entire build duration. |\n| `CLUSTER` | `AccessExclusiveLock` | Rewrites entire table in index order. |\n| `VACUUM FULL` | `AccessExclusiveLock` | Rewrites table to reclaim space. |\n\n**Key insight:** `AccessExclusiveLock` blocks everything — reads and writes. Even if the operation itself is fast (milliseconds), it must wait for all in-flight transactions to finish before acquiring the lock. A long-running query or idle transaction can cause an `ALTER TABLE` to hang and queue up all subsequent queries behind it.\n\n## Safe Migration Patterns\n\n### Add a Column\n\n```sql\n-- SAFE: nullable column, no default — instant\nALTER TABLE orders ADD COLUMN tracking_number TEXT;\n\n-- SAFE (PG 11+): column with non-volatile default — instant\nALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0;\n\n-- UNSAFE: column with volatile default — full table rewrite\n-- DON'T: ALTER TABLE orders ADD COLUMN created_at TIMESTAMPTZ DEFAULT now();\n-- DO: add nullable, then backfill, then set default + NOT NULL\nALTER TABLE orders ADD COLUMN created_at TIMESTAMPTZ;\n-- Backfill in batches (see Backfill section)\nALTER TABLE orders ALTER COLUMN created_at SET DEFAULT no","createdAt":"2026-09-25T11:52:20.675Z","updatedAt":"2026-09-25T11:52:20.675Z"},{"id":"cmugwiaxc01ubqu06r1ux36e1","slug":"timescale-pg-aiguide-postgres-hybrid-text-search","name":"postgres-hybrid-text-search","description":"Use this skill to implement hybrid search combining BM25 keyword search with semantic vector search using Reciprocal Rank Fusion (RRF). **Trigger when user asks to:** - Combine keyword and semantic search - Implement hybrid search or multi-modal retrieval - Use BM25/pg_textsearch with pgvector together - Implement RRF (Reciprocal Rank Fusion) for search - Build search that handles both exact terms and meaning **Keywords:** hybrid search, BM25, pg_textsearch, RRF, reciprocal rank fusion, keyword search, full-text search, reranking, cross-encoder Covers: pg_textsearch BM25 index setup, parallel query patterns, client-side RRF fusion (Python/TypeScript), weighting strategies, and optional ML reranking.","authorId":"gh:timescale","authorName":"timescale","version":"0.1.0","category":"Prompt","securityLevel":"Community","downloadsCount":0,"githubStars":1847,"pricePerCall":0,"manifest":{"name":"postgres-hybrid-text-search","tools":[],"category":"Prompt","entrypoint":{"type":"prompt"},"description":"Use this skill to implement hybrid search combining BM25 keyword search with semantic vector search using Reciprocal Rank Fusion (RRF). **Trigger when user asks to:** - Combine keyword and semantic search - Implement hybrid search or multi-modal retrieval - Use BM25/pg_textsearch with pgvector together - Implement RRF (Reciprocal Rank Fusion) for search - Build search that handles both exact terms and meaning **Keywords:** hybrid search, BM25, pg_textsearch, RRF, reciprocal rank fusion, keyword search, full-text search, reranking, cross-encoder Covers: pg_textsearch BM25 index setup, parallel query patterns, client-side RRF fusion (Python/TypeScript), weighting strategies, and optional ML reranking.","permissions":[],"systemPrompt":"# Hybrid Text Search\n\nHybrid search combines keyword search (BM25) with semantic search (vector embeddings) to get the best of both: exact keyword matching and meaning-based retrieval. Use Reciprocal Rank Fusion (RRF) to merge results from both methods into a single ranked list.\n\nThis guide covers combining [pg_textsearch](https://github.com/timescale/pg_textsearch) (BM25) with [pgvector](https://github.com/pgvector/pgvector). Requires both extensions. For high-volume setups, filtering, or advanced pgvector tuning (binary quantization, HNSW parameters), see the **pgvector-semantic-search** skill.\n\npg_textsearch is a new BM25 text search extension for PostgreSQL, fully open-source and available hosted on Tiger Cloud as well as for self-managed deployments. It provides true BM25 ranking, which often improves relevance compared to PostgreSQL's built-in ts_rank and can offer better performance at scale. Note: pg_textsearch is currently in prerelease and not yet recommended for production use. pg_textsearch currently supports PostgreSQL 17 and 18.\n\n## When to Use Hybrid Search\n\n- **Use hybrid** when queries mix specific terms (product names, codes, proper nouns) with conceptual intent\n- **Use semantic only** when meaning matters more than exact wording (e.g., \"how to fix slow queries\" should match \"query optimization\")\n- **Use keyword only** when exact matches are critical (e.g., error codes, SKUs, legal citations)\n\nHybrid search typically improves recall over either method alone, at the cost of slightly more complexity.\n\n## Data Preparation\n\nChunk your documents into smaller pieces (typically 500–1000 tokens) and store each chunk with its embedding. Both BM25 and semantic search operate on the same chunks—this keeps fusion simple since you're comparing like with like.\n\n## Golden Path (Default Setup)\n\n```sql\n-- Enable extensions\nCREATE EXTENSION IF NOT EXISTS vector;\nCREATE EXTENSION IF NOT EXISTS pg_textsearch;\n\n-- Table with both indexes\nCREATE TABLE documents (\n id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n content TEXT NOT NULL,\n embedding halfvec(1536) NOT NULL\n);\n\n-- BM25 index for keyword search\nCREATE INDEX ON documents USING bm25 (content) WITH (text_config = 'english');\n\n-- HNSW index for semantic search\nCREATE INDEX ON documents USING hnsw (embedding halfvec_cosine_ops);\n```\n\n### BM25 Notes\n\n- **Negative scores**: The `<@>` operator returns negative values where lower = better match. RRF uses rank position, so this doesn't affect fusion.\n- **Language config**: Change `text_config` to match your content language (e.g., `'french'`, `'german'`). See [PostgreSQL text search configurations](https://www.postgresql.org/docs/current/textsearch-configuration.html).\n- **Tuning**: BM25 has `k1` (term frequency saturation, default 1.2) and `b` (length normalization, default 0.75) parameters. Defaults work well; only tune if relevance is poor.\n ```sql\n CREATE INDEX ON documents USING bm25 (content) WITH (text_config = 'english', k1 = 1.5, b = 0.8);\n ```\n- **Partitioned tables**: Each partition maintains local statistics. Scores are not directly comparable across partitions—query individual partitions when score comparability matters.\n\n## RRF Query Pattern\n\nReciprocal Rank Fusion combines rankings from multiple searches. Each result's score is `1 / (k + rank)` where `k` is a constant (typically 60). Results are summed across searches and re-sorted.\n\n**Run both queries in parallel from your client** for lower latency, then fuse results client-side:\n\n```sql\n-- Query 1: Keyword search (BM25)\n-- $1: search text\nSELECT id, content FROM documents ORDER BY content <@> $1 LIMIT 50;\n```\n\n```sql\n-- Query 2: Semantic search (separate query, run in parallel)\n-- $1: embedding of your search text as halfvec(1536)\nSELECT id, content FROM documents ORDER BY embedding <=> $1::halfvec(1536) LIMIT 50;\n```\n\n```python\n# Client-side RRF fusion (Python)\ndef rrf_fusion(keyword_results, semantic_results, k=60, limit=10):\n scores = {}\n content_map = {}\n\n for rank, row in enumerate(keyword_results, start=1):\n scores[row['id']] = scores.get(row['id'], 0) + 1 / (k + rank)\n content_map[row['id']] = row['content']\n\n for rank, row in enumerate(semantic_results, start=1):\n scores[row['id']] = scores.get(row['id'], 0) + 1 / (k + rank)\n content_map[row['id']] = row['content']\n\n sorted_ids = sorted(scores, key=scores.get, reverse=True)[:limit]\n return [{'id': id, 'content': content_map[id], 'score': scores[id]} for id in sorted_ids]\n```\n\n```typescript\n// Client-side RRF fusion (TypeScript)\ntype Row = { id: number; content: string };\ntype Result = Row & { score: number };\n\nfunction rrfFusion(keywordResults: Row[], semanticResults: Row[], k = 60, limit = 10): Result[] {\n const scores = new Map();\n const contentMap = new Map();\n\n keywordResults.forEach((row, i) => {\n scores.set(row.id, (scores.get(row.id) ?? 0) + 1 / (k + i + 1));\n contentMap.set(row.id, row.content);\n });\n\n semanticResults.forEach((row, i) => {\n scores.set(row.id, (scores.get(row.id) ?? 0) + 1 / (k + i + 1));\n contentMap.set(row.id, row.content);\n });\n\n return [...scores.entries()]\n .sort((a, b) => b[1] - a[1])\n .slice(0, limit)\n .map(([id, score]) => ({ id, content: contentMap.get(id)!, score }));\n}\n```\n\n### RRF Parameters\n\n| Parameter | Default | Description |\n|-----------|---------|-------------|\n| `k` | 60 | Smoothing constant. Higher values reduce rank differences; 60 is standard |\n| Candidates per search | 50 | Higher = better recall, more work |\n| Final limit | 10 | Results returned after fusion |\n\nIncrease candidates if relevant results are being missed. The k=60 constant rarely needs tuning.\n\n## Weighting Keyword vs Semantic\n\nTo favor one method over another, multiply its RRF contribution:\n\n```python\n# Weight semantic search 2x higher than keyword\nkeyword_weight = 1.0\nsemantic_weight = 2.0\n\nfor rank, row in enumerate(keyword_results, start=1):\n scores[row['id']] = scores.get(row['id'], 0) + keyword_weight / (k + rank)\n\nfor rank, row in enumerate(semantic_results, start=1):\n scores[row['id']] = scores.get(row['id'], 0) + semantic_weight / (k + rank)\n```\n\n```typescript\n// Weight semantic search 2x higher than keyword\nconst keywordWeight = 1.0;\nconst semanticWeight = 2.0;\n\nkeywordResults.forEach((row, i) => {\n scores.set(row.id, (scores.get(row.id) ?? 0) + keywordWeight / (k + i + 1));\n});\n\nsemanticResults.forEach((row, i) => {\n scores.set(row.id, (scores.get(row.id) ?? 0) + semanticWeight / (k + i + 1));\n});\n```\n\nStart with equal weights (1.0 each) and adjust based on measured relevance.\n\n## Reranking with ML Models\n\nFor highest quality, add a reranking step using a cross-encoder model. Cross-encoders (e.g., `cross-encoder/ms-marco-MiniLM-L-6-v2`) are more accurate than bi-encoders but too slow for initial retrieval—use them only on the candidate set.\n\nRun the same parallel queries as above with a higher LIMIT (e.g., 100), then:\n\n```python\n# 1. Fuse results with RRF (more candidates for reranking)\ncandidates = rrf_fusion(keyword_results, semantic_results, limit=100)\n\n# 2. Rerank with cross-encoder\nfrom sentence_transformers import CrossEncoder\nreranker = CrossEncoder('cross-encoder/ms-marco-MiniLM-L-6-v2')\n\npairs = [(query_text, doc['content']) for doc in candidates]\nscores = reranker.predict(pairs)\n\n# 3. Return top 10 by reranker score\nreranked = sorted(zip(candidates, scores), key=lambda x: x[1], reverse=True)[:10]\n```\n\n```typescript\nimport { CohereClientV2 } from 'cohere-ai';\n\n// 1. Fuse results with RRF (more candidates for reranking)\nconst candidates = rrfFusion(keywordResults, semanticResults, 60, 100);\n\n// 2. Rerank via API (example uses Cohere SDK; Jina, Voyage, and others work similarly)\nconst cohere = new CohereClientV2({ token: COHERE_API_KEY });\n\nconst reranked = await cohere.rerank({\n model: 'rerank-v3.5',\n query: queryText,\n documents: candidates.map(c => c.content),\n topN: 10\n});\n\n// 3. Map back to original documents\nconst results = reranked.results.map(r => candidates[r.index]);\n```\n\nReranking is optional—hybrid RRF alone significantly improves over single-method search.\n\n## Performance Considerations\n\n- **Index both columns**: BM25 index on text, HNSW index on embedding\n- **Limit candidate pools**: 50–100 candidates per method is usually sufficient\n- **Run queries in parallel**: Client-side parallelism reduces latency vs sequential execution\n- **Monitor latency**: Hybrid adds overhead; ensure both indexes fit in memory\n\n## Scaling with pgvectorscale\n\nFor large datasets (10M+ vectors) or workloads with selective metadata filters, consider [pgvectorscale](https://github.com/timescale/pgvectorscale)'s StreamingDiskANN index instead of HNSW for the semantic search component.\n\n**When to use StreamingDiskANN:**\n- Large datasets where HNSW doesn't fit in memory\n- Queries that filter by labels (e.g., tenant_id, category, tags)\n- When you need high-performance filtered vector search\n\n**Label-based filtering:** StreamingDiskANN supports filtered indexes on `smallint[]` label columns. Labels are indexed alongside vectors, enabling efficient filtered search without post-filtering accuracy loss.\n\n```sql\n-- Enable pgvectorscale (in addition to pgvector)\nCREATE EXTENSION IF NOT EXISTS vectorscale;\n\n-- Table with label column for filtering\nCREATE TABLE documents (\n id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n content TEXT NOT NULL,\n embedding halfvec(1536) NOT NULL,\n labels smallint[] NOT NULL -- e.g., category IDs, tenant IDs\n);\n\n-- StreamingDiskANN index with label filtering\nCREATE INDEX ON documents USING diskann (embedding vector_cosine_ops, labels);\n\n-- BM25 index for keyword search\nCREATE INDEX ON documents USING bm25 (content) WITH (text_config = 'english');\n\n-- Filtered semantic search using && (array overlap)\nSELECT id, content FROM documents\nWHERE labels && ARRAY[1, 3]::smallint[]\nORDER BY embedding <=> $1::halfvec(1536) LIMIT 50;\n```\n\nSee the [pgvectorscale documentation](https://github.com/timescale/pgvectorscale) for more details on filtered indexes and tuning parameters.\n\n## Monitoring & Debugging\n\n```sql\n-- Force index usage for verification (planner may prefer seqscan on small tables)\nSET enable_seqscan = off;\n\n-- Verify BM25 index is used\nEXPLAIN SELECT id, content FROM documents ORDER BY content <@> 'search text' LIMIT 10;\n-- Look for: Index Scan using ... (bm25)\n\n-- Verify HNSW index is used\nEXPLAIN SELECT id, content FROM documents ORDER BY embedding <=> '[0.1, 0.2, ...]'::halfvec(1536) LIMIT 10;\n-- Look for: Index Scan using ... (hnsw)\n\nSET enable_seqscan = on; -- Re-enable for normal operation\n\n-- Check index sizes\nSELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) AS size\nFROM pg_indexes WHERE tablename = 'documents';\n```\n\nIf EXPLAIN still shows sequential scans with `enable_seqscan = off`, verify indexes exist and queries use correct operators (`<@>` for BM25, `<=>` for cosine). For more pgvector debugging guidance, see the **pgvector-semantic-search** skill.\n\n## Common Issues\n\n| Symptom | Likely Cause | Fix |\n|---------|--------------|-----|\n| Missing exact matches | Keyword search not returning them | Check BM25 index exists; verify text_config matches content language |\n| Poor semantic results | Embedding model mismatch | Ensure query embedding uses same model as stored embeddings |\n| Slow queries | Large candidate pools or missing indexes | Reduce inner LIMIT; verify both indexes exist and are used (EXPLAIN) |\n| Skewed results | One method dominating | Adjust RRF weights; verify both searches return reasonable candidates |","schemaVersion":1},"repoUrl":"https://github.com/timescale/pg-aiguide/tree/main/skills/postgres-hybrid-text-search","tags":["ai","ai-agents","ai-coding","claude-code-plugin","claude-code-plugins","claude-code-plugins-marketplace","claude-marketplace","claude-plugin","claude-skills","docs","documentation","mcp"],"stats":{"installVelocity7d":0,"retentionRate":0,"executions":0,"rating":null},"origin":"github","source":{"repo":"pg-aiguide","audit":{"files":["bun.lock","package.json"],"binaries":[],"findings":[],"packages":9,"auditedAt":"2026-09-25T11:52:20.587Z","lockfiles":["bun.lock"]},"forks":110,"owner":"timescale","stars":1847,"topics":["ai","ai-agents","ai-coding","claude-code-plugin","claude-code-plugins","claude-code-plugins-marketplace","claude-marketplace","claude-plugin","claude-skills","docs","documentation","mcp","mcp-server","postgres","postgresql","skills"],"license":"Apache-2.0","fullName":"timescale/pg-aiguide","homepage":null,"language":"Python","pushedAt":"2026-09-24T20:55:47Z","avatarUrl":"https://avatars.githubusercontent.com/u/8986001?v=4","crawledAt":"2026-09-25T11:52:15.055Z","openIssues":11,"manifestFile":"SKILL.md","manifestPath":"skills/postgres-hybrid-text-search/SKILL.md","defaultBranch":"main"},"readme":"# Hybrid Text Search\n\nHybrid search combines keyword search (BM25) with semantic search (vector embeddings) to get the best of both: exact keyword matching and meaning-based retrieval. Use Reciprocal Rank Fusion (RRF) to merge results from both methods into a single ranked list.\n\nThis guide covers combining [pg_textsearch](https://github.com/timescale/pg_textsearch) (BM25) with [pgvector](https://github.com/pgvector/pgvector). Requires both extensions. For high-volume setups, filtering, or advanced pgvector tuning (binary quantization, HNSW parameters), see the **pgvector-semantic-search** skill.\n\npg_textsearch is a new BM25 text search extension for PostgreSQL, fully open-source and available hosted on Tiger Cloud as well as for self-managed deployments. It provides true BM25 ranking, which often improves relevance compared to PostgreSQL's built-in ts_rank and can offer better performance at scale. Note: pg_textsearch is currently in prerelease and not yet recommended for production use. pg_textsearch currently supports PostgreSQL 17 and 18.\n\n## When to Use Hybrid Search\n\n- **Use hybrid** when queries mix specific terms (product names, codes, proper nouns) with conceptual intent\n- **Use semantic only** when meaning matters more than exact wording (e.g., \"how to fix slow queries\" should match \"query optimization\")\n- **Use keyword only** when exact matches are critical (e.g., error codes, SKUs, legal citations)\n\nHybrid search typically improves recall over either method alone, at the cost of slightly more complexity.\n\n## Data Preparation\n\nChunk your documents into smaller pieces (typically 500–1000 tokens) and store each chunk with its embedding. Both BM25 and semantic search operate on the same chunks—this keeps fusion simple since you're comparing like with like.\n\n## Golden Path (Default Setup)\n\n```sql\n-- Enable extensions\nCREATE EXTENSION IF NOT EXISTS vector;\nCREATE EXTENSION IF NOT EXISTS pg_textsearch;\n\n-- Table with both indexes\nCREATE TABLE documents (\n id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n content TEXT NOT NULL,\n embedding halfvec(1536) NOT NULL\n);\n\n-- BM25 index for keyword search\nCREATE INDEX ON documents USING bm25 (content) WITH (text_config = 'english');\n\n-- HNSW index for semantic search\nCREATE INDEX ON documents USING hnsw (embedding halfvec_cosine_ops);\n```\n\n### BM25 Notes\n\n- **Negative scores**: The `<@>` operator returns negative values where lower = better match. RRF uses rank position, so this doesn't affect fusion.\n- **Language config**: Change `text_config` to match your content language (e.g., `'french'`, `'german'`). See [PostgreSQL text search configurations](https://www.postgresql.org/docs/current/textsearch-configuration.html).\n- **Tuning**: BM25 has `k1` (term frequency saturation, default 1.2) and `b` (length normalization, default 0.75) parameters. Defaults work well; only tune if relevance is poor.\n ```sql\n CREATE INDEX ON documents USING bm25 (content) WITH (text_config = 'english', k1 = 1.5, b = 0.8);\n ```\n- **Partitioned tables**: Each partition maintains local statistics. Scores are not directly comparable across partitions—query individual partitions when score comparability matters.\n\n## RRF Query Pattern\n\nReciprocal Rank Fusion combines rankings from multiple searches. Each result's score is `1 / (k + rank)` where `k` is a constant (typically 60). Results are summed across searches and re-sorted.\n\n**Run both queries in parallel from your client** for lower latency, then fuse results client-side:\n\n```sql\n-- Query 1: Keyword search (BM25)\n-- $1: search text\nSELECT id, content FROM documents ORDER BY content <@> $1 LIMIT 50;\n```\n\n```sql\n-- Query 2: Semantic search (separate query, run in parallel)\n-- $1: embedding of your search text as halfvec(1536)\nSELECT id, content FROM documents ORDER BY embedding <=> $1::halfvec(1536) LIMIT 50;\n```\n\n```python\n# Client-side RRF fusion (Python)\ndef rrf_fusion(keyword_results, semantic_results, k=60, limit=10):\n scores = {}\n conte","createdAt":"2026-09-25T11:52:20.688Z","updatedAt":"2026-09-25T11:52:20.688Z"},{"id":"cmugwiaxn01uequ06dqmcufkq","slug":"timescale-pg-aiguide-postgres","name":"postgres","description":"Use this skill for any PostgreSQL database work — table design, indexing, data types, constraints, extensions (pgvector, PostGIS, TimescaleDB), search, and migrations. **Trigger when user asks to:** - Design or modify PostgreSQL tables, schemas, or data models - Choose data types, constraints, indexes, or partitioning strategies - Work with pgvector embeddings, semantic search, or RAG - Set up full-text search, hybrid search, or BM25 ranking - Use PostGIS for spatial/geographic data - Set up TimescaleDB hypertables for time-series data - Migrate tables to hypertables or evaluate migration candidates - Plan or execute safe schema migrations with zero downtime **Keywords:** PostgreSQL, Postgres, SQL, schema, table design, indexes, constraints, pgvector, PostGIS, TimescaleDB, hypertable, semantic search, hybrid search, BM25, time-series, migration","authorId":"gh:timescale","authorName":"timescale","version":"0.1.0","category":"Prompt","securityLevel":"Community","downloadsCount":0,"githubStars":1847,"pricePerCall":0,"manifest":{"name":"postgres","tools":[],"category":"Prompt","entrypoint":{"type":"prompt"},"description":"Use this skill for any PostgreSQL database work — table design, indexing, data types, constraints, extensions (pgvector, PostGIS, TimescaleDB), search, and migrations. **Trigger when user asks to:** - Design or modify PostgreSQL tables, schemas, or data models - Choose data types, constraints, indexes, or partitioning strategies - Work with pgvector embeddings, semantic search, or RAG - Set up full-text search, hybrid search, or BM25 ranking - Use PostGIS for spatial/geographic data - Set up TimescaleDB hypertables for time-series data - Migrate tables to hypertables or evaluate migration candidates - Plan or execute safe schema migrations with zero downtime **Keywords:** PostgreSQL, Postgres, SQL, schema, table design, indexes, constraints, pgvector, PostGIS, TimescaleDB, hypertable, semantic search, hybrid search, BM25, time-series, migration","permissions":[],"systemPrompt":"# PostgreSQL Expert Skills\n\nThis skill provides comprehensive PostgreSQL expertise through specialized references. Load the appropriate reference based on the task.\n\n## Available References\n\n### Table Design\n- **[design-postgres-tables](references/design-postgres-tables.md)** — Data types, constraints, indexes, JSONB patterns, partitioning, and PostgreSQL best practices. **Use for any general table/schema design task.**\n- **[design-postgis-tables](references/design-postgis-tables.md)** — PostGIS spatial table design: geometry vs geography types, SRIDs, spatial indexing, and location-based query patterns. **Use when the task involves geographic or spatial data.**\n\n### Search\n- **[pgvector-semantic-search](references/pgvector-semantic-search.md)** — Vector similarity search with pgvector: HNSW/IVFFlat indexes, halfvec storage, quantization, filtered search, and tuning. **Use for embeddings, RAG, or semantic search.**\n- **[postgres-hybrid-text-search](references/postgres-hybrid-text-search.md)** — Hybrid search combining BM25 keyword search with pgvector semantic search using RRF. **Use when combining keyword and meaning-based search.**\n\n### TimescaleDB\n- **[setup-timescaledb-hypertables](references/setup-timescaledb-hypertables.md)** — Hypertable creation, compression, retention policies, continuous aggregates, and indexes. **Use when setting up TimescaleDB from scratch.**\n- **[find-hypertable-candidates](references/find-hypertable-candidates.md)** — SQL queries to analyze existing tables and score them for hypertable conversion. **Use when evaluating which tables to migrate.**\n- **[migrate-postgres-tables-to-hypertables](references/migrate-postgres-tables-to-hypertables.md)** — Step-by-step migration: partition column selection, in-place vs blue-green, validation. **Use when executing a migration.**\n\n### Migrations\n- **[postgres-database-migration](references/postgres-database-migration.md)** — DDL lock reference, safe migration patterns, timeout strategies, rollback planning, and fork-based testing. **Use when planning or executing schema changes on production databases.**\n\n## How to Use\n\n1. Identify which reference matches the user's task from the descriptions above.\n2. Load the reference file to get detailed instructions and SQL patterns.\n3. For tasks spanning multiple areas (e.g., \"design a table with vector search\"), load multiple references as needed.","schemaVersion":1},"repoUrl":"https://github.com/timescale/pg-aiguide/tree/main/skills/postgres","tags":["ai","ai-agents","ai-coding","claude-code-plugin","claude-code-plugins","claude-code-plugins-marketplace","claude-marketplace","claude-plugin","claude-skills","docs","documentation","mcp"],"stats":{"installVelocity7d":0,"retentionRate":0,"executions":0,"rating":null},"origin":"github","source":{"repo":"pg-aiguide","audit":{"files":["bun.lock","package.json"],"binaries":[],"findings":[],"packages":9,"auditedAt":"2026-09-25T11:52:20.587Z","lockfiles":["bun.lock"]},"forks":110,"owner":"timescale","stars":1847,"topics":["ai","ai-agents","ai-coding","claude-code-plugin","claude-code-plugins","claude-code-plugins-marketplace","claude-marketplace","claude-plugin","claude-skills","docs","documentation","mcp","mcp-server","postgres","postgresql","skills"],"license":"Apache-2.0","fullName":"timescale/pg-aiguide","homepage":null,"language":"Python","pushedAt":"2026-09-24T20:55:47Z","avatarUrl":"https://avatars.githubusercontent.com/u/8986001?v=4","crawledAt":"2026-09-25T11:52:15.055Z","openIssues":11,"manifestFile":"SKILL.md","manifestPath":"skills/postgres/SKILL.md","defaultBranch":"main"},"readme":"# PostgreSQL Expert Skills\n\nThis skill provides comprehensive PostgreSQL expertise through specialized references. Load the appropriate reference based on the task.\n\n## Available References\n\n### Table Design\n- **[design-postgres-tables](references/design-postgres-tables.md)** — Data types, constraints, indexes, JSONB patterns, partitioning, and PostgreSQL best practices. **Use for any general table/schema design task.**\n- **[design-postgis-tables](references/design-postgis-tables.md)** — PostGIS spatial table design: geometry vs geography types, SRIDs, spatial indexing, and location-based query patterns. **Use when the task involves geographic or spatial data.**\n\n### Search\n- **[pgvector-semantic-search](references/pgvector-semantic-search.md)** — Vector similarity search with pgvector: HNSW/IVFFlat indexes, halfvec storage, quantization, filtered search, and tuning. **Use for embeddings, RAG, or semantic search.**\n- **[postgres-hybrid-text-search](references/postgres-hybrid-text-search.md)** — Hybrid search combining BM25 keyword search with pgvector semantic search using RRF. **Use when combining keyword and meaning-based search.**\n\n### TimescaleDB\n- **[setup-timescaledb-hypertables](references/setup-timescaledb-hypertables.md)** — Hypertable creation, compression, retention policies, continuous aggregates, and indexes. **Use when setting up TimescaleDB from scratch.**\n- **[find-hypertable-candidates](references/find-hypertable-candidates.md)** — SQL queries to analyze existing tables and score them for hypertable conversion. **Use when evaluating which tables to migrate.**\n- **[migrate-postgres-tables-to-hypertables](references/migrate-postgres-tables-to-hypertables.md)** — Step-by-step migration: partition column selection, in-place vs blue-green, validation. **Use when executing a migration.**\n\n### Migrations\n- **[postgres-database-migration](references/postgres-database-migration.md)** — DDL lock reference, safe migration patterns, timeout strategies, rollback planning, and fork-based testing. **Use when planning or executing schema changes on production databases.**\n\n## How to Use\n\n1. Identify which reference matches the user's task from the descriptions above.\n2. Load the reference file to get detailed instructions and SQL patterns.\n3. For tasks spanning multiple areas (e.g., \"design a table with vector search\"), load multiple references as needed.","createdAt":"2026-09-25T11:52:20.699Z","updatedAt":"2026-09-25T11:52:20.699Z"},{"id":"cmugwiaxy01uhqu06rmj43kk9","slug":"timescale-pg-aiguide-setup-timescaledb-hypertables","name":"setup-timescaledb-hypertables","description":"Use this skill when creating database schemas or tables for Timescale, TimescaleDB, TigerData, or Tiger Cloud, especially for time-series, IoT, metrics, events, or log data. Use this to improve the performance of any insert-heavy table. **Trigger when user asks to:** - Create or design SQL schemas/tables AND Timescale/TimescaleDB/TigerData/Tiger Cloud is available - Set up hypertables, compression, retention policies, or continuous aggregates - Configure partition columns, segment_by, order_by, or chunk intervals - Optimize time-series database performance or storage - Create tables for sensors, metrics, telemetry, events, or transaction logs **Keywords:** CREATE TABLE, hypertable, Timescale, TimescaleDB, time-series, IoT, metrics, sensor data, compression policy, continuous aggregates, columnstore, retention policy, chunk interval, segment_by, order_by Step-by-step instructions for hypertable creation, column selection, compression policies, retention, continuous aggregates, and indexes.","authorId":"gh:timescale","authorName":"timescale","version":"0.1.0","category":"Prompt","securityLevel":"Community","downloadsCount":0,"githubStars":1847,"pricePerCall":0,"manifest":{"name":"setup-timescaledb-hypertables","tools":[],"category":"Prompt","entrypoint":{"type":"prompt"},"description":"Use this skill when creating database schemas or tables for Timescale, TimescaleDB, TigerData, or Tiger Cloud, especially for time-series, IoT, metrics, events, or log data. Use this to improve the performance of any insert-heavy table. **Trigger when user asks to:** - Create or design SQL schemas/tables AND Timescale/TimescaleDB/TigerData/Tiger Cloud is available - Set up hypertables, compression, retention policies, or continuous aggregates - Configure partition columns, segment_by, order_by, or chunk intervals - Optimize time-series database performance or storage - Create tables for sensors, metrics, telemetry, events, or transaction logs **Keywords:** CREATE TABLE, hypertable, Timescale, TimescaleDB, time-series, IoT, metrics, sensor data, compression policy, continuous aggregates, columnstore, retention policy, chunk interval, segment_by, order_by Step-by-step instructions for hypertable creation, column selection, compression policies, retention, continuous aggregates, and indexes.","permissions":[],"systemPrompt":"# TimescaleDB Complete Setup\n\nInstructions for insert-heavy data patterns where data is inserted but rarely changed:\n\n- **Time-series data** (sensors, metrics, system monitoring)\n- **Event logs** (user events, audit trails, application logs)\n- **Transaction records** (orders, payments, financial transactions)\n- **Sequential data** (records with auto-incrementing IDs and timestamps)\n- **Append-only datasets** (immutable records, historical data)\n\n## Step 1: Create Hypertable\n\n```sql\nCREATE TABLE your_table_name (\n timestamp TIMESTAMPTZ NOT NULL,\n entity_id TEXT NOT NULL, -- device_id, user_id, symbol, etc.\n category TEXT, -- sensor_type, event_type, asset_class, etc.\n value_1 DOUBLE PRECISION, -- price, temperature, latency, etc.\n value_2 DOUBLE PRECISION, -- volume, humidity, throughput, etc.\n value_3 INTEGER, -- count, status, level, etc.\n metadata JSONB -- flexible additional data\n) WITH (\n tsdb.hypertable,\n tsdb.partition_column='timestamp',\n tsdb.enable_columnstore=true, -- Disable if table has vector columns\n tsdb.segmentby='entity_id', -- See selection guide below\n tsdb.orderby='timestamp DESC', -- See selection guide below\n tsdb.sparse_index='minmax(value_1),minmax(value_2),minmax(value_3)' -- see selection guide below\n);\n```\n\n### Compression Decision\n\n- **Enable by default** for insert-heavy patterns\n- **Disable** if table has vector type columns (pgvector) - indexes on vector columns incompatible with columnstore\n\n### Partition Column Selection\n\nMust be time-based (TIMESTAMP/TIMESTAMPTZ/DATE) or integer (INT/BIGINT) with good temporal/sequential distribution.\n\n**Common patterns:**\n\n- TIME-SERIES: `timestamp`, `event_time`, `measured_at`\n- EVENT LOGS: `event_time`, `created_at`, `logged_at`\n- TRANSACTIONS: `created_at`, `transaction_time`, `processed_at`\n- SEQUENTIAL: `id` (auto-increment when no timestamp), `sequence_number`\n- APPEND-ONLY: `created_at`, `inserted_at`, `id`\n\n**Less ideal:** `ingested_at` (when data entered system - use only if it's your primary query dimension)\n**Avoid:** `updated_at` (breaks time ordering unless it's primary query dimension)\n\n### Segment_By Column Selection\n\n**PREFER SINGLE COLUMN** - multi-column rarely optimal. Multi-column can only work for highly correlated columns (e.g., metric_name + metric_type) with sufficient row density.\n\n**Requirements:**\n\n- Frequently used in WHERE clauses (most common filter)\n- Good row density (>100 rows per value per chunk)\n- Primary logical partition/grouping\n\n**Examples:**\n\n- IoT: `device_id`\n- Finance: `symbol`\n- Metrics: `service_name`, `service_name, metric_type` (if sufficient row density), `metric_name, metric_type` (if sufficient row density)\n- Analytics: `user_id` if sufficient row density, otherwise `session_id`\n- E-commerce: `product_id` if sufficient row density, otherwise `category_id`\n\n**Row density guidelines:**\n\n- Target: >100 rows per segment_by value within each chunk.\n- Poor: <10 rows per segment_by value per chunk → choose less granular column\n- What to do with low-density columns: prepend to order_by column list.\n\n**Query pattern drives choice:**\n\n```sql\nSELECT * FROM table WHERE entity_id = 'X' AND timestamp > ...\n-- ↳ segment_by: entity_id (if >100 rows per chunk)\n```\n\n**Avoid:** timestamps, unique IDs, low-density columns (<100 rows/value/chunk), columns rarely used in filtering\n\n### Order_By Column Selection\n\nCreates natural time-series progression when combined with segment_by for optimal compression.\n\n**Most common:** `timestamp DESC`\n\n**Examples:**\n\n- IoT/Finance/E-commerce: `timestamp DESC`\n- Metrics: `metric_name, timestamp DESC` (if metric_name has too low density for segment_by)\n- Analytics: `user_id, timestamp DESC` (user_id has too low density for segment_by)\n\n**Alternative patterns:**\n\n- `sequence_id DESC` for event streams with sequence numbers\n- `timestamp DESC, event_order DESC` for sub-ordering within same timestamp\n\n**Low-density column handling:**\nIf a column has <100 rows per chunk (too low for segment_by), prepend it to order_by:\n\n- Example: `metric_name` has 20 rows/chunk → use `segment_by='service_name'`, `order_by='metric_name, timestamp DESC'`\n- Groups similar values together (all temperature readings, then pressure readings) for better compression\n\n**Good test:** ordering created by `(segment_by_column, order_by_column)` should form a natural time-series progression. Values close to each other in the progression should be similar.\n\n**Avoid in order_by:** random columns, columns with high variance between adjacent rows, columns unrelated to segment_by\n\n### Compression Sparse Index Selection\n\n**Sparse indexes** enable query filtering on compressed data without decompression. Store metadata per batch (~1000 rows) to eliminate batches that don't match query predicates.\n\n**Types:**\n\n- **minmax:** Min/max values per batch - for range queries (>, <, BETWEEN) on numeric/temporal columns\n\n**Use minmax for:** price, temperature, measurement, timestamp (range filtering)\n\n**Use for:**\n\n- minmax for outlier detection (temperature > 90).\n- minmax for fields that are highly correlated with segmentby and orderby columns (e.g. if orderby includes `created_at`, minmax on `updated_at` is useful).\n\n**Avoid:** rarely filtered columns.\n\nIMPORTANT: NEVER index columns in segmentby or orderby. Orderby columns will always have minmax indexes without any configuration.\n\n**Configuration:**\nThe format is a comma-separated list of type_of_index(column_name).\n\n```sql\nALTER TABLE table_name SET (\n timescaledb.sparse_index = 'minmax(value_1),minmax(value_2)'\n);\n```\n\nExplicit configuration available since v2.22.0 (was auto-created since v2.16.0).\n\n### Chunk Time Interval (Optional)\n\nDefault: 7 days (use if volume unknown, or ask user). Adjust based on volume:\n\n- High frequency: 1 hour - 1 day\n- Medium: 1 day - 1 week\n- Low: 1 week - 1 month\n\n```sql\nSELECT set_chunk_time_interval('your_table_name', INTERVAL '1 day');\n```\n\n**Good test:** recent chunk indexes should fit in less than 25% of RAM.\n\n### Indexes & Primary Keys\n\nCommon index patterns - composite indexes on an id and timestamp:\n\n```sql\nCREATE INDEX idx_entity_timestamp ON your_table_name (entity_id, timestamp DESC);\n```\n\n**Important:** Only create indexes you'll actually use - each has maintenance overhead.\n\n**Primary key and unique constraints rules:** Must include partition column.\n\n**Option 1: Composite PK with partition column**\n\n```sql\nALTER TABLE your_table_name ADD PRIMARY KEY (entity_id, timestamp);\n```\n\n**Option 2: Single-column PK (only if it's the partition column)**\n\n```sql\nCREATE TABLE ... (id BIGINT PRIMARY KEY, ...) WITH (tsdb.partition_column='id');\n```\n\n**Option 3: No PK**: strict uniqueness is often not required for insert-heavy patterns.\n\n## Step 2: Compression Policy (Optional)\n\n**IMPORTANT**: If you used `tsdb.enable_columnstore=true` in Step 1, starting with TimescaleDB version 2.23 a columnstore policy is **automatically created** with `after => INTERVAL '7 days'`. You only need to call `add_columnstore_policy()` if you want to customize the `after` interval to something other than 7 days.\n\nSet `after` interval for when: data becomes mostly immutable (some updates/backfill OK) AND B-tree indexes aren't needed for queries (less common criterion).\n\n```sql\n-- In TimescaleDB 2.23 and later only needed if you want to override the default 7-day policy created by tsdb.enable_columnstore=true\n-- Remove the existing auto-created policy first:\n-- CALL remove_columnstore_policy('your_table_name');\n-- Then add custom policy:\n-- CALL add_columnstore_policy('your_table_name', after => INTERVAL '1 day');\n```\n\n## Step 3: Retention Policy\n\nIMPORTANT: Don't guess - ask user or comment out if unknown.\n\n```sql\n-- Example - replace with requirements or comment out\nSELECT add_retention_policy('your_table_name', INTERVAL '365 days');\n```\n\n## Step 4: Create Continuous Aggregates\n\nUse different aggregation intervals for different uses.\n\n### Short-term (Minutes/Hours)\n\nFor up-to-the-minute dashboards on high-frequency data.\n\n```sql\nCREATE MATERIALIZED VIEW your_table_hourly\nWITH (timescaledb.continuous) AS\nSELECT\n time_bucket(INTERVAL '1 hour', timestamp) AS bucket,\n entity_id,\n category,\n COUNT(*) as record_count,\n AVG(value_1) as avg_value_1,\n MIN(value_1) as min_value_1,\n MAX(value_1) as max_value_1,\n SUM(value_2) as sum_value_2\nFROM your_table_name\nGROUP BY bucket, entity_id, category;\n```\n\n### Long-term (Days/Weeks/Months)\n\nFor long-term reporting and analytics.\n\n```sql\nCREATE MATERIALIZED VIEW your_table_daily\nWITH (timescaledb.continuous) AS\nSELECT\n time_bucket(INTERVAL '1 day', timestamp) AS bucket,\n entity_id,\n category,\n COUNT(*) as record_count,\n AVG(value_1) as avg_value_1,\n MIN(value_1) as min_value_1,\n MAX(value_1) as max_value_1,\n PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value_1) as median_value_1,\n PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY value_1) as p95_value_1,\n SUM(value_2) as sum_value_2\nFROM your_table_name\nGROUP BY bucket, entity_id, category;\n```\n\n## Step 5: Aggregate Refresh Policies\n\nSet up refresh policies based on your data freshness requirements.\n\n**start_offset:** Usually omit (refreshes all). Exception: If you don't care about refreshing data older than X (see below). With retention policy on raw data: match the retention policy.\n\n**end_offset:** Set beyond active update window (e.g., 15 min if data usually arrives within 10 min). Data newer than end_offset won't appear in queries without real-time aggregation. If you don't know your update window, use the size of the time_bucket in the query, but not less than 5 minutes.\n\n**schedule_interval:** Set to the same value as the end_offset but not more than 1 hour.\n\n**Hourly - frequent refresh for dashboards:**\n\n```sql\nSELECT add_continuous_aggregate_policy('your_table_hourly',\n start_offset => NULL,\n end_offset => INTERVAL '15 minutes',\n schedule_interval => INTERVAL '15 minutes');\n```\n\n**Daily - less frequent for reports:**\n\n```sql\nSELECT add_continuous_aggregate_policy('your_table_daily',\n start_offset => NULL,\n end_offset => INTERVAL '1 hour',\n schedule_interval => INTERVAL '1 hour');\n```\n\n**Use start_offset only if you don't care about refreshing old data**\nUse for high-volume systems where query accuracy on older data doesn't matter:\n\n```sql\n-- the following aggregate can be stale for data older than 7 days\n-- SELECT add_continuous_aggregate_policy('aggregate_for_last_7_days',\n-- start_offset => INTERVAL '7 days', -- only refresh last 7 days (NULL = refresh all)\n-- end_offset => INTERVAL '15 minutes',\n-- schedule_interval => INTERVAL '15 minutes');\n```\n\nIMPORTANT: you MUST set a start_offset to be less than the retention policy on raw data. By default, set the start_offset equal to the retention policy.\nIf the retention policy is commented out, comment out the start_offset as well. like this:\n\n```sql\nSELECT add_continuous_aggregate_policy('your_table_daily',\n start_offset => NULL, -- Use NULL to refresh all data, or set to retention period if enabled on raw data\n-- start_offset => INTERVAL '', -- uncomment if retention policy is enabled on the raw data table\n end_offset => INTERVAL '1 hour',\n schedule_interval => INTERVAL '1 hour');\n```\n\n## Step 6: Real-Time Aggregation (Optional)\n\nReal-time combines materialized + recent raw data at query time. Provides up-to-date results at the cost of higher query latency.\n\nMore useful for fine-grained aggregates (e.g., minutely) than coarse ones (e.g., daily/monthly) since large buckets will be mostly incomplete with recent data anyway.\n\nDisabled by default in v2.13+, before that it was enabled by default.\n\n**Use when:** Need data newer than end_offset, up-to-minute dashboards, can tolerate higher query latency\n**Disable when:** Performance critical, refresh policies sufficient, high query volume, missing and stale data for recent data is acceptable\n\n**Enable for current results (higher query cost):**\n\n```sql\nALTER MATERIALIZED VIEW your_table_hourly SET (timescaledb.materialized_only = false);\n```\n\n**Disable for performance (but with stale results):**\n\n```sql\nALTER MATERIALIZED VIEW your_table_hourly SET (timescaledb.materialized_only = true);\n```\n\n## Step 7: Compress Aggregates\n\nRule: segment_by = ALL GROUP BY columns except time_bucket, order_by = time_bucket DESC\n\n```sql\n-- Hourly\nALTER MATERIALIZED VIEW your_table_hourly SET (\n timescaledb.enable_columnstore,\n timescaledb.segmentby = 'entity_id, category',\n timescaledb.orderby = 'bucket DESC'\n);\nCALL add_columnstore_policy('your_table_hourly', after => INTERVAL '3 days');\n\n-- Daily\nALTER MATERIALIZED VIEW your_table_daily SET (\n timescaledb.enable_columnstore,\n timescaledb.segmentby = 'entity_id, category',\n timescaledb.orderby = 'bucket DESC'\n);\nCALL add_columnstore_policy('your_table_daily', after => INTERVAL '7 days');\n```\n\n## Step 8: Aggregate Retention\n\nAggregates are typically kept longer than raw data.\nIMPORTANT: Don't guess - ask user or you **MUST comment out if unknown**.\n\n```sql\n-- Example - replace or comment out\nSELECT add_retention_policy('your_table_hourly', INTERVAL '2 years');\nSELECT add_retention_policy('your_table_daily', INTERVAL '5 years');\n```\n\n## Step 9: Performance Indexes on Continuous Aggregates\n\n**Index strategy:** Analyze WHERE clauses in common queries → Create indexes matching filter columns + time ordering\n\n**Pattern:** `(filter_column, bucket DESC)` supports `WHERE filter_column = X AND bucket >= Y ORDER BY bucket DESC`\n\nExamples:\n\n```sql\nCREATE INDEX idx_hourly_entity_bucket ON your_table_hourly (entity_id, bucket DESC);\nCREATE INDEX idx_hourly_category_bucket ON your_table_hourly (category, bucket DESC);\n```\n\n**Multi-column filters:** Create composite indexes for `WHERE entity_id = X AND category = Y`:\n\n```sql\nCREATE INDEX idx_hourly_entity_category_bucket ON your_table_hourly (entity_id, category, bucket DESC);\n```\n\n**Important:** Only create indexes you'll actually use - each has maintenance overhead.\n\n## Step 10: Optional Enhancements\n\n### Space Partitioning (NOT RECOMMENDED)\n\nOnly for query patterns where you ALWAYS filter by the space-partition column with expert knowledge and extensive benchmarking. STRONGLY prefer time-only partitioning.\n\n## Step 11: Verify Configuration\n\n```sql\n-- Check hypertable\nSELECT * FROM timescaledb_information.hypertables\nWHERE hypertable_name = 'your_table_name';\n\n-- Check compression settings\nSELECT * FROM hypertable_compression_stats('your_table_name');\n\n-- Check aggregates\nSELECT * FROM timescaledb_information.continuous_aggregates;\n\n-- Check policies\nSELECT * FROM timescaledb_information.jobs ORDER BY job_id;\n\n-- Monitor chunk information\nSELECT\n chunk_name,\n range_start,\n range_end,\n is_compressed\nFROM timescaledb_information.chunks\nWHERE hypertable_name = 'your_table_name'\nORDER BY range_start DESC;\n```\n\n## Performance Guidelines\n\n- **Chunk size:** Recent chunk indexes should fit in less than 25% of RAM\n- **Compression:** Expect 90%+ reduction (10x) with proper columnstore config\n- **Query optimization:** Use continuous aggregates for historical queries and dashboards\n- **Memory:** Run `timescaledb-tune` for self-hosting (auto-configured on cloud)\n\n## Schema Best Practices\n\n### Do's and Don'ts\n\n- ✅ Use `TIMESTAMPTZ` NOT `timestamp`\n- ✅ Use `>=` and `<` NOT `BETWEEN` for timestamps\n- ✅ Use `TEXT` with constraints NOT `char(n)`/`varchar(n)`\n- ✅ Use `snake_case` NOT `CamelCase`\n- ✅ Use `BIGINT GENERATED ALWAYS AS IDENTITY` NOT `SERIAL`\n- ✅ Use `BIGINT` for IDs by default over `INTEGER` or `SMALLINT`\n- ✅ Use `DOUBLE PRECISION` by default over `REAL`/`FLOAT`\n- ✅ Use `NUMERIC` NOT `MONEY`\n- ✅ Use `NOT EXISTS` NOT `NOT IN`\n- ✅ Use `time_bucket()` or `date_trunc()` NOT `timestamp(0)` for truncation\n\n## API Reference (Current vs Deprecated)\n\n**Deprecated Parameters → New Parameters:**\n\n- `timescaledb.compress` → `timescaledb.enable_columnstore`\n- `timescaledb.compress_segmentby` → `timescaledb.segmentby`\n- `timescaledb.compress_orderby` → `timescaledb.orderby`\n\n**Deprecated Functions → New Functions:**\n\n- `add_compression_policy()` → `add_columnstore_policy()`\n- `remove_compression_policy()` → `remove_columnstore_policy()`\n- `compress_chunk()` → `convert_to_columnstore()` (use with `CALL`, not `SELECT`)\n- `decompress_chunk()` → `convert_to_rowstore()` (use with `CALL`, not `SELECT`)\n\n**Compression Stats (use functions, not views):**\n\n- Use function: `hypertable_compression_stats('table_name')`\n- Use function: `chunk_compression_stats('_timescaledb_internal._hyper_X_Y_chunk')`\n- Note: Views like `columnstore_settings` may not be available in all versions; use functions instead\n\n**Manual Compression Example:**\n\n```sql\n-- Compress a specific chunk\nCALL convert_to_columnstore('_timescaledb_internal._hyper_7_1_chunk');\n\n-- Check compression statistics\nSELECT\n number_compressed_chunks,\n pg_size_pretty(before_compression_total_bytes) as before_compression,\n pg_size_pretty(after_compression_total_bytes) as after_compression,\n ROUND(100.0 * (1 - after_compression_total_bytes::numeric / NULLIF(before_compression_total_bytes, 0)), 1) as compression_pct\nFROM hypertable_compression_stats('your_table_name');\n```\n\n## Questions to Ask User\n\n1. What kind of data will you be storing?\n2. How do you expect to use the data?\n3. What queries will you run?\n4. How long to keep the data?\n5. Column types if unclear","schemaVersion":1},"repoUrl":"https://github.com/timescale/pg-aiguide/tree/main/skills/setup-timescaledb-hypertables","tags":["ai","ai-agents","ai-coding","claude-code-plugin","claude-code-plugins","claude-code-plugins-marketplace","claude-marketplace","claude-plugin","claude-skills","docs","documentation","mcp"],"stats":{"installVelocity7d":0,"retentionRate":0,"executions":0,"rating":null},"origin":"github","source":{"repo":"pg-aiguide","audit":{"files":["bun.lock","package.json"],"binaries":[],"findings":[],"packages":9,"auditedAt":"2026-09-25T11:52:20.587Z","lockfiles":["bun.lock"]},"forks":110,"owner":"timescale","stars":1847,"topics":["ai","ai-agents","ai-coding","claude-code-plugin","claude-code-plugins","claude-code-plugins-marketplace","claude-marketplace","claude-plugin","claude-skills","docs","documentation","mcp","mcp-server","postgres","postgresql","skills"],"license":"Apache-2.0","fullName":"timescale/pg-aiguide","homepage":null,"language":"Python","pushedAt":"2026-09-24T20:55:47Z","avatarUrl":"https://avatars.githubusercontent.com/u/8986001?v=4","crawledAt":"2026-09-25T11:52:15.055Z","openIssues":11,"manifestFile":"SKILL.md","manifestPath":"skills/setup-timescaledb-hypertables/SKILL.md","defaultBranch":"main"},"readme":"# TimescaleDB Complete Setup\n\nInstructions for insert-heavy data patterns where data is inserted but rarely changed:\n\n- **Time-series data** (sensors, metrics, system monitoring)\n- **Event logs** (user events, audit trails, application logs)\n- **Transaction records** (orders, payments, financial transactions)\n- **Sequential data** (records with auto-incrementing IDs and timestamps)\n- **Append-only datasets** (immutable records, historical data)\n\n## Step 1: Create Hypertable\n\n```sql\nCREATE TABLE your_table_name (\n timestamp TIMESTAMPTZ NOT NULL,\n entity_id TEXT NOT NULL, -- device_id, user_id, symbol, etc.\n category TEXT, -- sensor_type, event_type, asset_class, etc.\n value_1 DOUBLE PRECISION, -- price, temperature, latency, etc.\n value_2 DOUBLE PRECISION, -- volume, humidity, throughput, etc.\n value_3 INTEGER, -- count, status, level, etc.\n metadata JSONB -- flexible additional data\n) WITH (\n tsdb.hypertable,\n tsdb.partition_column='timestamp',\n tsdb.enable_columnstore=true, -- Disable if table has vector columns\n tsdb.segmentby='entity_id', -- See selection guide below\n tsdb.orderby='timestamp DESC', -- See selection guide below\n tsdb.sparse_index='minmax(value_1),minmax(value_2),minmax(value_3)' -- see selection guide below\n);\n```\n\n### Compression Decision\n\n- **Enable by default** for insert-heavy patterns\n- **Disable** if table has vector type columns (pgvector) - indexes on vector columns incompatible with columnstore\n\n### Partition Column Selection\n\nMust be time-based (TIMESTAMP/TIMESTAMPTZ/DATE) or integer (INT/BIGINT) with good temporal/sequential distribution.\n\n**Common patterns:**\n\n- TIME-SERIES: `timestamp`, `event_time`, `measured_at`\n- EVENT LOGS: `event_time`, `created_at`, `logged_at`\n- TRANSACTIONS: `created_at`, `transaction_time`, `processed_at`\n- SEQUENTIAL: `id` (auto-increment when no timestamp), `sequence_number`\n- APPEND-ONLY: `created_at`, `inserted_at`, `id`\n\n**Less ideal:** `ingested_at` (when data entered system - use only if it's your primary query dimension)\n**Avoid:** `updated_at` (breaks time ordering unless it's primary query dimension)\n\n### Segment_By Column Selection\n\n**PREFER SINGLE COLUMN** - multi-column rarely optimal. Multi-column can only work for highly correlated columns (e.g., metric_name + metric_type) with sufficient row density.\n\n**Requirements:**\n\n- Frequently used in WHERE clauses (most common filter)\n- Good row density (>100 rows per value per chunk)\n- Primary logical partition/grouping\n\n**Examples:**\n\n- IoT: `device_id`\n- Finance: `symbol`\n- Metrics: `service_name`, `service_name, metric_type` (if sufficient row density), `metric_name, metric_type` (if sufficient row density)\n- Analytics: `user_id` if sufficient row density, otherwise `session_id`\n- E-commerce: `product_id` if sufficient row density, otherwise `category_id`\n\n**Row density guidelines:**\n\n- Target: >100 rows per segment_by value within each chunk.\n- Poor: <10 rows per segment_by value per chunk → choose less granular column\n- What to do with low-density columns: prepend to order_by column list.\n\n**Query pattern drives choice:**\n\n```sql\nSELECT * FROM table WHERE entity_id = 'X' AND timestamp > ...\n-- ↳ segment_by: entity_id (if >100 rows per chunk)\n```\n\n**Avoid:** timestamps, unique IDs, low-density columns (<100 rows/value/chunk), columns rarely used in filtering\n\n### Order_By Column Selection\n\nCreates natural time-series progression when combined with segment_by for optimal compression.\n\n**Most common:** `timestamp DESC`\n\n**Examples:**\n\n- IoT/Finance/E-commerce: `timestamp DESC`\n- Metrics: `metric_name, timestamp DESC` (if metric_name has too low density for segment_by)\n- Analytics: `user_id, timestamp DESC` (user_id has too low density for segment_by)\n\n**Alternative patterns:**\n\n- `sequence_id DESC` for event streams with sequence numbers\n- `timestamp DESC, event_order DESC` for su","createdAt":"2026-09-25T11:52:20.710Z","updatedAt":"2026-09-25T11:52:20.710Z"}],"total":12,"limit":24,"offset":0}
![\"Skill](\"docs/assets/logo.png\"){kind=logo}
\n![\"Fluxion](\"docs/assets/sponsors/fluxion-ai.png\")