7 ms·

Postgres is great but using pg_cron as your system to pull and clean data is a recipe for disaster in the long run. I've always regretted using complicated Post

Postgres is great but using pg_cron as your system to pull and clean data is a recipe for disaster in the long run. I've always regretted using complicated Postgres queries or functions instead of doing it in server. Sometimes I do it because I think "wow, I didn't know pg can do this. So cool, let's just use it!". Regret. Every time. They're too finicky, hard to test, and doesn't have the ecosystem like git and IDEs to make changes easily. And the next employee won't know about some random but crucial logic that lives inside Postgres instead of the code. You're also relying on your Postgres instance's precious CPU/RAM/network to do logic work which can be unpredictable. Let's be honest, most managed cloud Postgres providers charge an arm and a leg for big Postgres instances already. Why use those expensive resources when you can get a cheap EC2 instance or a serverless function to do the same and have the ability to scale efficiently? My general rule is, use Postgres to store as much of my data as possible, but never use Postgres to do anything other than be a data store. This rule of mine is why I never fell in love with Supabase even though many developers have. Use Postgres as my permissions layer? As my API server? You have to be kidding. If you're building a quick throwaway prototype, maybe. (The rest of Supabase is really nice though). PS. To the team at tembo, no one is going to sign up for a free trial without a pricing page.

namaria 3y ago
Well put. It's never not funny to me how one of the fundamental tenets of software engineering is 'separation of concerns' yet there's no shortage of people willing to say 'but it's so convenient to mix them at the start of a project...'.
samaysharma 3y ago
Thanks for the feedback. We're actively working on implementing the pricing page for our website. It should be out in the next few days.
codeflo 3y ago
> no unit testing I also prefer to do stuff on the server because doing things in the database does invariably seem to drift towards a confusing, brittle mess. However, I do wonder if that's necessarily the case or just a limitation of our frameworks. There's no principle that would prevent someone from setting up a simple test suite for database code. It would be a lot of work, but maybe there's an architecture nirvana right in front of our eyes if someone just were to properly engineer a good framework around Postgres functions.
- aurareturn 3y ago
  There's no IDE that can detect Postgres coding errors, no refactoring support, no Git integration. A lot of the ecosystem has to be built out for me to want to use Postgres functions. The benefit is not there. Edit: I didn't mean writing simple SQL queries. I meant writing your business/app logic in Postgres functions.
  - maxbond 3y ago
    This is a big problem, but I am encouraged by this project. https://github.com/supabase/postgres_lsp https://github.com/supabase/postgres_lsp
    - nextaccountic 3y ago
      Does it support multiple queries per sql file? My use case is to use this to autocomplete queries made with Cornucopia [0] Cornucopia queries look like this (here is a something.sql file) --! authors SELECT first_name, last_name, country FROM Authors; --! insert_author INSERT INTO Authors(first_name, last_name, country) VALUES (:first_name, :last_name, :country); There are multiple queries each separated by ; and on top of each query, there's a comment giving a name to the query (it's more like a header) I think the only thing that might require specific support in postgres_lsp is using the :parameter_name syntax for prepared statements [1] (in vanilla Postgres would be something like $1 or $2, but in Cornucopia it is named to aid readability). But, if postgres_lsp is forgiging enough to not choke on that, then it seems completely fit for this use case. [0] https://github.com/cornucopia-rs/cornucopia https://github.com/cornucopia-rs/cornucopia [1] https://cornucopia-rs.netlify.app/book/writing_queries/writing_queries#named-parameters https://cornucopia-rs.netlify.app/book/writing_queries/writi...
      - maxbond 3y ago
        Thank you for turning me on top Cornucopia, it looks awesome. I've used the very similar aiosql in Python, but I hadn't realized there was a Rust analog. To tell the truth I've been waiting for postgres_lsp to mature before trying it out, but based on this example [1] I think it does support multiple queries. Since it uses a parser extracted from Postgres, the nonstandard syntax would probably trip it up, but there's probably a way to fix that. [1] https://github.com/supabase/postgres_lsp/blob/main/example/file.sql https://github.com/supabase/postgres_lsp/blob/main/example/f...
ilikerashers 3y ago
Oracle also lets you run Java from stored procedures which I’m sure lets you tie yourself in knots too. There could be slim use cases for doing everything in the db but there’s a reason why it’s not widespread.
baq 3y ago
Yeah except for serious data warehouses there's so much data you have to bring the compute in because getting data out is infeasible. If that's not the case, you aren't running a data warehouse, just a (big, maybe) database.
- chuckhend 3y ago
  There will be another blog from us at some point about running the data warehouse at scale. We're already working on integrating with s3 storage, and distributed compute is in the roadmap. Both possible today with open source extensions, and our friends in industry are already doing it.
