Scaling PostgreSQL in Production: Indexing, RPC Functions, Views, and Query Optimization

Q: How do you find which queries to optimise first?

Read execution plans with EXPLAIN ANALYZE rather than guessing from response times, and aggregate per-query cost across real traffic so you optimise the query burning the most total time, not the one that felt slow once.

A practical look at how composite indexes, database views, Supabase RPC functions, pagination, and query optimization techniques improved the performance and maintainability of a multi-tenant PostgreSQL system handling over 120,000 daily database requests.

TL;DR — On a 99-table multi-tenant Postgres system serving 120,000+ requests a day, the wins came in this order. Fewer, better composite indexes beat more single-column ones, because an index only helps if its leading columns match how you actually filter. Push work into the database — RPC functions and views turn three round trips and a join in application memory into one planned query. Stop retrieving what you don’t use: SELECT * on a wide table, unpaginated lists, and sorts nobody reads are pure waste. Aggregate in SQL, not in Node. And use UUIDv7 for new tables so primary keys stay time-ordered instead of scattering index writes across random pages. None of this required re-architecting.

As applications grow, database performance becomes one of the biggest factors affecting user experience. Features can be implemented quickly, but poorly optimized queries, inefficient data access patterns, and increasing data volume eventually create bottlenecks.

Recently, I worked on optimizing a multi-tenant PostgreSQL system built on Supabase. The system consisted of:

99 database tables
Nearly 1 million records in the largest table
More than 120,000 database requests per day
Complex reporting and dashboard requirements
Transactional workloads with strict consistency requirements

While the application continued to function correctly, several reporting and aggregation queries had started taking multiple seconds to execute. As the dataset grew, it became clear that improvements were needed to keep the system responsive and maintainable.

This article covers the techniques that produced the biggest impact.

Understanding the Bottlenecks

The application served multiple tenants from a shared database. Most operations were tenant-scoped and involved data spread across several related tables.

The primary performance challenges were:

Slow reporting queries
Repeated aggregation logic
Increasing dashboard generation time
Large result sets being fetched unnecessarily
Excessive application-side processing
Growing query complexity

Rather than attempting a complete redesign, the focus was on improving the existing architecture through targeted database optimizations.

Composite Indexes Instead of More Indexes

One of the first discoveries was that several frequently executed queries were filtering on multiple columns simultaneously.

A common mistake is creating separate indexes for every column and expecting PostgreSQL to combine them efficiently via index merging. In practice, composite indexes often provide significantly better results when they match actual query patterns because they allow Postgres to perform a single index scan to satisfy multiple filters.

For example, instead of indexing columns independently:

-- ❌ Suboptimal: Individual index on each column
CREATE INDEX idx_events_tenant_id ON events (tenant_id);
CREATE INDEX idx_events_user_id ON events (user_id);
CREATE INDEX idx_events_event_date ON events (event_date);

we created a single composite index tailored to our access patterns — remembering that a multicolumn index is only usable by a query that constrains its leading column, which is what makes column order a design decision rather than a detail:

-- ✅ Optimal: Single composite index matching query filters
CREATE INDEX idx_events_tenant_user_date ON events (tenant_id, user_id, event_date);

Similarly, transaction-related queries benefited from composite indexes structured like:

CREATE INDEX idx_transactions_composite 
ON transactions (tenant_id, transaction_type, created_at, status_id);

and scheduling-related operations used patterns such as:

CREATE INDEX idx_schedules_composite 
ON schedules (tenant_id, category_id, week_number, day_of_week);

The most important lesson was that index order matters.

The leading columns in the index should closely match how the query filters data. Since most queries were tenant-scoped, the tenant identifier typically appeared first. If a query filters on tenant_id and user_id but not event_date, the composite index is still utilized. However, a query filtering only on event_date would not benefit from this index.

After reviewing query patterns and introducing composite indexes where appropriate, reporting workloads showed noticeable improvements and several slow queries were eliminated entirely.

Moving Business Logic into Supabase RPC Functions

Initially, many operations required multiple database round trips.

The application would:

Fetch data
Perform calculations
Execute additional queries
Aggregate results
Return the final response

This approach worked well during the early stages of development but became increasingly expensive as datasets grew and the latency of consecutive queries compounded.

To reduce overhead, complex operations were moved into Supabase RPC functions (PostgreSQL PL/pgSQL database functions).

Instead of multiple client-server interactions:

Application
 ├─ Query A (Fetch tenant orders)
 ├─ Query B (Fetch menu metrics)
 ├─ Query C (Insert audit log)
 └─ Processing (Aggregate in Node.js)

