4 ms·

Totally agree - I have tried many databases of all flavors, but I always come back to Postgres. HOWEVER - this blog post is missing a critical point.... the qu

Totally agree - I have tried many databases of all flavors, but I always come back to Postgres. HOWEVER - this blog post is missing a critical point.... the quote should be: ---> Just use Postgres AND ---> Just use SQL "Program the machine" stop using abstractions, ORMs, libraries and layers. Learn how to write SQL - or at least learn how to debug the very good SQL that ChatGPT writes. Please, use all the very powerful features of Postgres - Full-Text Search, Hstore, Common Table Expressions (CTEs) with Recursive Queries, Window Functions, Foreign Data Wrappers (FDW), put JSON in, get JSON out, Array Data Type, Exclusion Constraints, Range Types, Partial Indexes, Materialized Views, Unlogged Tables, Generated Columns, Event Triggers, Parallel Queries, Query Rewriting with RULES, Logical Replication, PartialIndexes, Policy-Based Row-Level Security (RLS), Publication/Subscription for Logical Replication. Push all your business logic into big long stored procedures/functions - don't be pulling the data back and munging it in some other language - make the database do the work! All this stuff you get from programming the machine. Stop using that ORM/lib and write SQL. EDIT: People replying saying "only use generic SQL so you cans switch databases!" - to that I say - rubbish! I nearly wrote a final sentence in the above saying "forget that old wives tale about the dangers of using a databases functionality because you'll need to switch databases in the future and then you'll be stuck!" Because the reason people switch databases is when they switch to Postgres after finding some other thing didn't get the job done. The old "tut tut, don't use the true power of a database because you'll need to switch to Oracle/MySQL/SQL server/MongoDB" - that just doesn't hold.

rcarmo 2y ago
your answer was fine and good until you classified ChatGPT's SQL generation as "very good" -- which it is not. I've had _all_ GPT models spit out monstrosities and slow queries of all kinds. ORMs are not all bad. In fact, some ORMs generate better code for really complex joins (think hundreds of tables, each with hundreds of columns) than humans, and often ensure that trivial best practices (like indexes and consistent foreign keys) are followed. Writing SQL is a great skill, but if you tie yourself to a single database engine's idioms then you're in for a shock when you switch platforms/jobs/environments.
- deleted 2y ago
  [deleted]
frithsun 2y ago
Try to avoid the bespoke features of psql in favor of generic SQL unless cornered by circumstances into doing so, methinks. If there's one complaint I have about pg, it's that it has too many features that encourage finding cute, non standard, non obvious ways of going about things.
- LukaD 2y ago
  > Try to avoid the bespoke features of psql in favor of generic SQL unless cornered by circumstances into doing so, methinks. Why? To make migration to another database easier? I've never had the need to migrate any application away from postgres. I usually take full advantage of what the database can do.
  - maccard 2y ago
    I’m a proponent of vendor lock in is not a big deal - you’re not going to switch from AWS to Azure on a whim and if you do, the fact that you’re using ecs instead of k8s isn’t going to slow you down. But data ownership is the one place I get iffy. What if your db does a rug pull and changes licenses? There’s certainly precedent in this space for that.