KptMarchewa 3y ago
I generally agree. I think one case that it weren't true was LISTEN/NOTIFY https://www.postgresql.org/docs/current/sql-notify.html https://www.postgresql.org/docs/current/sql-notify.html - the API is actually simple and well tested.
- fuy 3y ago
  and doesn't scale well. Meaning using it can take your Postgres server down way before regular SQL workload would.
- justinclift 3y ago
  Note that LISTEN/NOTIFY isn't compatible with 2 phase commit though, which can potentially rule out its use in some situations.
RGamma 3y ago
Is there any good data movement/transformation software for on-prem? Specifically integration of other databases and Excel into MSSQL (<100GB of data). I have looked at SSDT, which looks really clunky... Was going to use a similar approach to OP, especially because it's cheap and ressource-efficient, but lack of support infrastructure might come back to bite me.
- mmcgaha 3y ago
  I like Pentaho Data Integration. The community edition does everything I need but there is also a paid version.
- __mharrison__ 3y ago
  Pandas? Polars?
  - wredue 3y ago
    SAS. Just kidding. Fuck SAS. I’ve use some absolutely dogshit tools in my day, and of all those, SAS is downright the most time wasting, incredibly inconsistent piles of shit ever. Never had a worse experience. Useless. Run far away.
- tomnipotent 3y ago
  Checkout Airbyte and singer.io for data movement. They're tailored for moving offsite data local, but also have support for moving data between different database vendors. I'd recommend starting with Airbyte. For transformations I'm a sucker for dbt-core, which executes transformations as SQL against your data warehouse vs. a separate process. This keeps everything simple and (generally) snappy. It uses the Python Jinja2 template library to compose SQL templates, and the process that parses templates to SQL also implicitly creates an execution DAG so you could have hundreds of transformations running in the exact order they need to. There are also many commercial offerings (Fivetran, Dataform) that don't tick self-hosted, but could be easier to deploy if getting internal hardware support is an issue. https://github.com/airbytehq/airbyte https://github.com/airbytehq/airbyte https://airbyte.com/connectors https://airbyte.com/connectors https://www.singer.io/#taps https://www.singer.io/#taps https://github.com/dbt-labs/dbt-core https://github.com/dbt-labs/dbt-core
mooreds 3y ago
> PS. To the team at tembo, no one is going to sign up for a free trial without a pricing page. This, times 1000. I know setting a price is scary, but it is one of the critical pieces of information for anyone stumbling on your product. If you are looking for a way to take advantage of traffic but aren't ready for pricing, ask for my email address for your newsletter. That's easier for me to give than my time to "kick the tires" with a free trial.
- ninkendo 3y ago
  > ask for my email address for your newsletter So you’re the one who actually wants that popup? I guess the world really is coming to an end, I couldn’t imagine a single person who actually thinks to themselves “I wish a popup would happen asking for my email address right now.”
  - mooreds 3y ago
    Not a popup, please! Just a from please. I want a way to keep on top of tembo that doesn't involve investing my time. Not saying I'd read every newsletter, though.
    - kevincox 3y ago
      This 100%. A polite signup form at the bottom of a page (or an RSS feed) is totally fine. At this point I understand what you are about and will consider subscribing to your news. But those popups that show up after I have read a few paragraphs of into can go to hell. I just leave the page when I see this at this point.
    - mooreds 3y ago
      *"form" not "from"
- throwaway98797 3y ago
  everyone does it must work some of the time
culiao 3y ago
What exactly is tembo? and how does it compare to a supabase? You have me curious now.
- rywalker 3y ago
  we just launched earlier this month - manifesto: https://tembo.io/blog/manifesto https://tembo.io/blog/manifesto
hyeomans 3y ago
Agreed, I learned the hard way that complicated constraints, those that are really good to keep data integrity, are really hard to test and might have unintended consequences.
- ttfkam 3y ago
  Hard to test?
brianmcc 3y ago
+1 for data in the database, code as... well, code. And also learned this the hard way. Code you can build and run test suites against, extract logs from, place debug breakpoints in, is such a quality of like enhancer. edit: typo fix
- rywalker 3y ago
  We're working on a gitops DX for Tembo Cloud, that will allow you to ensure everything you build on Tembo is in source control, and runnable locally w/ our CLI. More info coming soon...
