Ready to supercharge your AI agents?

Install any skill in seconds. Free to start, no credit card required.

PostgreSQL Database Migrations · timescale | DecimalAI

Databy timescale

PostgreSQL Database Migrations

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 migr

Never made a case worseMaintained

SkillScore

SkillScore96/100

Test results 96

Safety

3/3 safety checks passed

Code scan
Intent
Content

What it changes

83%96%of test cases passed +13 pts

Without skill19 of 23

With skill22 of 23

3 fixed19 passed either way1 still failing0 broke

Measured on gemini-3.6-flash9/9/2026v221 comparable

Track record

Agent runs: 0; All time

See one task, with and without the skill

One test taskWe are planning a zero-downtime database migration on a PostgreSQL table containing over 20 million records. We need to introduce a new required column with default timestamps and modify an existing key structure while keeping our application online. Could you provide a multi-phase SQL migration plan and matching rollback strategy that prevents long locks and avoids blocking incoming read/write transactions?

Without the skillMissed

With the skillPassed

Same model, same task. Answers are capped and redacted.

Use this skill

Your agent runs on

Adds every file the skill has, pinned so you can update it. No account needed.

pip install "decimalai>=0.15"
decimalai skills add timescale-postgres-database-migration
claude --plugin-dir "$(git rev-parse --show-toplevel 2>/dev/null || pwd)/.agents"

The same install from Python, for a notebook or a setup script.

Install

pip install "decimalai>=0.15"

Add the skill

from decimalai import skillpkg

# Writes .agents/skills and .decimal/skills.toml. Safe to run twice.
result = skillpkg.add("timescale-postgres-database-migration")
if result.exit_code:
    raise SystemExit(result.problems)

print("Added. Start Claude Code with the command below.")

Start Claude Code with it

claude --plugin-dir "$(git rev-parse --show-toplevel 2>/dev/null || pwd)/.agents"

Type these in a Claude Code session. No account or API key. It writes the skill and any files it bundles, and asks first when its safety rating isn't Passed.

Add the DecimalAI plugin, once

/plugin marketplace add decimal-labs/decimalai-claude-plugins
/plugin install decimalai@decimalai

Install this skill

/decimalai:install timescale-postgres-database-migration

The plugin needs python3 and curl on your machine.

No Python or account. Claude Code picks it up in its next session.

Save it into your project

curl -fsSL --create-dirs -o .claude/skills/timescale-postgres-database-migration/SKILL.md https://app.decimal.ai/s/timescale-postgres-database-migration/SKILL.md

Copy SKILL.md

Save it in your project, as

.claude/skills/timescale-postgres-database-migration/SKILL.md

This gets SKILL.md only. The skill's 3 other files are not included.

Other ways to add it

Claude Code docs

agent_config() hands your agent the skill. With no DecimalAI key it reads your project's files and sends nothing. Needs SDK 0.27 or newer.

Install

pip install "decimalai[claude-agent-sdk]"
decimalai skills add timescale-postgres-database-migration

Add it to your agent

import asyncio

from claude_agent_sdk import ClaudeAgentOptions
from decimalai.claude_agent_sdk import agent_config

# With no DECIMAL_API_KEY this reads the skills your project lists, from disk.
support = agent_config("support", fallback="You are a helpful agent.")

async def main():
    question = "Hello!"
    async with support.run(question) as run:  # one user message, one run
        async for message in run.query(options=ClaudeAgentOptions(max_turns=6)):
            print(message)

# In a notebook or async code: await main()
asyncio.run(main())

Prefer its own loader? ClaudeAgentOptions(plugins=[skills_plugin()], skills="all") reads the same folder. Use one or the other: a skill delivered both ways reaches the prompt twice.

The same install from Python, for a notebook or a setup script. agent_config() hands your agent the skill. With no DecimalAI key it reads your project's files and sends nothing. Needs SDK 0.27 or newer.

Install

pip install "decimalai[claude-agent-sdk]"

Add it to your agent

from decimalai import skillpkg

# Writes .agents/skills and .decimal/skills.toml. Safe to run twice.
result = skillpkg.add("timescale-postgres-database-migration")
if result.exit_code:
    raise SystemExit(result.problems)

import asyncio

from claude_agent_sdk import ClaudeAgentOptions
from decimalai.claude_agent_sdk import agent_config

# With no DECIMAL_API_KEY this reads the skills your project lists, from disk.
support = agent_config("support", fallback="You are a helpful agent.")

async def main():
    question = "Hello!"
    async with support.run(question) as run:  # one user message, one run
        async for message in run.query(options=ClaudeAgentOptions(max_turns=6)):
            print(message)

# In a notebook or async code: await main()
asyncio.run(main())

Prefer its own loader? ClaudeAgentOptions(plugins=[skills_plugin()], skills="all") reads the same folder. Use one or the other: a skill delivered both ways reaches the prompt twice.

No DecimalAI package. The whole skill goes in every prompt, and nothing pins or updates it.

Copy SKILL.md

Save it in your project, as

.agents/skills/timescale-postgres-database-migration/SKILL.md

Give it to your agent

import asyncio
from pathlib import Path
from claude_agent_sdk import ClaudeAgentOptions, query

skill = Path(".agents/skills/timescale-postgres-database-migration/SKILL.md").read_text()
options = ClaudeAgentOptions(system_prompt=f"You are a helpful agent.\n\n{skill}", max_turns=6)

async def main():
    async for message in query(prompt="Hello!", options=options):
        print(message)

# In a notebook or async code: await main()
asyncio.run(main())

This gets SKILL.md only. The skill's 3 other files are not included.

Other ways to add it

Claude Agent SDK docs

agent_config() hands your agent the skill. With no DecimalAI key it reads your project's files and sends nothing. Needs SDK 0.27 or newer.

Install

pip install "decimalai[openai-agents]"
decimalai skills add timescale-postgres-database-migration

Add it to your agent

from agents import Agent, Runner
from decimalai.openai_agents import agent_config

# With no DECIMAL_API_KEY this reads the skills your project lists, from disk.
support = agent_config("support", fallback="You are a helpful agent.")

agent = Agent(
    name=support.name,
    instructions=support.instructions,  # your prompt, then the skill list
    tools=[*support.tools],             # your tools, then load_skill
)
# In a notebook or async code: result = await Runner.run(agent, "Hello!")
result = Runner.run_sync(agent, "Hello!")

Using SandboxAgent? capabilities=[Shell(), decimalai.openai_agents.sandbox_skills()] points its own loader at the same folder.

Install

pip install "decimalai[openai-agents]"

Add it to your agent

from decimalai import skillpkg

# Writes .agents/skills and .decimal/skills.toml. Safe to run twice.
result = skillpkg.add("timescale-postgres-database-migration")
if result.exit_code:
    raise SystemExit(result.problems)

from agents import Agent, Runner
from decimalai.openai_agents import agent_config

# With no DECIMAL_API_KEY this reads the skills your project lists, from disk.
support = agent_config("support", fallback="You are a helpful agent.")

agent = Agent(
    name=support.name,
    instructions=support.instructions,  # your prompt, then the skill list
    tools=[*support.tools],             # your tools, then load_skill
)
# In a notebook or async code: result = await Runner.run(agent, "Hello!")
result = Runner.run_sync(agent, "Hello!")

