Had an interview a couple of years ago for the Go position. Turns out most of their business logic was written in postgres, good thing it didn't work out. But apparently it's a trend in some circles to literally be writing apps with Postgres and a very thin layer of whatever happens to wrap it.

>>109043380
it's kinda comfy actually. especially since most sites are just db query + client side js, with very little backend

moving the logic to the data is faster than moving the data to the logic, news at eleven

>>109043380
Saw that a lot about twenty years ago when DBAs still existed and acted like a priesthood around their databases. Once developers were able to deploy their own DBs, most of that stopped as companies shitcanned DBAs and no one cared.

>>109043380
We do the same but in php instead of go
Its pretty goated tbqhwy

File: b02d7c896a9259828798175a2(...).jpg (68 KB, 735x905)

68 KB JPG

>>109043380

>>109043360
>Who asked for this shit?
Corpos did.
Postgres exists since 1996, yet nobody used it until about ten years ago. It has a MIT-style license where corpos can do whatever they want, and they throw lots of corpo garbage on it and run their own proprietary forks.
Then they scammed you into using it, so that you do free labor for them and bugtest their crap.

Mariadb is the current sane choice.
>b-but some corpo benchmark said that postgres is faster with this workload that doesn't represent mine and some dumb written queries
Learn to write a query and set indexes, loser. No switch between RDMS can cover for your stupidity.
Postgres does some stupid vacuuming, which is dumb. It's inferior for that reason alone already. I don't want to vacuum my database.

>>109046027
>I use a worse, slower, legacy db because its not as popular as this other one
Mental illness

>>109043380
>most of their business logic was written in postgres
That's a good hing.
If you have any operation that can described as:
>multiple fetches for data -> modify data -> store data
You save at least two round trips with a procedure. And if your database is on a different machine than your application, the round trip time is the bottle neck (unless your database design is inefficient and your queries are slow as a consequence).
There's an autistic security concern here as well, since the more logic you do in the database itself, the less data leaves the database. It allows for a security model where you can distrust your own application. Applications that are made by this logic are rock solid.

File: mongodb-logo-png_seeklogo(...).png (9 KB, 600x600)

9 KB PNG

>>109043360
It's fucking garbage. Using 1 process per connection is fucking retarded. It's so fucking bad you need to use pgbouncer. Also SQL was literally made so non-devs could query databases, why are we even using this?

I'll just keep using picrel and not have to care about any.

>>109046380
>worse, slower
Mariadb scales better, doesn't need vacuuming and has better MVCC
https://www.cs.cmu.edu/~pavlo/blog/2023/04/the-part-of-postgresql-we-hate-the-most.html
>legacy db
wtf is a "legacy db"?
Postgres is 20 years older than Mariadb. If you use this idiotic "its MODERN" argument, at least make sure that it's actually true.
The only reason why its shilled is because of its license, it is objectively technologically inferior.

>>109043360
how? werks for me.

>>109046027
>>109046469
I use MySQL too and I can't understand the reason behind the Postgres hype today. Most features feel like gimmicks where I'm like "I wouldn't want the database to do that anyway", while more fundamental architecture of the Postgres seems pretty weird in comparison, like the indexing in the article, and MySQL seems more sane.

It is a fact that the MIT license is preferable, though corpos are still free to offer their own version of MySQL as a service. For a database, it absolutely doesn't matter for the end user. It sounds nice that GPL keeps the code more open, but in practice, MIT has literally won.

File: 1773165995347287.jpg (56 KB, 878x1024)

56 KB JPG

>>109046463
>mongoDB
suits you

>>109046027
thanks for the input.
I am currently building a side project, am still on the frontend but once I reach the backend I couldn't decide yet if I should use Postgres or mariaDB.
We use Postgres at work but I wanted to try something new.

>>109047047
Postgres has an old model where every connection uses its own process.
That's because of age. It got made in 1996 and at that time, simple servers used to do this, because of the easy memory management. Kill the process and all its memory is freed.
Same thing with the requirement for a VACUUM, which is the database equivalent of defragmenting a filesystem. The first filesystem that was smart enough to not fragment like crazy in the first place, was afaik ext3 in 2001. Postgres is 5 years older than that.