ggregoire 3y ago
> Use Postgres as my permissions layer? It's actually so good I regret not having done it sooner. We migrated all our APIs into PostgREST [1] and it reduced our development/testing/deployment time by an insane amount, decreased the number of bugs automatically by removing 1 extra layer and made all our requests faster without having to optimize anything. I'm genuinely surprised it's not more popular. It would probably benefit like 99% of the small/medium size companies. Writing SQL is kinda fun too. [1] https://postgrest.org https://postgrest.org
- rywalker 3y ago
  We've integrated postgrest into Tembo Cloud already too, one click to deploy it as a sidecar.
rywalker 3y ago
Tembo CEO here I would have agreed w/ you 100% 18 months ago. And presently is is messy do to this in Postgres, but it's also messy to choose lots of tools that create sprawl and data islands. We're spending a significant part of our R&D effort in 2024 towards making it cleaner/safer to do more in Postgres. We have a lot of work to do to build a great gitops developer experience. We're also prioritizing auto-scale, auto-suspend, and auto-tune projects to help reduce the cost of doing "lumpy" things in Postgres. Would love to have you as a design partner @aurareturn — want to eventually win you over :) hit me up if you're interested in chatting ry [at] tembo [dot] io
radiator 3y ago
> And the next employee won't know about some random but crucial logic that lives inside Postgres instead of the code. The next employee should know this, because it will have been documented somewhere.
- ttfkam 3y ago
  I love Postgres and relational databases in general, but the lack of documentation generation found in general purpose programming languages for the last 20 years is a glaring omission. There simply are no good equivalent to Javadoc for SQL databases. (Except you Microsoft. I see you and MS SQL Server tools. I just don't work on Windows.) You have ERD tools that happily make a 500-table diagram that's utterly useless to humans, are ugly as sin, are not at all interactive, etc. Seriously, Javadoc (and Doxygen, etc.) have been on point since the 1990s. OOP and functional design patterns are common knowledge while 50-year-old relational concepts are still woefully unknown to far too many data architects. Folks don't write docs. They just don't. They start. They agree it's important. But they either get skipped or they stagnate. The only real option is automatically generating. Reading the column and table comments, the foreign key relationships, the indexes, the unique and primary keys, the constraints, etc. and rendering it all into a cohesive and consistent user interface. Not the data. The structure. It's the single biggest tooling failure in that sector in my opinion.
mannyv 3y ago
This is the definition of vendor lock in; the more you use embedded non-standard functionality of a product the less likely it is that you'll ditch it. As long as you're aware of what you're doing it's fine. And you're documenting your usage, right?
- ttfkam 3y ago
  If you don't write your database layer with vendor-specific features and optimizations in mind, you're definitely leaving tons of performance on the floor. Multi-DB support for a largely identical schema is a fool's errand. It's not Postgres's fault that MySQL is comparatively bereft of modern features. • transactional DDL • RETURNING clause • unfettered use of user-defined functions • a native UUID type • the ability to define custom data types and domains • DDL triggers • row-level security • actual arrays and booleans • MERGE • range types and constraints • IP address and CIDR types • partial indexes • LATERAL JOIN MS SQL has temporal table support, the ability to integrate any .Net functionality directly into the database, pivot tables, etc. It's really hard to explain to lifelong MySQL users what they've been missing in the name of database agnosticism. Using the lowest common denominator to all the engines feels like wearing a straitjacket attached to lead weights. https://www.sql-workbench.eu/dbms_comparison.html https://www.sql-workbench.eu/dbms_comparison.html
- justinclift 3y ago
  > vendor lock in Not sure the word "vendor" is exactly correct when using standard PostgreSQL functionality though, as PostgreSQL has multiple vendors providing that functionality. So, not really "vendor lock in". The term is clearly correct for databases with only one vendor though (Oracle, etc).
pmart123 3y ago
I hear your points, but can't git, CI tools, IDEs like DataGrip all be used to test and deploy the database code too versus letting some db admin create functions or procedures with no version control? Also, with things like RDS or readonly replicas, couldn't more analytical queries be done directly in the database versus busing data around?
ttfkam 3y ago
A data store *that enforces valid data at all times*. Far too many people miss that part. They skimp on foreign keys, check constraints, and the most appropriate data types. Time is far better spent getting the schema right at the outset than trying to fix bad data already mixed in haphazardly with the good. And then there's audit trails, which are best made centralized in the database with triggers. "This change was made by X at this time." Row-level access policies are yet another. Doesn't matter which app or user is querying, what joins are mixed in, etc., the DB simply omits data the user shouldn't see. Period. No "oh, my script forgot to include part of the WHERE clause for that". Totally agree about pg_cron (and MySQL scheduled events while we're on the topic). It artificially limits scheduled actions to just those that affect the DB, cannot adjust to current DB load, and are hard to find. Better to run cron jobs all accumulated in one place from the cloud provider a la EventBridge or from a dedicated schedule server. (I'm not a fan of app cluster leader elections either for complexity and opacity reasons as well. Put the schedules in one place to be audited and enumerated as needed.)

namaria 3y ago

Well put. It's never not funny to me how one of the fundamental tenets of software engineering is 'separation of concerns' yet there's no shortage of people willing to say 'but it's so convenient to mix them at the start of a project...'.

samaysharma 3y ago

Thanks for the feedback. We're actively working on implementing the pricing page for our website. It should be out in the next few days.

codeflo 3y ago

> no unit testing I also prefer to do stuff on the server because doing things in the database does invariably seem to drift towards a confusing, brittle mess. However, I do wonder if that's necessarily the case or just a limitation of our frameworks. There's no principle that would prevent someone from setting up a simple test suite for database code. It would be a lot of work, but maybe there's an architecture nirvana right in front of our eyes if someone just were to properly engineer a good framework around Postgres functions.

aurareturn 3y ago
There's no IDE that can detect Postgres coding errors, no refactoring support, no Git integration. A lot of the ecosystem has to be built out for me to want to use Postgres functions. The benefit is not there. Edit: I didn't mean writing simple SQL queries. I meant writing your business/app logic in Postgres functions.
- maxbond 3y ago
  This is a big problem, but I am encouraged by this project. https://github.com/supabase/postgres_lsp https://github.com/supabase/postgres_lsp
  - nextaccountic 3y ago
    Does it support multiple queries per sql file? My use case is to use this to autocomplete queries made with Cornucopia [0] Cornucopia queries look like this (here is a something.sql file) --! authors SELECT first_name, last_name, country FROM Authors; --! insert_author INSERT INTO Authors(first_name, last_name, country) VALUES (:first_name, :last_name, :country); There are multiple queries each separated by ; and on top of each query, there's a comment giving a name to the query (it's more like a header) I think the only thing that might require specific support in postgres_lsp is using the :parameter_name syntax for prepared statements [1] (in vanilla Postgres would be something like $1 or $2, but in Cornucopia it is named to aid readability). But, if postgres_lsp is forgiging enough to not choke on that, then it seems completely fit for this use case. [0] https://github.com/cornucopia-rs/cornucopia https://github.com/cornucopia-rs/cornucopia [1] https://cornucopia-rs.netlify.app/book/writing_queries/writing_queries#named-parameters https://cornucopia-rs.netlify.app/book/writing_queries/writi...
    - maxbond 3y ago
      Thank you for turning me on top Cornucopia, it looks awesome. I've used the very similar aiosql in Python, but I hadn't realized there was a Rust analog. To tell the truth I've been waiting for postgres_lsp to mature before trying it out, but based on this example [1] I think it does support multiple queries. Since it uses a parser extracted from Postgres, the nonstandard syntax would probably trip it up, but there's probably a way to fix that. [1] https://github.com/supabase/postgres_lsp/blob/main/example/file.sql https://github.com/supabase/postgres_lsp/blob/main/example/f...