Using SandboxAgent? capabilities=[Shell(), decimalai.openai_agents.sandbox_skills()] points its own loader at the same folder.

No DecimalAI package. The whole skill goes in every prompt, and nothing pins or updates it.

Copy SKILL.md

Save it in your project, as

.agents/skills/timescale-postgres-database-migration/SKILL.md

Give it to your agent

from pathlib import Path
from agents import Agent, Runner

skill = Path(".agents/skills/timescale-postgres-database-migration/SKILL.md").read_text()
agent = Agent(name="support", instructions=f"You are a helpful agent.\n\n{skill}")
# In a notebook or async code: result = await Runner.run(agent, "Hello!")
result = Runner.run_sync(agent, "Hello!")

This gets SKILL.md only. The skill's 3 other files are not included.

Other ways to add it

OpenAI Agents docs

agent_config() hands your agent the skill. With no DecimalAI key it reads your project's files and sends nothing. Needs SDK 0.27 or newer.

Install

pip install "decimalai[openai]"
decimalai skills add timescale-postgres-database-migration

Add it to your agent

import json

from openai import OpenAI
from decimalai.openai import agent_config

client = OpenAI()
# With no DECIMAL_API_KEY this reads the skills your project lists, from disk.
support = agent_config("support", fallback="You are a helpful agent.")


def lookup_order(order_id: str) -> str:
    return f"Order {order_id} shipped yesterday."


# Your tools, as Chat Completions takes them, and what answers each.
TOOLS = [{"type": "function", "function": {
    "name": "lookup_order",
    "description": "Look up the status of an order.",
    "parameters": {"type": "object", "properties": {"order_id": {"type": "string"}},
                   "required": ["order_id"]},
}}]
FUNCTIONS = {"lookup_order": lookup_order}

question = "Where is order A1001?"
with support.run(question) as run:  # one user message, one run
    # run.system is your prompt, then the skill list.
    messages = [{"role": "system", "content": run.system}, run.message]
    while True:
        reply = client.chat.completions.create(
            model="gpt-5.4-mini",
            messages=messages,
            tools=[*TOOLS, *run.tools],  # your tools, then load_skill
        ).choices[0].message
        messages.append(reply)
        if not reply.tool_calls:
            break
        for call in reply.tool_calls:
            # load_skill is answered by the run; your own tools by FUNCTIONS.
            function = FUNCTIONS.get(call.function.name)
            messages.append(run.tool_result(call) or {
                "role": "tool", "tool_call_id": call.id,
                "content": function(**json.loads(call.function.arguments)) if function
                else f"There is no tool named {call.function.name!r}.",
            })
print(reply.content)

# The Responses API takes run.response_tools instead of run.tools.

Install

pip install "decimalai[openai]"

Add it to your agent

from decimalai import skillpkg

# Writes .agents/skills and .decimal/skills.toml. Safe to run twice.
result = skillpkg.add("timescale-postgres-database-migration")
if result.exit_code:
    raise SystemExit(result.problems)

import json

from openai import OpenAI
from decimalai.openai import agent_config

client = OpenAI()
# With no DECIMAL_API_KEY this reads the skills your project lists, from disk.
support = agent_config("support", fallback="You are a helpful agent.")


def lookup_order(order_id: str) -> str:
    return f"Order {order_id} shipped yesterday."


# Your tools, as Chat Completions takes them, and what answers each.
TOOLS = [{"type": "function", "function": {
    "name": "lookup_order",
    "description": "Look up the status of an order.",
    "parameters": {"type": "object", "properties": {"order_id": {"type": "string"}},
                   "required": ["order_id"]},
}}]
FUNCTIONS = {"lookup_order": lookup_order}

question = "Where is order A1001?"
with support.run(question) as run:  # one user message, one run
    # run.system is your prompt, then the skill list.
    messages = [{"role": "system", "content": run.system}, run.message]
    while True:
        reply = client.chat.completions.create(
            model="gpt-5.4-mini",
            messages=messages,
            tools=[*TOOLS, *run.tools],  # your tools, then load_skill
        ).choices[0].message
        messages.append(reply)
        if not reply.tool_calls:
            break
        for call in reply.tool_calls:
            # load_skill is answered by the run; your own tools by FUNCTIONS.
            function = FUNCTIONS.get(call.function.name)
            messages.append(run.tool_result(call) or {
                "role": "tool", "tool_call_id": call.id,
                "content": function(**json.loads(call.function.arguments)) if function
                else f"There is no tool named {call.function.name!r}.",
            })
print(reply.content)

# The Responses API takes run.response_tools instead of run.tools.

No DecimalAI package. The whole skill goes in every prompt, and nothing pins or updates it.

Copy SKILL.md

Save it in your project, as

.agents/skills/timescale-postgres-database-migration/SKILL.md

Give it to your agent

from pathlib import Path
from openai import OpenAI

client = OpenAI()
skill = Path(".agents/skills/timescale-postgres-database-migration/SKILL.md").read_text()
reply = client.chat.completions.create(
    model="gpt-5.4-mini",
    messages=[
        {"role": "system", "content": f"You are a helpful agent.\n\n{skill}"},
        {"role": "user", "content": "Hello!"},
    ],
).choices[0].message

This gets SKILL.md only. The skill's 3 other files are not included.

Other ways to add it

OpenAI SDK docs

agent_config() hands your agent the skill. With no DecimalAI key it reads your project's files and sends nothing. Needs SDK 0.27 or newer.

Install

pip install "decimalai[anthropic]"
decimalai skills add timescale-postgres-database-migration

Add it to your agent

import anthropic
from decimalai.anthropic import agent_config

client = anthropic.Anthropic()
# With no DECIMAL_API_KEY this reads the skills your project lists, from disk.
support = agent_config("support", fallback="You are a helpful agent.")

question = "Hello!"
with support.run(question) as run:  # one user message, one run
    answer = client.beta.messages.tool_runner(
        model="claude-haiku-5-5",
        max_tokens=4096,
        system=run.system,     # your prompt, then the skill list
        tools=[*run.tools],    # your tools, then load_skill
        messages=[{"role": "user", "content": question}],
    ).until_done()

Install

pip install "decimalai[anthropic]"

Add it to your agent

from decimalai import skillpkg

# Writes .agents/skills and .decimal/skills.toml. Safe to run twice.
result = skillpkg.add("timescale-postgres-database-migration")
if result.exit_code:
    raise SystemExit(result.problems)

import anthropic
from decimalai.anthropic import agent_config

client = anthropic.Anthropic()
# With no DECIMAL_API_KEY this reads the skills your project lists, from disk.
support = agent_config("support", fallback="You are a helpful agent.")

question = "Hello!"
with support.run(question) as run:  # one user message, one run
    answer = client.beta.messages.tool_runner(
        model="claude-haiku-5-5",
        max_tokens=4096,
        system=run.system,     # your prompt, then the skill list
        tools=[*run.tools],    # your tools, then load_skill
        messages=[{"role": "user", "content": question}],
    ).until_done()

