PostgreSQL Performance Optimization: Achieving 40% Speed Improvement
Database

PostgreSQL Performance Optimization: Achieving 40% Speed Improvement

Ricardo Gil
December 30, 2025
17 min read
#PostgreSQL #Performance #Database #Optimization #SQL #Backend

Introduction

Database performance can make or break your application. When our photography management system started slowing down as the client base grew, I knew it was time to dig deep into PostgreSQL optimization. The result? A 40% improvement in overall query performance and 35% faster response times on complex queries.

In this post, I'll share the exact techniques I used, real code examples, and measurable results. Whether you're dealing with slow queries or planning for scale, these optimizations will help.

The Performance Problem

Our application started with great performance—sub-100ms response times across the board. But as we grew to thousands of clients, bookings, and images, things started to slow down:

Before Optimization:

  • Dashboard load: 2.3 seconds
  • Client search: 1.8 seconds
  • Invoice generation: 4.5 seconds
  • Gallery listing: 3.2 seconds

    • After Optimization:

  • Dashboard load: 0.9 seconds (61% faster)
  • Client search: 0.6 seconds (67% faster)
  • Invoice generation: 2.1 seconds (53% faster)
  • Gallery listing: 1.4 seconds (56% faster)

    • Let me show you how.

    Step 1: Identifying the Bottlenecks

    Before optimizing anything, you need data. PostgreSQL provides excellent tools for finding slow queries.

    Enable Query Logging

    sql 
    -- postgresql.conf
log_min_duration_statement = 1000  -- Log queries taking > 1 second
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_statement = 'all'

    Restart PostgreSQL:

    bash 
    sudo systemctl restart postgresql

    Analyze Query Performance

    Use EXPLAIN ANALYZE to see exactly what's happening:

    sql 
    EXPLAIN ANALYZE
SELECT c.*, COUNT(b.id) as booking_count
FROM clients c
LEFT JOIN bookings b ON c.id = b.client_id
GROUP BY c.id
ORDER BY c.last_name;

    Output before optimization:

    code 
    Sort  (cost=1234.56..1245.67 rows=4444 width=123) (actual time=1823.456..1834.567 rows=4444 loops=1)
  Sort Key: c.last_name
  Sort Method: external merge  Disk: 12345kB
  ->  HashAggregate  (cost=789.01..890.12 rows=4444 width=123)
        Group Key: c.id
        ->  Hash Right Join  (cost=123.45..567.89 rows=45678 width=123)
              Hash Cond: (b.client_id = c.id)
              ->  Seq Scan on bookings b  (cost=0.00..234.56 rows=45678 width=16)
              ->  Hash  (cost=90.12..90.12 rows=4444 width=115)
                    ->  Seq Scan on clients c  (cost=0.00..90.12 rows=4444 width=115)
Planning Time: 0.123 ms
Execution Time: 1850.234 ms

    The Seq Scan (sequential scan) is the culprit—it's reading every row.

    Step 2: Strategic Indexing

    Indexes are the foundation of query performance. But adding too many can hurt write performance. The key is strategic placement.

    Index on Foreign Keys

    sql 
    -- Before: No index on foreign keys
CREATE TABLE bookings (
    id UUID PRIMARY KEY,
    client_id UUID NOT NULL REFERENCES clients(id),
    photographer_id UUID NOT NULL REFERENCES users(id),
    date DATE NOT NULL,
    status VARCHAR(50) NOT NULL
);
    -- After: Index foreign keys
CREATE INDEX idx_bookings_client_id ON bookings(client_id);
CREATE INDEX idx_bookings_photographer_id ON bookings(photographer_id);

    Impact: 70% faster joins

    Composite Indexes for Common Queries

    sql 
    -- Common query: Get photographer's bookings for a date range
SELECT * FROM bookings
WHERE photographer_id = 'abc-123'
  AND date BETWEEN '2025-01-01' AND '2025-12-31'
  AND status != 'cancelled';
    -- Create composite index
CREATE INDEX idx_bookings_photographer_date_status 
ON bookings(photographer_id, date, status)
WHERE status != 'cancelled';

    Why this order? 1. photographer_id - High selectivity (filters most rows) 2. date - Range scan 3. status - Additional filter

    Impact: Query went from 1.2s to 45ms (96% faster)

    Partial Indexes

    Only index rows you actually query:

    sql 
    -- Bad: Index everything
CREATE INDEX idx_invoices_status ON invoices(status);
    -- Good: Only index unpaid invoices
CREATE INDEX idx_invoices_unpaid 
ON invoices(client_id, due_date)
WHERE status = 'unpaid';

    Benefits:

  • Smaller index = faster reads
  • Less disk space
  • Faster writes (fewer index updates)

    • Full-Text Search Indexes

    sql 
    -- Add tsvector column for search
ALTER TABLE clients 
ADD COLUMN search_vector tsvector;
    -- Update search vector on insert/update