the flow became:

Application
 └─ RPC Function (get_tenant_dashboard_metrics)
      ├─ Validation
      ├─ Aggregation
      ├─ Calculations
      └─ Result

Here is a simplified example of how we defined the database function in Postgres:

CREATE OR REPLACE FUNCTION get_tenant_summary(p_tenant_id UUID)
RETURNS TABLE (
  total_revenue NUMERIC,
  completed_orders BIGINT
) LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
  RETURN QUERY
  SELECT 
    COALESCE(SUM(amount), 0) AS total_revenue,
    COUNT(id) FILTER (WHERE status = 'completed') AS completed_orders
  FROM transactions
  WHERE tenant_id = p_tenant_id;
END;
$$;

We then call this function in a single, fast client-side request:

const { data, error } = await supabase
  .rpc('get_tenant_summary', { p_tenant_id: tenantId });

This reduced network round trips, centralized business logic, and improved execution speed. The biggest performance gains were observed in reporting-related workloads where response times dropped from several seconds to millisecond-level execution.

Simplifying Data Access with Database Views

As the application evolved, certain joins and aggregations began appearing repeatedly throughout the codebase. The same datasets were being reconstructed in multiple places.

To solve this, database views were introduced.

For example, to build a consolidated daily activity summary, we defined a Postgres View:

CREATE OR REPLACE VIEW daily_activity_summary AS
SELECT 
  tenant_id,
  DATE_TRUNC('day', created_at) AS activity_date,
  COUNT(id) AS total_events,
  COUNT(DISTINCT user_id) AS active_users
FROM events
GROUP BY tenant_id, DATE_TRUNC('day', created_at);

Instead of repeatedly joining numerous tables across different services, the application can now query the view directly, using client-side filters:

const { data, error } = await supabase
  .from('daily_activity_summary')
  .select('activity_date, total_events, active_users')
  .eq('tenant_id', tenantId)
  .order('activity_date', { ascending: false });

Benefits included:

Reduced query duplication
Cleaner application code
Easier reporting development
Improved maintainability
Faster onboarding for developers

Views effectively became reusable building blocks for reporting and dashboard generation.

Eliminating Unnecessary Data Retrieval

During optimization, several endpoints were found to be retrieving significantly more data than required.

Avoiding `SELECT *`

Instead of:

SELECT *
FROM transactions;

queries were modified to fetch only the required columns:

SELECT
  id,
  status,
  created_at
FROM transactions;

While simple, this optimization reduced network transfer and improved query efficiency across multiple endpoints.

Introducing Pagination

Some endpoints attempted to return entire datasets at once. As records increased, response sizes grew unnecessarily.

Pagination was introduced across reporting and administrative interfaces to ensure only the required records were fetched.

Instead of returning thousands of rows:

SELECT *
FROM transactions;

queries were limited appropriately:

SELECT *
FROM transactions
LIMIT 50 OFFSET 0;

This significantly reduced response sizes and improved perceived application performance.

Removing Unnecessary Sorting

Several queries performed sorting operations (ORDER BY) that were not actually required by the business logic or layout. Removing unnecessary sorting clauses reduced CPU execution time and lowered database workload, especially on larger datasets.

Optimizing Aggregations

Reporting systems often become slow because aggregation logic is repeatedly recalculated.

By simplifying query logic, moving heavy aggregations to periodic background rollups (or materialized views), and querying via optimized RPC functions, we reduced database complexity and significantly improved dashboard loading speeds.

Adopting UUIDv7 for New Tables

As the system evolved, UUIDv7 was adopted for newly created tables.

Traditional UUIDv4 values are completely random. While they provide excellent uniqueness guarantees, they introduce index fragmentation in B-Tree structures. When a database inserts random values, it forces the B-Tree index to split pages in random locations to fit the keys, lowering write speeds and caching efficiency.

UUIDv7 — standardised in RFC 9562 alongside v6 and v8 — solves this by introducing millisecond-level time ordering while maintaining globally unique identifiers.

-- Conceptual structure of UUIDv7:
-- [ 48-bit Timestamp ] [ 4-bit Version ] [ 12-bit Rand/Seq ] [ 2-bit Variant ] [ 62-bit Random ]

Benefits include:

Better insertion locality: Since keys are sequential, Postgres inserts new rows at the end of the B-Tree index structure, minimizing page splits.
More predictable index growth: Better utilization of memory-mapped buffer cache.
Improved long-term scalability: Higher write throughput on index pages.
Retained uniqueness guarantees: No risk of collisions across tenants.