No DecimalAI package. The whole skill goes in every prompt, and nothing pins or updates it.

Copy SKILL.md

Save it in your project, as

.agents/skills/timescale-postgres-database-migration/SKILL.md

Give it to your agent

from pathlib import Path
import anthropic

client = anthropic.Anthropic()
skill = Path(".agents/skills/timescale-postgres-database-migration/SKILL.md").read_text()
reply = client.messages.create(
    model="claude-haiku-5-5",
    max_tokens=4096,
    system=f"You are a helpful agent.\n\n{skill}",
    messages=[{"role": "user", "content": "Hello!"}],
)

This gets SKILL.md only. The skill's 3 other files are not included.

Other ways to add it

Anthropic docs

agent_config() hands your agent the skill. With no DecimalAI key it reads your project's files and sends nothing. Needs SDK 0.27 or newer.

Install

pip install "decimalai[google-genai]"
decimalai skills add timescale-postgres-database-migration

Add it to your agent

from google import genai
from google.genai import types
from decimalai.google_genai import agent_config

client = genai.Client()
# With no DECIMAL_API_KEY this reads the skills your project lists, from disk.
support = agent_config("support", fallback="You are a helpful agent.")


def lookup_order(order_id: str) -> str:
    return f"Order {order_id} shipped yesterday."


# Your functions, as declarations for the model, and what answers each.
TOOLS = [{"function_declarations": [{
    "name": "lookup_order",
    "description": "Look up the status of an order.",
    "parameters_json_schema": {"type": "object", "properties": {"order_id": {"type": "string"}},
                               "required": ["order_id"]},
}]}]
FUNCTIONS = {"lookup_order": lookup_order}

question = "Where is order A1001?"
with support.run(question) as run:  # one user message, one run
    config = types.GenerateContentConfig(
        system_instruction=run.system,     # your prompt, then the skill list
        tools=[*TOOLS, *run.tool_params],  # your functions, then load_skill
    )
    contents = [{"role": "user", "parts": [{"text": run.message}]}]
    while True:
        response = client.models.generate_content(
            model="gemini-3.5-flash", contents=contents, config=config,
        )
        if not response.function_calls:
            break
        contents.append(response.candidates[0].content)
        parts = []
        for call in response.function_calls:
            # load_skill is answered by the run; your own functions by FUNCTIONS.
            # A loop of your own rather than automatic function calling, which
            # raises KeyError when the model asks for a function you did not pass.
            function = FUNCTIONS.get(call.name)
            parts.append(run.tool_result(call) or {"function_response": {
                "name": call.name, "id": call.id,
                "response": {"result": function(**(call.args or {})) if function
                             else f"There is no function named {call.name!r}."},
            }})
        contents.append({"role": "user", "parts": parts})
print(response.text)

Install

pip install "decimalai[google-genai]"

Add it to your agent

from decimalai import skillpkg

# Writes .agents/skills and .decimal/skills.toml. Safe to run twice.
result = skillpkg.add("timescale-postgres-database-migration")
if result.exit_code:
    raise SystemExit(result.problems)

from google import genai
from google.genai import types
from decimalai.google_genai import agent_config

client = genai.Client()
# With no DECIMAL_API_KEY this reads the skills your project lists, from disk.
support = agent_config("support", fallback="You are a helpful agent.")


def lookup_order(order_id: str) -> str:
    return f"Order {order_id} shipped yesterday."


# Your functions, as declarations for the model, and what answers each.
TOOLS = [{"function_declarations": [{
    "name": "lookup_order",
    "description": "Look up the status of an order.",
    "parameters_json_schema": {"type": "object", "properties": {"order_id": {"type": "string"}},
                               "required": ["order_id"]},
}]}]
FUNCTIONS = {"lookup_order": lookup_order}

question = "Where is order A1001?"
with support.run(question) as run:  # one user message, one run
    config = types.GenerateContentConfig(
        system_instruction=run.system,     # your prompt, then the skill list
        tools=[*TOOLS, *run.tool_params],  # your functions, then load_skill
    )
    contents = [{"role": "user", "parts": [{"text": run.message}]}]
    while True:
        response = client.models.generate_content(
            model="gemini-3.5-flash", contents=contents, config=config,
        )
        if not response.function_calls:
            break
        contents.append(response.candidates[0].content)
        parts = []
        for call in response.function_calls:
            # load_skill is answered by the run; your own functions by FUNCTIONS.
            # A loop of your own rather than automatic function calling, which
            # raises KeyError when the model asks for a function you did not pass.
            function = FUNCTIONS.get(call.name)
            parts.append(run.tool_result(call) or {"function_response": {
                "name": call.name, "id": call.id,
                "response": {"result": function(**(call.args or {})) if function
                             else f"There is no function named {call.name!r}."},
            }})
        contents.append({"role": "user", "parts": parts})
print(response.text)

No DecimalAI package. The whole skill goes in every prompt, and nothing pins or updates it.

Copy SKILL.md

Save it in your project, as

.agents/skills/timescale-postgres-database-migration/SKILL.md

Give it to your agent

from pathlib import Path
from google import genai
from google.genai import types

client = genai.Client()
skill = Path(".agents/skills/timescale-postgres-database-migration/SKILL.md").read_text()
response = client.models.generate_content(
    model="gemini-3.5-flash",
    contents="Hello!",
    config=types.GenerateContentConfig(system_instruction=f"You are a helpful agent.\n\n{skill}"),
)

This gets SKILL.md only. The skill's 3 other files are not included.

Other ways to add it

Gemini API docs

agent_config() hands your agent the skill. With no DecimalAI key it reads your project's files and sends nothing. Needs SDK 0.27 or newer.

Install

pip install "decimalai[langchain]" langchain-openai
decimalai skills add timescale-postgres-database-migration

Add it to your agent

from langchain.agents import create_agent
from decimalai.langchain import agent_config

# With no DECIMAL_API_KEY this reads the skills your project lists, from disk.
support = agent_config("support", fallback="You are a helpful agent.")

agent = create_agent(
    "openai:gpt-5.4-mini",
    tools=[],                         # your tools; the middleware adds load_skill
    middleware=[support.middleware],  # your prompt, the skill list and load_skill
)
result = agent.invoke({"messages": [{"role": "user", "content": "Hello!"}]})

Install

pip install "decimalai[langchain]" langchain-openai

Add it to your agent

from decimalai import skillpkg

# Writes .agents/skills and .decimal/skills.toml. Safe to run twice.
result = skillpkg.add("timescale-postgres-database-migration")
if result.exit_code:
    raise SystemExit(result.problems)

from langchain.agents import create_agent
from decimalai.langchain import agent_config

# With no DECIMAL_API_KEY this reads the skills your project lists, from disk.
support = agent_config("support", fallback="You are a helpful agent.")

agent = create_agent(
    "openai:gpt-5.4-mini",
    tools=[],                         # your tools; the middleware adds load_skill
    middleware=[support.middleware],  # your prompt, the skill list and load_skill
)
result = agent.invoke({"messages": [{"role": "user", "content": "Hello!"}]})

No DecimalAI package. The whole skill goes in every prompt, and nothing pins or updates it.

Copy SKILL.md

Save it in your project, as

