Build a SaaS link shortener with Postgres and ClickHouse

A link shortener combines everyday application features, such as sign-in, custom domains, and campaign management, with analytics that users expect to find inside the product.

Those features create two different database workloads. A redirect needs the current destination immediately. A dashboard needs to count and compare traffic across thousands, then millions, of events. Both belong in the same product, but they ask very different things of a database.

For a SaaS application with click analytics, use Postgres for application state and transactions, ClickHouse for analytical queries, and change data capture to keep the relevant metadata in sync. This gives you a unified data stack built around a dedicated engine for each workload.

Let's build that application. We'll use Shortwave, an open-source link-shortener example, with ClickHouse Managed Postgres, ClickHouse Cloud, and ClickPipes. Then we'll deploy the whole web application to Cloudflare Workers.

The finished app has sign-in, short links, editable destinations, tags, folders, UTM templates, downloadable QR codes, and an analytics dashboard. The example is designed for an operator and their invited users; billing, teams, and the abuse operations needed for an unrestricted public shortening service are outside its scope.

Keep the example README open alongside this guide. We'll walk through the architecture and the main deployment steps, with links to the complete commands and implementation as we go.

Which database should a link shortener use? #

Start with the operations your users actually perform.

Postgres is our choice for online transaction processing, or OLTP. Its transactions and relational constraints fit the important promises in this app: a link belongs to one account, a folder must belong to that same account, and a redirect should use a consistent snapshot of the link it records. Our OLTP vs. OLAP guide explains the broader distinction.

ClickHouse is our choice for online analytical processing, or OLAP. A question such as “Which campaigns generated the most clicks in the last 30 days?” touches many events but only a few columns. ClickHouse's columnar storage, compression, and vectorized execution are a natural fit. See what an OLAP database does and how columnar databases work.

You can build a small link shortener entirely on Postgres. We're adding ClickHouse because analytics is part of the product we're building, and we want its growing scans and aggregations to run on separate compute from redirects and edits. This is a best-of-breed pairing: Postgres handles the transactional work it excels at, while ClickHouse handles the analytical work it was built for.

A unified data stack, with separate engines #

Both databases run in ClickHouse Cloud, with ClickPipes providing managed replication between them. You get an integrated operational experience while retaining separate transactional and analytical engines that can be sized independently.

This isn't a single-engine hybrid transactional/analytical processing (HTAP) database. The application explicitly sends operational queries to Postgres and analytical queries to ClickHouse. Replication is asynchronous, so analytics can lag behind an edit. Our guide to unifying OLTP and OLAP explains this architectural choice and its tradeoffs.

The architecture we're going to deploy #

Shortwave uses TanStack Start and React for the application, Click UI for interface components, and Clerk for authentication. Cloudflare Workers runs the pages, server routes, redirects, and scheduled event delivery.

There are two routes into ClickHouse. ClickPipes copies link metadata; the scheduled Worker delivers click events. Keeping those responsibilities explicit makes the rest of the application easier to reason about.

You can follow both routes in the architecture document. Let's get the stack running.

1. Get the example and prepare your accounts #

You'll need Node.js 22.12 or later, Git, a ClickHouse Cloud account with Managed Postgres and Postgres ClickPipes available, a Cloudflare account with an active DNS zone, and a Clerk application. The database setup also uses psql, jq, and OpenSSL; the README prerequisites include installation commands.

Clone the example and install its dependencies:

Install clickhousectl, which we'll use to create and configure both databases and the ClickPipe:

Create a ClickHouse Cloud Admin API key, then authenticate both CLIs:

Wrangler is installed with the example's dependencies. Choose your organization, Cloud region, Postgres size, and provisioning IP range, then save them in the private .deployment/resources.env file described in the README. Its variable names are used in the following commands.

Start with ClickHouse Cloud's 30-day free trial, which includes $300 in credits. That's plenty to deploy and try this example's Postgres, ClickHouse, and CDC stack at no ClickHouse Cloud cost with modest sizing and test traffic. Review Cloud pricing when you're ready to keep it running beyond the trial. Keep the returned IDs and credentials: if setup is interrupted, you'll resume against the resources you already created.

2. Create Postgres and ClickHouse in the same Cloud region #

Once you've populated and loaded the deployment inputs, create the services:

These are the example's initial settings: one ClickHouse replica and Postgres without HA. Choose your production availability configuration separately; this tutorial configuration doesn't establish a failover guarantee.

Follow the database creation step to extract the service IDs, wait for readiness, download the Postgres CA certificate, and save connection details. Use the direct Postgres endpoint for migrations and replication.

Next, apply the supplied SQL in the README's documented order. It creates separate migration, runtime, and replication users, followed by the application tables and grants. The schema starts in 001_initial.sql; subsequent migrations add saved tags, folders, and custom-domain relationships.