ilikerashers 3y ago

Oracle also lets you run Java from stored procedures which I’m sure lets you tie yourself in knots too. There could be slim use cases for doing everything in the db but there’s a reason why it’s not widespread.

baq 3y ago

Yeah except for serious data warehouses there's so much data you have to bring the compute in because getting data out is infeasible. If that's not the case, you aren't running a data warehouse, just a (big, maybe) database.

chuckhend 3y ago
There will be another blog from us at some point about running the data warehouse at scale. We're already working on integrating with s3 storage, and distributed compute is in the roadmap. Both possible today with open source extensions, and our friends in industry are already doing it.

KptMarchewa 3y ago

I generally agree. I think one case that it weren't true was LISTEN/NOTIFY https://www.postgresql.org/docs/current/sql-notify.html https://www.postgresql.org/docs/current/sql-notify.html - the API is actually simple and well tested.

fuy 3y ago
and doesn't scale well. Meaning using it can take your Postgres server down way before regular SQL workload would.
justinclift 3y ago
Note that LISTEN/NOTIFY isn't compatible with 2 phase commit though, which can potentially rule out its use in some situations.

RGamma 3y ago

Is there any good data movement/transformation software for on-prem? Specifically integration of other databases and Excel into MSSQL (<100GB of data). I have looked at SSDT, which looks really clunky... Was going to use a similar approach to OP, especially because it's cheap and ressource-efficient, but lack of support infrastructure might come back to bite me.

