Re:Are triggers really that slow in Postgres?
by magicalhippo on Wednesday May 23, 2018 @04:10AM
(#17132210)
We have the DB schema in a custom XML file, and have written a tool to compare the actual schema with that of the XML file. The tool then applies the necessary changes if they're non-destructive (extending column, adding index etc), or fails and lets the user know what needs to be done if run interactively.
By making sure we do as few potentially "destructive" changes to the schema as possible, this tool automatically upgrades our customers database when we release a new version with high reliability.
Adding or altering triggers, stored procs and views are considered "non-destructive" in this context, but of course that requires some discipline from us. One aspect of that is that we try to keep the number of triggers to an absolute minimum. Removing triggers and similar is considered "destructive", however we have a way in the XML to explicitly delete the object if needed.
The XML file lives in our version control repository alongside the source code, and we have triggers which updates test databases automatically when an update is committed etc.
It's a fairly simple idea but has worked quite well for us.
By making sure we do as few potentially "destructive" changes to the schema as possible, this tool automatically upgrades our customers database when we release a new version with high reliability.
Adding or altering triggers, stored procs and views are considered "non-destructive" in this context, but of course that requires some discipline from us. One aspect of that is that we try to keep the number of triggers to an absolute minimum. Removing triggers and similar is considered "destructive", however we have a way in the XML to explicitly delete the object if needed.
The XML file lives in our version control repository alongside the source code, and we have triggers which updates test databases automatically when an update is committed etc.
It's a fairly simple idea but has worked quite well for us.