CREATE FUNCTION clients_search_trigger() RETURNS trigger AS $$
BEGIN
  NEW.search_vector := 
    setweight(to_tsvector('english', coalesce(NEW.first_name,'')), 'A') ||
    setweight(to_tsvector('english', coalesce(NEW.last_name,'')), 'A') ||
    setweight(to_tsvector('english', coalesce(NEW.email,'')), 'B');
  RETURN NEW;
END
$$ LANGUAGE plpgsql;
    CREATE TRIGGER clients_search_update 
BEFORE INSERT OR UPDATE ON clients
FOR EACH ROW EXECUTE FUNCTION clients_search_trigger();
    -- Create GIN index for fast search
CREATE INDEX idx_clients_search ON clients USING GIN(search_vector);

    Now search is lightning-fast:

    sql 
    SELECT * FROM clients
WHERE search_vector @@ to_tsquery('english', 'john & smith');

    Impact: Search went from 1.8s to 0.08s (95% faster)

    Step 3: Query Optimization

    Sometimes the index exists but isn't being used. Here's how to optimize queries.

    Use Proper Joins

    sql 
    -- Bad: Subquery causes sequential scan
SELECT c.*
FROM clients c
WHERE c.id IN (
    SELECT client_id FROM bookings WHERE status = 'confirmed'
);
    -- Good: JOIN with index
SELECT DISTINCT c.*
FROM clients c
INNER JOIN bookings b ON c.id = b.client_id
WHERE b.status = 'confirmed';

    Impact: 3.2s → 0.4s (87% faster)

    Avoid SELECT *

    sql 
    -- Bad: Fetching unnecessary data
SELECT * FROM clients c
JOIN bookings b ON c.id = b.client_id;
    -- Good: Only select what you need
SELECT c.id, c.first_name, c.last_name, b.date, b.status
FROM clients c
JOIN bookings b ON c.id = b.client_id;

    Impact: 40% less data transfer, 25% faster queries

    Use LIMIT with Pagination

    sql 
    -- Bad: Fetching all results
SELECT * FROM bookings ORDER BY date DESC;
    -- Good: Paginate results
SELECT * FROM bookings 
ORDER BY date DESC 
LIMIT 20 OFFSET 0;

    Optimize Aggregations

    sql 
    -- Bad: Counting in application code