mmcgaha 3y ago
I like Pentaho Data Integration. The community edition does everything I need but there is also a paid version.
__mharrison__ 3y ago
Pandas? Polars?
- wredue 3y ago
  SAS. Just kidding. Fuck SAS. I’ve use some absolutely dogshit tools in my day, and of all those, SAS is downright the most time wasting, incredibly inconsistent piles of shit ever. Never had a worse experience. Useless. Run far away.
tomnipotent 3y ago
Checkout Airbyte and singer.io for data movement. They're tailored for moving offsite data local, but also have support for moving data between different database vendors. I'd recommend starting with Airbyte. For transformations I'm a sucker for dbt-core, which executes transformations as SQL against your data warehouse vs. a separate process. This keeps everything simple and (generally) snappy. It uses the Python Jinja2 template library to compose SQL templates, and the process that parses templates to SQL also implicitly creates an execution DAG so you could have hundreds of transformations running in the exact order they need to. There are also many commercial offerings (Fivetran, Dataform) that don't tick self-hosted, but could be easier to deploy if getting internal hardware support is an issue. https://github.com/airbytehq/airbyte https://github.com/airbytehq/airbyte https://airbyte.com/connectors https://airbyte.com/connectors https://www.singer.io/#taps https://www.singer.io/#taps https://github.com/dbt-labs/dbt-core https://github.com/dbt-labs/dbt-core

mooreds 3y ago

> PS. To the team at tembo, no one is going to sign up for a free trial without a pricing page. This, times 1000. I know setting a price is scary, but it is one of the critical pieces of information for anyone stumbling on your product. If you are looking for a way to take advantage of traffic but aren't ready for pricing, ask for my email address for your newsletter. That's easier for me to give than my time to "kick the tires" with a free trial.

ninkendo 3y ago
> ask for my email address for your newsletter So you’re the one who actually wants that popup? I guess the world really is coming to an end, I couldn’t imagine a single person who actually thinks to themselves “I wish a popup would happen asking for my email address right now.”
- mooreds 3y ago
  Not a popup, please! Just a from please. I want a way to keep on top of tembo that doesn't involve investing my time. Not saying I'd read every newsletter, though.
  - kevincox 3y ago
    This 100%. A polite signup form at the bottom of a page (or an RSS feed) is totally fine. At this point I understand what you are about and will consider subscribing to your news. But those popups that show up after I have read a few paragraphs of into can go to hell. I just leave the page when I see this at this point.
  - mooreds 3y ago
    *"form" not "from"
throwaway98797 3y ago
everyone does it must work some of the time

culiao 3y ago

What exactly is tembo? and how does it compare to a supabase? You have me curious now.

rywalker 3y ago
we just launched earlier this month - manifesto: https://tembo.io/blog/manifesto https://tembo.io/blog/manifesto

hyeomans 3y ago

Agreed, I learned the hard way that complicated constraints, those that are really good to keep data integrity, are really hard to test and might have unintended consequences.

ttfkam 3y ago
Hard to test?

brianmcc 3y ago

+1 for data in the database, code as... well, code. And also learned this the hard way. Code you can build and run test suites against, extract logs from, place debug breakpoints in, is such a quality of like enhancer. edit: typo fix

rywalker 3y ago
We're working on a gitops DX for Tembo Cloud, that will allow you to ensure everything you build on Tembo is in source control, and runnable locally w/ our CLI. More info coming soon...

ggregoire 3y ago

> Use Postgres as my permissions layer? It's actually so good I regret not having done it sooner. We migrated all our APIs into PostgREST [1] and it reduced our development/testing/deployment time by an insane amount, decreased the number of bugs automatically by removing 1 extra layer and made all our requests faster without having to optimize anything. I'm genuinely surprised it's not more popular. It would probably benefit like 99% of the small/medium size companies. Writing SQL is kinda fun too. [1] https://postgrest.org https://postgrest.org

rywalker 3y ago
We've integrated postgrest into Tembo Cloud already too, one click to deploy it as a sidecar.

