托管10GB以内只读归档数据：寻求Rails应用低成本可查询方案

Hey there! Let’s work through this together—since your 10GB PostgreSQL archive is read-only and cost is make-or-break for your non-profit, we can focus on lean optimizations that keep your Rails app’s querying and search capabilities intact without breaking the bank on your low-end Digital Ocean Droplet.

Since your data never changes, we can strip out all write-related overhead and optimize for fast reads:

• Enable strict read-only mode: Update postgresql.conf with default_transaction_read_only = on to block accidental writes, then adjust pg_hba.conf to remove any write permissions for your Rails app’s database user. This reduces lock contention and keeps the database stable.
• Right-size memory parameters: With only 4GB of RAM, every MB counts. Try these settings in postgresql.conf:
  • shared_buffers = 1GB (roughly 1/4 of your total RAM—ideal for read-heavy workloads)
  • work_mem = 16MB (keep this low to avoid memory bloat from multiple concurrent queries)
  • effective_cache_size = 3GB (tells PostgreSQL to assume most data will be cached in system RAM)
  • maintenance_work_mem = 512MB (enough for one-time index builds, since your data is static)
• Build targeted indexes: Spend time creating indexes for all your Rails app’s common queries and search patterns. For example:
  • B-tree indexes for simple WHERE clauses (e.g., CREATE INDEX idx_archive_date ON archive_table(created_at);)
  • GIN/GIST indexes if you’re using PostgreSQL’s full-text search (e.g., CREATE INDEX idx_archive_search ON archive_table USING GIN(to_tsvector('english', content));)
    Avoid over-indexing—each index takes disk space, but since data is static, you only need to do this once.
• Run a one-time vacuum & analyze: Even read-only data benefits from up-to-date statistics. Run VACUUM ANALYZE; once after setting up the database to help PostgreSQL’s query planner make optimal decisions. You won’t need to run this again unless you ever update the data.
• Partition large tables (optional): If your 10GB archive is split into logical chunks (e.g., monthly data), partition the table by that dimension. Queries will only scan the relevant partition instead of the entire table, cutting down on disk I/O and memory usage.

Make the most of your 4GB RAM/40GB SSD Droplet:

• Preload frequently used data: Enable the pg_prewarm extension and run SELECT pg_prewarm('your_large_table'); (and indexes) to load critical data into RAM on startup. This reduces slow disk reads for common queries.
• Enable swap space: Add a 2GB swap file to act as a safety net for memory spikes. Run these commands:

  fallocate -l 2G /swapfile
  chmod 600 /swapfile
  mkswap /swapfile
  swapon /swapfile
  

  Then add /swapfile none swap sw 0 0 to /etc/fstab to make it permanent.
• Kill unused services: Disable any Ubuntu services you don’t need (e.g., Apache, MySQL, or unused system daemons) with sudo systemctl disable --now <service-name>. Every freed MB of RAM helps PostgreSQL run smoother.
• Monitor bottlenecks: Use htop or Digital Ocean’s built-in monitoring to track CPU, RAM, and disk I/O. If queries are slow because of disk thrashing, double-check your indexes or preloaded data.

Take pressure off PostgreSQL by optimizing how your Rails app interacts with it:

• Cache everything: Since data is static, cache frequent query results using Rails’ built-in caching (file-based caching works great here—no need for Redis unless you have high concurrency). For example:

  def archive_stats
    Rails.cache.fetch('archive_stats', expires_in: 1.year) do
      ArchiveTable.count
      # Add other static stats here
    end
  end
  

• Paginate all large result sets: Use gems like Kaminari or WillPaginate to split query results into smaller chunks. This reduces the amount of data transferred from the database and keeps your app’s memory usage low.
• Eliminate N+1 queries: Use includes or joins to preload associated data in a single query instead of hitting the database multiple times. For example:

  # Bad: N+1 queries
  ArchiveTable.all.each { |item| puts item.category.name }
  # Good: Single query with preloaded categories
  ArchiveTable.includes(:category).all.each { |item| puts item.category.name }
  

• Optimize full-text search: If your app uses search, lean into PostgreSQL’s native tsvector/tsquery instead of adding heavy external tools like Elasticsearch. Pair it with a GIN index for fast searches.

Since it’s an archive, you need backups but don’t want to pay extra. Use pg_dump to create a compressed backup monthly (or as needed) and store it in Digital Ocean Spaces (their low-cost object storage). Run:

pg_dump -U your_user -d your_db | gzip > archive_backup_$(date +%Y%m%d).sql.gz

Then upload the file to Spaces with s3cmd (or the Digital Ocean CLI).

内容的提问来源于stack exchange，提问作者Rockster160