Database Management

5Stack provides powerful database management tools that give Administrators deep visibility into PostgreSQL/TimescaleDB performance, query optimization, and data maintenance. These tools are essential for ensuring optimal database performance and reliability.

Database management features require Administrator role access and are available at /database.

Overview

The database management interface provides multiple specialized views for different aspects of database administration:

Query Performance

Monitor slow queries, execution times, and optimize query performance.

Connections

Track active database connections, connection pools, and client activity.

Locks & Transactions

Identify blocking queries, deadlocks, and long-running transactions.

I/O & Buffer Stats

Monitor disk I/O, buffer hit ratios, and cache performance.

Index Usage

Analyze index effectiveness and identify missing or unused indexes.

Storage

View table sizes, schema statistics, and storage utilization.

TimescaleDB

Manage time-series hypertables, chunks, and compression.

Backups

Monitor database backups and their sizes.

Interface Controls

Auto-Refresh Configuration

The database interface includes configurable auto-refresh with pause capability:

<template>
  <ButtonGroup>
    <Button
      variant="outline"
      size="sm"
      @click="forceRefresh"
    >
      <RefreshCwIcon
        :class="[
          'w-4 h-4 transition-transform',
          isRefreshing && 'animate-spin',
        ]"
      />
    </Button>
    <Select v-model="refreshInterval" :disabled="isPaused">
      <SelectTrigger class="w-14 h-9">
        <SelectValue />
      </SelectTrigger>
      <SelectContent>
        <SelectItem value="3">3s</SelectItem>
        <SelectItem value="5">5s</SelectItem>
        <SelectItem value="10">10s</SelectItem>
        <SelectItem value="15">15s</SelectItem>
        <SelectItem value="30">30s</SelectItem>
        <SelectItem value="60">60s</SelectItem>
      </SelectContent>
    </Select>
    <Button
      :variant="isPaused ? 'default' : 'outline'"
      size="sm"
      @click="togglePause"
    >
      <component
        :is="isPaused ? PlayIcon : PauseIcon"
        class="w-4 h-4"
      />
    </Button>
  </ButtonGroup>
</template>

Refresh Logic

const pollInterval = computed(() => {
  if (isPaused) {
    return 0; // 0 disables polling in Apollo
  }
  return parseInt(refreshInterval) * 1000;
});

function forceRefresh() {
  // Show spinning animation
  loadingCount++;
  // Increment trigger to force child components to refresh
  refreshTrigger++;
  // Stop animation after 1 second
  setTimeout(() => {
    loadingCount = Math.max(0, loadingCount - 1);
  }, 1000);
}

Query Performance

The Query Performance tab monitors query execution statistics using PostgreSQL’s pg_stat_statements extension.

Key Metrics

Execution Statistics

Calls: Total number of query executions
Avg Time: Mean execution time in milliseconds
Max Time: Maximum execution time observed
Total Time: Cumulative execution time

Cache Performance

Cache Hit Ratio: Percentage of data served from cache vs disk
High ratios (>95%) indicate good cache utilization

Resource Usage

Temp Blocks Written: Queries using temporary disk storage
High temp block usage may indicate insufficient memory

Query Filtering

Filter and sort queries to identify performance issues:

<template>
  <Card>
    <CardContent class="pt-6">
      <div class="flex gap-4 items-end">
        <div class="flex-1">
          <Label>Search Query</Label>
          <Input
            v-model="searchQuery"
            type="text"
            placeholder="Filter by query text..."
          />
        </div>
        <div>
          <Label>Min Calls</Label>
          <Input
            v-model.number="minCalls"
            type="number"
            placeholder="5"
            class="w-24"
          />
        </div>
        <div class="w-40">
          <Label>Sort By</Label>
          <Select v-model="sortBy">
            <SelectTrigger>
              <SelectValue placeholder="Sort by..." />
            </SelectTrigger>
            <SelectContent>
              <SelectItem value="mean_exec_time">Avg Time</SelectItem>
              <SelectItem value="max_exec_time">Max Time</SelectItem>
              <SelectItem value="calls">Calls</SelectItem>
              <SelectItem value="total_exec_time">Total Time</SelectItem>
            </SelectContent>
          </Select>
        </div>
      </div>
    </CardContent>
  </Card>
