Let me discuss a topic that is not inherently PostgreSQL specific, but that I regularly run into while investigating issues on customer systems, evaluating "supportability" of those systems, etc. It's the importance of having a monitoring solution for system metrics, configuring it reasonably, and why sar is still by far my favorite tool (at least on Linux).(more…)
A few weeks ago I covered the basics of tuning checkpoints, and in that post I also mentioned that the second common source of performance issues is autovacuum (based on what we see on the mailing list and at our customers under support). So let me follow-up on that with this post about the basics of autovacuum tuning. I'll very briefly explain the necessary theory (dead tuples, bloat and how autovacuum deals with it), but the main focus of this blog post is tuning - what configuration options are there, rules of thumb, etc.(more…)
pgFincore 1.2 est une extension PostgreSQL pour auditer et manipuler le cache de pages de données du système d'exploitation. L'extension a déjà une histoire de 7 ans d'utilisation, avec des évolutions correspondant aux besoins de production.Télécharger ici la dernière version 1.2, compatible avec PostgreSQL 9.6.(more…)
PostgreSQL's manual is generally pretty clear about the locks taken by various operations - but nothing's perfect. If you need to, you can also ask PostgreSQL directly.You can check lock levels trivially with psql or PgAdmin.(more…)
During the last October's Italian PGDay and European PostgreSQL conference, my friend Marco Nenciarini and I had the pleasure to talk about a new open source plugin for PostgreSQL, called redislog.In that presentation ("Integrating PostgreSQL with Logstash for real-time monitoring") we provided an example of our exploration/experimentation approach, with extensive and thorough coverage of testing and benchmarking activities. If you are curious to know more about that process, please refer to the slides of that talk, which are publicly available on Prezi.For the impatient: redislog taps into PostgreSQL's logging facility and allows DBAs to ship log events into a Redis queue, directly in JSON format, and to enter the ELK stack through the first class lane.Devops and the importanc...
PostgreSQL 9.4 introduces a new statistic in the catalogue, called pg_stat_archiver. Thanks to the SQL language it is now possible, in an instant, to check the state of the archiving process of transactional logs (WALs), crucial component of a PostgreSQL disaster recovery system.(more…)