DEV Community

Corey Ward
Corey Ward

Posted on

Use Postgres as a Segment Data Warehouse

Segment allows users to connect a Postgres database as a data warehouse. Officially they only support Heroku, who seems to be on its way out, and AWS RedShift, which has rather complex pricing and a more limited feature set than a standard Postgres install. Unofficially, though, the service works with any standard, modern Postgres install.

Neon is a serverless-oriented provider of hosted, managed Postgres databases that scale automatically based on usage. It's convenient, and because you don't have to commit to a storage tier and worry about complicated processes for upgrading to larger instances, it winds up priced in a compelling manner.

Connecting Segment to Neon is a mostly straightforward affair, but because Segment doesn't support SNI-based TLS on Postgres connections, Neon needs a bit of additional information to know which endpoint is being connected to. Usually it's sufficient to add this information to the connection string, but Segment insists on having all of the values broken up and fails if you modify the hostname to include a path or query string. Thankfully, Neon has a multitude of ways to specify the endpoint, including one that works with Postgres.

Connecting to Neon PG from Segment

In the Segment interface, create a new Postgres data warehouse. Be sure to choose "Destination" connection and not the "Source" version. Choose a source as desired, and then you'll get brought to the connection details screen.

Connection details screen

From here you'll enter most of the values copied from your Neon connection string, but when you get to the password field you'll need to make a change. Instead of entering the password as-is, you'll prefix it with a configuration flag specifying the endpoint, like so:

Enter fullscreen mode Exit fullscreen mode

The endpoint ID is the first-listed (leftmost) subdomain in the hostname. For example, if your full hostname is, the endpoint ID is ep-fun-coding-94135731. If your password is abcd1234, your final value for the password-field would be:

Enter fullscreen mode Exit fullscreen mode

Plug that in and you'll get something like this

connection settings with credentials in place

Click "Connect" and you should be all set!

Top comments (0)