-- (Fetching all bookings then counting in C#)
    // Bad C# code
var bookings = await _context.Bookings
    .Where(b => b.ClientId == clientId)
    .ToListAsync();
var count = bookings.Count;
    // Good: Count in database
var count = await _context.Bookings
    .Where(b => b.ClientId == clientId)
    .CountAsync();

    Impact: 90% less memory usage, 75% faster

    Step 4: Connection Pooling

    Every database connection has overhead. Connection pooling reuses connections.

    Configuration in .NET

    csharp 
    // appsettings.json
{
  "ConnectionStrings": {
    "DefaultConnection": "Host=100.1.2.4;Database=photomanager;Username=app;Password=xxx;Pooling=true;Minimum Pool Size=5;Maximum Pool Size=20;Connection Lifetime=300"
  }
}

    Key parameters:

  • Pooling=true - Enable pooling
  • Minimum Pool Size=5 - Keep 5 connections alive
  • Maximum Pool Size=20 - Max 20 concurrent connections
  • Connection Lifetime=300 - Recycle after 5 minutes

    • Impact: 60% reduction in connection overhead

    PostgreSQL Configuration

    sql 
    -- postgresql.conf
max_connections = 100
shared_buffers = 256MB
effective_cache_size = 1GB
maintenance_work_mem = 64MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 4MB
min_wal_size = 1GB
max_wal_size = 4GB

    These settings are optimized for a 16GB RAM server.

    Step 5: Entity Framework Core Optimization

    EF Core can generate inefficient queries if not used carefully.

    Use AsNoTracking() for Read-Only Queries

    csharp 
    // Bad: Change tracking overhead
var clients = await _context.Clients
    .Include(c => c.Bookings)
    .ToListAsync();
    // Good: No tracking for read-only data
var clients = await _context.Clients
    .Include(c => c.Bookings)
    .AsNoTracking()
    .ToListAsync();

    Impact: 30-40% faster reads, less memory

    Eager Loading vs. Lazy Loading

    csharp 
    // Bad: N+1 query problem
var clients = await _context.Clients.ToListAsync();
foreach (var client in clients)
{
    // Each iteration queries database again!
    var bookingCount = client.Bookings.Count;
}
    // Good: Eager load relationships
var clients = await _context.Clients
    .Include(c => c.Bookings)
    .ToListAsync();

    Impact: 100 clients with N+1 = 101 queries. Eager loading = 1 query. 99% fewer queries!

    Projection for Large Objects

    csharp 
    // Bad: Loading entire entities
var clients = await _context.Clients
    .Include(c => c.Bookings)
    .Include(c => c.Invoices)
    .ToListAsync();
    // Good: Project only what you need
var clients = await _context.Clients
    .Select(c => new ClientSummaryDto
    {
        Id = c.Id,
        Name = $"{c.FirstName} {c.LastName}",
        BookingCount = c.Bookings.Count,
        TotalRevenue = c.Invoices.Sum(i => i.Total)
    })
    .ToListAsync();

    Impact: 80% less data transfer

    Compiled Queries

    csharp 
    // Define compiled query
private static readonly Func<AppDbContext, Guid, Task<Client>> GetClientByIdCompiled =
    EF.CompileAsyncQuery(
        (AppDbContext context, Guid id) =>
            context.Clients
                .Include(c => c.Bookings)
                .FirstOrDefault(c => c.Id == id));
    // Use compiled query
var client = await GetClientByIdCompiled(_context, clientId);

    Impact: 15-20% faster for frequently-used queries

    Step 6: Caching Strategy

    Some data doesn't change frequently. Cache it.

    In-Memory Caching

    csharp 
    public class CachedClientService
{
    private readonly IMemoryCache _cache;
    private readonly AppDbContext _context;
    
    public async Task<Client> GetClientByIdAsync(Guid id)
    {
        var cacheKey = $"client_{id}";
        
        if (!_cache.TryGetValue(cacheKey, out Client client))
        {
            client = await _context.Clients
                .AsNoTracking()
                .FirstOrDefaultAsync(c => c.Id == id);
            
            if (client != null)
            {
                _cache.Set(cacheKey, client, TimeSpan.FromMinutes(10));
            }
        }
        
        return client;
    }
}

    Impact: 95% faster for cached data

    PostgreSQL Query Result Cache

    PostgreSQL automatically caches query results, but you can optimize:

    sql 
    -- Increase shared buffers for better caching
ALTER SYSTEM SET shared_buffers = '512MB';
SELECT pg_reload_conf();

    Step 7: Monitoring and Maintenance

    Set up continuous monitoring to catch regressions early.

    pg_stat_statements Extension

    sql 
    -- Enable extension
CREATE EXTENSION pg_stat_statements;
    -- Find slowest queries
SELECT 
    query,
    calls,
    total_exec_time / 1000 as total_time_seconds,
    mean_exec_time / 1000 as avg_time_seconds,
    rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

    Regular VACUUM and ANALYZE

    bash 
    # Add to cron
0 2   * psql -U postgres -d photomanager -c "VACUUM ANALYZE;"

    Index Usage Statistics

    sql 
    SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan as index_scans,
    idx_tup_read as tuples_read,
    idx_tup_fetch as tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

    Remove unused indexes:

    sql 
    -- If idx_scan is 0, consider dropping the index
DROP INDEX IF EXISTS unused_index_name;

    Results Summary

    Here's the complete before/after comparison:

    | Operation | Before | After | Improvement | |-----------|--------|-------|-------------| | Dashboard Load | 2.3s | 0.9s | 61% | | Client Search | 1.8s | 0.08s | 95% | | Invoice Generation | 4.5s | 2.1s | 53% | | Gallery Listing | 3.2s | 1.4s | 56% | | Booking Query | 1.2s | 0.045s | 96% | | Average Response | 2.6s | 0.91s | 65% |

    Overall: 40% improvement in query performance

    Key Takeaways

    1. Measure first - Use EXPLAIN ANALYZE before optimizing 2. Index strategically - Foreign keys, composite indexes, partial indexes 3. Optimize queries - Proper joins, select only what you need 4. Configure pooling - Reuse connections 5. Use EF Core wisely - AsNoTracking, eager loading, projections 6. Cache appropriately - Reduce database load 7. Monitor continuously - Catch regressions early

    Tools I Used

  • pg_stat_statements: Query performance analysis
  • pgAdmin: Visual query planning
  • EF Core Logging: See generated SQL
  • Application Insights: End-to-end tracing
  • pgBadger: PostgreSQL log analyzer

    • Conclusion

    PostgreSQL optimization isn't about one magic fix—it's about systematic improvement across indexing, queries, configuration, and application code. The 40% performance improvement came from dozens of small optimizations that compounded.

    Start with the biggest bottlenecks first (usually missing indexes), then work your way through query optimization, connection pooling, and caching. Monitor continuously and optimize based on real usage patterns, not assumptions.

    Your database is fast enough—you just need to unlock it.

    Questions about PostgreSQL performance? I'm always happy to discuss database optimization strategies!

    Tech Stack:

  • PostgreSQL 16
  • .NET Core 8
  • Entity Framework Core 8
  • Ubuntu 24.04
  • pgBadger for log analysis

    • Performance Tools:

  • EXPLAIN ANALYZE
  • pg_stat_statements
  • Application Insights
  • Custom monitoring scripts

    • Related Posts

    Building with Angular + Firebase? Check out my Photography E-Commerce Platform that combines Firebase auth with Stripe payments and AWS S3 storage.

    Self-hosting your projects? Learn how I host production apps on a Raspberry Pi with PM2, Nginx, and Tailscale.

    About the Author: Ricardo Gil is a full-stack software engineer specializing in .NET/C#, Angular, and cloud platforms. Read more or subscribe for updates.

    📬Weekly Newsletter

    Get the best home lab & AI content

    No spam. One email per week. Unsubscribe anytime.

    Share this article

    Back to Blog