</template>

Performance Indicators

Queries are color-coded by average execution time:

function getTimeVariant(meanExecTime: number) {
  if (meanExecTime > 1000) return "destructive"; // > 1 second
  if (meanExecTime > 500) return "warning";      // > 500ms
  return "default";                               // < 500ms
}

Slow Query Thresholds:

Red (Destructive): > 1000ms average
Yellow (Warning): > 500ms average
Queries consistently above these thresholds need optimization

Connection Management

The Connections tab shows all active database connections and their states.

Connection States

active: Currently executing a query
idle: Connected but not executing queries
idle in transaction: Connection is in an open transaction
idle in transaction (aborted): Transaction encountered an error

High numbers of “idle in transaction” connections can indicate application issues with transaction management.

Locks & Transactions

Monitor database locks to identify contention and blocking queries.

Lock Types

AccessShareLock: SELECT queries
RowShareLock: SELECT FOR UPDATE
RowExclusiveLock: INSERT, UPDATE, DELETE
ShareUpdateExclusiveLock: VACUUM, CREATE INDEX CONCURRENTLY
ShareLock: CREATE INDEX
ExclusiveLock: REFRESH MATERIALIZED VIEW
AccessExclusiveLock: DROP TABLE, TRUNCATE, ALTER TABLE

Identifying Blocking Queries

The locks view shows which queries are blocked and which queries are blocking them, helping identify deadlock situations quickly.

I/O & Buffer Statistics

Monitor disk I/O patterns and buffer cache effectiveness.

Key Metrics

Buffer Hit Ratio

Percentage of reads served from memory cache. Target: >95%Low ratios indicate insufficient shared_buffers configuration.

Disk Blocks Read

Number of disk blocks read from storage.High values indicate cache misses and potential I/O bottlenecks.

Blocks Written

Disk blocks written by queries.Track write-heavy operations and potential disk wear.

Temp Files

Temporary files created when memory is insufficient.Frequent temp files indicate need for more work_mem.

Index Usage Analysis

The Index Usage tab helps identify index optimization opportunities.

Index Effectiveness

Unused Indexes

Indexes that are never or rarely used waste storage and slow down writes.Consider dropping indexes with zero or very low scan counts.

Missing Indexes

Sequential scans on large tables may indicate missing indexes.Review seq_scan counts relative to idx_scan counts.

Index Bloat

Over time, indexes can become fragmented.Use REINDEX to rebuild bloated indexes.

Index Recommendations

When seq_scan significantly exceeds idx_scan on large tables:

Review queries accessing that table
Identify common WHERE clause columns
Consider adding composite indexes for multi-column filters
Test index effectiveness before deploying

Storage Management

Monitor database storage utilization across tables and schemas.

Storage Metrics

Table Size: Actual data size
Index Size: Space used by indexes
Total Size: Combined table + index size
Row Count: Approximate number of rows

Storage Optimization

VACUUM

Regular vacuuming reclaims storage from deleted rows:

VACUUM ANALYZE table_name;

Consider enabling autovacuum for automatic maintenance.

Table Partitioning

Large tables benefit from partitioning, especially time-series data.TimescaleDB automatically partitions hypertables into chunks.

Archive Old Data

For historical data rarely accessed:

Export to archive storage
Delete from active database
Maintain access via external tables if needed

TimescaleDB Features

The TimescaleDB tab provides management for time-series data.

Hypertables

Hypertables are TimescaleDB’s abstraction for time-series tables:

Automatically partitioned into chunks by time
Chunks can be individually compressed
Old chunks can be dropped efficiently
Continuous aggregates for rollups

Compression

Compress older chunks to reduce storage:

ALTER TABLE metrics SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id'
);

SELECT add_compression_policy('metrics', INTERVAL '7 days');

Retention Policies

Automatically drop old data:

SELECT add_retention_policy('metrics', INTERVAL '90 days');

Backup Management

The Backups tab displays available database backups.

Backup Information

apollo: {
  $subscribe: {
    backups: {
      query: generateSubscription({
        db_backups: [
          {},
          {
            id: true,
            name: true,
            size: true,
            created_at: true,
          },
        ],
      }),
      result: function ({ data }) {
        this.backups = data.db_backups;
      },
    },
  },
}

Backup Display

