---
sidebar_position: 1
---

# Functions Reference

Complete API reference for all `pg_ttl_index` functions.

## TTL Management Functions

### ttl_create_index()

Creates or updates a TTL index configuration for automatic data expiration.

#### Signature

```sql
ttl_create_index(
    p_table_name TEXT,
    p_column_name TEXT,
    p_expire_after_seconds INTEGER,
    p_batch_size INTEGER DEFAULT 10000,
    p_soft_delete_column TEXT DEFAULT NULL
) RETURNS BOOLEAN
```

#### Parameters

| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `p_table_name` | TEXT | Yes | Table to apply TTL to (schema-qualified is recommended, e.g. `app.sessions`) |
| `p_column_name` | TEXT | Yes | Name of the timestamp column for expiration |
| `p_expire_after_seconds` | INTEGER | Yes | Number of seconds before data expires |
| `p_batch_size` | INTEGER | No | Rows to delete per batch (default: 10000) |
| `p_soft_delete_column` | TEXT | No | Nullable timestamp/timestamptz column to mark soft deletes (e.g. `deleted_at`) |

#### Return Value

- `true` - TTL index created/updated successfully
- `false` - Operation failed (check logs for details)

#### Behavior

1. **Creates an index** on the timestamp column if it doesn't exist
   - Index name: `idx_ttl_{table}_{column}`
   - If a suitable index already exists on the column, it is reused
2. **Registers or updates** the TTL configuration in `ttl_index_table`
3. **Activates** automatic cleanup for the table
4. **Idempotent** - Safe to call multiple times (updates configuration)
5. **Schema-aware** - Stores normalized `schema_name` + `table_name` internally
6. **Hardened execution** - Uses fixed function `search_path` to prevent search-path hijacking
7. **Soft delete optional** - If `p_soft_delete_column` is provided, expired rows are marked instead of deleted

#### Examples

**Basic usage:**
```sql
-- Sessions expire after 1 hour
SELECT ttl_create_index('public.user_sessions', 'created_at', 3600);
```

**With custom batch size:**
```sql
-- High-volume table with large batch size
SELECT ttl_create_index('public.app_logs', 'logged_at', 604800, 50000);
```

**Immediate expiration (cache use case):**
```sql
-- Expire based on expires_at column
SELECT ttl_create_index('public.cache_entries', 'expires_at', 0);
```

**Update existing TTL:**
```sql
-- Change expiry from 1 hour to 2 hours
SELECT ttl_create_index('public.user_sessions', 'created_at', 7200);
```

**Soft delete mode:**
```sql
-- Mark expired rows instead of deleting them
SELECT ttl_create_index('public.user_sessions', 'created_at', 3600, 10000, 'deleted_at');
```

#### Error Handling

The function returns `false` on error and logs warnings:
```sql
WARNING:  TTL create_index failed: column "invalid_col" does not exist
```

---

### ttl_drop_index()

Removes a TTL index configuration and drops the associated index.

#### Signature

```sql
ttl_drop_index(
    p_table_name TEXT,
    p_column_name TEXT
) RETURNS BOOLEAN
```

#### Parameters

| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `p_table_name` | TEXT | Yes | Name of the table |
| `p_column_name` | TEXT | Yes | Name of the timestamp column |

#### Return Value

- `true` - TTL index removed successfully
- `false` - No matching TTL index found

#### Behavior

1. **Drops the auto-created index** if it exists
2. **Removes the configuration** from `ttl_index_table`
3. **Stops automatic cleanup** for this table/column

#### Examples

```sql
-- Remove TTL from sessions table
SELECT ttl_drop_index('public.user_sessions', 'created_at');

-- Verify removal
SELECT * FROM ttl_summary();
```

---

### ttl_runner()

Manually executes TTL cleanup for all active TTL indexes.

#### Signature

```sql
ttl_runner() RETURNS INTEGER
```

#### Parameters

None.

#### Return Value

- `INTEGER` - Total number of rows deleted across all tables

#### Behavior

1. **Acquires advisory lock** to prevent concurrent runs
2. **Processes each active TTL index** sequentially
3. **Cleans expired rows in batches** according to configured batch size
   - hard delete mode: `DELETE`
   - soft delete mode: `UPDATE ... SET <soft_delete_column> = NOW()`
4. **Updates statistics** (`rows_deleted_last_run`, `total_rows_deleted`)
5. **Per-table error handling** - errors in one table don't affect others
6. **Releases advisory lock** when complete

#### Examples

```sql
-- Manually trigger cleanup
SELECT ttl_runner();
-- Returns: 1523 (total rows deleted)

-- View deletion details
SELECT * FROM ttl_summary();
```

#### Performance Notes

- Uses `ctid` for efficient batch deletion
- Sleeps 10ms between batches to yield to other processes
- Skips run if another instance is already running (via advisory lock)

---

## Worker Management Functions

### ttl_start_worker()

Starts the background worker for automatic TTL cleanup.

#### Signature

```sql
ttl_start_worker() RETURNS BOOLEAN
```

#### Parameters

None.

#### Return Value

- `true` - Worker started successfully
- `false` - Failed to start worker

#### Behavior

- Launches a dedicated background worker for the current database
- Worker runs `ttl_runner()` every `pg_ttl_index.naptime` seconds (default: 60)
- **Must be called manually** per database (not automatic)
- Only one worker runs per database

#### Examples

```sql
-- Start the worker
SELECT ttl_start_worker();

-- Verify it's running
SELECT * FROM ttl_worker_status();
```

:::warning Important
The worker **does not persist** across PostgreSQL restarts. You must start it again after server restart.
:::

---

