2023-10

2023-10-26 Making PostgreSQL tick: New features in pg_cron

pg_cron is an open source PostgreSQL extension that provides a cron-based scheduler to periodically run SQL commands.

Almost every managed PostgreSQL service supports pg_cron and it has become a standard tool for many PostgreSQL users .

Since Citus has been my full-time job, pg_cron has always been a side project for me, and so I tried to architect it for simplicity, reliability, and low maintenance.

Of course, with many users there is a long list of feature requests, and with the help of the Postgres community pg_cron keeps becoming more and more capable over time.

We recently added PostgreSQL 16 support (in version 1.6), but perhaps the most exciting feature added to pg_cron in the past year (in version 1.5) is the ability to schedule a job every few seconds.

I shunned this feature idea for a while, because (a) it is not something regular cron can do; and (b) any issue in pg_cron would get much more severe if it were to happen every few seconds.

However, by now pg_cron is reasonably battle-tested and second-granularity jobs had become the most popular pg_cron feature request by far.

Schedule a job every few seconds

Being able to run second-granularity jobs enables you to react quickly to incoming events in your database. Some example use cases include:

  • update aggregations for real-time insights

  • detect anomalies (e.g. many requests from the same IP)

  • poll external sources (e.g. frequently sync from a remote server)

  • implement more sophisticated job scheduling workflows

Since pg_cron 1.5, you can easily schedule jobs that run every 1-59 seconds : 

-- Call my procedure every 10 seconds
SELECT cron.schedule('call-my-agent', '10 seconds', 'call my_agent()')

2023-10-17 Introducing pglift

Today, we are releasing pglift 1.0, a new framework for deploying and operating PostgreSQL at scale .

The project ships with both a command-line interface to manage the life-cycle of your databases and a collection of Ansible modules to drive your infrastructure as code in a production context.

By this 1.0 milestone, the user interface of pglift is now stable and the product considered ready for general use.

What is pglift ?

The project originates from the need of Dalibo’s customers to be able to deploy and operate many PostgreSQL clusters as part of their production environments, in a uniform manner across their infrastructure:

  • The production aspect means that PostgreSQL, the database engine, usually needs to be complemented by other services for backup, monitoring or high-availability.

  • The uniform aspect typically refers to automation, meaning that end-users want to streamline the deployment of their database services so as to operate it in a safe manner.

pglift tries to answer these challenges by providing:

  • a single entry-point and user-friendly command-line interface to operate a database cluster on site, and,

  • a collection of Ansible modules, to automate deployment and operations across an infrastructure.

Either way, the behaviour of pglift is highly configurable, from how the PostgreSQL cluster is deployed (authentication, storage layout, logging, backup) to which components are set up alongside the instance to make it production-ready (physical backup, monitoring, high-availability).

2023-10-06 Manuels : la version 23.9

Sommaire des formations DALIBO

Les manuels de formation Dalibo sont mis à jour chaque trimestre. Voici les liens vers leur dernière version, la 23.09, pour votre autoformation, vos formations internes ou celles assurées par Dalibo.

Le 22 septembre 2023, la version 23.09 a été publiée. La prochaine est prévue pour décembre ou janvier, et elle tiendra compte des nouveautés de PostgreSQL 16.

Licence

Les manuels sont publiés sous la licence CC BY-NC-SA, qui autorise l’utilisation non commerciale, le partage dans les mêmes conditions et la modification à condition de créditer Dalibo.

Privilège stagiaires

Chaque personne inscrite à une formation Dalibo reçoit un code pour commander gratuitement l’impression et la livraison à domicile de deux manuels de son choix.

Nous rappelons d’ailleurs que les formations ont lieu en intra ou inter-entreprises, en présentiel dans nos locaux parisiens, ou à distance.

Retrouvez le sommaire des formations Dalibo et de leurs modules ici

Si vous notez des éléments qui demandent à être clarifiés, précisés, corrigés… envoyez-nous vos suggestions !

Un grand merci à nos collègues pour leurs contributions, en particulier au discret Christophe Courtois .

Bonne lecture !

L’équipe Dalibo

Retrouvez le catalogue des formations ici !

Contactez formation @ dalibo . com pour toute demande.