[HN Gopher] Stored Procedures as a Back End
___________________________________________________________________
Stored Procedures as a Back End
Author : steve-chavez
Score : 106 points
Date : 2020-12-03 16:13 UTC (6 hours ago)
(HTM) web link (gnuhost.medium.com)
(TXT) w3m dump (gnuhost.medium.com)
| nwsm wrote:
| Nice work; PostgREST seems neat. However, the aim of this stack
| is simplicity, but it is incompatible with serverless products,
| which I now vastly prefer for ease of use and lower effort-
| traits I would consider part of "simplicity".
|
| But I'm sure there is a valid argument contrasting simplicity
| with a managed nature of a tech stack, not to mention a cost
| comparison.
| capableweb wrote:
| > However, the aim of this stack is simplicity, but it is
| incompatible with serverless products, which I now vastly
| prefer for ease of use and lower effort- traits
|
| The issue with this statement is that you're comparing things
| that works on different layers. PostgREST is for someone to
| host the database without any backend, serverless as you
| describe it is just a SaaS service you use. If there was a SaaS
| that exposed PostgREST as a service, it'll be a similar
| experience.
|
| So unless you're comparing running a serverless platform
| yourself with running PostgREST yourself (which would be pretty
| obvious which one is the easiest), I feel like you missed with
| your argument here.
| nwsm wrote:
| I'm talking about the stack as a whole, which happens to be
| built on PostgREST.
|
| The OP's goal was to simplify their stack, which they did do.
| However, they cannot run this stack without managing a
| database, as the database and REST API are run in the same
| container. My point is that another way to lower a project's
| required effort is to offload database management.
|
| Sure, OP now only has two layers in the stack instead of
| three. But now they are locked into managing the database
| entirely, including updates, backups, and scaling.
| steve-chavez wrote:
| If you like serverless, Supabase[1] will handle all the
| PostgreSQL and PostgREST management for you :)
|
| [1]: https://supabase.io/beta
| 8note wrote:
| How do you handle decoupling your API model from your database
| model?
| ellimilial wrote:
| This won't answer this question in full but perhaps some part
| of it.
|
| Projects like PostgREST (and PostGraphile) use schema
| introspection to generate the API. When the schema changes it's
| automatically reflected in the API. Sure one has to keep the
| changes synced on the frontend but the premise is you don't
| want to decouple.
| loldot_ wrote:
| What I've found being the problem with this approach is that sql
| does not lend itself very well to composability. Sure, you can
| make functions, views and stored procedures and compose them. But
| when you start actually composing queries of these parts the
| could lead to different execution plans having wildly different
| performance characteristics.
|
| Also tooling around SQL, i.e. refactoring tools and debuggers, is
| not great - if even available at all.
| corytheboyd wrote:
| How does this work with database replication? Does all
| "application logic" have to execute on the master database?
| kemiller2002 wrote:
| Not necessarily. You can make it so that cross replicated
| databases can function independently and then sync the data
| together. This of course assumes that you don't have something
| like an ordering system and you absolutely need to make sure
| that you don't sell more than your inventory.
| corytheboyd wrote:
| That makes sense, yeah. I admittedly haven't studied the
| pieces of the stack, but maybe something like PostgREST
| allows routing certain requests to master vs any replica,
| where you could make the atomic transactions needed for
| inventory based updates.
|
| This is all completely outside the realm of things I'll ever
| get into haha. IMO so what if the current app/db server
| boundary is "slow" or "adds too many moving parts" I already
| spent my entire career learning it, there is value in that to
| me.
| throwaway_pdp09 wrote:
| > Also typical database uses 96% of the time for logging, locking
| etc. and 4% percent for actual data operations
|
| That's a strange claim, in my work it's always been 100% data
| ops.
|
| > "Letting people connect directly to the database is a madness"
| -- yes it is.
|
| why?
|
| > Killing database by multiple connections from single network
| port is simply impossible in real life scenario. You will long
| run out of free ports
|
| Depends entirely on the workload. And it's very possible. All too
| entirely so.
| taffer wrote:
| > why? while true: select count(*) from
| huge_table
|
| thats why.
| _benj wrote:
| In my experience I actually got A LOT more from this approach
| using postgraphile/Hasura in front of my DB and later moved to
| dosco/super-graph in order to add custom functionality that was
| kind of a pain to do on the DB.
|
| I really liked the mixed approach of have the DB do everything
| that it can and have a light backend that can handle http
| requests, api callbacks, image manipulation and whatever else.
| BenjieGillam wrote:
| Out of interest (as the PostGraphile maintainer) did you look
| into https://www.graphile.org/postgraphile/make-extend-schema-
| plu... for extending the PostGraphile schema to do whatever you
| need, or were you specifically looking to implement the
| extensions in Go?
| lflux wrote:
| At a previous job I was at 2007-2014, we were doing classified
| ads[1] we had a lot of logic in stored procedures in our Postgres
| database written in pl/pgsql. It made sense at the time, but
| after a while these got really unwieldy and deploying things got
| tricky, since you we needed to update both appserver code and
| database stored procs at the same time. There also wasn't a great
| way to update stored procs at the time - we built this into an
| RPM that just ran a postinstall script to do an atomic swap.
|
| I wouldn't build this into the database were I to build it today.
|
| [1] blocket.se/leboncoin.fr/segundamano.(es,mx)
| FlyingSnake wrote:
| Using a database as a backend comes up time and again and
| developers are sharpely divided over this concept. I feel that
| developers who started their careers before the advent of NoSQL
| DBs/MEAN stack/etc had to go through the rigor of fine tuning
| their SQL queries and witness the powers (and quirks) of a
| performant SQL DB. These devs are usually a fan of using DB-as-a-
| backend. Personally, I am a big fan of PostgREST and I apply it
| whenever I find a use case that suits it. I find it quite
| pragmatic and it just shines when it plays to it's strength.
| Pragmatic developers should took a pragmatic look at such
| approaches and try to use the powerhouse that are SQL DB engines.
|
| Shameless plug: I've written an article to showcase it's
| philosophy and how easy it is to get started with PostgREST.
|
| [^1]:
| https://samkhawase.com/blog/postgrest/postgrest_introduction...
| pjmlp wrote:
| I am on the stored procedure side, and see no value in wasting
| network bandwidth and client resources for what the RDMS should
| do itself.
| Raidion wrote:
| If I make the change in code, I can roll it out, test it in prod,
| and then slowly migrate traffic over in very standard and well
| defined ways.
|
| If I deploy to the database and something goes wrong, I need to
| really trust my rollback scripts. If the rollback scripts go
| haywire, you're in a tough spot. If that happens to code, you can
| literally just move all the traffic to the same thing that was
| working before, you don't quite have that luxury with the
| database.
|
| You can have a bunch of servers, you really only can have one
| database. This means the database should be very sensitive to
| changes because it's a single point of failure. I don't like
| modifying or changing single points of failure without very good
| and well tested reasons.
|
| Could you version your procs and have the new version of the code
| call the new procs? Sure, but now you have to manage deployment
| of both a service, and the database, and have to handle rollover
| and/or A/B for both. If my logic is in there service, I only have
| to worry about rolling back the service.
|
| Database logic saves you a little bit of pain in development for
| a ton of resilience and maintenance costs, and it's not worth it
| in the long run IMO. Maybe it's because the tooling isn't that
| mature, but until I can A/B test an atomic database, this doesn't
| work for a lot of applications.
| ComputerGuru wrote:
| I fully agree and treat my databases the same way. However,
| there's no reason a stored procedure couldn't be treated the
| same way with a random weight determining which version of a
| stored procedure is run.
|
| But I still wouldn't.
| chaps wrote:
| Not sure I follow in the context of the post. The post is about
| a database that can be wiped and effectively recreated from
| scratch. There wouldn't really be a need for rollback scripts
| when you can just rerun the entire workflow from scratch. Even
| easier if you have a hot/warm/cold setup.
|
| In this sort of setup, changing a db function isn't unlike
| making a change to db-interfacing code in that it's not
| changing the underlying data, just the interface to it.
|
| I've built similar systems that instead of making changes to db
| functions, changes are made to materialized views. The
| underlying data doesn't change, but its presentation does. No
| huge need for rollbacks. It works really well!
| Roboprog wrote:
| Presumably you can revert to a previous tag/label in source
| control and reload the previous procs/funcs to roll back
| versions.
|
| The apps I have been working on are monoliths which run at
| about 2% CPU on the server, so there is no A/B comparison runs
| in production (e.g. on January 1, some law goes into effect and
| you WILL implement it). I would happily see ORM usage curb
| stomped.
| alphanumeric0 wrote:
| I personally would not make this choice for new projects. I only
| use technology that is appropriate for the domain I'm using it
| in. Although, I am hoping this trend encourages better tooling
| and more modernization of SQL.
|
| As others stated, my concerns are primarily that SQL lacks a lot
| of general-purpose language features (modules, namespacing,
| composability, no easy way to debug, etc.) which seem to be ideal
| for writing applications.
|
| I would be interested to see a project written with PL/Python +
| PL/SQL stored functions in Postgres. PL/Python functions would be
| the interface between API calls and the database, and SQL would
| do what it is currently designed to do: straight-forward data
| manipulation.
| Brentward wrote:
| Not that this is an important part of the post, but in general
| parsing the output of ls like the author does in the for loop is
| a bad idea[1].
|
| I think the one-liner would be better as for i in
| [0-8]*/*.sql; do psql -U <user> -h localhost -d <dbname> -f $i ;
| done
|
| or even better as something like find . -name
| "*.sql" -exec psql -U <user> -h localhost -d <dbname> -f {} \;
|
| [1] http://mywiki.wooledge.org/ParsingLs
| tacticaldev wrote:
| I'm currently part of a team trying to UNDO this very concept. It
| sounded great in the beginning, but after years of headaches and
| wasted cash; we're building the backend in something else. I
| wasn't part of the original team. Nor was I part of the decision
| to migrate off of the system. I just know that for my employer,
| it was a bad decision those many years ago...
| pjmlp wrote:
| Sure, because wasting network bandwith for going through
| needless data on the client is such a clever idea.
| sumtechguy wrote:
| This takes a balance to get right. For some data yes drag it
| over and keep it on the 'client'. But for other data just
| join it out and let the SQL handle it. However, for some
| reason SQL seems to scare a lot of people. So they end up
| making lots of microservices/functions/methods that just drag
| the data together and mash it together in a language they
| understand. That comes at a cost of network bandwidth.
|
| I found that if you are 'happy' with the style of lots of
| stored procs and data lifting on the server side you usually
| have a decent source delivery system in place. If you do not
| have that you will fight every step of the way changing the
| schema and procs as this monolithic glob that no one dares to
| touch.
| gnat wrote:
| Could you be more specific about the problems you're hoping to
| fix/benefits you're hoping to gain by dragging the backend
| logic out of the database?
| jrochkind1 wrote:
| Everything old is new again. This was a common way to write
| client-server software in the pre-web world, clients talking
| directly to a central rdbms.
|
| I guess the one true thing in software dev is the cycle/pendulum
| keeps rotating/swinging. Often without people realizing it's
| swinging back instead of brand new!
| laudable-logic wrote:
| Maybe sorta off-topic, but does anybody have any stories to tell
| about successes/fails with the .NET CLR in MSSQL?
|
| I worked on a project that made moderate use of this. Worked
| alright; biggest problem was convincing DBAs/IT to enable it.
|
| https://en.m.wikipedia.org/wiki/SQL_CLR
| flerchin wrote:
| Good luck debugging! Seriously. You can't attach a debugger to
| your DB.
| vsareto wrote:
| DBs can have debuggers and breakpoints, but they do not come
| anywhere close to something like Visual Studio debugging C#
| code.
|
| Of course, many programming languages also do not have a
| debugging quality-of-life compared to Visual Studio.
| bsg75 wrote:
| https://www.pgadmin.org/docs/pgadmin4/development/debugger.h...
|
| https://dbeaver.com/docs/wiki/PGDebugger/
|
| https://www2.navicat.com/manual/online_manual/en/navicat/mac...
|
| https://www.cybertec-postgresql.com/en/debugging-pl-pgsql-ge...
|
| Other platforms have options as well:
|
| https://docs.microsoft.com/en-us/sql/ssms/scripting/transact...
| pjmlp wrote:
| I do it all the time on Oracle, SQL Server and Informix.
|
| Better I can even optimize to native code, and still debug
| them.
| throwaway_pdp09 wrote:
| IME I've never needed one. You get a copy of prod and test
| against that. SPs are typically short, even when they're long
| their functionality tends to be well defined, it's never been a
| problem for me.
|
| You certainly can and do add logging, this can be useful.
| mdoms wrote:
| I don't know what industry you are in or what kind of data
| your users entrust in you, but "just get a copy of prod" is
| not an idea that would EVER fly in any organisation I have
| worked in. Developers do not get access to production
| customers' financial records, medical data or PII, ever.
| throwaway_pdp09 wrote:
| Hands up, it's a fair point. I've worked with data sets
| that are mainly large aggregates of public data so security
| isn't an issue. It's the code that's carefully protected.
|
| One exception is having access to that data because I was
| working ON the prod server. Read that again. It was deeply
| uncomfortable to work like this. Very worrying every day.
| throwaway201103 wrote:
| In those cases you have a set of test data that you use for
| development and testing. It's not like you can attach a
| Visual Studio debugger to production either.
| gnat wrote:
| I'm very interested in this, as I come from the web world yet
| find myself in a company that has a 30-year legacy of putting
| business logic into the database. It served them really well, and
| that they run quite lean in terms of hardware. But they're built
| on Microsoft tech ($$$), and moving things to a traditional
| database=datastore cloud architecture would require a substantial
| change. Does anyone have resources they can point me to on the
| wisdom or folly of keeping this logic-in-database approach? It
| seems like the tradeoffs in the different approaches are all
| about where the smarts are, and whether you can horizontally
| scale those smarts. Does that sound accurate?
| mattmanser wrote:
| Honestly, I find SQL Server worth the $$$, it's certainly not
| Oracle $$$$$$$$$ as far as I understand it.
| taffer wrote:
| > It seems like the tradeoffs in the different approaches are
| all about where the smarts are, and whether you can
| horizontally scale those smarts. Does that sound accurate?
|
| The real question is whether you are going to ship your code to
| your data or your data to your code.
| aszen wrote:
| I'm very curious about this pattern, but does the tooling exist
| to make this feasible across a team. One of the big problems with
| SQL views is their lack of discoverability. The lack of tooling
| also plagues SQL based systems, I would miss goto definition,
| find all references and sensible autocompletion. I am curious why
| no one has built suitable tooling around all this, because it is
| a great idea for lot's of scenarios
| jdmoreira wrote:
| Oh I feel like it's 2007 again and I'm a Junior developer writing
| a frontend in Delphi and the business logic in PL/SQL. Those were
| the days.
| mrits wrote:
| I'm not old enough to remember the first one but I know at least
| Oracle beat you to this idea in the early 90s.
| milesvp wrote:
| Oracle really pushed this idea too, because they knew that it
| would mean more load in the database so you'd need a bigger box
| with with more cpus. Oracle charged per tiered cpu so their
| incentives were not aligned with the dev's. IMHO this is pretty
| damning reason to generally avoid using the db for this reason.
| It may be true that a db has lots of spare cycles to utilize
| but so do your hot spare servers, and I'd hope that you're not
| trying to wring performance out of those without thinking long
| and hard as to what hot spare means.
|
| Source: migrated code from oracle to mysql to avoid insane
| licensing fees once upon a time. Luckily we only had limited
| stored procedures at the time, most of which I could ignore.
| jandrewrogers wrote:
| Back in the mid-90s, this was how many of the high-end web apps
| were built, using Oracle. Despite its limitations, and Oracle
| license fees (but back then you were paying for Sun servers
| anyway), it worked surprisingly well and was a reasonable model
| for many database-driven web applications. Some of these systems
| were used for mission-critical global operations handling
| enormous amounts of money and complex business. It had a lot of
| advantages versus other web app stacks at the time (commonly a
| mountain of Perl).
|
| Oracle was happy to sell database licenses but made zero effort
| to develop this way of using the database as a first-class
| capability. They were astonished that it even worked as well as
| it did, they did not expect people to take it that far.
|
| For some types of applications, something along these lines is
| likely still a very ergonomic way of writing web apps, if the
| tooling was polished for this use case.
| pjmlp wrote:
| I can assure you that still is how many high-end web apps are
| built today.
|
| Just delivered one last month with plenty of T-SQL code in it.
| kemiller2002 wrote:
| Not surprising. Databases are built to be really efficient,
| and with enough hardware, they can forgive a lot. Mediocre
| developers can produce a lot of "working" code if they stand
| on the shoulders of giants. (Not saying you or your team was
| mediocre, I'm just saying that I've seen really bad code run
| a lot of business processes. At the end of the day, it worked
| and the company made money.)
| pjmlp wrote:
| Just like I keep seeing plenty of mediocre teams doing for
| loops over GB of downloaded data and pegging Webserver CPUs
| for stuff that should have stayed on the DB.
|
| Usually that is one of the things I end up fixing when
| asked for performance tips, other is getting rid of ORMs
| and learn SQL.
| kemiller2002 wrote:
| Oh don't get me wrong, there is a time and a place for
| everything. There are many things that should be in the
| database, and there are a lot of people that avoid it
| because they don't want to learn SQL. You can have really
| good reasons and elegant code in SQL. I'm merely saying
| when someone immediately says, "that's dumb, you
| shouldn't do that," they aren't looking at the whole
| picture possibly and it was the choice that got the
| programming running which is important.
| jacques_chester wrote:
| I did a take-home exercise earlier this week. They
| provided access to a database and said << do thing >>.
| Implied was that I should << do thing >> with a program
| rather than a query.
|
| One of my solutions used Golang. It pulled data, looped a
| lot, then put results back. It took approximately 4
| minutes.
|
| I included a second solution, using SQL. It selected into
| the results table with a little bit of CTE magic. It took
| approximately 600ms.
| bartread wrote:
| Interesting, and I've seen and implemented similar
| optimisations plenty of times. The right query in the
| right place at the right time can make all the
| difference.
|
| Did you hear back from them with a positive response?
| jacques_chester wrote:
| Not yet, but it's a small firm. I trust the people
| involved and I don't mind giving them some time to go
| over it.
| NicoJuicy wrote:
| ORM's aren't that slow, they are quick to develop.
|
| When you see a bottleneck, you could use 2 systems ( eg.
| EF by default and Dapper when doing optimizations)
| kemiller2002 wrote:
| It should be noted too that the 90s was really a different
| world back then. Processing capabilities, and development tools
| were vastly different.
|
| There was just so much stuff that you had to manually build you
| don't have to now. (There are other complexities that we get to
| worry about now.) One thing I noticed back then is that putting
| stuff in the database just made things easier. In some
| instances, it really did act as like an amplifier for getting
| work done. Need to sort data? The database has things in place
| to do that and really efficiently too. I remember working with
| people, and if they had to make things like a linked list or a
| binary tree, they were lost (Yes, these were CS people too. We
| can argue about their education, but yes, they did graduate
| with a CS degree). There was nothing really like Stack Overflow
| to ask for help. You were really on your own for a large
| portion of it. Database code really took care of a lot of that
| for you. (Truthfully, the companies I worked for used MSSQL
| Server back then, so your results might have been different.)
| throwaway201103 wrote:
| Yeah nobody in the 1990s with a real CS degree didn't know
| what a linked list or binary tree was. They just didn't want
| to bother coding it and testing it, when the database was
| there and being used anyway.
| jandrewrogers wrote:
| Another unusual usage for SQL databases (today!) is as a data
| type server. SQL databases have a very rich set of data types
| with rigorous specification and myriad operators,
| conversions, etc for all the types, more so than just about
| any other programming environment. If you need correct
| support for a complex data type operation in a development
| environment with weak or non-existent type support, you can
| use a SQL runtime to do that computation for you over a local
| client connection.
|
| I've used PostgreSQL this way a few times to great effect.
| The connection is over local IPC and never touches storage,
| so the operation throughput is quite high.
| kevincox wrote:
| The primary issue I have seen with stored procedures is how you
| update them. I would be curious how they manage that.
|
| Generally when releasing new code you want to do a gradual
| release so that a bad release is mitigated. It would be possible
| by creating multiple functions during the migration and somehow
| dispatching between them in PostgREST but I would be interested
| to see what they do.
|
| The other obvious concern is scaling which was only briefly
| mentioned. In general the database is the hardest component of a
| stack to scale, and if you start doing it do more of the
| computation you are just adding more load. Not to mention that
| you may have trouble scaling CPU+RAM+Disk separately with them
| all being on a single machine.
| capableweb wrote:
| > Generally when releasing new code you want to do a gradual
| release so that a bad release is mitigated
|
| Make each stored procedure not depend on any other procedure.
| If you change one procedure, make sure that every existing
| procedure can work with both the current and the future
| version. Once compatible, start upgrading procedures one by
| one. Add in monitoring and automatic checking of the values
| during deployment, and you have yourself a system that can
| safely roll out changes slowly.
|
| I'm not advising anyone to do this, it sounds horribly
| inefficient to me, but it's probably possible with the right
| tooling.
| taffer wrote:
| > The primary issue I have seen with stored procedures is how
| you update them. I would be curious how they manage that.
|
| 1. You put your stored procedures in git.
|
| 2. You write tests for your stored procedures and have them run
| as part of your CI.
|
| 3. You put your stored procedures in separate schema(s) and
| deploy them by dropping and recreating the schema(s). You never
| log into the server and change things by hand.
| mattmanser wrote:
| Wouldn't that lose all the cached query plans every deploy?
| Could be wrong, bit rusty on that stuff now, been a while
| since I worked on something where we had to worry about that.
| jandrewrogers wrote:
| Many scaling problems in databases are _because_ the
| computation is not done in the database. Outside of exotic
| cases, good database engines are rarely computation-bound in a
| competently designed system. By pushing computation outside of
| the database, it tends to increase the consumption of database
| resources that actually are bottlenecks, typically effective
| bandwidth (both memory and I /O).
|
| As a heuristic, moving computation to the data is almost always
| much more scalable and performant than moving the data to the
| computation. The root cause of most scalability issues is
| excessive and unnecessary data motion.
| tuatoru wrote:
| Profound comment. Should be top of mind whenever programmers
| think they need a cache.
|
| We've just seen a lower-level example of this principle in
| action, with Apple's M1 processor and its Unified Memory
| Architecture.
| mattmanser wrote:
| That's not been my experience at all. Quite the opposite.
|
| I actually can't remember a single time in 15 years where
| I've ever seen that. Maybe it's happened, but I don't
| remember, but I can easily recount tons of poorly performing
| SQL queries though. Sub-selects, too many joins, missing
| indexes, dead-locks, missing foreign keys...
|
| I'm not saying it wouldn't cause a problem, I'm saying I've
| never, ever seen production code where someone dumped tons of
| data out and then processed it.
|
| I have seen people try and make extremely complex ORM calls
| that were fixed by hand-coded SQL, but that's a different
| problem.
| marmaduke wrote:
| I think read replicas scale easily, so if it's views and such
| then there is no problem. If you have to compute stuff during
| writes, then it's hard, but probably solved by normalizing the
| data.
| kemiller2002 wrote:
| Really anything can be a good or a bad idea depending on the
| problem domain and how it's implemented. He doesn't really go
| into how he made it work too closely, so it's easy to say it was
| a success. The real interesting thing to understand would be,
| "Why was it a success versus other approaches?" What really made
| it a better choice vs other approaches? What was the team make
| up? He says that it made the code easy to understand. To whom?
| Interesting the team made it work, but not enough to say, "Sure
| let's try it without a lot more forethought."
| loldot_ wrote:
| Exactly! If you're writing a C compiler, it would probably be a
| bad idea to have that logic in a database, though it could be
| fun to try. On the other hand if your application is
| summarizing a lot data by different dimensions, it would
| probably be a bad idea to transfer all that data over the
| network only to summarize it in an application layer. Most
| applications have a little bit of both though and so will need
| both an application layer and database layer.
| revskill wrote:
| I'm using this pattern on my projects for my customers.
|
| One of the reason is that my team knows only SQL things, they
| don't have time to install other programming tools on their
| machine.
|
| So, just install postgresql and code the logic you want !
| marcosdumay wrote:
| The one problem this technique has is that data and software
| normally have two very different speed and correctness
| requirements. That means that data and software should evolve
| following different procedures, what heavily implies that you
| want different people working at them, or at least you want
| people to look at them at different times.
|
| For that, you really want independent access controls and CI
| tooling.
|
| Of course, you can't separate much if you are a 1 or 2 people
| team at early stages of some project. And it may help you move
| faster.
|
| But:
|
| > "Rebuilding the database is as simple as executing one line
| bash loop (I do it every few minutes)"
|
| This denounces a very development-centric worldview where
| operations and maintenance don't even appear. You can never
| rebuild a database, and starting with the delusion that you will
| harm you on the future.
| capableweb wrote:
| > You can never rebuild a database, and starting with the
| delusion that you will harm you on the future.
|
| That seems like a silly assumption. Who's to say you can't use
| databases that you can rebuild from scratch whenever you want?
| What about append-only logs built with CRDTs and the other
| ways?
| shuntress wrote:
| "rebuilt from scratch" means "as if the production server
| just fell into a woodchipper and I'm taking a new one out of
| the crate now"
|
| You __cannot __do that with databases because the __ _data_
| __needs to come from somewhere.
|
| I'm sure you could implement some kind of echo server in SQL
| somehow and yeah that is a "database" that could be rebuilt
| from scratch with no loss but that's obviously not the kind
| of thing we are talking about here.
| taffer wrote:
| For this reason you should always separate data and code and
| put them into separate schemas. In such a setup, deploying new
| code essentially means dropping and re-creating the code schema
| in a single transaction.
| Roboprog wrote:
| Thanks for pointing that out.
|
| Safe to assume views go in the "code" schema with
| procs/funcs/packages, leaving just tables and sequences (if
| needed) in the "data" schema?
|
| Considering building a side project using this kind of
| approach...
| siscia wrote:
| > Performance hint: developers using cursors should have their
| hands cut off. This should do the trick.
|
| What is wrong with cursor?
| unklefolk wrote:
| Performance basically. As SQL is a set based language you can
| often replace cursors (which are basically loops) with joins
| and that is orders of magnitude quicker for larger workloads.
| mdoms wrote:
| I have worked on an application that heavily used sprocs for
| business logic. I would not wish it on anyone. It will seem fine
| (and actually it will seem better - faster and easier to manage)
| while your project is small.
|
| In a few years when you need to make some major changes you will
| see why it was a bad idea, and it will be too late. Have fun
| modifying hundreds or thousands of sprocs when you need to make a
| large-scale change to the structure of your data, because SQL
| won't compose. Have fun modifying dozens of sprocs for each
| change in business logic, because SQL won't compose. I guarantee
| you will have mountains of duplicated code because SQL won't
| compose.
| rishav_sharan wrote:
| Can you elaborate a bit more on duplicated code/composition
| issue? I would think that functions would ensure that there
| isn't much duplicated code.
| mdoms wrote:
| Say for example you need two queries, one for fetching a
| paged list of customers ordered by birth date, and one for
| fetching a paged list of customers who are male.
|
| You will find you'll need two queries with a huge amount of
| duplicate logic - the SELECT clauses, even if they're pulling
| the same columns, will need to be duplicated (don't forget to
| keep them in sync when the table changes!), because there's
| no reasonable way to compose the query from a common SELECT
| clause. The paging logic will be duplicated for the same
| reason. So although the queries are similar, only sorted and
| filtered differently, you will need to duplicate logic if you
| want to avoid pulling the entire data set first and
| performing filtering and sorting in separate modules.
|
| These problems become significantly worse when you're talking
| about inserting, updating or upserting data that involves
| validation and business rules. These rules are not only more
| difficult to implement in SQL, but they change more often
| than the structure of data (in most cases) so the duplication
| becomes a huge issue.
|
| I haven't tried this on a system that uses Postgres functions
| so I could be way off base here, my experience was pure
| sprocs in MS SQL.
| panarky wrote:
| Views help with this.
|
| Write one view for selecting customers, with all the joins,
| sub-queries and case-when logic.
|
| Then select from this view order by birth date, and select
| from the same view by sex.
|
| Yes, the limit/offset paging clause will be duplicated, but
| all the joins, sub-queries and case-when stuff can be DRY'd
| in a single view.
| kqr wrote:
| I agree. To me this sounds more like bad programming (high
| coupling and low cohesion) and like it would be a problem
| even if a separate application acted as the backend.
| aaomidi wrote:
| Well some systems make good programming really difficult.
| Stored procedures, especially back then.... Yeah.
| throwaway894345 wrote:
| I believe this in the abstract, but would be very
| interested in some concrete explanation for why sprocs
| compose worse than other programming languages. How would
| the OP's situation have improved if instead of sprocs he
| had a similarly complex webapp and had to deal with the
| schema changes there? I'm not challenging the OP; I don't
| have enough experience with sprocs to have an informed
| opinion, but this information is necessary to make sense
| of the OP's criticism.
|
| The best argument against sprocs that I've heard is that
| you really don't want any code running on your database
| hosts that you don't absolutely need because it steals
| CPU cycles from those hosts and they don't scale
| horizontally as well as stateless web servers. This is a
| completely different argument than the OP's, however.
| cameronh90 wrote:
| Databases are engineered for high reliability - data
| integrity above all else. That means they develop more
| slowly and are perpetually behind what you expect from a
| modern programming language.
|
| If someone created a database with the intention of it
| being a good development framework, it would probably be
| more pleasurable to code against, but would you trust it
| with your data?
| aszen wrote:
| Becuase of lack of tooling there is tons of duplicate logic
| in SQL based systems, I see the same logic repeated across
| queries which already exists in some view but no knows about
| it so it ends up being repeated. Lack of abstractions like
| modules and namespaces for logically grouping stored
| procedures makes it hard to understand where is what
| fiddlerwoaroof wrote:
| Every database I've used has namespaces: most application
| code ignores SQL schemas, but that's what they're for.
| [deleted]
| cneu wrote:
| 21 years ago, I joined a young team that wrote an ERP, with all
| business logic in PL/SQL. Customers are still amazed how fast
| these guys are going. 10 years ago I joined Oracle to work on
| APEX (created in 1999), which is a web app development and
| runtime environment, written in PL/SQL. We estimate that there
| are about 260K developers world-wide who are developing apps
| with APEX. All of their business logic is also on the server.
| Call me biased because I am working for the man, but my data
| points are completely different.
| sbuttgereit wrote:
| I worked with two other ERP class systems that did this, one
| using Oracle (with an Oracle Forms/Reports front-end), the
| other PostgreSQL.
|
| I can confirm your experience. There's no doubt that stored
| procedure/function based business logic requires a certain
| discipline, knowledge set, and the ability to get a team in
| marching in the same direction. But if you can achieve the
| organizational discipline to make it work there are definite
| advantages, especially in the ERP space.
|
| And that may well be differentiator... those of us working
| with more traditional business systems have trade-offs that
| you won't find in a startup. For example, a COTS ERP system
| is more likely to not really be a single "application", but a
| bunch of applications sharing data. This means a lot of
| application servers, integrations, etc., not all from the
| people that made the ERP, needing access to the ERP data. The
| easiest integration is often at the database, especially
| since you can have technology from different decades (and
| made according to the fashions of their time) needing to
| access that one common denominator. Since many ERP systems
| are built on traditional RDBMSs, and talking to those doesn't
| change much over long periods of time... having the logic be
| there to make these best of breed systems work sensibly with
| the ERP can be very helpful. All that said, in this context
| I'm an advocate of the approach.
|
| Now, take a team that maybe has a high churn, sees the
| database as some dark and frighting mystical power that can
| only safely be approached with an ORM, or a team that thinks
| their "cowboy coding" is their core strength (if perhaps not
| that thought directly) and database based business logic
| certainly has many foot-guns. But, there are really few
| technologies that don't suffer without a good well-rounded,
| consistent approach.
| piyh wrote:
| I developed an application on APEX to make a new front end
| for our old payroll database. Thanks for making my life
| easier.
| zubairq wrote:
| I agree, Apex is awesome. Oracle Apex being written in stored
| procedures was actually a big inspiration for a low code tool
| project I did myself, which while not being written in stored
| procedures took alot of other ideas from Apex (I was an
| Oracle Apex developer for 4 years)
| cneu wrote:
| Cool, good luck with your project!
| xupybd wrote:
| I have seen large database heavy apps. They are the cleanest
| I've ever worked with. I've had no trouble composing SQL. I
| can't think of one example where this has been an issue. You
| need to rely on views, functions and tables to store your
| business rules and look up the rules.
|
| The big down side I see is the learning curve. Writing business
| logic in the database is not easy and not easy to maintain. It
| can take half a day to grow a large function. Even longer to
| debug sometimes. But you learn to structure things well.
| ak39 wrote:
| If you write your views, tvf and stored procedures to use
| common elements defined once and once only (this starts with
| once and once only _tables_ ), maintenance is easy peasy. If
| you let every developer create their own view for the same
| business logic, then you are creating an unmanageable creature.
|
| This also applies to "composable" code written in the
| application layer outside the database.
| LukeShu wrote:
| That's the approach that https://crt.sh uses.
|
| https://www.lukeshu.com/blog/crt-sh-architecture.html
| dang wrote:
| jgrahamc wrote a fascinating comment about how Cloudflare started
| this way: https://news.ycombinator.com/item?id=22883548
| njkleiner wrote:
| Related: https://sive.rs/pg
| notJim wrote:
| See also
|
| https://www.slideshare.net/beamrider9/scaling-etsy-what-went...
|
| https://www.youtube.com/watch?v=eenrfm50mXw
___________________________________________________________________
(page generated 2020-12-03 23:01 UTC)