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.
@umairshahid@StormaticsTech Unfortunately I did not know before, and flight back is already booked. Also have to go to a wedding which is on Saturday, and @eascherbaum really wants me to drive...
En cherchant un truc par hazard à propos de #PostgreSQL, j'ai appris par hazard l'existence de l'héritage des tables. À la base, c'est de quoi que je gérais manuellement.
Disons que la prochaine fois que j'ai à implémenter de quoi qui a besoin de ce tel concept, je vais probablement utiliser ça! #onEnApprendsÀToutLesJours
@krysztophe@xavsworld on a eu un cas du genre récemment, avec différents types d'alertes avec différents colonnes en plus de celles de la table de base, et on a utilisé le polymorphisme proposé par sqlalchemy au lieu de la feature native, mais je trouve que le résultat est un peu le même, des requêtes très complexes avec de la logique en fonction des types de toutes façons, + la complexité de déclarer/configurer correctement, si c'était a refaire je pense que je reviendrais à un "extra" JSONB.
Je n'ai qu'effleuré le sujet, plus pour répondre à «qu'est-ce que ça fait», sans aller dans les détails des inconvénients/limitations/problèmes.
Dans mon cas, j'ai en tête une table parent et plusieurs tables enfants, pour avoir une clée unique partagé entre tout les types hérités (limités et précis dans leur nombre). Exemple: "client" ou "employé", qui héritent de "usager".
Je crois que je vais rester au SQL classique avec des jointures ;-)
