In #postgresql database land today. Thinking through striping and mirroring on #ZFS. Klara Systems has unpacked a lot of this:
... This means two things: solid state and mirrors. SSD drives provide far lower latency than conventional drives possibly can. And mirrors provide far better random access performance than RAIDz can—particularly when we’re talking about small random access ops, which can’t be effectively spread across tons of disks in a wide striped vdev...
sigh The #postgresql logical replication process that has been running for around 3 weeks failed upon completion last night.
ERROR: table copy could not finish transaction on publisher: FATAL: terminating connection due to idle-in-transaction timeout
SSL connection has been closed unexpectedly
Time for google I suppose and an email to our on-retainer postgres expert.
Moving billions of records from one database to another is a PITA yo.
I'll be speaking at Posette, formerly Citus Con, a free and virtual developer conference on June 11-13, 2024 organized by PostgreSQL team at Microsoft 🐘
Save the date! 📅
Yesterday we migrated many #postgresql databases to 16.2. It went smoothly for all of them except one.
The database is used by Sidekiq for 90% of the traffic. We choose a time window outside of working hours, but still we had some traffic, and we didn't turn off pods. The database was inaccessible for 10min. Jobs rescheduled, and quickly auto-scaling was triggered. I was not able to perform ANALYZE. So requests retrying at the same time + huge disk read. 💥
Don't get me wrong, my experience told me is usually not a good idea to rely on managed databases that much, if I had full control of the servers then it may be fine, we can do dry-runs multiple times and monitor before proceeding.
When I do not have control of the #postgresql server I prefer to follow what Heroku have documented:
@theory well, it's complicated. The script queues any changes in the event, and adds a reminder if there is enough time. But I also need to handle changes in past event, as example if a recording is added later on. That deserves a new post.
Thanks for bringing this up, will see what I can do there.