It's the weird mix between 30 years old software and new stuff thrown on top of it, that created current postgres.
Postgres isn't bad. It is fast in those benchmarks they gloat about.
But if you have performance problems in your database, it's better to rethink your queries and to ensure that you have good indexes. A bad query is bad no matter the RDBMS you choose. Indexing is especially important: https://use-the-index-luke.com

>It is a fact that the MIT license is preferable
No, hat is a lie. Whatever corpos shill shouldn't affect you.

>>109046027
>Mariadb is the current sane choice.
tbdesu for most personal projects one can just use sqlite

>>109047480
For most use cases, Sqlite is not an alternative to Postgres or MariaDB.

>>109047815
For most use cases you use what your corp is already using, again that was about personal projects

>>109047964
t. fizzbuzzer
If you make any personal project that is intended to be used by multiple people at the same time, you're not going to use SQLite.

>>109047975
>muh single writer
Not really a problem

>>109047964
No matter what you use, do NOT use any ORM.
You will hate yourself for it down the line.

>>109048037
werks on my machine. are /g/tards ever right?

>>109048037
Most ORMs ship with a query builder

>>109048037
>do NOT use any ORM.
you actually write the sql queries yourself? I use sqlalchemy and unless you're an utter moron it works fine.

>>109048144
your python script isn't going to see any high load, or any query more complex than a single row select, so go ahead and use it

>>109047469
>>It is a fact that the MIT license is preferable
>No, hat is a lie. Whatever corpos shill shouldn't affect you.
Whether you like it or not, it's an objective fact that MIT has won.
The result is that Postgres is getting a lot more development and adoption than Mariadb, despite the latter being the go-to database for over 2 decades, running behing systems making trillions of dollars even today.
Licensing no longer works the same as it did in the 90s and 2000s.

>>109046027
MySQL likes are shit. People like Postgres because it's mvcc so it's less hard to shoot your penis off. It is a shit DB, but it's foss and generally proves the mantra that sometimes worse is better, both cost and in complexity budget.

>>109047975
Why? It's 2026 boomer. sqlite will very likely outperform any rdbms on latency characteristics alone.
>B-but concurrent writes!
queue them, hold mutex on writable connection, "shard" data over multiple sqlite's.

>>109048057
>Makes another N+1 query because ORM tard.

>>109048277
why is it shit and what isn't shit?

>>109048336
>it's the ORMs fault when I'm a retard who doesn't understand the queries it produces

>>109048259
If it would have won, then MIT(-like) software wouldn't be so shit.
Postgres suffocates on all the corpo trash that got thrown at it and got objectively worse with corpo attention. Same with LLM. And lets not forget about the BSDs who got adopted by corpos and then left to rot.
Getting corpo attention is a death sentence.

>>109048348
>you just have to learn how the ORM generates queries and look at its output
Then whats the use case of ORMs again?

>>109048340
Because it's a preforking boomer database. They're slowly fixing it, but I sure as shit wouldn't trust any comprehensive overhaul of something as critical as a database, especially one with as much 90s cruft and legacy C shitcode as Postgres. MySQL is bad because it has its own baggage and is in a quasi (un)supported state. MariaDB tards are stuck in the past and the great lawnmower sometimes throws a team together to push something new in MySQL. Vacuuming can be a footgun, depending how you use the db, but I'd rather take minor shit like that than try and correctly use MySQL.

>>109048348
I mean, you realize you basically self-owned yourself, right?

>>109048259
>Postgres is getting a lot more development and adoption than Mariadb
The corpo world uses Oracles propriatery DB... you know, the one that they named their company after.
In the non-corpo world, MySQL is still king, doesn't matter how much you want to deny it, but most of the internet runs on it.
Which leaves postgres to the current-thing using retards. The same retards who swore that MongoDB is the future ten years ago. And in a few years you will switch to the next current thing that corpos tell you to switch to.

>>109048348
>hurr durr just look at the output
It may sound like a crazy idea but have you considered writing the queries yourself in the first place?

>>109048367
>90s cruft and legacy c shitcode
Did you just list everything that is bad about Postgres and claim that its MySQL?

