a month ago
Project: vigilant-upliftment
Project ID: 43a99fbe-8b54-473b-bab6-47295f0e9000
Environment: production (c43dfeac-dc05-48d1-88a1-bcd4700d9d99)
Region: sfo
Affected service: Postgres (postgres-volume), image ghcr.io/railwayapp-templates/postgres-ssl:18
App service: sanuch-server (48c8fed9-d7ac-41d8-bebe-4138133dd61f)
Issue:
Our Postgres database periodically degrades to the point of being effectively unusable — simple queries that normally take 3-5ms start taking 30-60+ seconds or time out entirely. This has happened repeatedly over the past several days, on BOTH the Hobby and Pro plan (we upgraded Hobby → Pro on 2026-07-18 specifically hoping it would fix this, but the exact same symptom returned within ~24 hours on Pro as well).
Evidence from Postgres logs during degraded periods:
- Checkpoint writes taking 8-16 seconds for only 1.5-3MB of data (should be milliseconds on any normal SSD):
2026-07-19 20:07:42.534 UTC LOG: checkpoint starting: time
2026-07-19 20:07:53.573 UTC LOG: checkpoint complete: wrote 110 buffers (0.7%), wrote 1 SLRU buffers; write=11.024 s, sync=0.005 s, total=11.040 s; distance=3423 kB
- Recurring synchronized bursts of dropped connections (5-6 client connections reset at the exact same millisecond, every few minutes):
2026-07-19 20:11:27.478-479 UTC — 6 simultaneous:
LOG: SSL error: unexpected eof while reading
LOG: could not receive data from client: Connection reset by peer
(same pattern repeated at 20:08:23, 20:12:29, and many other timestamps)
- Our app's own Postgres client (node-pg pool, max 10 connections) logs matching errors during these windows:
[pgStorage] write error: Connection terminated due to connection timeout
[pgStorage] write error: timeout exceeded when trying to connect
Our own app-side load is minimal — only 1-2 active users hitting the database right now (we are pre-launch, testing before a wider rollout), so this is not caused by application-level write volume or connection pool exhaustion on our end.
What we've already ruled out:
- App-level connection pool size (raised max 3→10, did not help)
- App write frequency/patterns (checked all write paths, volume is low — 1-2 users only)
- A one-off outage — this recurs every ~1 day, both before and after restarting the Postgres service
- Plan tier — identical symptom on Hobby and now on Pro
What temporarily "fixes" it: restarting the Postgres service gives a clean state for roughly 15-24 hours (DB ping drops back to ~3ms), then the same checkpoint-slowness + connection-reset pattern gradually returns.
This strongly suggests an underlying issue with the specific Postgres volume/instance (disk performance degradation or possible fragmentation) rather than anything on our application side. We migrated to this Railway Postgres instance a few days ago specifically to get away from cross-region latency issues with our previous provider (Supabase, eu-central-1) — so this is a fresh volume, not a long-accumulated one.
Could you please investigate the underlying disk/volume performance for this Postgres instance, and let us know whether a volume migration/recreation on our end would help, or whether this is something on your infrastructure side?
Thank you.
1 Replies
a month ago
This thread has been opened as a public bounty so the community can help solve it. The thread and any further activity are now visible to everyone.
Status changed to Open Railway • about 1 month ago
16 days ago
One important correction: the posted checkpoint line does not show an 11-second fsync:
write=11.024 s, sync=0.005 s, total=11.040 sPostgreSQL deliberately spreads checkpoint writes across the checkpoint interval (checkpoint_completion_target defaults to 0.9). In that line, the synchronization phase took 5 ms. The 11-second paced write phase is therefore not, by itself, evidence that 3 MB took 11 seconds to flush to storage.
The fastest way to stop guessing is to capture the PostgreSQL wait event while one of the 30-60 second stalls is happening. From a second psql session, run this and leave \watch active:
SELECT
clock_timestamp() AS sampled_at,
pid,
application_name,
client_addr,
state,
now() - xact_start AS transaction_age,
now() - query_start AS query_age,
wait_event_type,
wait_event,
left(query, 160) AS query
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND state <> 'idle'
ORDER BY query_start NULLS LAST;
\watch 1Interpret the result at the exact time the application stalls:
IO / WalSync,IO / WalWrite, orLWLock / WALWritepoints to the WAL/storage path.Lock / ...points to database lock contention, even if total application traffic is low.- A long
ClientWritewait points to the client/network side of the session. - If the application is timing out while no matching backend ever appears, the delay is in connection establishment or the network path, not query execution.
On PostgreSQL 18, also take two snapshots of these counters - one before degradation and one during it - and compare the deltas:
SELECT name, setting
FROM pg_settings
WHERE name IN (
'track_io_timing',
'track_wal_io_timing',
'synchronous_commit',
'checkpoint_completion_target'
)
ORDER BY name;
SELECT * FROM pg_stat_checkpointer;
SELECT
backend_type,
object,
context,
reads,
read_time,
writes,
write_time,
fsyncs,
fsync_time
FROM pg_stat_io
ORDER BY backend_type, object, context;The timing columns remain zero when the corresponding I/O timing setting is disabled, so do not interpret zero timing as zero latency without checking the first query.
At the Railway layer, correlate the same UTC window with the Postgres service's CPU, memory, volume, and network graphs. Current CLI versions can export the window without exposing credentials:
railway metrics --service Postgres --since 24h --cpu --memory --volumeIf the sampler catches WalSync/WalWrite for tens of seconds and pg_stat_io shows high WAL fsync time while CPU, memory, and capacity are normal, give Railway that UTC window and request volume-host correlation or migration. Back up the volume first. Lock waits or no matching backend instead point to the query, application, or connection path.