.agents/skills/timescale-postgres-database-migration/SKILL.md

Give it to your agent

from pathlib import Path
from langchain.agents import create_agent

skill = Path(".agents/skills/timescale-postgres-database-migration/SKILL.md").read_text()
agent = create_agent("openai:gpt-5.4-mini", tools=[], system_prompt=f"You are a helpful agent.\n\n{skill}")
result = agent.invoke({"messages": [{"role": "user", "content": "Hello!"}]})

This gets SKILL.md only. The skill's 3 other files are not included.

Other ways to add it

LangChain docs

CrewAI's own skill loader reads the installed folder. No DecimalAI account needed.

Install

pip install "decimalai>=0.16" "crewai>=1.15.27"
decimalai skills add timescale-postgres-database-migration

Add it to your agent

import decimalai.crewai
from crewai import Agent, Crew, Task

agent = Agent(
    role="Support", goal="Help the customer", backstory="A support agent.",
    llm=llm,                               # your own CrewAI LLM
    skills=decimalai.crewai.skills(),      # the installed folder
    tools=decimalai.crewai.skill_tools(),  # reads a skill's files, runs its scripts
)
task = Task(description="Hello!", expected_output="an answer", agent=agent)
Crew(agents=[agent], tasks=[task]).kickoff()

The same install from Python, for a notebook or a setup script. CrewAI's own skill loader reads the installed folder. No DecimalAI account needed.

Install

pip install "decimalai>=0.16" "crewai>=1.15.27"

Add it to your agent

from decimalai import skillpkg

# Writes .agents/skills and .decimal/skills.toml. Safe to run twice.
result = skillpkg.add("timescale-postgres-database-migration")
if result.exit_code:
    raise SystemExit(result.problems)

import decimalai.crewai
from crewai import Agent, Crew, Task

agent = Agent(
    role="Support", goal="Help the customer", backstory="A support agent.",
    llm=llm,                               # your own CrewAI LLM
    skills=decimalai.crewai.skills(),      # the installed folder
    tools=decimalai.crewai.skill_tools(),  # reads a skill's files, runs its scripts
)
task = Task(description="Hello!", expected_output="an answer", agent=agent)
Crew(agents=[agent], tasks=[task]).kickoff()

No DecimalAI package. CrewAI's own loader reads the folder, and nothing pins or updates it.

Copy SKILL.md

Save it in your project, as

.agents/skills/timescale-postgres-database-migration/SKILL.md

Give it to your agent

from pathlib import Path
from crewai import Agent, Crew, Task

agent = Agent(
    role="Support", goal="Help the customer", backstory="A support agent.",
    llm=llm,                         # your own CrewAI LLM
    skills=[Path(".agents/skills")],
)
task = Task(description="Hello!", expected_output="an answer", agent=agent)
Crew(agents=[agent], tasks=[task]).kickoff()

This gets SKILL.md only. The skill's 3 other files are not included.

Other ways to add it

CrewAI docs

agent_config() hands your agent the skill. With no DecimalAI key it reads your project's files and sends nothing. Needs SDK 0.27 or newer.

Install

pip install "decimalai[adk]"
decimalai skills add timescale-postgres-database-migration

Add it to your agent

from decimalai.adk import DecimalAIPlugin, agent_config
from google.adk.agents import LlmAgent
from google.adk.apps import App

# With no DECIMAL_API_KEY this reads the skills your project lists, from disk.
support = agent_config("support", fallback="You are a helpful agent.")

agent = LlmAgent(
    name="support",          # ADK needs a Python identifier
    model="gemini-3.5-flash",
    instruction=support.instruction,  # your prompt, then the skill list
    tools=[*support.tools],           # your tools, then load_skill
)
# DecimalAIPlugin makes each invocation of runners built from this App one trace.
app = App(name="support", root_agent=agent, plugins=[DecimalAIPlugin()])

Prefer ADK's own loader? SkillToolset(skills=decimalai.adk.skills(), ...) reads the same folder. Use one or the other: a skill delivered both ways reaches the prompt twice.

Install

pip install "decimalai[adk]"

Add it to your agent

from decimalai import skillpkg

# Writes .agents/skills and .decimal/skills.toml. Safe to run twice.
result = skillpkg.add("timescale-postgres-database-migration")
if result.exit_code:
    raise SystemExit(result.problems)

from decimalai.adk import DecimalAIPlugin, agent_config
from google.adk.agents import LlmAgent
from google.adk.apps import App

# With no DECIMAL_API_KEY this reads the skills your project lists, from disk.
support = agent_config("support", fallback="You are a helpful agent.")

agent = LlmAgent(
    name="support",          # ADK needs a Python identifier
    model="gemini-3.5-flash",
    instruction=support.instruction,  # your prompt, then the skill list
    tools=[*support.tools],           # your tools, then load_skill
)
# DecimalAIPlugin makes each invocation of runners built from this App one trace.
app = App(name="support", root_agent=agent, plugins=[DecimalAIPlugin()])

Prefer ADK's own loader? SkillToolset(skills=decimalai.adk.skills(), ...) reads the same folder. Use one or the other: a skill delivered both ways reaches the prompt twice.

No DecimalAI package. The whole skill goes in every prompt, and nothing pins or updates it.

Copy SKILL.md

Save it in your project, as

.agents/skills/timescale-postgres-database-migration/SKILL.md

Give it to your agent

from pathlib import Path
from google.adk.agents import LlmAgent

skill = Path(".agents/skills/timescale-postgres-database-migration/SKILL.md").read_text()
agent = LlmAgent(
    name="support",
    model="gemini-3.5-flash",
    instruction=lambda context: f"You are a helpful agent.\n\n{skill}",
)

This gets SKILL.md only. The skill's 3 other files are not included.

Other ways to add it

Google ADK docs

agent_config() hands your agent the skill. With no DecimalAI key it reads your project's files and sends nothing. Needs SDK 0.27 or newer.

Install

pip install "decimalai[pydantic-ai]"
decimalai skills add timescale-postgres-database-migration

Add it to your agent

from pydantic_ai import Agent
from decimalai.pydantic_ai import agent_config

# With no DECIMAL_API_KEY this reads the skills your project lists, from disk.
support = agent_config("support", fallback="You are a helpful agent.")

agent = Agent(
    "openai:gpt-4o-mini",
    name=support.name,
    tools=[],                 # your tools; the capability adds load_skill
    capabilities=[support],   # your prompt, the skill list and load_skill
)
# In a notebook or async code: result = await agent.run("Hello!")
result = agent.run_sync("Hello!")

On the Pydantic AI harness, decimalai.pydantic_ai_harness.skills(project) uses its own loader instead.

Install

pip install "decimalai[pydantic-ai]"

Add it to your agent

from decimalai import skillpkg

# Writes .agents/skills and .decimal/skills.toml. Safe to run twice.
result = skillpkg.add("timescale-postgres-database-migration")
if result.exit_code:
    raise SystemExit(result.problems)

from pydantic_ai import Agent
from decimalai.pydantic_ai import agent_config

# With no DECIMAL_API_KEY this reads the skills your project lists, from disk.
support = agent_config("support", fallback="You are a helpful agent.")

