How to Calculate SaaS Pricing in PostgreSQL

How can I calculate SaaS subscription pricing tiers in PostgreSQL?

Use PostgreSQL CASE expressions, CTEs, and aggregate functions to compute tiered or usage-based SaaS subscription prices directly in SQL.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why calculate SaaS pricing inside PostgreSQL?

Running pricing logic in SQL keeps billing close to the data, removes app code duplication, and lets finance teams audit numbers quickly.

What tables and columns are required?

You need a subscriptions table (customer_id, plan, seats, usage_units) and a pricing table (plan, base_fee, unit_price, included_units).

How do I model tiered SaaS pricing?

Join subscriptions to pricing and use CASE to add overage charges when usage exceeds included units.

Example tiered formula

total = base_fee + GREATEST(usage_units – included_units, 0) * unit_price

Can I calculate usage-based pricing per invoice period?

Create a CTE that aggregates usage events per subscription per month, then apply the same formula.

How do I bill annually with discounts?

Store contract_term and discount_pct; multiply the monthly total by 12 and apply the discount in one CASE expression.

Best practices for performance?

Index foreign keys, cache pricing in a materialized view, and schedule recalculations with REFRESH CONCURRENTLY.

What’s the safest way to update pricing?

Version your pricing table with valid_from/valid_to columns and reference the correct row using WHERE current_date BETWEEN.

Why How to Calculate SaaS Pricing in PostgreSQL is important

How to Calculate SaaS Pricing in PostgreSQL Example Usage


-- Calculate May 2024 invoice totals for each customer
WITH monthly_usage AS (
  SELECT
    o.customer_id,
    DATE_TRUNC('month', o.order_date) AS invoice_month,
    SUM(oi.quantity) AS usage_units
  FROM Orders o
  JOIN OrderItems oi ON oi.order_id = o.id
  WHERE DATE_TRUNC('month', o.order_date) = '2024-05-01'
  GROUP BY 1,2
)
SELECT
  c.id,
  c.name,
  mu.usage_units,
  p.base_fee,
  p.included_units,
  p.unit_price,
  p.base_fee + GREATEST(mu.usage_units - p.included_units,0) * p.unit_price AS may_invoice
FROM monthly_usage mu
JOIN Customers c ON c.id = mu.customer_id
JOIN subscriptions s ON s.customer_id = c.id
JOIN pricing p ON p.plan = s.plan;

How to Calculate SaaS Pricing in PostgreSQL Syntax


-- Basic tiered SaaS pricing formula
SELECT
  s.customer_id,
  s.plan,
  p.base_fee,
  s.usage_units,
  p.included_units,
  p.unit_price,
  p.base_fee + GREATEST(s.usage_units - p.included_units, 0) * p.unit_price AS monthly_charge
FROM subscriptions AS s
JOIN pricing AS p ON p.plan = s.plan;

-- Annual billing with discount per customer in Customers table
SELECT
  c.id AS customer_id,
  c.name,
  (p.base_fee + GREATEST(s.usage_units - p.included_units, 0) * p.unit_price) * 12 * (1 - COALESCE(c.discount_pct,0)) AS annual_charge
FROM Customers c
JOIN subscriptions s ON s.customer_id = c.id
JOIN pricing p ON p.plan = s.plan;

Common Mistakes

Mistake: Hard-coding prices in SQL queries. Why wrong: updates require code changes and risk stale data. Fix: keep prices in a dedicated pricing table with versioning.
Mistake: Ignoring overage caps. Why wrong: customers can exceed budget unexpectedly. Fix: use CASE to cap charges or alert when usage > cap_units.