>>109048388
I complained about both. I don't like either and I really hate SQL in general. I'd still rather use postgres because I know it's easier to correctly use.
If it weren't nonfree, I'd rather use MS SQL Server all the way.

>>109048323
Sure, on latency, it outperforms it. And Redis outperforms Sqlite on latency. And Memcached probably outperforms Redis on latency. And an in-memory cache directly in your web application outperforms Memcached on latency.
>just add a bunch of complexity nobody needs or wants so you can use this wrench as a hammer
The point of a database is that it does its thing and keeps the data safe. MariaDB and Postgres literally just work and solve all the problems with concurrency.
Sure for your personal blog where you post twice a day, Sqlite is perfect. But for a social media or e-commerce site with constant concurrent writes, it's not the right tool.

>>109048426
>solve all the problems of concurrency
No sql database does this. Every transaction has to be carefully considered. Other databases may have different isolation levels and fallability, but I'd be careful to suggest it's "solved" by any stretch.

>>109048426
Also you don't need a lot. In most cases just using immediate transactions and set some sensible write lock timeout. It's a half assed solution that can still fail with SQLITE_BUSY, but it works.

>>109043380
This was super common in oracle days here, and went kind of out of fashion after mysql got popular and eventually postres, seems its going back to the oracle days

>>109048358
of course you have to understand it if you don't want to produce garbage. just like with any tool.
you don't have to look up the resulting queries if you understand the ORM.
it has a lot of usecase like conveniently mapping to entities, lazy loading relationships, automatically generating schemas and migration scripts from entities, built-in pagination support etc. etc.
it does tons of shit that would be a pain in the ass to implement manually each time.
>>109048376
no, not really.
>>109048385
I didn't say look at the output. the fact you don't know the output just shows it's skill issues on your side.

>>109043380
Apps like this end up being horribly denormalized garbage with like 5 triggers per table. God it's awful.

>>109048451
Duh? Sqlite creates extra concurrency problems for you you don't have with other databases. Quit pretending to be retarded.

>>109048498
>strict serializable writes can cause issues!
uh, ya? And other isolation levels can cause you silent(er) issues. My point is it's trade-offs. There are experimental concurrent writes at the page level that fail for conflict, so hopefully that's enough to stop this back and forth.

>>109043380
this reoccurring trend somehow
every once in a while some retards come up with idea 'lets write logic on DB layer!' and then quickly get burned on it due to problems.
Around ~10 years ago I worked on a project where ALL of the logic was written in PL/SQL and Java only acted as a gateway for UI. 1k lines PL/SQL scripts generating banking reports for clients, updating complex taxing calculations based on current law etc. It was stupid as hell, as it was difficult to test, write unit tests for and one incompetent dev could crash the whole db.

>>109047815
I would argue that it is. Certain features the latter two provide are not present in SQLite but in most cases they're not necessary.

>>109048377
>The corpo world uses Oracles propriatery DB
I'm a europoor who's worked in Big Banks and Big Pharma, the only SQL database I've seen used is Postgres. Maybe things are different in other parts of the world.

>>109046027
>Postgres exists since 1996, yet nobody used it until about ten years ago
The fuck are you on about? people who weren't happy with the lack of reliability of mysql used and still use it. Of course this didn't include webdev soitards.

>>109046387
>There's an autistic security concern here as well, since the more logic you do in the database itself, the less data leaves the database.
You can just solve this with containers/pods, where you insert Postgres and a service in the same network stack

>>109048940
But postgres is the most unreliable rdbms?
You are sitting on a ticking time bomb when you use it, hoping that the vacuuming can keep up with he trash creation.

>>109048915
Oracle does 3000 license audits at corpos, who use their system, all over the world per year.
And the average settlement after an audit is a 6 figures $$$ amount. Meaning that they make billions with those alone.
Oracle buttfucks other corpos hard and with no lube.
And there are contractor companies all over the world who specialized in helping corpos when Oracle comes knocking, here is one from Switzerland: https://www.bbi.ch/blog/oracle-audit

>>109046463
lmao, go back to 2013