agent = Agent(
    "openai:gpt-4o-mini",
    name=support.name,
    tools=[],                 # your tools; the capability adds load_skill
    capabilities=[support],   # your prompt, the skill list and load_skill
)
# In a notebook or async code: result = await agent.run("Hello!")
result = agent.run_sync("Hello!")

On the Pydantic AI harness, decimalai.pydantic_ai_harness.skills(project) uses its own loader instead.

No DecimalAI package. The whole skill goes in every prompt, and nothing pins or updates it.

Copy SKILL.md

Save it in your project, as

.agents/skills/timescale-postgres-database-migration/SKILL.md

Give it to your agent

from pathlib import Path
from pydantic_ai import Agent

skill = Path(".agents/skills/timescale-postgres-database-migration/SKILL.md").read_text()
agent = Agent("openai:gpt-4o-mini", instructions=f"You are a helpful agent.\n\n{skill}")
# In a notebook or async code: result = await agent.run("Hello!")
result = agent.run_sync("Hello!")

This gets SKILL.md only. The skill's 3 other files are not included.

Other ways to add it

Pydantic AI docs

Deep Agents' own skill loader reads the installed folder. No DecimalAI account needed.

Install

pip install "decimalai>=0.16" deepagents
decimalai skills add timescale-postgres-database-migration

Add it to your agent

import decimalai.deepagents
from deepagents import create_deep_agent

agent = create_deep_agent(model=model, **decimalai.deepagents.skills())
agent.invoke({"messages": [{"role": "user", "content": "Hello!"}]})

The default backend reads files but has no shell. To run a skill's scripts, pass skills(backend=LocalShellBackend(...)).

The same install from Python, for a notebook or a setup script. Deep Agents' own skill loader reads the installed folder. No DecimalAI account needed.

Install

pip install "decimalai>=0.16" deepagents

Add it to your agent

from decimalai import skillpkg

# Writes .agents/skills and .decimal/skills.toml. Safe to run twice.
result = skillpkg.add("timescale-postgres-database-migration")
if result.exit_code:
    raise SystemExit(result.problems)

import decimalai.deepagents
from deepagents import create_deep_agent

agent = create_deep_agent(model=model, **decimalai.deepagents.skills())
agent.invoke({"messages": [{"role": "user", "content": "Hello!"}]})

The default backend reads files but has no shell. To run a skill's scripts, pass skills(backend=LocalShellBackend(...)).

No DecimalAI package. The whole skill goes in every prompt, and nothing pins or updates it.

Copy SKILL.md

Save it in your project, as

.agents/skills/timescale-postgres-database-migration/SKILL.md

Give it to your agent

from pathlib import Path
from deepagents import create_deep_agent

skill = Path(".agents/skills/timescale-postgres-database-migration/SKILL.md").read_text()
agent = create_deep_agent(model=model, system_prompt=f"You are a helpful agent.\n\n{skill}")
agent.invoke({"messages": [{"role": "user", "content": "Hello!"}]})

This gets SKILL.md only. The skill's 3 other files are not included.

Other ways to add it

Deep Agents docs

Its own skill loader reads the installed folder. No DecimalAI account needed.

Install

pip install "decimalai>=0.16" agent-framework
decimalai skills add timescale-postgres-database-migration

Add it to your agent

import asyncio

from agent_framework import Agent
from decimalai.agent_framework import skills

agent = Agent(client=client, instructions="You are a helpful agent.", context_providers=[skills()])
asyncio.run(agent.run("Hello!"))

It caches skills by default, so restart the process after decimalai skills update.

The same install from Python, for a notebook or a setup script. Its own skill loader reads the installed folder. No DecimalAI account needed.

Install

pip install "decimalai>=0.16" agent-framework

Add it to your agent

from decimalai import skillpkg

# Writes .agents/skills and .decimal/skills.toml. Safe to run twice.
result = skillpkg.add("timescale-postgres-database-migration")
if result.exit_code:
    raise SystemExit(result.problems)

import asyncio

from agent_framework import Agent
from decimalai.agent_framework import skills

agent = Agent(client=client, instructions="You are a helpful agent.", context_providers=[skills()])
asyncio.run(agent.run("Hello!"))

It caches skills by default, so restart the process after decimalai skills update.

No DecimalAI package. The whole skill goes in every prompt, and nothing pins or updates it.

Copy SKILL.md

Save it in your project, as

.agents/skills/timescale-postgres-database-migration/SKILL.md

Give it to your agent

import asyncio
from pathlib import Path
from agent_framework import Agent

skill = Path(".agents/skills/timescale-postgres-database-migration/SKILL.md").read_text()
agent = Agent(client=client, instructions=f"You are a helpful agent.\n\n{skill}")
asyncio.run(agent.run("Hello!"))

This gets SKILL.md only. The skill's 3 other files are not included.

Other ways to add it

Microsoft Agent Framework docs

Strands' own skill loader reads the installed folder. No DecimalAI account needed.

Install

pip install "decimalai>=0.16" strands-agents
decimalai skills add timescale-postgres-database-migration

Add it to your agent

from decimalai.strands import skills
from strands import Agent
from strands.vended_tools import file_editor, shell

agent = Agent(
    model=model,                # always pass one: the default is Amazon Bedrock
    system_prompt="You are a helpful agent.",
    tools=[file_editor, shell],
    plugins=[skills()],
)
agent("Hello!")

Strands loads an agent's skills once, so build the agent again after decimalai skills update.

The same install from Python, for a notebook or a setup script. Strands' own skill loader reads the installed folder. No DecimalAI account needed.

Install

pip install "decimalai>=0.16" strands-agents

Add it to your agent

from decimalai import skillpkg

# Writes .agents/skills and .decimal/skills.toml. Safe to run twice.
result = skillpkg.add("timescale-postgres-database-migration")
if result.exit_code:
    raise SystemExit(result.problems)

from decimalai.strands import skills
from strands import Agent
from strands.vended_tools import file_editor, shell

agent = Agent(
    model=model,                # always pass one: the default is Amazon Bedrock
    system_prompt="You are a helpful agent.",
    tools=[file_editor, shell],
    plugins=[skills()],
)
agent("Hello!")

Strands loads an agent's skills once, so build the agent again after decimalai skills update.

No DecimalAI package. The whole skill goes in every prompt, and nothing pins or updates it.

Copy SKILL.md

Save it in your project, as

.agents/skills/timescale-postgres-database-migration/SKILL.md

Give it to your agent

from pathlib import Path
from strands import Agent

skill = Path(".agents/skills/timescale-postgres-database-migration/SKILL.md").read_text()
agent = Agent(model=model, system_prompt=f"You are a helpful agent.\n\n{skill}")
agent("Hello!")

This gets SKILL.md only. The skill's 3 other files are not included.

Other ways to add it

Strands docs

Adds every file the skill has, pinned so you can update it. Cursor, Copilot, Cline and Warp read this folder by themselves.

pip install "decimalai>=0.15"
decimalai skills add timescale-postgres-database-migration

No Python or account. Cursor, Copilot, Cline and Warp read this folder by themselves.

Save it into your project

