🗒️ l-lin

Search

SearchSearch

✏️ 2025-03-23 🗓️️ Log

Scaling with postgresql without boiling the ocean

src: Scaling with PostgreSQL without boiling the ocean - 2025-02-09

“Postgres was great when we started but now that our service is being used heavily we are running into a lot of ‘weird’ issues”

This sentiment is frequently echoed by CTOs and senior engineers at high-growth startups when I speak with them.

Scaling PostgreSQL successfully doesn’t always require a full team of DBAs and experts. The beauty of PostgreSQL is that solutions often lie within the database itself - by rethinking your data access patterns from first principles, you can solve many business problems at scale.

In this post, I’ll address some common “weird” issues I’ve encountered and explore solutions that work at scale.

ℹ️ This is not an exhaustive list of all scaling challenges I have come across and is meant to be fairly high level, so YMMV. If you’d like to chat more, my DMs and email are always open.

1. Lock Contention

Lock contention becomes a significant bottleneck as write operations increase, especially with foreign key relationships. Long-running transactions can block other operations, creating a cascade of waiting processes.

This is likely a no-brainer at this point: Solutions

  • Use SELECT FOR UPDATE SKIP LOCKED for queue-like operations
  • Keep transactions short and focused
  • Consider deferring foreign key constraints for batch operations
  • Monitor lock queues and implement appropriate timeouts via lock_timeout and statement_timeout
  • Consider NOWAIT option for operations that can’t afford to wait

Considerations

  • Transaction isolation levels affect lock behavior
  • Some operations (like CASCADE DELETE) can create unexpected lock chains

2. Advisory Locks

If you find yourself needing to coordinate read/write access to certain resources in your application, advisory locks can be super handy. Unlike table or row locks that Postgres manages automatically, advisory locks give you explicit control over your locking strategy at the application level.

Solution

  • Use pg_advisory_lock() for exclusive access patterns
  • Implement pg_try_advisory_lock() for non-blocking attempts
  • Consider session-level vs transaction-level advisory locks
  • Use multiple lock IDs to create fine-grained locking schemes
  • Implement timeouts with statement_timeout to prevent indefinite blocking

Example (Ruby): https://github.com/ClosureTree/with_advisory_lock

Considerations

  • Advisory locks persist after transaction rollback unless explicitly released
  • Session-level locks remain until connection ends or explicit release
  • No automatic cleanup like regular row locks
  • Lock IDs must be carefully managed to avoid conflicts

3. Foreign Keys (🌶️)

My simple take is - While Foreign Keys are great at upholding data integrity, their impact on performance, migrations, and system complexity is hard to ignore. So, take a moment to ponder your database schema. Are all those constraints pulling their weight, or could shedding a few lighten your system’s load?

Solutions

  • Consider removing foreign keys for high-write tables
  • Use application-level validation where appropriate
  • Implement async validation jobs for data consistency
  • Use deferred constraints for batch operations
  • Consider CHECK constraints as lighter alternatives
  • Monitor and optimize index usage on foreign key columns

Considerations

  • Removing FKs means handling referential integrity in application code
  • Migration complexity increases without constraints
  • Indexes on foreign key columns can’t always be dropped
  • Your application and business logic can be more prone to data inconsistencies than you think and may need periodic cleanup jobs

If you’d like to read more, I wrote more at: Do You Really Need Foreign Keys?

4. Index Bloat

When rows are updated or deleted in PostgreSQL, the old versions aren’t immediately removed from indexes. This “bloat” accumulates over time, causing indexes to grow larger than necessary and potentially impacting query performance.

Solutions

  • Regular VACUUM maintenance to reclaim space
  • Set appropriate autovacuum parameters based on write patterns
  • Use REINDEX CONCURRENTLY for live index rebuilds
  • Consider partitioning for time-series data to manage index size
  • Use pg_repack for online bloat clean up
  • Use fillfactor settings for frequently updated tables

Considerations

  • VACUUM FULL locks tables and can cause downtime
  • Autovacuum might not keep up during high-write periods
  • Index rebuilds require significant temporary disk space
  • Concurrent reindexing has higher overhead but is safer

5. TOAST (The Oversized-Attribute Storage Technique)

PostgreSQL uses TOAST (The Oversized-Attribute Storage Technique) to handle large field values like JSON blobs or long text by storing them in separate tables. Values larger than 2KB are automatically moved to TOAST storage. While this clever mechanism helps manage large data efficiently, it can introduce unexpected performance characteristics, especially when these values are frequently accessed.

Solutions

  • Move large, infrequently accessed data to separate storage
    • Use external blob storage (S3, etc.) for files > 1MB
    • Store URLs/references instead of actual content
  • Use appropriate column types and compression strategies
    • JSONB instead of JSON for better compression
    • TEXT with compression for long strings
    • Consider TOAST_TUPLE_TARGET for fine-tuning
  • Monitor TOAST table sizes and access patterns via pg_stat_sys_tables
  • Use partial indexes to optimize TOAST’d column queries
  • Set TOAST policies per column:
    • EXTENDED for rarely accessed large values
    • MAIN for frequently accessed values that should stay in main table
    • EXTERNAL for very large values that need compression

Considerations

  • TOAST can cause unexpected query performance degradation
  • Updates to TOAST’d values create new versions, increasing storage
  • Some operations require reading the entire TOAST’d value
  • TOAST tables need their own vacuum maintenance
  • Joins involving TOAST’d columns can be particularly expensive
  • Memory usage can spike when processing many TOAST’d values

