PostgreSQL Node

Overview

The PostgreSQL Node is a database integration component that automates data synchronization from PostgreSQL databases. This node enables scheduled fetching of rows from specified tables and materialized views, supporting vectorization and indexing for Retrieval-Augmented Generation (RAG) workflows.

This node is a Batch Trigger node that automates database synchronization processes and enables RAG workflows with PostgreSQL data.

Features

Prerequisites

Before using PostgreSQL Node, ensure the following:

• PostgreSQL Database: Access to a PostgreSQL database with appropriate credentials.
• Database Permissions: Read permissions on target schemas, tables, and materialized views.
• Connection Details: Database host, port, database name, username, and password.
• Cron Expression Knowledge: Understanding of cron expressions for scheduling flows.
• Network Access: Proper network connectivity to the PostgreSQL database server.

Setup

Step 1: Set Up PostgreSQL Credentials

Please refer to the Postgres Integration documentation to complete the setup and obtain the necessary credentials.

Step 2: Configure PostgreSQL Credentials

Use the following format to set up your credentials:

Step 3: Set Up Lamatic Flow

1. Add PostgreSQL Node: Drag the PostgreSQL node to your flow
2. Enter Credentials: Provide your database connection details
3. Configure Schema: Enter the database schema to process
4. Set Table/View: Specify the table or materialized view name

Configuration Reference

Batch Trigger Configuration

Sync Configuration Options

Sync Modes

# Incremental Sync (recommended)
sync_mode: "incremental"  # Only sync new/modified data
 
# Full Refresh
sync_mode: "full-refresh"  # Re-index all data

Schedule Examples

# Daily at midnight
sync_schedule: "0 0 * * *"
 
# Every 6 hours
sync_schedule: "0 */6 * * *"
 
# Weekdays only at 9 AM
sync_schedule: "0 9 * * 1-5"
 
# Every 30 minutes
sync_schedule: "*/30 * * * *"

Action Configuration

Configure the Postgres node by selecting the desired action and providing the necessary parameters.

Low-Code Example

triggerNode:
  nodeId: triggerNode_1
  nodeType: postgresNode
  nodeName: PostgreSQL
  values:
    credentials: PostgreSQL Connection
    schema: public
    table: users
    sync_mode: incremental
    sync_schedule: "0 0 * * *"

Event Trigger Output

The PostgreSQL node outputs database data in the following format:

Example Output

{
  "table_name": "users",
  "schema_name": "public",
  "data": [
    {
      "id": 1,
      "name": "John Doe",
      "email": "[email protected]",
      "created_at": "2024-01-01T00:00:00Z"
    },
    {
      "id": 2,
      "name": "Jane Smith",
      "email": "[email protected]",
      "created_at": "2024-01-02T00:00:00Z"
    }
  ],
  "metadata": {
    "total_rows": 2,
    "sync_mode": "incremental",
    "last_sync": "2024-01-01T12:00:00Z"
  }
}

Output Schema

• table_name: String name of the processed table
• schema_name: String name of the database schema
• data: Array of objects containing row data with column names as keys
• metadata: Additional information about the sync operation
  • total_rows: Number of rows processed
  • sync_mode: Sync mode used (incremental/full-refresh)
  • last_sync: Timestamp of the sync operation

Output Schema

Batch Trigger Output

• table_name: String name of the processed table or view
• schema_name: String name of the database schema
• data: Array of objects containing row data
• metadata: Additional sync information
  • total_rows: Integer count of processed rows
  • sync_mode: String indicating sync mode used
  • last_sync: ISO timestamp of sync operation
  • columns: Array of column names in the table
  • row_count: Integer total number of rows in table

Troubleshooting

Common Issues

Debugging

• Check Lamatic Flow logs for detailed error messages
• Verify PostgreSQL credentials and database connectivity
• Test database connection using a PostgreSQL client
• Validate schema and table names exist and are accessible
• Confirm database user has read permissions on target objects
• Monitor sync logs for specific table processing errors
• Test with a small table before processing large datasets
• If the connection fails, whitelist the following IPs: https://www.cloudflare.com/ips/ (opens in a new tab)

Best Practices

• Use incremental sync mode for better performance with large tables
• Schedule syncs during off-peak hours to minimize database impact
• Use materialized views for complex queries that need regular updates
• Implement appropriate database indexes for better sync performance
• Regularly monitor sync logs for any issues
• Consider using read replicas for sync operations to avoid impacting production
• Test sync configurations with small datasets before full deployment
• Implement proper error handling for database connection failures

Example Use Cases

Data Intelligence Workflows

• User Analytics: Sync user behavior data for AI-powered insights
• Business Metrics: Process sales, inventory, and performance data
• Customer Data: Index customer profiles and interactions for personalized experiences
• Operational Data: Automate access to logs, events, and system metrics

RAG Applications

• Semantic Search: Enable natural language search across database records
• Question Answering: Build AI assistants that can answer questions about business data
• Data Summarization: Automatically summarize large datasets and reports
• Content Discovery: Help users find relevant information across database tables

Additional Resources

• PostgreSQL Documentation (opens in a new tab)
• Cron Expression Guide (opens in a new tab)
• PostgreSQL Connection Troubleshooting (opens in a new tab)