PartitionManagerService

Overview

The PartitionManagerService manages PostgreSQL table partitioning for the points table, which stores GPS tracking data. It automatically creates daily partitions, manages indexes, and provides cleanup functionality. Source: src/app/points/partition-manager.service.ts

Key Features

Automatic Partition Creation: Creates partitions for upcoming days via cron job
Index Management: Automatically creates optimized indexes for each partition
Range Partitioning: Partitions by timestamp (one partition per day)
Parent Table Bootstrap: Initializes the partitioned parent table
Cleanup Utilities: Methods to drop old partitions

Constructor

The service is initialized with TypeORM’s DataSource for raw SQL query execution:

constructor(
  @InjectDataSource() private readonly dataSource: DataSource,
) { }

dataSource

DataSource

TypeORM DataSource injected for executing PostgreSQL queries

Methods

createParentTableIfNotExists()

Creates the partitioned parent table for points if it doesn’t already exist. This should be executed once during application bootstrap. Source: src/app/points/partition-manager.service.ts:18

async createParentTableIfNotExists(): Promise<void>

Implementation Details

Checks if the points table exists
If not, executes the SQL from CREATE_POINTS_PARENT_TABLE constant
Creates a partitioned table using PostgreSQL’s PARTITION BY RANGE (timestamp)

Example

import { PartitionManagerService } from './partition-manager.service';

@Injectable()
export class AppBootstrapService {
  constructor(
    private readonly partitionManager: PartitionManagerService,
  ) {}

  async onApplicationBootstrap() {
    await this.partitionManager.createParentTableIfNotExists();
  }
}

Parent Table Schema

The parent table is created with the following structure (see src/app/database/constants/points.sql.ts):

CREATE TABLE points (
  id UUID NOT NULL DEFAULT gen_random_uuid(),
  timestamp BIGINT NOT NULL,
  
  -- Device identification
  tracker_device_imei BIGINT NOT NULL,
  tracker_device_license_plate VARCHAR(20),
  tracker_device_alias VARCHAR(100),
  
  -- Geospatial position (PostGIS)
  location GEOMETRY(Point, 4326),
  tracker_device_latitude DOUBLE PRECISION NOT NULL,
  tracker_device_longitude DOUBLE PRECISION NOT NULL,
  tracker_device_altitude DOUBLE PRECISION,
  
  -- Additional tracking fields...
  
  PRIMARY KEY (id, timestamp)
) PARTITION BY RANGE (timestamp);

createPartitionForDate()

Creates a partition for a specific date with all necessary indexes. Source: src/app/points/partition-manager.service.ts:45

async createPartitionForDate(date: Date): Promise<void>

Parameters

date

Date

required

The date for which to create a partition

Partition Creation Process

Generates partition name: points_YYYY_MM_DD format
Calculates range: From start of day to end of day (in milliseconds)

Creates partition table:

CREATE TABLE points_2026_03_03
  PARTITION OF points
  FOR VALUES FROM (1709424000000) TO (1709510400000)

Creates indexes: Four indexes for optimized queries

Index Strategy

Each partition gets four indexes:

1. Timestamp Index

CREATE INDEX idx_points_2026_03_03_timestamp
  ON points_2026_03_03 (timestamp)

Optimizes time-based queries.

2. IMEI + Timestamp Index

CREATE INDEX idx_points_2026_03_03_imei_ts
  ON points_2026_03_03 (tracker_device_imei, timestamp)

Optimizes device-specific time-range queries.

3. IMEI + Timestamp + Trip Index

CREATE INDEX idx_points_2026_03_03_imei_ts_trip
  ON points_2026_03_03 (tracker_device_imei, timestamp, tracker_device_trip_id)

Optimizes trip-specific queries.

4. Geospatial Index (GIST)

CREATE INDEX idx_points_2026_03_03_location
  ON points_2026_03_03 USING GIST (location)

Optimizes geospatial queries using PostGIS.

Example

// Create partition for tomorrow
const tomorrow = new Date();
tomorrow.setDate(tomorrow.getDate() + 1);

await partitionManager.createPartitionForDate(tomorrow);

Duplicate Handling

If a partition already exists for the given date, the method logs a message and returns without error:

if (partitionExists) {
  this.logger.log(`Partition ${tableName} already exists. Skipping.`);
  return;
}

dropPartitionDaysAgo()

Drops a partition from N days ago to free up storage. Source: src/app/points/partition-manager.service.ts:96

async dropPartitionDaysAgo(daysAgo: number): Promise<void>

Parameters

daysAgo

number

required

Number of days in the past to drop the partition for

Example

// Drop partition from 30 days ago
await partitionManager.dropPartitionDaysAgo(30);

Implementation

const date = new Date();
date.setDate(date.getDate() - daysAgo);
const tableName = `points_${toDateStr(date)}`;

await this.dataSource.query(`DROP TABLE IF EXISTS ${tableName}`);
this.logger.log(`Partition ${tableName} dropped.`);

Dropping partitions permanently deletes data. Ensure you have backups or data retention policies in place.

buildPartitionName()

Private helper method that generates partition table names. Source: src/app/points/partition-manager.service.ts:105

private buildPartitionName(date: Date): string

Parameters

date

Date

required

Date to generate partition name for

Returns

name

string

Partition name in format points_YYYY_MM_DD

Example

const name = this.buildPartitionName(new Date('2026-03-03'));
// Returns: "points_2026_03_03"