<template>
  <Table>
    <TableHeader>
      <TableRow>
        <TableHead>Name</TableHead>
        <TableHead class="text-right">Size</TableHead>
        <TableHead>Created At</TableHead>
      </TableRow>
    </TableHeader>
    <TableBody v-if="backups.length">
      <TableRow v-for="backup in backups" :key="backup.id">
        <TableCell>{{ backup.name }}</TableCell>
        <TableCell class="text-right tabular-nums">
          {{ formatBytes(backup.size) }}
        </TableCell>
        <TableCell>
          {{ new Date(backup.created_at).toLocaleString() }}
        </TableCell>
      </TableRow>
    </TableBody>
  </Table>
</template>

Best Practices

Regular Monitoring

Review database metrics daily:

Check for slow queries
Monitor connection counts
Review lock contention
Verify backup completion

Query Optimization

For slow queries:

Copy the query from the performance tab
Run EXPLAIN ANALYZE to understand execution plan
Identify missing indexes or inefficient joins
Test optimization in staging before production

Proactive Maintenance

Schedule regular maintenance:

Weekly VACUUM ANALYZE
Monthly index rebuilds for high-write tables
Quarterly storage review and archival
Regular backup verification

Performance Baselines

Establish baselines during normal operation:

Typical query execution times
Normal connection counts
Expected buffer hit ratios
Standard lock wait times

Use baselines to detect anomalies.

Capacity Planning

Monitor trends over time:

Database growth rate
Query volume increases
Connection pool utilization
I/O throughput trends

Plan scaling before reaching capacity limits.

Troubleshooting

High Query Times

Identify slow queries in Query Performance tab
Check for missing indexes in Index Usage tab
Review lock contention in Locks & Transactions tab
Verify buffer hit ratio in I/O & Buffer Stats tab

Connection Pool Exhaustion

Review active connections in Connections tab
Identify long-running queries
Check for connections stuck in transactions
Increase connection pool size if needed

Storage Growth

Review table sizes in Storage tab
Identify largest tables and growth rate
Run VACUUM to reclaim deleted row space
Implement retention policies for time-series data
Consider archiving old data

Lock Contention

Review Locks & Transactions tab
Identify blocking queries
Terminate blocking sessions if necessary
Optimize query patterns to reduce lock duration
Consider table partitioning to reduce lock scope

System Monitoring

Monitor overall platform health

Roles & Permissions

Understand administrator access

Player Sanctions

Manage player moderation

Get Started

User Guide

Matches

Tournaments

Server Management

Administration

​Overview

Query Performance

Connections

Locks & Transactions

I/O & Buffer Stats

Index Usage

Storage

TimescaleDB

Backups

​Interface Controls

​Auto-Refresh Configuration

​Refresh Logic

​Query Performance

​Key Metrics

​Query Filtering

​Performance Indicators

​Connection Management

​Connection States

​Locks & Transactions

​Lock Types

​Identifying Blocking Queries

​I/O & Buffer Statistics

​Key Metrics

Buffer Hit Ratio

Disk Blocks Read

Blocks Written

Temp Files

​Index Usage Analysis

​Index Effectiveness

​Index Recommendations

​Storage Management

​Storage Metrics

​Storage Optimization

​TimescaleDB Features

​Hypertables

​Compression

​Retention Policies

​Backup Management

​Backup Information

​Backup Display

​Best Practices

​Troubleshooting

​High Query Times

​Connection Pool Exhaustion

​Storage Growth

​Lock Contention

​Related Topics

System Monitoring

Roles & Permissions

Player Sanctions

Build docs developers (and LLMs) love

Overview

Interface Controls

Auto-Refresh Configuration

Refresh Logic

Query Performance

Key Metrics

Query Filtering

Performance Indicators

Connection Management

Connection States

Locks & Transactions

Lock Types

Identifying Blocking Queries

I/O & Buffer Statistics

Key Metrics

Index Usage Analysis

Index Effectiveness

Index Recommendations

Storage Management

Storage Metrics

Storage Optimization

TimescaleDB Features

Hypertables

Compression

Retention Policies

Backup Management

Backup Information

Backup Display

Best Practices

Troubleshooting

High Query Times

Connection Pool Exhaustion

Storage Growth

Lock Contention

Related Topics