This is where Postgres earns its place. Ownership isn't just a UI filter: account-scoped operations and relational constraints protect the data model. The server derives the account from a verified Clerk session before it reads or changes that account's records. See the application service and our guide to multi-tenant SaaS architecture on Postgres.

Finally, complete the ClickHouse schema and runtime-user step. The event table lives in link_shortener; the replicated metadata will live in default.cdc_links. The application receives only the database permissions it needs to serve the app and deliver events.

3. Connect link metadata with ClickPipes #

Imagine you tag a link newsletter. You want the analytics dashboard to show traffic for links currently carrying that tag, including clicks recorded before you added it.

For that to work efficiently, ClickHouse needs the link's current metadata as well as its click history. Postgres ClickPipes handles the initial snapshot and subsequent inserts, updates, and deletes through change data capture (CDC).

The example publishes only public.links. Accounts, domain ownership records, and the click outbox stay outside that publication. Its publication SQL and table mapping make that scope explicit.

After completing the roles, publication, and connection settings in the preceding steps, create the ClickPipe:

Keep shell tracing off when passing passwords, and save the returned ClickPipe ID privately. Complete the verification and read grants before moving on.

ClickPipes creates and owns cdc_links. Don't apply the example's local metadata fixture to Cloud: that fixture is for a different environment.

The destination uses ReplacingMergeTree with row versions and delete markers. Current-state queries use FINAL and _peerdb_is_deleted = 0 to select the latest replicated, non-deleted rows. That follows the ClickPipes deduplication guidance.

The configured ten-second sync interval isn't an end-to-end freshness guarantee. When metadata is still catching up, the app compares link revisions and reports a syncing state. Redirects continue to read their destination directly from Postgres.

4. Record clicks durably, then query them in ClickHouse #

Let's follow a request to https://go.example.com/r/launch.

The Worker looks up the exact hostname and slug in Postgres, checks that the domain and link are valid, and inserts a click event into click_outbox inside the transaction. Once that transaction commits, it returns an HTTP 302 redirect with caching disabled. The code is in resolveRedirect and the redirect route.

The outbox is a durable queue in Postgres. It lets us capture the event before responding without waiting for ClickHouse to accept it. If ClickHouse is unavailable, events wait for delivery; if Postgres or the outbox commit fails, the redirect fails too. That's the explicit reliability tradeoff in this example.

Every minute, a scheduled Worker drains up to five batches of 500 events. The outbox sender uses row locks with SKIP LOCKED, retries failures, and waits for ClickHouse's asynchronous insert buffer to flush before removing delivered events. The settings are async_insert = 1 and wait_for_async_insert = 1; see the insert-strategy documentation.

That schedule means the dashboard updates asynchronously, typically after a delivery tick, with further delay possible under backlog or failures. It also gives the example a bounded delivery budget of at most 2,500 events per scheduled invocation. Increase and test delivery capacity before using it for sustained high-volume traffic.

Count events once, even when delivery retries #

An insert might succeed in ClickHouse while its acknowledgement is lost. The sender then retries the same event. We handle that by assigning each event an immutable ID and counting distinct event IDs in reports.

Here's a simplified version of the daily query in analytics.ts, with the database name made explicit:

The server supplies the account and date parameters. uniqExact(event_id) prevents repeat deliveries from inflating the total; the table's sorting key does not enforce uniqueness.

The event schema orders data by (account_id, occurred_at, link_id, event_id). That matches the dashboard's account and time filters, helping ClickHouse skip irrelevant data. See choosing a primary key for the design principle.

For a tag filter, the application adds a membership check against the replicated links:

Add that condition to the preceding query's WHERE clause, before GROUP BY. It's a membership test, so matching metadata doesn't multiply the event rows.

There are two useful product semantics here. Tags describe links now; UTM values describe the click when it happened. Changing a link's tags can regroup its earlier traffic after CDC catches up. Changing a saved UTM template doesn't rewrite historical events.

The dashboard counts recorded redirect GET requests, including bots and previews. HEAD requests don't count. These are click-event totals, not unique people or proof that the destination page loaded. The example reports in UTC and gives raw events an eventual 180-day TTL.

5. Deploy the application to Cloudflare Workers #

Now let's put the application online. Finish the runtime configuration and Postgres checks, then follow the Workers hosting guide.

Choose an app hostname and a short-link hostname, such as app.example.com and go.example.com. Configure a production Clerk application and restrict signup to your intended users. Cloudflare hosts the application; both databases remain in ClickHouse Cloud.

Connect Workers to Postgres through Hyperdrive #

Hyperdrive provides connection pooling between the Worker and Postgres. Upload the Postgres CA certificate and save its ID, then create the connection using the runtime user:

Two settings matter for this application. verify-full verifies the Postgres server's certificate and hostname. Disabling Hyperdrive query caching ensures a cached lookup doesn't keep serving an old destination or enabled state. Keep the pooling; read current operational data. The TLS documentation covers the certificate setup.

Follow the hosting guide to configure database ingress, record the Hyperdrive ID, and fill .deployment/wrangler.json with your account, hostnames, binding, and schedule. Workers Custom Domains supplies routing and HTTPS. The configured Cron Trigger invokes event delivery every minute.

Build and deploy #

After loading the deployment and Clerk settings as shown in the hosting guide, build with your private configuration:

Check the generated account, Worker name, binding, hostnames, and scheduler against your saved inputs. Create the runtime-only secrets file using the hosting guide's exact list, then deploy that generated configuration:

Hyperdrive holds the Postgres runtime connection. The Worker receives its Clerk and ClickHouse runtime secrets; administrator, migration, replication, and Cloud-management credentials stay out of it.

You now have a hosted app and scheduler. Your laptop isn't part of the running system.

6. Create a link and check the whole data flow #

Open your app, sign in, and choose Domains. Add your short hostname, publish the exact TXT challenge shown by the app, and verify it. This ownership check is separate from Cloudflare's routing and HTTPS setup; both need to succeed. The custom-domain guide explains the steps.

Create a link to a page you control, give it a tag, and add a campaign through the UTM editor. Open its short URL and check that you land on the expected destination. After scheduled delivery, open Analytics and find the recorded event.

Then try the changes that exercise the architecture:

1. Edit the destination. The next request should redirect to the new URL, without waiting for ClickPipes.
2. Change the tag. Once metadata has synced, the earlier traffic should appear under the new current tag.
3. Disable the link. It should return HTTP 410.
4. Send a HEAD request. It should resolve without increasing the click count.
5. Sign in with a second account. It must not be able to read or modify the first account's data.
6. Close your deployment terminal. New visits should still reach the dashboard through the hosted scheduler.

A QR code encodes the short URL, so you can change the destination without reprinting the code. Try that too: it's a useful demonstration of why the redirect belongs in the transactional database.

Use the fresh-deployment checklist for the full acceptance pass, including CDC changes and retry-safe counts. The operations guide covers delivery failures, credential rotation, upgrades, and removing the Cloud resources when you're finished.

Why use ClickHouse Managed Postgres rather than another Postgres host? #

For this app, we're choosing the transactional service for two reasons: its Postgres performance and its integration with the analytical stack.

ClickHouse Managed Postgres puts the primary database on local NVMe storage alongside compute. A database page read can use local storage rather than requiring a network round trip to a separate storage service. The benchmark configuration below uses an AWS m8gd.4xlarge VM with NVMe as primary storage, giving Postgres fast local I/O for transactional workloads whose working data doesn't all fit in memory.

For measured results, let's use PostgresBench, our public, reproducible pgbench benchmark. This selection compares 16 vCPUs, 64 GB RAM, scale factor 6,849, 256 clients, and three 600-second runs of the built-in TPC-B-like workload in AWS us-east-2. All selected entries are labeled “No HA.” Values below are arithmetic means across their three runs, rounded for readability.

In this configuration, ClickHouse Managed Postgres delivers about 3.3 times Neon's throughput, with lower mean transaction latency. These are database benchmark results, not Shortwave redirect timings or an application capacity claim. Hardware, storage architectures, and Postgres minor versions differ; Neon reports 18.2 and the other selected entries report 18.3. The benchmark's HA labels also don't imply identical provider durability architectures. See the methodology and limitations before applying the numbers to your workload.

For a deployment budget, include Postgres, ClickHouse, CDC, Workers, Hyperdrive, and authentication, using the pricing links in the README. Match the compute sizes, storage, availability settings, and expected usage when comparing provider costs.

Performance is only part of our choice. We also get the Postgres service, ClickHouse analytics service, and managed CDC through one Cloud platform. We still configure each component, but we don't have to operate a separate replication platform ourselves. If you want to compare other buying criteria, our managed Postgres providers for SaaS guide goes deeper.

Adapt the example to your application #

The most useful thing to take from Shortwave is the boundary between current state and historical events. Keep ownership and mutable business records in Postgres. Send the events you want to analyze to ClickHouse. Replicate the metadata those reports need, and make the freshness and counting rules explicit in the product.

For Shortwave, that means an edit takes effect on the next redirect while the dashboard catches up asynchronously. We can expand the dashboard without moving authorization into an analytical replica, or change the link-management UI without rebuilding event ingestion.

Start with the Shortwave example, create your ClickHouse Managed Postgres and ClickHouse services, and follow the deployment steps. You'll have a concrete application to extend, with the transactional and analytical parts already connected.