StefanBatory 2y ago
May I ask question about this part? "Push all your business logic into big long stored procedures/functions - don't be pulling the data back and munging it in some other language - make the database do the work!" From my courses I had at university, I've been led to believe that the current trend is doing hexagonal architecture, as that allows for better modularisation of the project and helps keep code clean over many years with many software engineers coming in and out. As a part of that I've been taught that the only part you could trust then is your internal modules - and even database has to treated as an external source, whose only job is to pull data in and out. How does that work in what you're suggesting? Is it just a different way of approaching things that will work depending on what's your goal is? I'm just curious about this as I'm trying to get myself to learn a bit more, just to clarify
- tazu 2y ago
  The model described in the parent comment is essentially using the database (in this case PostgreSQL, but any RDMS would do) as the hexagonal "core" in which adapters plug in to. This is a powerful pattern that works very well when you use the full features of the RDMS like constraints, triggers, views, etc. This does require "coupling" to the RDMS-specific features, which makes migrating to an alternative system difficult, but in practice this rarely happens if you choose a strong RDMS from the beginning. You can certainly use the database as a "dumb storage" tool in the hexagonal architecture, that is, as just another adapter. But most of the time you'll end up re-creating RDMS features in poorly written/documented application code that has to interact with the database anyways. Why not just do it all in the database? With a RDMS core, hexagonal adapters can be pure functional components, making them much easier to reason about and maintain. For more on this idea, and how to avoid pitfalls with the hexagonal pattern, I recommend reading Out of the Tar Pit [1]. It's a short but highly influential paper on "functional relational programming". [1]: https://curtclifton.net/papers/MoseleyMarks06a.pdf https://curtclifton.net/papers/MoseleyMarks06a.pdf
  - StefanBatory 2y ago
    I see it, that makes sense! Right, I don't think that you'd have a reason to swap RDMS unless licencing issues come up, like with Oracle. Thank you for helping out.
worik 2y ago
> People replying saying "only use generic SQL so you cans switch databases!" - to that I say - rubbish! No, not rubbish Portability matters. Lockin sucks
tormeh 2y ago
Stored procedures suck. First of all SQL is a dubious language to write business logic in because it doesn’t have static typing and other goodies we expect nowadays. But more importantly stored procedures tend to drift out of version control. So please don’t.
- andrewstuart 2y ago
  >> tend to drift out of version control Old wives tale. Modern IDEs are very good at version control over all database elements. https://www.jetbrains.com/help/datagrip/databases-in-the-version-control-system.html https://www.jetbrains.com/help/datagrip/databases-in-the-ver...
  - codr7 2y ago
    Still a lot more complicated to deal with in every aspect, obviously so to anyone who has long term experience with them, what are you gaining from pretending otherwise?
andatki 2y ago
Great list of Postgres features called out that highlight the extensive feature set. Most of these are covered in my book, for anyone that’s interested in learning them. The book uses a Ruby on Rails app with Postgres instances for examples and exercises. Hope the plug is ok here as some folks may be looking for learning resources for Postgres. https://andyatkinson.com/pgrailsbook https://andyatkinson.com/pgrailsbook
codr7 2y ago
You should use the most powerful and convenient language you have at your disposal, which is most likely the host language. ORMs are funny things, it's like we got stuck in the idea of making the database object oriented. MongoDB just means we don't have to pretend anymore, not that it was a good idea. It is perfectly possible to use relational concepts in a general purpose language. Tables, Columns, Foreign Keys, Records, Indexes, Queries etc. And you can build whatever Model abstractions you need on top of that; or not, for simple CRUD you don't really need a type system. I usually build that layer along with the foundation of the application, it still evolves slightly every time around but the basics are very tried and proven by now. https://github.com/codr7/hostr/tree/main/src/Hostr/DB https://github.com/codr7/hostr/tree/main/src/Hostr/DB

rcarmo 2y ago

your answer was fine and good until you classified ChatGPT's SQL generation as "very good" -- which it is not. I've had _all_ GPT models spit out monstrosities and slow queries of all kinds. ORMs are not all bad. In fact, some ORMs generate better code for really complex joins (think hundreds of tables, each with hundreds of columns) than humans, and often ensure that trivial best practices (like indexes and consistent foreign keys) are followed. Writing SQL is a great skill, but if you tie yourself to a single database engine's idioms then you're in for a shock when you switch platforms/jobs/environments.

deleted 2y ago
[deleted]

frithsun 2y ago

Try to avoid the bespoke features of psql in favor of generic SQL unless cornered by circumstances into doing so, methinks. If there's one complaint I have about pg, it's that it has too many features that encourage finding cute, non standard, non obvious ways of going about things.

LukaD 2y ago
> Try to avoid the bespoke features of psql in favor of generic SQL unless cornered by circumstances into doing so, methinks. Why? To make migration to another database easier? I've never had the need to migrate any application away from postgres. I usually take full advantage of what the database can do.
- maccard 2y ago
  I’m a proponent of vendor lock in is not a big deal - you’re not going to switch from AWS to Azure on a whim and if you do, the fact that you’re using ecs instead of k8s isn’t going to slow you down. But data ownership is the one place I get iffy. What if your db does a rug pull and changes licenses? There’s certainly precedent in this space for that.