### ttl_stop_worker()

Stops the background worker for TTL cleanup.

#### Signature

```sql
ttl_stop_worker() RETURNS BOOLEAN
```

#### Parameters

None.

#### Return Value

- `true` - Worker stopped successfully
- `false` - No worker was running

#### Behavior

- Terminates the background worker for the current database
- Cleanup will no longer run automatically
- TTL configurations remain in `ttl_index_table`

#### Examples

```sql
-- Stop the worker
SELECT ttl_stop_worker();

-- Verify it stopped
SELECT * FROM ttl_worker_status();
-- Should return no rows
```

---

## Monitoring Functions

### ttl_worker_status()

Returns the status of TTL background workers.

#### Signature

```sql
ttl_worker_status() RETURNS TABLE(
    worker_pid INTEGER,
    application_name TEXT,
    state TEXT,
    backend_start TIMESTAMPTZ,
    state_change TIMESTAMPTZ,
    query_start TIMESTAMPTZ,
    database_name TEXT
)
```

#### Parameters

None.

#### Return Columns

| Column | Type | Description |
|--------|------|-------------|
| `worker_pid` | INTEGER | Process ID of the worker |
| `application_name` | TEXT | Always "TTL Worker DB \{dbname\}" |
| `state` | TEXT | Current state (usually "idle") |
| `backend_start` | TIMESTAMPTZ | When the worker started |
| `state_change` | TIMESTAMPTZ | Last state change time |
| `query_start` | TIMESTAMPTZ | When current query started |
| `database_name` | TEXT | Database name |

#### Examples

```sql
-- Check if worker is running
SELECT 
    worker_pid,
    state,
    backend_start,
    NOW() - backend_start AS uptime
FROM ttl_worker_status();
```

**Output:**
```
 worker_pid |  state  |        backend_start        |     uptime
------------+---------+-----------------------------+-----------------
      12345 | idle    | 2026-01-03 02:00:00+00      | 00:42:15.123
```

---

### ttl_summary()

Returns a comprehensive summary of all TTL configurations and statistics.

#### Signature

```sql
ttl_summary() RETURNS TABLE(
    schema_name TEXT,
    table_name TEXT,
    column_name TEXT,
    expire_after_seconds INTEGER,
    batch_size INTEGER,
    active BOOLEAN,
    last_run TIMESTAMPTZ,
    time_since_last_run INTERVAL,
    rows_deleted_last_run BIGINT,
    total_rows_deleted BIGINT,
    index_name TEXT,
    soft_delete_column TEXT,
    cleanup_mode TEXT
)
```

#### Parameters

None.

#### Return Columns

| Column | Type | Description |
|--------|------|-------------|
| `schema_name` | TEXT | Schema containing the TTL-enabled table |
| `table_name` | TEXT | Table with TTL enabled |
| `column_name` | TEXT | Timestamp column used for expiration |
| `expire_after_seconds` | INTEGER | Expiration time in seconds |
| `batch_size` | INTEGER | Rows deleted per batch |
| `active` | BOOLEAN | Whether TTL is active |
| `last_run` | TIMESTAMPTZ | When cleanup last ran |
| `time_since_last_run` | INTERVAL | Time since last cleanup |
| `rows_deleted_last_run` | BIGINT | Rows deleted in last run |
| `total_rows_deleted` | BIGINT | Total rows deleted all-time |
| `index_name` | TEXT | Name of the auto-created index |
| `soft_delete_column` | TEXT | Soft-delete timestamp column if configured, else `NULL` |
| `cleanup_mode` | TEXT | `hard_delete` or `soft_delete` |

#### Examples

**Basic monitoring:**
```sql
SELECT 
    schema_name,
    table_name,
    expire_after_seconds / 3600.0 AS expire_hours,
    rows_deleted_last_run,
    total_rows_deleted
FROM ttl_summary();
```

**Active tables only:**
```sql
SELECT * FROM ttl_summary() WHERE active = true;
```

**Recent activity:**
```sql
SELECT 
    schema_name,
    table_name,
    last_run,
    time_since_last_run,
    rows_deleted_last_run
FROM ttl_summary()
WHERE last_run > NOW() - INTERVAL '1 hour'
ORDER BY last_run DESC;
```

---

## Function Usage Patterns

### Complete Setup Workflow

```sql
-- 1. Start worker
SELECT ttl_start_worker();

-- 2. Create TTL indexes
SELECT ttl_create_index('public.sessions', 'created_at', 1800);
SELECT ttl_create_index('public.logs', 'logged_at', 604800);

-- 3. Monitor
SELECT * FROM ttl_summary();
SELECT * FROM ttl_worker_status();
```

### Maintenance Workflow

```sql
-- Check worker health
SELECT * FROM ttl_worker_status();

-- Review cleanup statistics
SELECT 
    table_name,
    time_since_last_run,
    rows_deleted_last_run
FROM ttl_summary()
WHERE active = true;

-- Manual cleanup if needed
SELECT ttl_runner();
```

### Disable/Enable Workflow

```sql
-- Disable TTL temporarily
UPDATE ttl_index_table 
SET active = false 
WHERE schema_name = 'public' AND table_name = 'sessions';

-- Or stop worker completely
SELECT ttl_stop_worker();

-- Re-enable
UPDATE ttl_index_table 
SET active = true 
WHERE schema_name = 'public' AND table_name = 'sessions';
SELECT ttl_start_worker();
```

## See Also

- [Configuration Parameters](configuration.md) - GUC settings
- [Tables Reference](tables.md) - `ttl_index_table` schema
- [Monitoring Guide](../guides/monitoring.md) - Detailed monitoring strategies