Scheduled Jobs

scheduleNextDay()

Automatically creates tomorrow’s partition every day at 23:50. Source: src/app/points/partition-manager.service.ts:112

@Cron('50 23 * * *')
async scheduleNextDay(): Promise<void>

Cron Schedule

schedule

string

default:"'50 23 * * *'"

Runs daily at 23:50 (10 minutes before midnight)

Behavior

Calculates tomorrow’s date and creates the partition:

const tomorrow = new Date();
tomorrow.setDate(tomorrow.getDate() + 1);
await this.createPartitionForDate(tomorrow);

This ensures that when midnight arrives, the new partition is ready to receive data.

cleanYesterday() (Commented)

Optional cron job for automatic cleanup, currently commented out:

// @Cron('5 0 * * *')  // Every day at 00:05 → drop yesterday's partition
// async cleanYesterday(): Promise<void> {
//   await this.dropPartitionDaysAgo(1);
// }

You can uncomment this to automatically drop partitions after one day, but use with caution in production.

Helper Functions

The service uses helper functions from ./helpers/dates.helper:

startOfDayMs()

Converts a date to the start of day in milliseconds (Unix timestamp).

const tsStart = startOfDayMs(new Date('2026-03-03'));
// Returns: 1709424000000

toDateStr()

Formats a date as YYYY_MM_DD for partition naming.

const dateStr = toDateStr(new Date('2026-03-03'));
// Returns: "2026_03_03"

Usage Example

Complete example of bootstrapping and using the partition manager:

import { Injectable, OnApplicationBootstrap } from '@nestjs/common';
import { PartitionManagerService } from './partition-manager.service';

@Injectable()
export class PointsModule implements OnApplicationBootstrap {
  constructor(
    private readonly partitionManager: PartitionManagerService,
  ) {}

  async onApplicationBootstrap() {
    // Create parent table if it doesn't exist
    await this.partitionManager.createParentTableIfNotExists();
    
    // Create partition for today
    await this.partitionManager.createPartitionForDate(new Date());
    
    // Create partition for tomorrow
    const tomorrow = new Date();
    tomorrow.setDate(tomorrow.getDate() + 1);
    await this.partitionManager.createPartitionForDate(tomorrow);
    
    // Optionally clean up old data (e.g., 90 days old)
    // await this.partitionManager.dropPartitionDaysAgo(90);
  }
}

Best Practices

Partition Retention

Decide on a data retention policy based on your needs:

// Keep 30 days of data
@Cron('0 1 * * *') // Daily at 1 AM
async cleanupOldPartitions() {
  await this.partitionManager.dropPartitionDaysAgo(30);
}

Pre-create Partitions

Create partitions in advance to avoid runtime errors:

// Create partitions for next 7 days
async prepareWeeklyPartitions() {
  for (let i = 0; i < 7; i++) {
    const date = new Date();
    date.setDate(date.getDate() + i);
    await this.partitionManager.createPartitionForDate(date);
  }
}

Error Handling

All methods throw errors if partition operations fail:

try {
  await partitionManager.createPartitionForDate(new Date());
} catch (error) {
  this.logger.error('Failed to create partition', error);
  // Handle error appropriately
}

Query Performance

Partitioning improves query performance by:

Partition Pruning: PostgreSQL only scans relevant partitions
Smaller Indexes: Each partition has its own smaller indexes
Parallel Queries: Can query multiple partitions in parallel

Example Query

SELECT * FROM points
WHERE tracker_device_imei = 123456789
  AND timestamp BETWEEN 1709424000000 AND 1709510400000;

PostgreSQL automatically routes this to the points_2026_03_03 partition and uses the idx_points_2026_03_03_imei_ts index.

Next Steps

Database Configuration - Learn about the overall database setup
Point Model - See the complete Point entity definition

Authentication

Points

Users

Database

​Overview

​Key Features

​Constructor

​Methods

​createParentTableIfNotExists()

​Implementation Details

​Example

​Parent Table Schema

​createPartitionForDate()

​Parameters

​Partition Creation Process

​Index Strategy

1. Timestamp Index

2. IMEI + Timestamp Index

3. IMEI + Timestamp + Trip Index

4. Geospatial Index (GIST)

​Example

​Duplicate Handling

​dropPartitionDaysAgo()

​Parameters

​Example

​Implementation

​buildPartitionName()

​Parameters

​Returns

​Example

​Scheduled Jobs

​scheduleNextDay()

​Cron Schedule

​Behavior

​cleanYesterday() (Commented)

​Helper Functions

​startOfDayMs()

​toDateStr()

​Usage Example

​Best Practices

​Partition Retention

​Pre-create Partitions

​Error Handling

​Query Performance

​Example Query

​Next Steps

Build docs developers (and LLMs) love

Overview

Key Features

Constructor

Methods

createParentTableIfNotExists()

Implementation Details

Example

Parent Table Schema

createPartitionForDate()

Parameters

Partition Creation Process

Index Strategy

Example

Duplicate Handling

dropPartitionDaysAgo()

Parameters

Example

Implementation

buildPartitionName()

Parameters

Returns

Example

Scheduled Jobs

scheduleNextDay()

Cron Schedule

Behavior

cleanYesterday() (Commented)

Helper Functions

startOfDayMs()

toDateStr()

Usage Example

Best Practices

Partition Retention

Pre-create Partitions

Error Handling

Query Performance

Example Query

Next Steps