6. Vertical vs Horizontal Scaling

As your database grows, vertical scaling (bigger machines) eventually becomes cost-prohibitive or hits hardware limits. Before jumping to sharding, consider a staged approach to scaling your PostgreSQL infrastructure.

Solutions

  1. Federation
    • Split into new DB clusters by functionality (auth, billing, analytics, etc)
    • Simpler to implement than sharding
    • Natural service boundaries
    • Each database can be optimized independently
    • Easier to maintain and backup
  2. Partitioning Within Cluster
    • Table partitioning for large tables (by time, id ranges, or hash)
    • Improves query performance and manageability
    • Easier maintenance operations (bulk deletes, selective backups)
    • Stays within single database instance
    • Good for time-series or tenant-based like data
  3. Sharding Last
    • Partition data across multiple instances
    • Complex but necessary for massive scale
    • Requires careful key design
    • Consider data locality and access patterns
    • Plan for rebalancing and migration strategies

Considerations

  • Cross-database transactions become challenging
  • Application logic needs to handle multiple databases
  • Operational complexity increases significantly
  • Backup and recovery becomes more complex
  • Query patterns may need significant refactoring
  • Monitoring and observability become critical
  • Schema changes need careful coordination

7. Append-Only vs Update-Heavy Tables

Update-heavy tables often lead to write amplification, increased VACUUM overhead, and index fragmentation. In contrast, append-only tables with time-based access patterns can scale remarkably well, as they avoid these issues and enable efficient partitioning strategies.

Solutions

  • Design schemas to favor append-only patterns where possible
    • Store changes as new rows instead of updates
    • Use effective_date or version columns
    • Consider event sourcing patterns
  • Implement partitioning for large tables
    • Partition by time, id ranges, or hash values
    • Enable easy archival of older data
    • Optimize partition pruning for queries
    • Consider business domain for partition strategy
  • Consider materialized views or summary tables
    • Pre-compute common query patterns
    • Refresh async to maintain performance
    • Balance freshness vs query speed

Considerations

  • Historical queries need careful optimization
  • Storage growth needs active management
  • Application logic needs to handle data versions
  • Eventual consistency patterns may be needed

8. DDL Changes Under Load

Schema changes can block other operations, potentially causing application downtime. As your database grows, even simple alterations like adding an index can take hours if not handled carefully.

Solutions

  • Set appropriate lock_timeout values
    • Use SET lock_timeout = '10s' for migrations
    • Consider even shorter timeouts for busy tables
  • Use CREATE INDEX CONCURRENTLY instead of regular index creation
  • Break large migrations into smaller steps
    • Add nullable columns instead of columns with defaults
    • Backfill data in batches with UPDATE
    • Create indexes concurrently before adding constraints
  • Monitor locks and active queries during changes via pg_stat_activity

Considerations

9. Zero-Downtime Major Version Upgrades

Major version upgrades traditionally required downtime, which isn’t acceptable for many businesses. Using logical replication for blue/green deployments can help achieve near-zero downtime upgrades, typically reducing downtime from hours to just seconds.

There is a lot of different ways to achieve, so I will just like to some of my content and and content that I have found useful on other places on the internet

10. Counting

Simple COUNT queries become problematic at scale, especially for frequently accessed counts. This remains hard at scale too, however I have found in some cases moving the computational cost from read-time to write-time be valuable.

Solutions

  • Implement counter caches
    • Store pre-computed counts in separate columns
    • Update counts during write operations
    • Trade write complexity for read performance
  • Use proxy counter tables
-- dedicated counter table
CREATE TABLE agent_counts (
  agent_id bigint PRIMARY KEY,
  count bigint DEFAULT 0
);
 
-- Increment counter during writes
INSERT INTO agent_counts (agent_id, count)
VALUES (123, 1)
ON CONFLICT (agent_id)
DO UPDATE SET count = agent_counts.count + 1;

Considerations

  • Counter drift needs periodic reconciliation
  • Race conditions in high-concurrency scenarios
  • Need to handle counter updates in transactions
  • Consider backup and recovery implications
  • Initial population of counters needs planning

11. Multi-tenancy

Multi-tenant databases require careful consideration of data isolation, performance, and scalability. There are several approaches, each with their own tradeoffs.

Solutions

  1. Schema-based Separation
    • Each tenant gets their own schema
    • Strong isolation
    • Can be challenging to manage many schemas
    • Requires connection management
  2. Row-level Separation
    • Single schema, tenant_id column
    • Simpler to manage
    • Requires careful indexing
    • Need to ensure tenant isolation in queries
    • Tools like activerecord-multi-tenant can help
  3. Hybrid Approaches
    • Separate databases for large tenants
    • Row-level for smaller tenants
    • Balance between management overhead and performance
    • More complex application logic

Considerations

  • Shared resources can cause noisy neighbor issues
  • Migrations become more complex
  • Backup/restore needs tenant awareness
  • Connection pooling configuration becomes critical
  • Some queries might cross tenant boundaries
  • Need careful monitoring per tenant

I have found activerecord-multi-tenant gem (for Rails apps) provides a nice abstraction for row-level multi-tenancy, handling the tenant context in your queries automatically. At its core, it injects WHERE tenant_id = ? conditions into your queries, ensuring proper data isolation at the application level.

Parting thoughts

As I mentioned, these are by no means an exhaustive list and it’s meant to be fairly high level - if you’d like to chat more, my DMs and email are always open.

Recent Notes

Graph View

Backlinks

  • No backlinks found