4 ms·

we heavily rely on postgres as well, in the past we have tried to do the entire backend on postgres as we believe it could be the best solution, but we have had

we heavily rely on postgres as well, in the past we have tried to do the entire backend on postgres as we believe it could be the best solution, but we have had a terrifically hard time of maintaining stored procs, etc. We still use them, but a lot less than we probably would and wound up building an ORM and moving most logic to PHP. I would be really interested in how you maintain your database code if you are up for another write up or have some time to chat.

jeltz 13y ago
I have seen some companies using schemas and the search path to deploy stored procedures. In every deploy a new schema is created for the deployed version and all stored procedures are added to it from the repository. The new version of the application will use the new schema while the old version still uses the old schema. When the old schema is no longer needed it can be dropped with all procedures. I have seen other ways to deploy stored procedures but these often require being very careful about function API:s and delta scripts.
drob 13y ago
For now, the following works for us: - For plpgsql functions that are required by app queries, we just roll them out when we write them / when they change (via ansible). - For plpgsql functions used in jobs, the job just reloads the relevant plpgsql functions on the relevant DBs before they start doing anything. (It's a little wasteful, but not in a way that matters for now.) - The UDFs we've written in C don't change too often, but we deploy them manually when they do. What are some of the headaches you've had in managing stored procs? How often is your app code changing / requiring new ones?
- erichurkman 13y ago
  One headache I had in the past was with a highly-available app with stored procs. It was rare to do parallel app server restarts, they were instead done in serial. (This did make certain migrations a pain.) Outdated app servers would continue calling pgpgsql functions in the outdated way until they received new code. We solved it with a small wrapper around the calls to plpgsql functions — we set up a build process to generate unique names that were called by version. During deploy, we'd have two or more versions of the 'same' function running in parallel. The last deploy step dropped the now-outdated functions. It worked relatively well.
  - jeltz 13y ago
    Have you thought about using schemas and the search path for this? That is what Zalando does to deploy stored procedures without risking any downtime.
    - erichurkman 13y ago
      If I had to do that again, I would. It's been a while, but as I recall from that project the database adapter had shoddy support for search paths.
akbar501 13y ago
This is not really an answer to your question, but it sounds like you have PHP developers trying to become part time database developers. I don't mean this negatively, just that server-side developers are different from database devs which are different from UI/front-end devs. Once you get beyond normal queries and enter the realm of real database development you likely want someone who is a genuine database developer. I'm worked with several database developers plus my wife is a database developer. They are a different breed, and they really develop applications in the data layer. I can say from experience that once you add a database developer you'll never look at the data layer the same. Often, there is a substantial amount of work that can be performed better by the database (whether it's Oracle, SQL Server, Postgres or whatever) since they have 30+ years of tuning for specific data operations.
- dsugarman 13y ago
  no I don't have PHP or database developers, I have hackers and they are trained to be great at database development and PHP development. I don't mean this negatively but you shouldn't hire people who can't figure out a new technology.
  - jodrellblank 13y ago
    If hackers 'trained to be skilled at database development' and good at figuring out new things couldn't make Postgres work for you, why do you believe it "could be the best solution"?

jeltz 13y ago

I have seen some companies using schemas and the search path to deploy stored procedures. In every deploy a new schema is created for the deployed version and all stored procedures are added to it from the repository. The new version of the application will use the new schema while the old version still uses the old schema. When the old schema is no longer needed it can be dropped with all procedures. I have seen other ways to deploy stored procedures but these often require being very careful about function API:s and delta scripts.

drob 13y ago

For now, the following works for us: - For plpgsql functions that are required by app queries, we just roll them out when we write them / when they change (via ansible). - For plpgsql functions used in jobs, the job just reloads the relevant plpgsql functions on the relevant DBs before they start doing anything. (It's a little wasteful, but not in a way that matters for now.) - The UDFs we've written in C don't change too often, but we deploy them manually when they do. What are some of the headaches you've had in managing stored procs? How often is your app code changing / requiring new ones?

erichurkman 13y ago
One headache I had in the past was with a highly-available app with stored procs. It was rare to do parallel app server restarts, they were instead done in serial. (This did make certain migrations a pain.) Outdated app servers would continue calling pgpgsql functions in the outdated way until they received new code. We solved it with a small wrapper around the calls to plpgsql functions — we set up a build process to generate unique names that were called by version. During deploy, we'd have two or more versions of the 'same' function running in parallel. The last deploy step dropped the now-outdated functions. It worked relatively well.
- jeltz 13y ago
  Have you thought about using schemas and the search path for this? That is what Zalando does to deploy stored procedures without risking any downtime.
  - erichurkman 13y ago
    If I had to do that again, I would. It's been a while, but as I recall from that project the database adapter had shoddy support for search paths.

akbar501 13y ago

This is not really an answer to your question, but it sounds like you have PHP developers trying to become part time database developers. I don't mean this negatively, just that server-side developers are different from database devs which are different from UI/front-end devs. Once you get beyond normal queries and enter the realm of real database development you likely want someone who is a genuine database developer. I'm worked with several database developers plus my wife is a database developer. They are a different breed, and they really develop applications in the data layer. I can say from experience that once you add a database developer you'll never look at the data layer the same. Often, there is a substantial amount of work that can be performed better by the database (whether it's Oracle, SQL Server, Postgres or whatever) since they have 30+ years of tuning for specific data operations.

dsugarman 13y ago
no I don't have PHP or database developers, I have hackers and they are trained to be great at database development and PHP development. I don't mean this negatively but you shouldn't hire people who can't figure out a new technology.
- jodrellblank 13y ago
  If hackers 'trained to be skilled at database development' and good at figuring out new things couldn't make Postgres work for you, why do you believe it "could be the best solution"?