>>109043380
>But apparently it's a trend in some circles to literally be writing apps with Postgres and a very thin layer of whatever happens to wrap it.
>>109045756
>Saw that a lot about twenty years ago when DBAs still existed and acted like a priesthood around their databases.
Stored procedures are not inherently bad, although Javascript shitters and microservice shillers and agile priests deride them as "legacy technology". They're great for something like "transactionally either reserve this widget into anon's cart or fail", where checking all the conditions for that touches 5 tables and requires an order record gets created. They're okay for protecting the database from retards using ORMs and shit or writing dogshit SQL since writing a proc in PL/SQL is a natural filter. And if you have a weird team dynamic you can use a read-only procedure to keep the non-database people out of your hair with "Just CALL this function and FETCH the cursor I give you and go away." The limit for me is once you put something like a tax charging rule into a procedure you went too far, because you can't test it and the next guy almost certainly won't find that bit of SQL deployed on the database cluster is the magic bean counter step.
>>109048915
>I'm a europoor who's worked in Big Banks and Big Pharma, the only SQL database I've seen used is Postgres.
>>109048377
>The corpo world uses Oracles propriatery DB...
>Which leaves postgres to the current-thing using retards.
Basically >>109049285 got it. Postgres picked up a ton of bells and whistles because companies that got ratfucked by Oracle tried using it as a drop in replacement. MySQL has different syntax and behavior, Microsoft costs money, and the other databases don't fit in the server client RDBMS architecture. But it's not a drop in replacement because sequence and locking behavior is just different enough to cause bugs and errors.

>>109043360
wtf are you talking about it just stores tables

>>109043360
Databases need to do many of the things an OS does so it isn't surprising.

File: OEM.gif (33 KB, 677x518)

33 KB GIF

Sometimes splitting logic between layers has obvious choices and sometimes it's a matter of local taste. Twenty years ago DBAs got really aggressive about putting everything into the database, even trying to serve image blobs from it straight through to the web server. Many of them openly bragged about how they were going to put an end to web devs by doing everything in the database, which only they were allowed to touch. Things didn't quite work out that way for them.
In our current environment filled with boot camp grads who are expected to be jacks of all trades, there's lots of really bad shit going on in all layers, including the database layer, that a competent DBA would put a stop to if they existed and had the authority to do so. There's some breathtakingly bad shit going on, like devs who will pull an entire table's content down to the browser and have JS filter out what the frontend needs. Conceptually, they can't understand why this is bad or even why their babel tower of libraries is doing it. They just know they need a to populate a selector with the name of all of the states and the customers table has all of them in it, so why not use that? The amount of fucktardation going on is amazing.
DBAs got arrogant and drunk on power. The labor market punished them for it and we're all worse off for it. Most of them are now SREs or DevOps, handling all manner of IT sewage work.

>>109049663
>Twenty years ago DBAs got really aggressive about putting everything into the database, even trying to serve image blobs from it straight through to the web server.
It's remarkable how the ivory towers fell and how badly the result after was.
Blob shit never belonged in a database, and neither does XML or JSON or pretty much any column containing more than one value in it. Shitty pipe delimited string arrays give me nightmares to this day. Actually having a guy who's job it was to keep the database schema definitions together was great along with someone to blame when the queries were slow.
And I hate the NoSQL meme shit so much. Fucking DynamoDB makes you implement a pagination while loop to read everything, and fuck you if you need to search for partition key A or partition key B or partition key C.

>>109043360
It's the free db the closest to oracle, my former company used to exclusively ship its softwares with oracle but customers started complaining about paying for it (user or cpu licences) so we started to think about implementing them with postgres. I left before the project started but I knew rewriting all this plsql procedures and triggers in pl/pgsql would be a nightmare, furthermore we also had scheduling jobs which postgres lacks completely.

>>109050119
>we also had scheduling jobs which postgres lacks completely.
can you not schedule things outside of the DB and have that call the DB to perform whatever job you need?

Also, might as well try to learn something here, do you guys have a book to recommend on any DB-related subject? Something you found useful or interesting?

File: 1772195228709006.gif (2.58 MB, 200x314)

2.58 MB GIF

so now that we are all frens in this thread, what's the best and more lightweight for hobby side projects which eventually some people might use?

>>109043380
That's how any serious company works.
Airlines, banks, financial companies, etc.