StefanBatory 2y ago

May I ask question about this part? "Push all your business logic into big long stored procedures/functions - don't be pulling the data back and munging it in some other language - make the database do the work!" From my courses I had at university, I've been led to believe that the current trend is doing hexagonal architecture, as that allows for better modularisation of the project and helps keep code clean over many years with many software engineers coming in and out. As a part of that I've been taught that the only part you could trust then is your internal modules - and even database has to treated as an external source, whose only job is to pull data in and out. How does that work in what you're suggesting? Is it just a different way of approaching things that will work depending on what's your goal is? I'm just curious about this as I'm trying to get myself to learn a bit more, just to clarify

tazu 2y ago
The model described in the parent comment is essentially using the database (in this case PostgreSQL, but any RDMS would do) as the hexagonal "core" in which adapters plug in to. This is a powerful pattern that works very well when you use the full features of the RDMS like constraints, triggers, views, etc. This does require "coupling" to the RDMS-specific features, which makes migrating to an alternative system difficult, but in practice this rarely happens if you choose a strong RDMS from the beginning. You can certainly use the database as a "dumb storage" tool in the hexagonal architecture, that is, as just another adapter. But most of the time you'll end up re-creating RDMS features in poorly written/documented application code that has to interact with the database anyways. Why not just do it all in the database? With a RDMS core, hexagonal adapters can be pure functional components, making them much easier to reason about and maintain. For more on this idea, and how to avoid pitfalls with the hexagonal pattern, I recommend reading Out of the Tar Pit [1]. It's a short but highly influential paper on "functional relational programming". [1]: https://curtclifton.net/papers/MoseleyMarks06a.pdf https://curtclifton.net/papers/MoseleyMarks06a.pdf
- StefanBatory 2y ago
  I see it, that makes sense! Right, I don't think that you'd have a reason to swap RDMS unless licencing issues come up, like with Oracle. Thank you for helping out.

worik 2y ago

> People replying saying "only use generic SQL so you cans switch databases!" - to that I say - rubbish! No, not rubbish Portability matters. Lockin sucks

tormeh 2y ago

Stored procedures suck. First of all SQL is a dubious language to write business logic in because it doesn’t have static typing and other goodies we expect nowadays. But more importantly stored procedures tend to drift out of version control. So please don’t.

andrewstuart 2y ago
>> tend to drift out of version control Old wives tale. Modern IDEs are very good at version control over all database elements. https://www.jetbrains.com/help/datagrip/databases-in-the-version-control-system.html https://www.jetbrains.com/help/datagrip/databases-in-the-ver...
- codr7 2y ago
  Still a lot more complicated to deal with in every aspect, obviously so to anyone who has long term experience with them, what are you gaining from pretending otherwise?

andatki 2y ago

Great list of Postgres features called out that highlight the extensive feature set. Most of these are covered in my book, for anyone that’s interested in learning them. The book uses a Ruby on Rails app with Postgres instances for examples and exercises. Hope the plug is ok here as some folks may be looking for learning resources for Postgres. https://andyatkinson.com/pgrailsbook https://andyatkinson.com/pgrailsbook

codr7 2y ago

You should use the most powerful and convenient language you have at your disposal, which is most likely the host language. ORMs are funny things, it's like we got stuck in the idea of making the database object oriented. MongoDB just means we don't have to pretend anymore, not that it was a good idea. It is perfectly possible to use relational concepts in a general purpose language. Tables, Columns, Foreign Keys, Records, Indexes, Queries etc. And you can build whatever Model abstractions you need on top of that; or not, for simple CRUD you don't really need a type system. I usually build that layer along with the foundation of the application, it still evolves slightly every time around but the basics are very tried and proven by now. https://github.com/codr7/hostr/tree/main/src/Hostr/DB https://github.com/codr7/hostr/tree/main/src/Hostr/DB