Can someone explain the difference between declarative and imperative in this context? My understanding is SQL is declarative.
So is declarative something like “writing high level statements that postgres turns into more specific (imperative?) instructions”? And imperative being writing those specific “do this, then do this” yourself (although probably differently than how postgres does it under the hood).
SQL is declarative, but Postgres provides an alternative language for database-side programming called PgSQL[1]. PgSQL adds some imperative features, like FOR loops and variables, as a superset of SQL's features.
So functions in the database can be written in this imperative language (or in SQL), and integrated into features like database triggers or called be clients (or, commonly, used to write pg_cron jobs).
And your understanding of the distinction between them is correct. Declarative describes what you want to do but not how you want to do it, and Postgres' query planner (or that any fully featured SQL database) will uses statistics it has recorded about your data and indexes you've created to optimize the execution of your query (decide how to do it). PgSQL can tell Postgres
what to do and how, except in the places where you're writing declarative SQL statements (since it's a superset). Breaking things up into smaller units like that limits the scope the planner can optimize over, which may defeat or limit certain optimizations and you may end up with something less performant.
For some straightforward examples: if you write a min or max function with a FOR loop, you have written an O(n) implementation for something the query planner can sometimes do in O(1) using an index. If you write a sum function, you've written a sequential implementation of something the query planner may be able to parallelize.
By being declarative you tap into what's good about the database, into the reason you are using databases at all. You are doing it because databases have primitives for operating on data in bulk, searching, aggregating, updating.
When you doing things imperatively instead you are degrading performance to pretty much the same you could achieve with a piece of python or js operating on the data set pulled out of the database wholesale without using any fast database features.
Main trouble with imperative SQL code is that some people that are forced to do something in the database, don't really know why SQL exists and given the opportunity of writing imperative code they jump straight to it instead of solving the puzzle of understanding SQL declarative primitives and applying them to their problem. So the project ends up with slow, buggy imperative code in horrible syntax. When somebody needs to fix it later they first have to find the slow business logic ... database is not the first place one looks for it. Then understand ugly imperative code to figure out what it's doing, then actually solve the problem properly which takes time and thinking. Some queries I wrote in my jobs took me a day or two of thinking. Imperative versions would take 2 hours but would be orders of magnitude slower on medium sized data sets.
So is declarative something like “writing high level statements that postgres turns into more specific (imperative?) instructions”? And imperative being writing those specific “do this, then do this” yourself (although probably differently than how postgres does it under the hood).