All of the critical stuff is enforced at the database level, not by some shit-tier Java written by pajeets.

That is quite literally the right approach.

>>109052446
It really depends on your architecture.
Generally you use SQLite if you can, Postgres otherwise, and that should cover 99% of your needs.

>>109052469
nothing big, just a small project with typescript and web components for the frontend, am just learning stuff and wanted to build an application tracker for myself

>>109049735
>Blob shit never belonged in a database, and neither does XML or JSON or pretty much any column containing more than one value in it.

It's data. Unless we're talking enormous blobs (videos, etc.) there is no reason not to store that in the database. It solves various issues related to security, auditing, retention, backup, etc. Which is pretty much what a database management system is for.

And modern databases can in index JSON and XML documents.

>>109052477
SQLite is honestly great.
It has fewer features than Postgres but it's still a real RDBMS and a great way to learn.
No need to deploy anything (it's just a file). So you can copy that file, experiment with it, etc.

There is a point where you start longing for more features, and that's where Postgres becomes useful. The skills transfer easily. Aside from minor syntax things, what you learn on SQLite also works on Postgres.

File: 1758130135787701.png (50 KB, 200x200)

50 KB PNG

>>109052499
Great, thank you very much!

>>109052477
unironically cloudflare wont cost you a fucking dime.
that company does wonders for poverty tinkerers.
get D1 SQLite DB, CF Worker & Page(s).
Worker (basically edge server(less) that only "exists" when its executing code, free i think can basically host any webapp that doesnt need a function executing for over 30s continuously, $5 a month lets you up it to 5 minutes or setup docker containers) can serve typescript / javascript / python / rust. pages is for the actual hosted pages themselves to be driven by the worker.
its $0.00. forever.
if you ever scale they have just a $5/mo "Paid" plan that basically gives you that higher execution time and like 10x allotted monthly resources.

>>109049254
>You are sitting on a ticking time bomb when you use it, hoping that the vacuuming can keep up with he trash creation.

What are you talking about?
Vacuuming is literally just an optimisation, to better pack the data on disk (remove the holes created by deleted rows) in order to reduce cache thrashing.

There is no time bomb. You realize that Postgres is used in extremely large deployments by some very big companies?

>>109052520
Thanks will check out cloudflare.
I have played around with GitLab pages for the frontend and Render for the backend once and it worked fine too, for free.

>>109045756
>Saw that a lot about twenty years ago when DBAs still existed and acted like a priesthood around their databases
I've noticed must data engineers I meet who are former DBAs are usually chronically insecure jerks that hate that they have to do things in normal programming languages now
Why is this?

>>109052563
cloudflare is dope because their infrastructure is basically first class.
performance and latency is as good as it gets cause that worker you host is just instanced real-time as needed on the nearest node to any particular user visiting your app.
considering they provide service to like 40% of the internet, never had anything but exceptional experience.
my understanding is they make such stupid fucking money selling their expensive enterprise services to corporations that they basically provide more than enough self-hosting capability a dude fucking around with some projects or even hosting a webapp 5-10 people use daily essentially out of good-will.
i will never not dickride them. good people.

>>109047469
>But if you have performance problems in your database, it's better to rethink your queries and to ensure that you have good indexes. A bad query is bad no matter the RDBMS you choose. Indexing is especially important: https://use-the-index-luke.com
Are indexes really that much of a mystery to Devs?
I consider them to be some of the most basic shit anyone touching a database should know, right alongside creating a table

If you inspect the execution plan it'll even tell if there's a missing index it needs and recommends what to create

>>109052591
>performance and latency is as good as it gets cause that worker you host is just instanced real-time as needed on the nearest node to any particular user visiting your app.
that's very interesting.
thanks for the input, will definitely check it out.

>>109045756
>Once developers were able to deploy their own DBs, most of that stopped as companies shitcanned DBAs and no one cared.

Clearly you don't know what you are talking about.
I used to work for one of the big HFT companies, the DBAs were the only guys who refused to become employees and preferred to stay on contract, making crazy daily fees, and they made more money than most traders or engineers.

Serious companies take their data seriously, so they take their databases seriously, so they hire good DBAs.
(I'm saying that as a developer.)

Without DBAs to gatekeep databases, the modern world would be utter chaos, you'd have random transactions appear and disappear on your bank account, you'd go to the airport for your flight and no flight would be scheduled for you on that day, and so on.

>>109043360
You get these types who dogmatically moan

"THE DATABASE IS THE ONE AND ONLY SOURCE OF TRUTH, NOTHING MAY EVER BE CACHED, REPLICATED, OR WORSE, DUPLICATED"

If they ever have a bad experience with poor performance (that could probably be fixed with an index), or bugs in application software, they will respond by moving responsibilities from the application to the database. That's how you get business logic inside stored procedures, with awful, awful code only a minority will ever be able to maintain.

What's left for the software then is querying data through HTTP interfaces, and that's how you get sql/code-generation libraries. There's also a postgres extension for that.
What's left is then at most a scheduled job, and guess what, there's an extension for that too.

The result is unmanageable software (except by those who made it) that's slow to change, update, that quite likely does not achieve the goal of a perfect application state anyway.

>>109046387
>>109045830
I'm calling bullshit. Putting business logic in the database just makes for a hot unmanageable mess that's not worth maintaining both stored procedures and whatever language your software uses.

Any performance concerns you may have is likely the result of a poorly managed database structure rather than anything else.

>>109052626
With AI slop code, this trend is only going to increase.
Because we're going to see more and more throwaway code, the only place of sanity/stability will be the database, and that's where all the controls/safeguards will be.

Better get used to it.

>>109052646
sorry bro, supabase called
[spoiler]their ai advisor is actually insanely good at handholding them its just none of the vibeslop pay any attention to it[/spoiler]

>>109052642
The reason companies put (some) business logic in the database is to have a central place to audit it, and make it correct.

In a company, a database might have literally hundreds of applications connecting to it. You're not going to enforce the most critical rules there, that's what the database is for.

The bigger the company and the more applications you have, the more the database is going to do "business logic". This is the only way to guarantee that all those applications show the same results whether it's shown in:
- website
- mobile app
- some reporting backend for auditors
- some analytics for financial controllers
- etc. etc.

It's not a conspiracy to take some creativity away from programmers, it's a way to make sure you see the same number everywhere.

>>109052646
You're sorely mistaken if you think jeets can't use Claude to write stored procedures. They do, and I know that for a fact because that's what I see happening where I work.

When they fuck up, and they do, where consumers get OBJECTIVELY WRONG FUCKING DATA, all you'll get in response is "well that's the price to pay for this awesome performance :^)". I wish I was joking.

There's no silver bullet for quality in software, and stored procedures just make things objectively worse. Sure they have their use cases, but PLpgSQL for everything is not it.

>>109052655
People who write PLpgSQL or schema creation statements ARE in fact programmers.

Also, last I checked, databases, even PGSQL, have ACID for guaranteeing data validity. I'll also say that you are in fact, not showing the same results across websidtes, apps, reports whatever because each of those are stores in themselves, whether they show the same data or not depends on their own caching rules, fetch times and even how the software modifies your data.

The concern just isn't real, and is perfectly fixable by using transactions.

Once you get into PLpgSQL and doing manual transaction handling with begin/commit, you're going to get race bugs you wouldnt think were possible. Throwing back wrong data is worse than giving correct data slower.

>>109052655
Maybe don't have applications sharing Databases, at least not in the way where they can write into them. Seriously, this is almost obvious, isn't it? If you have some data shared between apps which they can both write into, it sounds like a separate service to me altogether.

Your example for webapp/mobile app is an example where you misunderstand how it should architectured. Regardless of what kind of backend you have and how you produce your website - whether it's SSR or fontend framework + backend API, theat same backend should handle API requests from a mobile app. In reality itreven simpler, because most mobile apps are just WebApps inside WebView.

Use SQLITE untill you need more.

>>109053264
NVKEEE

File: 1775148037195699.png (2.3 MB, 1080x1377)

2.3 MB PNG

>>109043360
>Who asked for this shit?

>>109052609
No one cares about your n=1 experience. You're not the entirety of humanity, calm your ego a bit.

>>109052609
I also work for a "serious company" and database management in its entirety is left up to the developers.

>>109043380
Man made horrors