Thingworx – Notes of Analysis

Introduction:

In the context of Thingworx, maintaining a clean and optimized PostgreSQL database is crucial for ensuring optimal performance and reliability. Analyzing and cleaning up the database periodically can help identify and address issues related to data growth, performance bottlenecks, and disk space utilization.

Query for Analyzing Table Sizes:

To identify tables occupying the most space in your PostgreSQL database, you can use the following SQL query. This query retrieves the top 10 tables based on their total size, providing insights into where the majority of disk space is being consumed.

-- Query: Find Tables Occupying Maximum Space
-- Purpose: This query retrieves the top 10 tables in a PostgreSQL database based on their total size, showing the table name and its total size in a human-readable format.
-- Author: Swapnil Popat
-- Date: 09/05/2024

-- Selecting the table name and its total size
SELECT
    nspname || '.' || relname AS "Table",  -- Concatenating schema name and table name to display full table name
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "Total Size"  -- Converting total size to human-readable format
FROM
    pg_class C  -- pg_class contains metadata about tables, indexes, etc.
LEFT JOIN
    pg_namespace N ON (N.oid = C.relnamespace)  -- Joining pg_class with pg_namespace to get schema names
WHERE
    nspname NOT IN ('pg_catalog', 'information_schema')  -- Excluding system schemas
    AND relkind='r'  -- Selecting only regular tables (not indexes, sequences, etc.)
ORDER BY
    pg_total_relation_size(C.oid) DESC  -- Sorting the result by total size in descending order
LIMIT
    10;  -- Limiting the output to the top 10 tables

It excludes system tables (pg_catalog and information_schema) and sorts the result by total size in descending order, limiting the output to the top 10 tables.

You can execute this query using a PostgreSQL client such as psql or any GUI tool like pgAdmin or DBeaver connected to your PostgreSQL database.

Explanation of TRUNCATE:

When it comes to quickly deleting a large amount of data from a single table, the TRUNCATE statement can be highly effective. Unlike DELETE, which removes rows one by one and generates transaction log entries, TRUNCATE deallocates the data pages used by the table, making it much faster for large-scale data removal.

However, there are some important considerations to keep in mind when using TRUNCATE:

No WHERE Clause: Unlike DELETE, TRUNCATE does not support a WHERE clause. It will delete all rows in the table.
Faster, but Less Flexible: TRUNCATE is usually faster than DELETE, especially for large tables, but it’s less flexible and cannot be rolled back. Once you execute TRUNCATE, the data is gone.
Doesn’t Fire Triggers: TRUNCATE does not fire any triggers associated with the table.
Resets Auto-increment Values: If the table has auto-increment columns, TRUNCATE will reset the auto-increment values to their initial state.
Here’s how you can use TRUNCATE to quickly delete all data from a table:

TRUNCATE TABLE public.value_stream;

Safety Precautions:

Before performing any data deletion operation, it’s crucial to take backups to ensure that data can be restored in case of any unintended consequences. Additionally, be aware that TRUNCATE is an irreversible operation and cannot be rolled back, so use it with caution.

Additional Cleanup Techniques:

While TRUNCATE is efficient for deleting all data from a table, there are other cleanup techniques you may consider. These include using DELETE with batching for more controlled data removal, archiving old data to free up disk space, and optimizing database configurations for better performance.

DELETE with Batching: To delete a large amount of data in a controlled manner, use the DELETE statement with batching. This allows you to delete data in smaller chunks, reducing the impact on database performance.
Archiving Old Data: Identify and archive old or obsolete data to free up disk space and improve database performance. You can create archival tables or export data to external storage for long-term retention.
Optimizing Database Configurations: Fine-tune PostgreSQL configurations such as memory allocation, indexing strategies, and vacuuming parameters to optimize database performance and resource utilization.

Conclusion:

Regular analysis and cleanup of your Thingworx PostgreSQL database are essential for maintaining optimal performance and efficiency. By using tools like the provided SQL query and employing cleanup techniques like TRUNCATE, you can ensure that your database remains healthy and performs optimally for your Thingworx applications.

It Connects disparate devices and applications, from plant control systems to enterprise information systems.

KEPServerEX leverages OPC (the automation industry’s standard for interoperability) and IT-centric communication protocols (such as SNMP, ODBC, and web services) to provide users with a single source for industrial data.

KEPServerEX provides critical technical features that enable accessibility, aggregation, optimization, connectivity, security, and diagnostics.