ThingWorx Model Definition in Composer > Data Storage > Persistence Providers > Using PostgreSQL as the Persistence Provider
Using PostgreSQL as the Persistence Provider
PostgreSQL is an open source object-relational database system. The PostgreSQL persistence provider supports both model and data providers.
* 
Getting started with PostgreSQL requires registration, installation, and configuration. Most of this process is performed independently of ThingWorx, and is documented in Installing ThingWorx.
PostgreSQL HA
You can also use PostgreSQL High Availability (HA) as part of your data solution. HA offers the option to set up separate servers to capture reads and writes for data in case of a failure on the primary database server. If HA is part of your PostgreSQL implementation, refer to the appropriate sections in the guide for recommended installation and deployment details.
High Level Process for the PostgreSQL Implementation
1. Determine if PostgreSQL is the right solution for your data. Refer to the sizing and planning sections for additional information.
2. Download and install PostgreSQL. This process is performed independently of ThingWorx. A deployment example is provided in the guide.
* 
The standard PostgreSQL implementation includes a persistence provider, which cannot be edited within ThingWorx. However, you can edit it via the platform-settings.json or services.
3. If you want to create additional persistence provider instances in ThingWorx that will connect the PostgreSQL data store, go to Data Storage > Persistence Providers, and click the green plus sign (+).
4. On the General Information screen, enter a name for the persistence provider.
5. In the Persistence Provider Package field, use the magic picker to select the PostgreSQL persistence provider package.
6. Click Configuration and configure the following settings:
You can edit the following stream and value stream queue settings, which will apply to all streams and value streams. You cannot change these settings for a specific stream or value stream.
Stream Processor Settings
Base Type
Default
Notes
Max Queue Size
Number
250000
Maximum number of stream entries to queue.Once the specified value is reached, the following entries will be rejected.
Max Wait Time Before Flushing Stream Buffer (millisec)
Number
2000
Number of milliseconds the system waits before flushing the stream buffer
Number of Processing Threads
Number
5
Number of processing threads allocated to the stream
Max no. of Items Before Flushing Stream Buffer
Number
500
Maximum number of items to accumulate before flushing the stream buffer
Max no. of Stream Writes in Process Block
Number
2500
Maximum number of stream writes to process in one block
Buffer Status Scan Rate (millisec)
Number
5
The buffer status is checked at the specified rate value in milliseconds.
Query Timeout
Number
600000
The amount of time (in milliseconds) a query will wait to complete before it is cancelled.
Network Timeout
Number
900000
The amount of time (in milliseconds) for a thread to wait for response from database.
If no response is received within this configured time, platform will close the underlying connection and will release the thread waiting for the response.
Producer Timeout
Number
3000
This setting applies to the Stream Entry Processors and is currently only effective for Persistent Property Processor.
In the event that a queue is full and does not have space for a new entry, this is the maximum time (milliseconds) a producer will wait to put entry on the queue. Once this wait time is over, if still the queue is full and space cannot be made available, then the entry will fail to be added to queue.
Value Stream Processor Settings
Base Type
Default
Notes
Max Queue Size
Number
250000
Maximum number of value stream entries to queue. Once the specified value is reached, the following entries will be rejected.
Max Wait Time Before Flushing Value Stream Buffer (millisec)
Number
2000
Number of milliseconds the system waits before flushing the value stream buffer
Number of Processing Threads
Number
5
Number of processing threads allocated to the value stream
Max no. of Items Before Flushing Value Buffer
Number
500
Maximum number of items to accumulate before flushing the value stream buffer
Max no. of Value Stream Writes in Process Block
Number
2500
Maximum number of value stream writes to process in one block
Buffer Status Scan Rate (millisec)
Number
5
The buffer status is checked at the specified rate value in milliseconds.
Producer Timeout
Number
3000
This setting applies to the Stream Entry Processors and is currently only effective for Persistent Property Processor.
In the event that a queue is full and does not have space for a new entry, this is the maximum time (milliseconds) a producer will wait to put entry on the queue. Once this wait time is over, if still the queue is full and space cannot be made available, then the entry will fail to be added to queue.
Database Connection Stack Trace Settings
Default
Notes
Threshold Database Connection Pool Saturation to trigger Stack Traces (in Percent)
90
Threshold for a database connection pool to reach saturation to trigger Stack Traces.
Count of Set of Stack Traces logged once triggered
5
Count of Set of Stack Traces logged once the stack trace settings are triggered
Interval at which the Stack Traces will be logged (in Seconds)
10
The time interval at which the Stack Traces will be logged.
Minimum elapsed time before Stack Trace logging triggers again (in Minutes)
60
The minimum time elapsed before Stack Trace logging triggers again.
Duration the connection is held for Stack Traces to be logged (in milliseconds)
1000
The time duration the connection is held for Stack Traces to be logged.
Connection Information Needed to Connect to PostgreSQL
Name
Default Value
Notes
JDBC URL
jdbc:postgresql://localhost:5432/thingworx
The JDBC URL of the database from which connections should be acquired. You can specify multiple schemas in this URL.
Username
thingworx
User name used to acquire a database connection
Password
n/a
Password used to acquire a database connection
Initial Connection Pool Size
5
Number of connections a pool will try to acquire upon startup
Connection Acquire Increment
5
Determines how many connections will be acquired when the pool is exhausted
Max Connection Pool Size
100
Maximum number of connections a pool maintains at any given time
Min Connection Pool Size
5
Minimum number of connections a pool maintains at any given time
Max Cached Statements
100
Size of global PreparedStatement cache
Driver Class
org.postgresql.Driver
Database JDBC driver class
Acquire Retry Attempts
3
Defines how many times the connection pool will try to acquire a new connection
Acquire Retry Delay
10000
Time in milliseconds in which the connection pool will wait between acquire attempts
Checkout Retry Timeout
1000000
Number of milliseconds a client calling getConnection will wait for a connection to be checked in or acquired when the pool is exhausted
Max Idle Time
0
Seconds a connection can remain pooled but unused before being discarded. Zero means idle connections never expire.
Max Connection Age
0
Connections older than this time, in seconds, will be destroyed and purged from the pool. Zero means no maximum age is enforced.
Number of Helper Threads
8
Slow JDBC operations are generally performed by helper threads that do not hold contended locks. Spreading these operations over multiple threads can significantly improve performance by allowing multiple operations to be performed simultaneously.
Unreturned Connection Timeout
0
If the application acquires a connection but failed to close it within the specified period of time, in seconds, the pool will destroy the connection. Zero means no timeout, and the applications are expected to close their own connections.
Max Idle Time for Excess Connections
300
Number of seconds that connections in excess of minPoolSize should be permitted to remain idle in the pool before being destroyed. Zero means no enforcement and excess connections are not destroyed.
7. If necessary, migrate entities and data.
8. Monitor and maintain your PostgreSQL implementation. Best practices for creating a successful maintenance plan are described in the guide.
Was this helpful?