Conference Schedule - PGConf.EU 2012Back
Java Wrapper for PostgreSQL Stored Procedures
Date: Oct. 25, 2012
At Zalando we store most of our critical data in PostgreSQL databases. To achieve both high performance and implement a secure API layer all database access is done through stored procedures, using plpgsql and sql functions. Previously we used a Spring based stored procedure implementation where a lot of extra classes and custom mappers were required before any stored procedure could be executed.
After implementing a type mapper to get rid of writing mappers for every procedure and types returned, implementing a lightwight wrapper was the next step to reduce the amount of java code necessary.
We will present our stored procedure wrapper that uses annotated Java methods to mirror available stored procedures and thus makes the usage of stored procedures very easy in Java.
The wrapper in its current state additionally supports database sharding, executing the same query in parallel on multiple shards, searching across shards, setting a custom statement timeout, and handling of advisory locks.
We will close the presentation with a brief introduction to our stored procedure monitor that tracks all relevant statistics, and especially stored procedure data, over time to continuously analyse our database performance. Both tools will make it onto github in the very near future.