curl -fsSL --create-dirs -o .agents/skills/timescale-postgres-database-migration/SKILL.md https://app.decimal.ai/s/timescale-postgres-database-migration/SKILL.md

Copy SKILL.md

Save it in your project, as

.agents/skills/timescale-postgres-database-migration/SKILL.md

This gets SKILL.md only. The skill's 3 other files are not included.

Other ways to add it

Cursor and other editors docs

Fetch the file and give its text to your model as instructions. No account or package needed.

Get the file

curl -fsSL https://app.decimal.ai/s/timescale-postgres-database-migration/SKILL.md

Read the summary as JSON

curl -fsSL https://app.decimal.ai/s/timescale-postgres-database-migration.json

This gets SKILL.md only. The skill's 3 other files are not included.

Other ways to add it

Other setups docs

You don't need a DecimalAI account.

Version: v2, tested 9/9/2026
Source: timescale/pg-aiguide ↗
License: Apache-2.0

Manage it in DecimalAI

Saved prompts and skills, pinned versions, and traces of this skill in your runs. DecimalAI does not run your agent.

Claude Code reads files from your project, so DecimalAI config does not apply there.

On Claude Agent SDK: save the skill to an agent here, set DECIMAL_API_KEY, and the same code uses it with no command to run. Changes you save reach the next run with no redeploy.

On OpenAI Agents: save the skill to an agent here, set DECIMAL_API_KEY, and the same code uses it with no command to run. Changes you save reach the next run with no redeploy.

On OpenAI SDK: save the skill to an agent here, set DECIMAL_API_KEY, and the same code uses it with no command to run. Changes you save reach the next run with no redeploy.

On Anthropic: save the skill to an agent here, set DECIMAL_API_KEY, and the same code uses it with no command to run. Changes you save reach the next run with no redeploy.

On Gemini API: save the skill to an agent here, set DECIMAL_API_KEY, and the same code uses it with no command to run. Changes you save reach the next run with no redeploy.

On LangChain: save the skill to an agent here, set DECIMAL_API_KEY, and the same code uses it with no command to run. Changes you save reach the next run with no redeploy.

On CrewAI: set DECIMAL_API_KEY and add decimalai.init(crewai=True) to record runs and skill use. The skill version stays in your repo.

On Google ADK: save the skill to an agent here, set DECIMAL_API_KEY, and the same code uses it with no command to run. Changes you save reach the next run with no redeploy.

On Pydantic AI: save the skill to an agent here, set DECIMAL_API_KEY, and the same code uses it with no command to run. Changes you save reach the next run with no redeploy.

On Deep Agents: set DECIMAL_API_KEY and add decimalai.init(langchain=True) to record runs and skill use. The skill version stays in your repo.

On Microsoft Agent Framework: set DECIMAL_API_KEY and add decimalai.init(otel=True, agent_name="support") to record runs and skill use. The skill version stays in your repo.

On Strands: set DECIMAL_API_KEY and add decimalai.init(otel=True, agent_name="support") to record runs and skill use. The skill version stays in your repo.

Editors read files from your project, so DecimalAI config does not apply there.

Anywhere else: record a use with decimalai.log_skill_activation() inside a trace.

Use this skill

SKILL.md

PostgreSQL Database Migrations

A 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.

DDL Lock Reference

Every schema change acquires a lock. The critical question is: does it block reads and writes, and for how long?

Fast, Non-Blocking Operations

These complete in milliseconds regardless of table size. They only hold a brief AccessExclusiveLock for the catalog update, not for data rewriting.

Operation	Lock Level	Notes
`ADD COLUMN` (nullable, no default)	`AccessExclusiveLock` (brief)	Fast. No table rewrite. Metadata-only change.
`ADD COLUMN ... DEFAULT x` (PG 11+)	`AccessExclusiveLock` (brief)	Fast. Non-volatile defaults stored in catalog, not backfilled.
`DROP COLUMN`	`AccessExclusiveLock` (brief)	Fast. Column marked invisible; space reclaimed by VACUUM over time.
`SET DEFAULT` / `DROP DEFAULT`	`AccessExclusiveLock` (brief)	Metadata change only. Does not touch existing rows.
`CREATE INDEX CONCURRENTLY`	`ShareUpdateExclusiveLock`	Non-blocking. Allows reads and writes during build. Slower than regular index creation.
`DROP INDEX CONCURRENTLY`	`ShareUpdateExclusiveLock`	Non-blocking. Waits for queries using the index to finish, then drops. No table-level exclusive lock.
`RENAME COLUMN`	`AccessExclusiveLock` (brief)	Metadata change only. Fast.
`RENAME TABLE`	`AccessExclusiveLock` (brief)	Metadata change only. Fast.
`ADD CONSTRAINT ... NOT VALID`	`ShareUpdateExclusiveLock`	Adds constraint for new rows only. Does not scan existing data.
`VALIDATE CONSTRAINT`	`ShareUpdateExclusiveLock`	Scans existing rows but allows concurrent reads and writes.
`CREATE/DROP TRIGGER`	`ShareRowExclusiveLock`	Brief catalog update.

Slow or Blocking Operations

These rewrite the table or scan all rows. On large tables, they can lock out all access for seconds to hours.

Operation	Lock Level	Why It's Slow
`ADD COLUMN ... DEFAULT x` (volatile, e.g. `now()`, `gen_random_uuid()`)	`AccessExclusiveLock`	Full table rewrite. Every row gets the computed value.
`ALTER COLUMN TYPE` (most type changes)	`AccessExclusiveLock`	Full table rewrite to convert stored data.
`SET NOT NULL` (PG < 12, or without existing CHECK)	`AccessExclusiveLock`	Full table scan to verify no NULLs. See safe pattern below.
`ADD CONSTRAINT ... CHECK/UNIQUE/FK` (validated)	`AccessExclusiveLock` or `ShareRowExclusiveLock`	Scans all rows to verify, blocks writes.
`CREATE INDEX` (without CONCURRENTLY)	`ShareLock`	Blocks writes for the entire build duration.
`CLUSTER`	`AccessExclusiveLock`	Rewrites entire table in index order.
`VACUUM FULL`	`AccessExclusiveLock`	Rewrites table to reclaim space.

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.

Safe Migration Patterns

Add a Column

-- SAFE: nullable column, no default — instant
ALTER TABLE orders ADD COLUMN tracking_number TEXT;

-- SAFE (PG 11+): column with non-volatile default — instant
ALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0;

-- UNSAFE: column with volatile default — full table rewrite
-- DON'T: ALTER TABLE orders ADD COLUMN created_at TIMESTAMPTZ DEFAULT now();
-- DO: add nullable, then backfill, then set default + NOT NULL
ALTER TABLE orders ADD COLUMN created_at TIMESTAMPTZ;
-- Backfill in batches (see Backfill section)
ALTER TABLE orders ALTER COLUMN created_at SET DEFAULT now();
ALTER TABLE orders ALTER COLUMN created_at SET NOT NULL;  -- only if PG12+ or CHECK exists

Drop a Column

-- SAFE: instant (column marked invisible, space reclaimed by VACUUM)
ALTER TABLE orders DROP COLUMN old_status;

Application coordination: Ensure your application no longer references the column before dropping it. For zero-downtime deploys, this requires two steps:

