4 ms·

Interesting, I think that a good CI/CD story is probably the biggest thing holding broader use of stored procedures back.

jerrysievert 8y ago
that's part of the reason I built equinox: https://github.com/jerrysievert/equinox https://github.com/jerrysievert/equinox if you're moving logic into your database, it's very important to be able to test and understand your use cases.
hinkley 8y ago
This sort of sentence is uttered by someone who is so far down a hole that all they can see is stars. It’s a big hole that a lot of people are in but it’s the consequence of a nasty trade off. All of this is trivial if your team decides that a shared database for dev work is bad for repeatability and thus bad for scaling the team. You should be able to spool up a local database with good sample data in it. To do that your schema, indexes and triggers would be under version control, and a data dump is somewhere people can get it. Once you have this your CI system runs Postgres locally or in a container, runs the drop create scripts, and then runs your integration and end to end tests on the canned data. In addition to getting a CI solution for next to free you get rid of the concurrent access Wild West and this particularly painful conversation: Why did this break and why didn’t you notice it before you pushed? Oh I saw that problem the other day but I thought someone else was changing data (and not my code being broken). If the data is on your machine and it gets broken, then it is only your machine that could have broken it. You can’t delude yourself into thinking it was someone else mucking around. The problem is either in your code or in your latest pull from master. You are responsible for determining the cause, not me, not the release manager, not QA. You.
- sp527 8y ago
  Who hurt you?
- manquer 8y ago
  Not all bugs are logical. Many times CI workflow does not catch poorly written but logically correct queries because sample db has few records in it, emulating large data sets with data patterns like production to get the same kind of query plans is hard.
  - hobs 8y ago
    I would say this is the first objection that is on point in this thread, not that figuring out deployment isn't hard, but the blog posts on how to solve it for your platform are multitudinous. Getting useful data loads to test queries is much more difficult but you have a few options (at least coming from a SQL Server approach): * Query hints to emulate larger sets of data (so you can see what type of IO you would get, of course multiplication is your friend) * Check if your database offers something along the lines of DBCC CLONEDATABASE https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-clonedatabase-transact-sql?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/t-sql/database-console-... (gets you a copy of statistics from prod without the data) * Building a data masking process so that the data is somewhat representative (in volume, mocking it such that the histogram of values is the same in your database is WAY harder) * Building an isolated load test environment with something like distributed replay https://docs.microsoft.com/en-us/sql/tools/distributed-replay/sql-server-distributed-replay?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/tools/distributed-repla... (record workload, replay, measure, make your change, replay, measure, yes - it is tedious but that's a performance regression test for you)
halostatue 8y ago
This is where [sqitch](http://sqitch.org http://sqitch.org) and [pgtap](http://pgtap.org http://pgtap.org) are useful. We aren’t currently using stored procedures, but we have a fairly substantial pgtap set for our schema definitions (where we have a number of complex constraints).
axelfontaine 8y ago
Flyway repeatable migrations make this trivial. Keep your stored procs under version control and Flyway will only recreate the ones that changed. See https://flywaydb.org/documentation/migrations#repeatable-migrations https://flywaydb.org/documentation/migrations#repeatable-mig...

jerrysievert 8y ago

that's part of the reason I built equinox: https://github.com/jerrysievert/equinox https://github.com/jerrysievert/equinox if you're moving logic into your database, it's very important to be able to test and understand your use cases.

hinkley 8y ago

This sort of sentence is uttered by someone who is so far down a hole that all they can see is stars. It’s a big hole that a lot of people are in but it’s the consequence of a nasty trade off. All of this is trivial if your team decides that a shared database for dev work is bad for repeatability and thus bad for scaling the team. You should be able to spool up a local database with good sample data in it. To do that your schema, indexes and triggers would be under version control, and a data dump is somewhere people can get it. Once you have this your CI system runs Postgres locally or in a container, runs the drop create scripts, and then runs your integration and end to end tests on the canned data. In addition to getting a CI solution for next to free you get rid of the concurrent access Wild West and this particularly painful conversation: Why did this break and why didn’t you notice it before you pushed? Oh I saw that problem the other day but I thought someone else was changing data (and not my code being broken). If the data is on your machine and it gets broken, then it is only your machine that could have broken it. You can’t delude yourself into thinking it was someone else mucking around. The problem is either in your code or in your latest pull from master. You are responsible for determining the cause, not me, not the release manager, not QA. You.

sp527 8y ago
Who hurt you?
manquer 8y ago
Not all bugs are logical. Many times CI workflow does not catch poorly written but logically correct queries because sample db has few records in it, emulating large data sets with data patterns like production to get the same kind of query plans is hard.
- hobs 8y ago
  I would say this is the first objection that is on point in this thread, not that figuring out deployment isn't hard, but the blog posts on how to solve it for your platform are multitudinous. Getting useful data loads to test queries is much more difficult but you have a few options (at least coming from a SQL Server approach): * Query hints to emulate larger sets of data (so you can see what type of IO you would get, of course multiplication is your friend) * Check if your database offers something along the lines of DBCC CLONEDATABASE https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-clonedatabase-transact-sql?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/t-sql/database-console-... (gets you a copy of statistics from prod without the data) * Building a data masking process so that the data is somewhat representative (in volume, mocking it such that the histogram of values is the same in your database is WAY harder) * Building an isolated load test environment with something like distributed replay https://docs.microsoft.com/en-us/sql/tools/distributed-replay/sql-server-distributed-replay?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/tools/distributed-repla... (record workload, replay, measure, make your change, replay, measure, yes - it is tedious but that's a performance regression test for you)

halostatue 8y ago

This is where [sqitch](http://sqitch.org http://sqitch.org) and [pgtap](http://pgtap.org http://pgtap.org) are useful. We aren’t currently using stored procedures, but we have a fairly substantial pgtap set for our schema definitions (where we have a number of complex constraints).

axelfontaine 8y ago

Flyway repeatable migrations make this trivial. Keep your stored procs under version control and Flyway will only recreate the ones that changed. See https://flywaydb.org/documentation/migrations#repeatable-migrations https://flywaydb.org/documentation/migrations#repeatable-mig...