To implement this on new tables, we define the column default using a generator:

CREATE TABLE new_tenant_log (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
  tenant_id UUID NOT NULL,
  log_message TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

The migration strategy was intentionally conservative: existing tables remained unchanged while newly created tables adopted UUIDv7 moving forward. Since no formal benchmarking was performed on the UUID transition, no specific performance improvements are claimed. However, UUIDv7 aligns well with modern PostgreSQL best practices for large-scale systems.

Key Lessons Learned

Several lessons became clear throughout the optimization process.

1. Indexes Are Not a Magic Solution

Adding indexes blindly often increases write overhead while providing little benefit. Indexes should be designed around real query patterns.

2. Composite Indexes Usually Matter More Than Individual Indexes

Understanding how queries filter data is more important than increasing the total number of indexes.

3. Database-Side Processing Can Be Extremely Effective

For aggregation-heavy workloads, RPC functions reduced round trips and simplified application logic.

4. Views Improve Maintainability

Complex reporting logic becomes significantly easier to manage when reusable views are introduced.

5. Pagination Should Be Introduced Early

Large datasets eventually become expensive. Pagination prevents future performance problems.

6. Small Optimizations Compound

Removing unnecessary sorting, reducing selected columns, optimizing aggregations, and improving indexes may seem minor individually. Together, they can transform the performance characteristics of a system.

Final Thoughts

Database optimization is rarely about finding a single breakthrough improvement. In this case, the largest gains came from systematically addressing multiple bottlenecks:

Composite indexing
RPC-based processing
Database views
Query optimization
Aggregation improvements
Pagination strategies
UUIDv7 adoption

The result was a more maintainable system, faster reporting workflows, and significantly improved responsiveness under production workloads. Most importantly, these improvements were achieved without redesigning the entire architecture. Instead, they came from understanding how the database was being used and optimizing the paths that mattered most.

What actually broke in production

Adding an index is not free, and I learned that on a write path rather than a read path. One of the composite indexes I added covered a reporting query beautifully and sat on the busiest write table in the system. Order inserts got measurably slower during the lunch peak, because every insert now had one more B-tree to maintain. The reporting query it served ran a few hundred times a day; the insert ran tens of thousands of times. I moved that read onto a materialised path instead and dropped the index. Indexes trade write cost for read cost, and it is worth knowing which side of that trade your table actually lives on.

What I’d still improve

Everything above is single-instance tuning. I have no read replica, so reporting queries and transactional writes compete for the same resources — which is exactly why that one index hurt so much. Splitting reads off is the obvious next step and I have deliberately not taken it yet, because replication lag introduces a correctness question I would rather not answer casually.

FAQ

Are more indexes always better for query performance? No. An index only helps when its leading columns match how you actually filter, and every index adds write cost and planning overhead. A handful of composite indexes shaped around real access patterns consistently beats a pile of single-column ones the planner has to try to combine.

Why does composite index column order matter? Because Postgres can only use a multicolumn index for a query that constrains its leading column. An index on (tenant_id, status, created_at) serves a filter on tenant alone, or tenant plus status — but not a filter on status alone. Order the columns the way your queries narrow.

Should business logic live in the database or the application? Push it down when it saves round trips over data the database already holds. Aggregations, multi-table joins and set operations are what a query planner is for; doing them in application memory means fetching everything first. Keep genuinely branching business rules in code.

Why is SELECT * a problem at scale? It transfers columns nobody renders, which costs bandwidth and parse time on every request, and it couples your API response shape to every future schema change. On wide tables over slow mobile connections, payload size affects perceived speed more than query time does.

What is the benefit of UUIDv7 over UUIDv4? UUIDv4 is fully random, so inserts scatter across B-tree pages and force splits in random locations, hurting write throughput and cache efficiency. UUIDv7 puts a millisecond timestamp in the high bits, so new keys are roughly sequential and land together.

How do you find which queries to optimise first? Read execution plans with EXPLAIN ANALYZE rather than guessing from response times, and aggregate per-query cost across real traffic so you optimise the query burning the most total time, not the one that felt slow once.

The one idea to take away

The plan is the source of truth, not your intuition about the plan. Every improvement here started with EXPLAIN ANALYZE telling me something I did not expect: an index that was never used, a sort that dominated the runtime, a join order I would not have chosen. Reading the plan is a faster skill to acquire than guessing well, and it is the only one that keeps working as the data grows.