How to Calculate SaaS Pricing in PostgreSQL

Galaxy Glossary

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.

Sign up for the latest in SQL knowledge from the Galaxy Team!
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

Table of Contents

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

Frequently Asked Questions (FAQs)

Can I run pricing calculations daily?

Yes. Use a cron job or Postgres cron extension to insert invoice lines nightly.

How do I handle currency conversion?

Store charges in the smallest unit (cents) and convert in the reporting layer or with a rates table joined by date.

Want to learn about other SQL terms?

Trusted by top engineers on high-velocity teams
Aryeo Logo
Assort Health
Curri
Rubie Logo
Bauhealth Logo
Truvideo Logo
Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)
Oops! Something went wrong while submitting the form.