Deploy code that doesn't read/write the column
Then drop the column in a separate migration

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.

Rename a Column

-- SAFE: instant metadata change
ALTER TABLE orders RENAME COLUMN status TO order_status;

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:

Add the new column
Deploy code that writes to both columns
Backfill old rows
Deploy code that reads from the new column
Drop the old column

Change a Column Type

Most type changes rewrite the entire table. Safe alternatives:

-- UNSAFE: full table rewrite, blocks everything
-- DON'T: ALTER TABLE orders ALTER COLUMN amount TYPE NUMERIC(12,2);

-- SAFE: use a new column + backfill
ALTER TABLE orders ADD COLUMN amount_new NUMERIC(12,2);

-- Backfill in batches (see Backfill section below)
UPDATE orders SET amount_new = amount WHERE id BETWEEN 1 AND 10000;
-- ... continue in batches ...

-- Swap columns
ALTER TABLE orders DROP COLUMN amount;
ALTER TABLE orders RENAME COLUMN amount_new TO amount;

Exception: Some casts don't require a rewrite and are fast:

From	To	Rewrite?
`VARCHAR(n)` → `VARCHAR(m)` where m > n	No	Metadata only
`VARCHAR(n)` → `TEXT`	No	Metadata only
`NUMERIC(p,s)` → `NUMERIC(p2,s)` where p2 > p (same scale)	No	Metadata only
`INTEGER` → `BIGINT`	Yes	Full rewrite
`TIMESTAMP` → `TIMESTAMPTZ`	Yes	Full rewrite

Add a NOT NULL Constraint

-- PG 18+: simplified two-step pattern
ALTER TABLE orders ALTER COLUMN order_status SET NOT NULL NOT VALID;
ALTER TABLE orders VALIDATE NOT NULL ON order_status;

-- PG 12–17: fast if a valid CHECK constraint already exists
-- Step 1: add CHECK (non-blocking scan)
ALTER TABLE orders ADD CONSTRAINT orders_status_nn CHECK (order_status IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_nn;

-- Step 2: add NOT NULL (PG12+ recognizes the CHECK and skips the scan)
ALTER TABLE orders ALTER COLUMN order_status SET NOT NULL;

-- Step 3: drop the now-redundant CHECK
ALTER TABLE orders DROP CONSTRAINT orders_status_nn;

-- PG < 12: SET NOT NULL always scans the full table.
-- Ensure no NULLs exist first, then accept the brief lock.

Add a Foreign Key

-- UNSAFE: validates all existing rows while holding a heavy lock
-- DON'T: ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

-- SAFE: two-step approach
-- Step 1: add without validation (blocks writes briefly, doesn't scan data)
ALTER TABLE orders ADD CONSTRAINT fk_user
    FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- Step 2: validate existing rows (allows concurrent reads and writes)
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;

Add an Index

-- UNSAFE on large tables: blocks all writes for the entire build
-- DON'T: CREATE INDEX idx_orders_user ON orders (user_id);

-- SAFE: concurrent index creation (allows reads and writes)
CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);

-- IMPORTANT: if concurrent index creation fails (crashes, deadlock),
-- it leaves an INVALID index behind. Check and clean up:
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
  AND indexrelname = 'idx_orders_user';

-- Check for invalid indexes
SELECT indexrelid::regclass AS index_name, indisvalid
FROM pg_index
WHERE NOT indisvalid;

-- Drop and retry if invalid
DROP INDEX CONCURRENTLY idx_orders_user;
CREATE INDEX CONCURRENTLY idx_orders_user ON orders (user_id);

Add a Unique Constraint

-- A UNIQUE constraint creates an index. Use CONCURRENTLY to avoid blocking:

-- Step 1: create a unique index concurrently
CREATE UNIQUE INDEX CONCURRENTLY idx_orders_tracking_uniq ON orders (tracking_number);

-- Step 2: attach it as a constraint (instant)
ALTER TABLE orders ADD CONSTRAINT orders_tracking_uniq UNIQUE USING INDEX idx_orders_tracking_uniq;

Redefine a Primary Key

Redefining 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:

-- Step 1: add CHECK NOT NULL constraint without validation (brief lock)
ALTER TABLE orders ADD CONSTRAINT orders_new_id_nn
    CHECK (new_id IS NOT NULL) NOT VALID;

-- Step 2: validate existing rows (allows concurrent reads and writes)
ALTER TABLE orders VALIDATE CONSTRAINT orders_new_id_nn;

-- Step 3: build unique index concurrently (non-blocking)
CREATE UNIQUE INDEX CONCURRENTLY idx_orders_new_pkey
    ON orders (new_id);

-- Step 4: drop the old PK
ALTER TABLE orders DROP CONSTRAINT orders_pkey;

-- Step 5: add new PK using the existing index (instant — also implicitly adds NOT NULL)
ALTER TABLE orders ADD CONSTRAINT orders_pkey
    PRIMARY KEY USING INDEX idx_orders_new_pkey;

-- Step 6: drop the now-redundant CHECK constraint
ALTER TABLE orders DROP CONSTRAINT orders_new_id_nn;

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+).

Drop a Constraint

-- SAFE: instant metadata change
ALTER TABLE orders DROP CONSTRAINT orders_tracking_uniq;

-- If dropping a FK that has a supporting index you no longer need:
ALTER TABLE orders DROP CONSTRAINT fk_user;
DROP INDEX idx_orders_user_id;  -- only if no other queries use it

Backfill Strategies

Always backfill in batches — never in a single UPDATE. See backfill-strategies for batch-by-PK patterns, resumable progress tracking, and tuning guidance.

Migration Validation

Run validation queries before and after every migration. See validation-queries for the full set of checks: NULL detection, duplicate detection, orphan rows, cast failures, duration estimation, schema verification, data integrity, and query performance.

Rollback Planning

Every migration should have a rollback plan documented before execution.

Reversible Operations

Operation	Rollback
`ADD COLUMN`	`DROP COLUMN`
`ADD CONSTRAINT`	`DROP CONSTRAINT`
`CREATE INDEX`	`DROP INDEX`
`RENAME COLUMN x TO y`	`RENAME COLUMN y TO x`
`SET DEFAULT x`	`SET DEFAULT old_value` or `DROP DEFAULT`
`ADD COLUMN new + DROP COLUMN old`	Cannot directly undo — need to re-add old column and backfill from a backup

Irreversible Operations

These require restoring from a backup or the database fork to undo:

DROP COLUMN — data is gone once VACUUM reclaims it
ALTER COLUMN TYPE with lossy cast (e.g., NUMERIC → INTEGER, TEXT → VARCHAR(50))
DELETE / TRUNCATE during data cleanup
DROP TABLE

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.

Transaction Strategy

There are two approaches for executing multiple DDL statements. Each has tradeoffs:

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).

BEGIN;

ALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0;
ALTER TABLE orders ADD COLUMN tags TEXT[] NOT NULL DEFAULT '{}';
CREATE INDEX ON orders USING GIN (tags);
ALTER TABLE orders DROP COLUMN old_priority;

-- Verify before committing
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'orders'
ORDER BY ordinal_position;

