An open-source relational database, PostgreSQL is a powerful and well-known system that has received recognition from both its users and the industry at large. Unlike some other database systems, PostgreSQL is completely customizable and yours to do with as you please (assuming, of course, that your instance is self-hosted).
For a more in-depth look at PostgreSQL, click here.
This guide serves as a reference for version 1 of Stitch’s PostgreSQL destination.
Details and features
Stitch features
High-level details about Stitch’s implementation of PostgreSQL, such as supported connection methods, availability on Stitch plans, etc.
| Release status |
Released |
| Stitch plan availability |
All Stitch plans |
| Stitch supported regions |
Operating regions determine the location of the resources Stitch uses to process your data. Learn more. |
| Supported versions |
9.3 or higher |
| Connect API availability |
Supported
This version of the PostgreSQL destination can be created and managed using Stitch’s Connect API. Learn more. |
| SSH connections |
Supported
Stitch supports using SSH tunnels to connect to PostgreSQL destinations. |
| SSL connections |
Supported
Stitch will attempt to use SSL to connect by default. No additional configuration is needed. |
| VPN connections |
Unsupported
Virtual Private Network (VPN) connections may be implemented as part of a Premium plan. Contact Stitch Sales for more info. |
| Static IP addresses |
Supported
This version of the PostgreSQL destination has static IP addresses that can be whitelisted. |
| Default loading behavior |
Upsert |
| Nested structure support |
Unsupported
|
Destination details
Details about the destination, including object names, table and column limits, reserved keywords, etc.
Note: Exceeding the limits noted below will result in loading errors or rejected data.
| Maximum record size |
20MB |
| Table name length |
63 characters |
| Column name length |
59 characters |
| Maximum columns per table |
250-1,600 |
| Maximum table size |
None |
| Maximum tables per database |
100,000 |
| Case sensitivity |
Sensitive |
| Reserved keywords |
Refer to the Reserved keywords documentation. |
PostgreSQL pricing
Pricing for PostgreSQL depends on where your instance is hosted.
-
Self-hosted: PostgreSQL is open-source, meaning you don’t need to pay an upfront cost to obtain the necessary software. You may, however, have hosting and maintenance costs associated with the server housing the instance. You may have to do a little bit of internal number crunching to figure out these potential costs.
-
Heroku: Heroku has a variety of plans to choose from, and a guide to help you select the right plan for you or your company.
-
Amazon Aurora and RDS: Amazon offers a variety of plans for both on-demand instances and Multi-AZ Deployment. To get an estimate of what your monthly bill might look like, check out their monthly calculator.
-
Google CloudSQL PostgreSQL: Unlike many other cloud-based data warehouse solutions, Google’s pricing model is based on usage and not a fixed-rate. This means that your bill can vary over time.
Before fully committing yourself to using Google CloudSQL PostgreSQL as your data warehouse, we recommend familiarizing yourself with Google’s pricing model and using their pricing calculator to estimate your potential costs.
Replication
Replication process overview
A Stitch replication job consists of three stages:
Step 1: Data extraction
Stitch requests and extracts data from a data source. Refer to the System overview guide for a more detailed explanation of the Extraction phase.
Step 2: Preparation
During this phase, the extracted data is buffered in Stitch’s durable, highly available internal data pipeline and readied for loading. Refer to the System overview guide for a more detailed explanation of the Preparation phase.
Step 3: Loading
Stitch loads the data into PostgreSQL.
Loading behavior
By default, Stitch will use Upsert loading when loading data into PostgreSQL.
If the conditions for Upsert loading aren’t met, data will be loaded using Append-Only loading.
Refer to the Understanding loading behavior guide for more info and examples.
Primary Keys
Stitch requires Primary Keys to de-dupe incrementally replicated data. When tables are created in the destination, Stitch will apply Primary Key constraints to columns used as Primary Keys. Primary Key constraints require that column values be unique and not null.
Incompatible sources
The PostgreSQL destination has reported incompatibilities with some of Stitch's integrations. Refer to the table below for more info.
| Integration | Version | Level | Reason |
| HubSpot | ANY |
Flattening nested JSON structures may result in tables and columns with names that exceed PostgreSQL’s character limit for object names:
|
|
| Stripe | ANY |
Flattening nested JSON structures may result in tables and columns with names that exceed PostgreSQL’s character limit for object names:
|
See all destination and integration incompatibilities.
Transformations
System tables and columns
Stitch will create the following tables in each integration’s dataset:
Additionally, Stitch will insert system columns (prepended with _sdc) into each table.
Data typing
Stitch converts data types only where needed to ensure the data is accepted by PostgreSQL. In the table below are the data types Stitch supports for PostgreSQL destinations, and the Stitch types they map to.
- Stitch type: The Stitch data type the source type was mapped to. During the Extraction and Preparing phases, Stitch identifies the data type in the source and then maps it to a common Stitch data type.
- Destination type: The destination-compatible data type the Stitch type maps to. This is the data type Stitch will use to store data in PostgreSQL.
- Notes: Details about the data type and/or its allowed values in the destination, if available. If a range is available, values that exceed the noted range will be rejected by PostgreSQL.
| Stitch type | Destination type | Notes |
| BIGINT | BIGINT |
|
| BOOLEAN | BOOLEAN | |
| DATE | TIMESTAMP |
|
| DOUBLE | DOUBLE PRECISION | |
| FLOAT | DOUBLE PRECISION | |
| INTEGER | INTEGER |
|
| NUMBER | DECIMAL |
|
| STRING | TEXT |
|
JSON structures
PostgreSQL destinations don’t have native support for nested data structures. To ensure nested data can be loaded, Stitch will flatten objects and arrays into columns and subtables, respectively. For more info and examples, refer to the Handling nested data structures guide.
Column names
Column names in PostgreSQL:
- Must contain only letters (a-z, A-Z), numbers (0-9), or underscores (
_) - Must begin with a letter or underscore
-
Must be less than the maximum length of 59 characters. Columns that exceed this limit will be rejected by PostgreSQL.
- Must not be prefixed or suffixed with any of PostgreSQL’s or Stitch’s reserved keyword prefixes or suffixes
Stitch will perform the following transformations to ensure column names adhere to the rules imposed by PostgreSQL:
| Transformation | Source column | Destination column |
| Remove special characters |
customer#id or !CuStoMeRId
|
customerid and CuStoMeRId
|
| Remove non-letter leading characters |
4customerid or _customerid
|
customerid
|
Timezones
PostgreSQL will store the value as TIMESTAMP WITH TIMEZONE. In PostgreSQL, this data is stored with timezone information and expressed as UTC.
Compare destinations
Not sure if PostgreSQL is the data warehouse for you? Check out the Choosing a Stitch Destination guide to compare each of Stitch’s destination offerings.
| Related | Troubleshooting |
Questions? Feedback?
Did this article help? If you have questions or feedback, feel free to submit a pull request with your suggestions, open an issue on GitHub, or reach out to us.