rywalker 3y ago

Tembo CEO here I would have agreed w/ you 100% 18 months ago. And presently is is messy do to this in Postgres, but it's also messy to choose lots of tools that create sprawl and data islands. We're spending a significant part of our R&D effort in 2024 towards making it cleaner/safer to do more in Postgres. We have a lot of work to do to build a great gitops developer experience. We're also prioritizing auto-scale, auto-suspend, and auto-tune projects to help reduce the cost of doing "lumpy" things in Postgres. Would love to have you as a design partner @aurareturn — want to eventually win you over :) hit me up if you're interested in chatting ry [at] tembo [dot] io

radiator 3y ago

> And the next employee won't know about some random but crucial logic that lives inside Postgres instead of the code. The next employee should know this, because it will have been documented somewhere.

ttfkam 3y ago
I love Postgres and relational databases in general, but the lack of documentation generation found in general purpose programming languages for the last 20 years is a glaring omission. There simply are no good equivalent to Javadoc for SQL databases. (Except you Microsoft. I see you and MS SQL Server tools. I just don't work on Windows.) You have ERD tools that happily make a 500-table diagram that's utterly useless to humans, are ugly as sin, are not at all interactive, etc. Seriously, Javadoc (and Doxygen, etc.) have been on point since the 1990s. OOP and functional design patterns are common knowledge while 50-year-old relational concepts are still woefully unknown to far too many data architects. Folks don't write docs. They just don't. They start. They agree it's important. But they either get skipped or they stagnate. The only real option is automatically generating. Reading the column and table comments, the foreign key relationships, the indexes, the unique and primary keys, the constraints, etc. and rendering it all into a cohesive and consistent user interface. Not the data. The structure. It's the single biggest tooling failure in that sector in my opinion.

mannyv 3y ago

This is the definition of vendor lock in; the more you use embedded non-standard functionality of a product the less likely it is that you'll ditch it. As long as you're aware of what you're doing it's fine. And you're documenting your usage, right?

ttfkam 3y ago
If you don't write your database layer with vendor-specific features and optimizations in mind, you're definitely leaving tons of performance on the floor. Multi-DB support for a largely identical schema is a fool's errand. It's not Postgres's fault that MySQL is comparatively bereft of modern features. • transactional DDL • RETURNING clause • unfettered use of user-defined functions • a native UUID type • the ability to define custom data types and domains • DDL triggers • row-level security • actual arrays and booleans • MERGE • range types and constraints • IP address and CIDR types • partial indexes • LATERAL JOIN MS SQL has temporal table support, the ability to integrate any .Net functionality directly into the database, pivot tables, etc. It's really hard to explain to lifelong MySQL users what they've been missing in the name of database agnosticism. Using the lowest common denominator to all the engines feels like wearing a straitjacket attached to lead weights. https://www.sql-workbench.eu/dbms_comparison.html https://www.sql-workbench.eu/dbms_comparison.html
justinclift 3y ago
> vendor lock in Not sure the word "vendor" is exactly correct when using standard PostgreSQL functionality though, as PostgreSQL has multiple vendors providing that functionality. So, not really "vendor lock in". The term is clearly correct for databases with only one vendor though (Oracle, etc).

pmart123 3y ago

I hear your points, but can't git, CI tools, IDEs like DataGrip all be used to test and deploy the database code too versus letting some db admin create functions or procedures with no version control? Also, with things like RDS or readonly replicas, couldn't more analytical queries be done directly in the database versus busing data around?

ttfkam 3y ago

A data store *that enforces valid data at all times*. Far too many people miss that part. They skimp on foreign keys, check constraints, and the most appropriate data types. Time is far better spent getting the schema right at the outset than trying to fix bad data already mixed in haphazardly with the good. And then there's audit trails, which are best made centralized in the database with triggers. "This change was made by X at this time." Row-level access policies are yet another. Doesn't matter which app or user is querying, what joins are mixed in, etc., the DB simply omits data the user shouldn't see. Period. No "oh, my script forgot to include part of the WHERE clause for that". Totally agree about pg_cron (and MySQL scheduled events while we're on the topic). It artificially limits scheduled actions to just those that affect the DB, cannot adjust to current DB load, and are hard to find. Better to run cron jobs all accumulated in one place from the cloud provider a la EventBridge or from a dedicated schedule server. (I'm not a fan of app cluster leader elections either for complexity and opacity reasons as well. Put the schedules in one place to be audited and enumerated as needed.)