COMMIT;
-- Or ROLLBACK; if something looks wrong

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.

-- Each statement auto-commits
ALTER TABLE orders ADD COLUMN priority INTEGER NOT NULL DEFAULT 0;
ALTER TABLE orders ADD COLUMN tags TEXT[] NOT NULL DEFAULT '{}';
ALTER TABLE orders DROP COLUMN old_priority;

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.

Cannot use transactions with:

CREATE INDEX CONCURRENTLY (explicitly disallowed inside a transaction)
DROP INDEX CONCURRENTLY
Any statement that requires its own transaction context

Dealing with Long-Running Queries

A 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:

Session 1: SELECT COUNT(*) FROM orders;          -- long query, holds AccessShareLock
Session 2: ALTER TABLE orders ADD COLUMN ...;     -- waits for Session 1 (needs AccessExclusiveLock)
Session 3: SELECT * FROM orders WHERE id = 123;   -- BLOCKED by Session 2's lock queue entry
Session 4: INSERT INTO orders (...) VALUES (...);  -- also BLOCKED
-- All sessions freeze until Session 1 finishes and Session 2 completes or times out

This is why lock_timeout is critical — without it, a single slow query can cascade into an application-wide outage.

Set Timeouts

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.

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.

Choosing timeout values:

There are two schools of thought:

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.
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.

Pick 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).

-- Fail fast instead of blocking all queries behind you
SET lock_timeout = '5s';
SET statement_timeout = '30s';

ALTER TABLE orders ADD COLUMN tracking_number TEXT;

-- If it fails with "canceling statement due to lock timeout":
-- 1. Find what's blocking
SELECT pid, state, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- 2. Wait for the blocker to finish, or cancel it if appropriate
-- SELECT pg_cancel_backend(<pid>);

-- 3. Retry the ALTER TABLE
SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN tracking_number TEXT;

-- Reset timeouts when done
RESET lock_timeout;
RESET statement_timeout;

The Retry-With-Timeout Pattern

For automated migration runners, wrap DDL in a retry loop with a short lock timeout:

DO $$
DECLARE
    max_attempts INTEGER := 5;
    attempt INTEGER := 1;
    success BOOLEAN := FALSE;
BEGIN
    WHILE attempt <= max_attempts AND NOT success LOOP
        BEGIN
            SET lock_timeout = '3s';
            -- Replace with your DDL statement
            ALTER TABLE orders ADD COLUMN tracking_number TEXT;
            success := TRUE;
            RAISE NOTICE 'DDL succeeded on attempt %', attempt;
        EXCEPTION
            WHEN lock_not_available THEN
                RAISE NOTICE 'Attempt % failed (lock not available), retrying...', attempt;
                PERFORM pg_sleep(2 * attempt);  -- linear backoff
                attempt := attempt + 1;
        END;
    END LOOP;

    IF NOT success THEN
        RAISE EXCEPTION 'DDL failed after % attempts', max_attempts;
    END IF;
END $$;

This 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.

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:

BEGIN;
LOCK TABLE orders IN ACCESS EXCLUSIVE MODE NOWAIT;
-- If we get here, we have the lock — run DDL
ALTER TABLE orders ADD COLUMN tracking_number TEXT;
COMMIT;
-- If LOCK fails with "could not obtain lock", retry after a short sleep

Fork-Based Migration Testing

The 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 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.

With Forking

Fork your database — create a full copy using your provider's fork feature (takes seconds)
Inspect the current schema on the fork to confirm it matches production
Run your migration on the fork
Validate — run your checks (see Pre/Post-Migration Validation sections above)
If it worked: apply the same migration to production
If it failed: delete the fork — your production database is untouched

This catches problems that never show up in empty test databases:

Data that violates a new constraint
Type casts that fail on real values
Migrations that are fast on 100 rows but lock the table for minutes on 10 million
Index creation that runs out of memory or disk space

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'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.

Without Forking

Create a test database from a backup or dump:

# Dump your production database
pg_dump -Fc my_app_db > backup.dump

# Restore into a test database
createdb migration_test
pg_restore -d migration_test backup.dump

# Or clone from a live database (requires downtime on source during copy)
createdb migration_test -T my_app_db

Complete Migration Example

For a full end-to-end walkthrough (plan, fork, run, validate, apply, clean up), see complete-example.

Advanced Considerations

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.

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.

Common Pitfalls

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.
Forgetting CONCURRENTLY on index creation — CREATE INDEX (without CONCURRENTLY) blocks all writes. On a table with active traffic, this causes downtime.
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.
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.
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.
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.
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.
Not checking replication lag — large backfills generate heavy WAL. If you have read replicas, monitor pg_stat_replication during and after the migration.
Assuming ALTER COLUMN TYPE is safe — most type changes rewrite the entire table. Use the add-new-column + backfill + swap pattern for large tables.

About this skill

Automatic

Data Engineer pack

SkillSafety

✓ 3 of 3 passed

Code scan15 of 15 checks clean

0 critical · 0 warning · 0 info — scanner v5 — 9/9/2026

View 15 scanner checks

live_secretremote_code_executionreverse_shelldata_exfiltrationssrf_metadataagent_snoopinganti_refusaldestructive_commandsobfuscated_unicodeunicode_homoglyphopaque_blobsuspicious_urltool_overreachtrigger_abuseprompt_injection_phrasing

IntentSafe

LLM judge · does the skill do what it claims

ContentClean

LLM judge · policy & harmful-content review

Source

Imported

timescale / pg-aiguide

skills/postgres-database-migration/SKILL.md

View on GitHub ↗

License Apache-2.0Imported 9/9/2026Version v2Bundled 3 files

Illustrative Want to see what timescale/postgres-database-migration would put in front of your model? Preview the rendered body and check its trigger phrases — no LLM call, no signup.

Other skills for the same job

Published skills whose descriptions closely match this one, with their headline benchmark lift.

zzafergok/database-migrations-skillSQL database migration strategies with zero-downtime deployment, rollback plans, and data integrity validation for PostgreSQL, MySQL, and SQL Server. Use when designing schema changes, adding columns/indexes, renaming tables, or planning production migrations with minimal downtime.Close match+9 pts pass-rate lift

Related skills

Skills that do similar work, with their headline benchmark lift.

From the same source and nearby in the catalogue.

Ready to supercharge your AI agents?

SkillScore

Safety

What it changes

Track record

Use this skill

PostgreSQL Database Migrations

DDL Lock Reference

Fast, Non-Blocking Operations

Slow or Blocking Operations

Safe Migration Patterns

Add a Column

Drop a Column

Rename a Column

Change a Column Type

Add a NOT NULL Constraint

Add a Foreign Key

Add an Index

Add a Unique Constraint

Redefine a Primary Key

Drop a Constraint

Backfill Strategies

Migration Validation

Rollback Planning

Reversible Operations

Irreversible Operations

Transaction Strategy

Dealing with Long-Running Queries

Set Timeouts

The Retry-With-Timeout Pattern

Fork-Based Migration Testing

With Forking

Without Forking

Complete Migration Example

Advanced Considerations

Common Pitfalls

SkillSafety

Source

Other skills for the same job

Related skills

More in the registry