I've been reading some stuff about postgres extensions, and I've got some interested about writing my own extension in C to attend a demand from my company.
The scenario is: We have a big and slow query that iterates through all the schemas verifying a specific datetime column from a specific table. A cron runs this query every 5 hours.
Does it make sense to write a extension that does same thing in C? Would it be a benefit or am I misunderstanding the concept of postgres extensions?
You have to distinguish these two things:
A PostgreSQL extension is an “installation package” in the database. If you create the extension, that will automatically create a number of database objects in the database (the members of the extension). These objects could be anything, and there is no relationship to C code.
A user-defined function in language C is a database function that calls a function in a shared library, which is dynamically linked into the server the first time you call the function in a database session.
It may make sense to package your C function into an extension, but those two things are in principle independent.
To figure out if your task would be faster in a C function, you have to figure out where the time is spent. If the time is spent performing sequential scans on tables, a C function won't help you. You'd need an index to speed that up. On the other hand, if most of your time is spent in PL/pgSQL function code that performs calculations or loops through query results, a C function can speed up processing considerably.