9 ms·

We built our customer data warehouse all on Postgres

234 points by samaysharma 3y ago

fourfun 3y ago
I definitely agree that using a tool like Fivetran is generally needless complexity when FDWs would get the job done.
aurareturn 3y ago
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...
- 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.)
draven 3y ago
I'm always amazed (and a bit frightened) by the amount of logic that can be implemented directly in the database. How is the code debugged / managed / versioned / deployed ? I would be thankful for any pointer to books / blog posts about that.
- docapotamus 3y ago
  I personally put some logic in the database especially when I’m expressing constraints. If it’s there it means another engineer can’t go directly to the database to bypass these constraints. (By logic, I’m meaning for example a transaction can only transition between states if it’s in a required state). When it comes to debugging, versioning, deployment all these live alongside the code and are managed via migrations. Testing it is done as an integration test with the rest of the system. It helps that we don’t use an ORM and deal with SQL everywhere.
  - quectophoton 3y ago
    In my experience, chances are that the database will outlive whatever application code is layered right on top of it. So ensuring the database itself protects the data integrity and prevents the application code (current or a future refactor or rewrite) from messing it up, sounds to me like the sane thing to do. Be it with triggers, with functions, or whatever. Though I can understand that people usually don't like how PL/pgSQL looks like (I don't). But if you ignore the ugly language syntax, testing it is no more difficult than testing, say, an AWS Lambda function that is triggered by SQS and writes stuff to DynamoDB.
- Timshel 3y ago
  For the versionning part there is usually tools in your language. For ex: Flyway for java, diesel_migration for rust ...
  - sverhagen 3y ago
    I don't know... the places I've worked where there was that much of the application shifted into the database layer, there would be no Flyway. You'd be happy to get write permissions, let alone permissions to update the structure. Perhaps correlated, those organizations managed the versioning part through a heavy-handed change management process, ie. humans. Perhaps in some places it's cool now to do this, but for me having the entire application logic modeled in the database will always be associated with painful enterprise culture.
wokwokwok 3y ago
> We were able to build a data warehouse that is easy to maintain, and easy to reason about, and quick to onboard new engineers. A data warehouse? Ingesting raw data != a data warehouse. I presume their cron jobs are significantly more complicated than that, implementing their domain logic in functions, which will not scale well into an easy to maintain system over time as all database function implementations do… or, what they’ve built is something… …but that thing is not a data warehouse. It’s just a database.
- sverhagen 3y ago
  "Others might have a data warehouse, but we get by on Postgres" ;) (PS, I love Postgres.)
jwmoz 3y ago
By "customer data warehouse" I presume they mean "customer database"?
- reportgunner 3y ago
  I understood that it's more like "customer usage metrics"
efxhoy 3y ago
I love postgres too. We went from a brittle event sourcing architecture to postgres_fdw, works really well. We do something similar with a few differences: We swap entire schemas on every run (one schema being one upstream database or collection of analysis) atomically instead of deleting data and then inserting data. This means anyone querying the dw never sees an empty table, it's yesterdays data until instantly its todays data, consistently for the entire source. If something breaks the data is just stale, never missing. We don't do long running selects from foreign tables. This is because a long running select of a huge table can take many minutes. If an upstream database deploys a migration which changes the database schema that migration could time-out and stop the deploy while waiting for the select to finish so it can aquire its ACCESS EXCLUSIVE lock. This means we never hold even ACCESS SHARE locks for more than a couple of seconds. Basically the agreement is "the DW should never interrupt the operation of the upstream databases". We do this by a custom batched_create_table_from procedure. For integer id tables it's trivial. For uuid tables we fetch all the uuids in one go into a temp table with an integer id and then get chunks of uuid values at a time. This works well because we're always sending indexed id values to the foreign server so the foreign server can use its primary key indexes to only give us the data we want for that chunk, without scanning the entire table. It's a bit of a hack and I wish postgres_fdw's fetch_size parameter would release locks between fetches. Of course that would break atomicity so I'm doubting it's gonna happen. We run an external cron schedule which launches an ECS task that runs a bash script with our sql files that define the DW's schema. It's a really primitive DAG with bash & and wait. No scheduling happens in the db itself. This means we can schedule, check logs, monitor, etc like all our other ECS tasks. Some bash magic lets us define basically a DAG, though we run blocks of independent tasks (sql files) instead of building a proper dependency tree between individual tasks. I learned about ctid recently, that could have worked for the chunking. We're currently moving to bigquery and DBT for a few reasons. Primarily to be able to handle silly amounts of user interaction data where the experience for analysts is just much better in bigquery. DBT is also really nice, though I do miss the simplicity of just sql files. The development setup for the old DW relied on rebuilding it locally on a developer machine, which eventually ran into storage limitations and people getting lazy and not rebuilding from scratch before submitting their PRs, we just never got around to building proper CI for it. Rebuilding our postgres DW from scratch takes a few hours now, the same logic in bigquery runs in 10 minutes, which is undeniably amazing.
madjam002 3y ago
One thing that I think would be awesome is if there was a Postgres extension for streaming queries, where you could join tables with millions of rows and when any of the upstream data is changed you’ll be notified of the delta changes. I tried Materialize but it seemed incredibly wasteful to duplicate terabytes of data and was also extremely slow compared to the same query running in Postgres. For now I’ve rolled my own using Debezium CDC and simply reevaluating entire queries with joins when any data changes, but having something more smart would be ideal.
- tedheath123 3y ago
  RethinkDB attempts to do something like this, but I agree, an efficient SQL version of this would be nice.
- chuckhend 3y ago
  Check out what the folks at https://docs.epsio.io/ https://docs.epsio.io/ built. I'm not aware of any other projects doing this, but I think it's a great idea.
  - madjam002 3y ago
    This looks pretty cool, thanks for the pointer! It would be nice if there was a way to create a stream using this where the materialized data doesn't actually get stored on disk in a materialized table, instead it just streams changes to e.g kafka.
anymouse123456 3y ago
I was looking for a way to run migrations across SQLite and Postgres without requiring an unrelated language ecosystem (e.g., Node, Python, Ruby, Java, etc), and discovered Shmig. It's pretty great. https://github.com/mbucc/shmig https://github.com/mbucc/shmig Would love to see DB unit test infra with the the same values.
simonhaven 3y ago
I wish you well with FDW. I thought postgres_fdw seemed great and tried using it to construct cross-database queries. Unfortunately it performed like a complete dog for any kind of batch DML or mildly complex batch select statement, despite me trying every possible trick I could find online. Apparently it's a known drawback with postgres_fdw, supposedly fixed in later versions (which my organisation wasn't ready to upgrade to). Finding much pertinent information about that particular problem either in PG docs or online was a challenge in itself. It turned out to be far quicker, if less than ideal, to have a program with a connection to each database act as middleman because the queries it ran were locally optimised and could be tuned properly. All this may, of course, be a sign of a major gap in my Postgres-fu and I accept all derisory replies with appropriate humility.
debarshri 3y ago
Netezza's DWH appliance was built on top of Postgres [1]. Building datawarehouse on top of postgres is not something thats new. It is definitely lost art. [1] https://en.wikipedia.org/wiki/Netezza https://en.wikipedia.org/wiki/Netezza
osigurdson 3y ago
> This becomes a huge cost in the form of cognitive overhead for the team in addition to the time and resources it takes to set up, manage and maintain it The problem with using one system that you know well for everything is it becomes necessary to build (usually suboptimal) systems that emulate well known ones. In the end you trade one snowflake for another. That being said, there really ought to be standardized approaches to auth, backup and operations as various services today do things very differently and this does indeed increase cognitive load.
- Too 3y ago
  This. If it weren’t for auth and backups, “docker run prometheus” could be 100 times easier than coming up with your own schema and setting up foreign data wrappers in your already running postgres. Faster too, row vs column storage, etc. Prometheus integrates with every other tool in existence so there is very little reason to wrap it and loose those integrations. With FDW you don’t get off the hook of operating the backing data store anyway. You only get a consolidated query frontend. Kubernetes has solved a lot of the operational hassle of running multiple services. Standardized storage is still a nirvana, if you want to care about consistency, knowing if your database has a WAL or if you need to flush before snapshots, point in time, delayed replication and all that. Getting better, with many new databases connecting straight to S3 or equivalent. Standard auth was supposed to be solved but oauth2 is still a fragmented, difficult mess, often only part of the enterprise version.
infecto 3y ago
The website does a terrible job of communicating what they are. Had to click to the docs to kind of get a quick summary. In the context of their business I guess this writeup makes sense, it is afterall a marketing piece for their service. I don't agree with it though. I get the simplicity of running everything in a single db stack but I don't think any of the tools they listed were really going to be required to build something like this into another data warehouse.
- rywalker 3y ago
  thanks for feedback. website will get better soon, we're adding more marketing content around the product and pricing this sprint
  - infecto 3y ago
    I should have been more constructive. My issue was on the top fold of the entry site it did not immediately connect to me what the service solves. Why I need the service. Hope that helps!
tayloramurphy 3y ago
The first data warehouse at Gitlab was postgres! It worked quite well until we started pulling Snowplow event data. I share some of the concerns of other commenters of having postgres do everything. Having a separation of concern around orchestration in particular seems wise. That said, getting something up and running using just pg makes sense and it's always possible to refractor.
kdamica 3y ago
I would not recommend this approach to anyone, for these reasons: - If your data is small enough that Postgres works as an analytics warehouse, then the cost of running a warehouse like BigQuery are going to be small too, and without the complexity of managing the infra yourself. The company here specializes in Postgres, so it makes sense for them to do this, but for everyone else it makes much more sense to use a warehouse so you can focus on building your product and not worry about scaling your analytics infra. - Because Postgres doesn't scale for analytics, they only keep 30 days of metrics. I don't think this would be acceptable for most businesses. BigQuery costs 2 cents per GiB per month, so storage is generally a minimal expense until you get to pretty large scale. - Tools like Fivetran and Airbyte are much simpler for maintaining these pipelines than setting up FDWs, and each support a huge number of data sources. If you're worried about cost, you can self-host Airbyte or use Stitch (which is kind of a budget player in the space). It's true that Fivetran can get very expensive as you scale, but there is enough competition now that you have options to reduce cost. Disclaimer: I run a data consulting company that specializes in zero to one data projects for startups, including setting up analytics warehouses.
- lysecret 3y ago
  Very good response, can I ask you for a way to find your company? I am working in the same space.
- verticalscaler 3y ago
  Counterpoint: - The cost of running a warehouse like BigQuery is pulling in Google Cloud, or whatever 3rd parties like the ones you mentioned, and integrating with it somehow. How you gonna get the data in there and back out? What should the ACL be? Sure might be a trivial amount of data, heck, could be 1GB. Depending on the type of data (and company) there's potentially a lot more busywork involved here than just shuttling files around and trivial scripting. "Postgres doesn't scale for analytics" and "you should not spend money on an internal guy/team to run a database, you should blow it on Google and data consulting companies instead" is a bit of a blanket statement. Seems like teams with great DBAs will go with what they are familiar and teams that are cloud-all-the-things will go with what they are familiar. But the real choice here isn't even technical in the narrow sense, it is strategic. You wouldn't recommend anybody use Postgres, really? Man, so many teams are doing it wrong I guess.
hellcow 3y ago
Data warehouse use-cases in Postgres for me generally rely on calculating counts/stats over time, and estimates are sufficient. I really like HyperLogLog for this use-case—fast performance, low storage use, and there’s a great Postgres extension that’s widely supported.
c0balt 3y ago
Fyi, The pricing page[0] is unnecessarily hidden (4-5 clicks). Please make it part of the top menu or show it with the free trial screen. [0]: https://tembo.io/docs/tembo-cloud/billing https://tembo.io/docs/tembo-cloud/billing
ramesh31 3y ago
Sounds expensive.
effnorwood 3y ago
[dead]

234 points by samaysharma 3y ago

I definitely agree that using a tool like Fivetran is generally needless complexity when FDWs would get the job done.

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...
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.)

draven 3y ago

I'm always amazed (and a bit frightened) by the amount of logic that can be implemented directly in the database. How is the code debugged / managed / versioned / deployed ? I would be thankful for any pointer to books / blog posts about that.

docapotamus 3y ago
I personally put some logic in the database especially when I’m expressing constraints. If it’s there it means another engineer can’t go directly to the database to bypass these constraints. (By logic, I’m meaning for example a transaction can only transition between states if it’s in a required state). When it comes to debugging, versioning, deployment all these live alongside the code and are managed via migrations. Testing it is done as an integration test with the rest of the system. It helps that we don’t use an ORM and deal with SQL everywhere.
- quectophoton 3y ago
  In my experience, chances are that the database will outlive whatever application code is layered right on top of it. So ensuring the database itself protects the data integrity and prevents the application code (current or a future refactor or rewrite) from messing it up, sounds to me like the sane thing to do. Be it with triggers, with functions, or whatever. Though I can understand that people usually don't like how PL/pgSQL looks like (I don't). But if you ignore the ugly language syntax, testing it is no more difficult than testing, say, an AWS Lambda function that is triggered by SQS and writes stuff to DynamoDB.
Timshel 3y ago
For the versionning part there is usually tools in your language. For ex: Flyway for java, diesel_migration for rust ...
- sverhagen 3y ago
  I don't know... the places I've worked where there was that much of the application shifted into the database layer, there would be no Flyway. You'd be happy to get write permissions, let alone permissions to update the structure. Perhaps correlated, those organizations managed the versioning part through a heavy-handed change management process, ie. humans. Perhaps in some places it's cool now to do this, but for me having the entire application logic modeled in the database will always be associated with painful enterprise culture.

wokwokwok 3y ago

> We were able to build a data warehouse that is easy to maintain, and easy to reason about, and quick to onboard new engineers. A data warehouse? Ingesting raw data != a data warehouse. I presume their cron jobs are significantly more complicated than that, implementing their domain logic in functions, which will not scale well into an easy to maintain system over time as all database function implementations do… or, what they’ve built is something… …but that thing is not a data warehouse. It’s just a database.

sverhagen 3y ago
"Others might have a data warehouse, but we get by on Postgres" ;) (PS, I love Postgres.)

jwmoz 3y ago

By "customer data warehouse" I presume they mean "customer database"?

reportgunner 3y ago
I understood that it's more like "customer usage metrics"

efxhoy 3y ago

I love postgres too. We went from a brittle event sourcing architecture to postgres_fdw, works really well. We do something similar with a few differences: We swap entire schemas on every run (one schema being one upstream database or collection of analysis) atomically instead of deleting data and then inserting data. This means anyone querying the dw never sees an empty table, it's yesterdays data until instantly its todays data, consistently for the entire source. If something breaks the data is just stale, never missing. We don't do long running selects from foreign tables. This is because a long running select of a huge table can take many minutes. If an upstream database deploys a migration which changes the database schema that migration could time-out and stop the deploy while waiting for the select to finish so it can aquire its ACCESS EXCLUSIVE lock. This means we never hold even ACCESS SHARE locks for more than a couple of seconds. Basically the agreement is "the DW should never interrupt the operation of the upstream databases". We do this by a custom batched_create_table_from procedure. For integer id tables it's trivial. For uuid tables we fetch all the uuids in one go into a temp table with an integer id and then get chunks of uuid values at a time. This works well because we're always sending indexed id values to the foreign server so the foreign server can use its primary key indexes to only give us the data we want for that chunk, without scanning the entire table. It's a bit of a hack and I wish postgres_fdw's fetch_size parameter would release locks between fetches. Of course that would break atomicity so I'm doubting it's gonna happen. We run an external cron schedule which launches an ECS task that runs a bash script with our sql files that define the DW's schema. It's a really primitive DAG with bash & and wait. No scheduling happens in the db itself. This means we can schedule, check logs, monitor, etc like all our other ECS tasks. Some bash magic lets us define basically a DAG, though we run blocks of independent tasks (sql files) instead of building a proper dependency tree between individual tasks. I learned about ctid recently, that could have worked for the chunking. We're currently moving to bigquery and DBT for a few reasons. Primarily to be able to handle silly amounts of user interaction data where the experience for analysts is just much better in bigquery. DBT is also really nice, though I do miss the simplicity of just sql files. The development setup for the old DW relied on rebuilding it locally on a developer machine, which eventually ran into storage limitations and people getting lazy and not rebuilding from scratch before submitting their PRs, we just never got around to building proper CI for it. Rebuilding our postgres DW from scratch takes a few hours now, the same logic in bigquery runs in 10 minutes, which is undeniably amazing.

madjam002 3y ago

One thing that I think would be awesome is if there was a Postgres extension for streaming queries, where you could join tables with millions of rows and when any of the upstream data is changed you’ll be notified of the delta changes. I tried Materialize but it seemed incredibly wasteful to duplicate terabytes of data and was also extremely slow compared to the same query running in Postgres. For now I’ve rolled my own using Debezium CDC and simply reevaluating entire queries with joins when any data changes, but having something more smart would be ideal.

tedheath123 3y ago
RethinkDB attempts to do something like this, but I agree, an efficient SQL version of this would be nice.
chuckhend 3y ago
Check out what the folks at https://docs.epsio.io/ https://docs.epsio.io/ built. I'm not aware of any other projects doing this, but I think it's a great idea.
- madjam002 3y ago
  This looks pretty cool, thanks for the pointer! It would be nice if there was a way to create a stream using this where the materialized data doesn't actually get stored on disk in a materialized table, instead it just streams changes to e.g kafka.

anymouse123456 3y ago

I was looking for a way to run migrations across SQLite and Postgres without requiring an unrelated language ecosystem (e.g., Node, Python, Ruby, Java, etc), and discovered Shmig. It's pretty great. https://github.com/mbucc/shmig https://github.com/mbucc/shmig Would love to see DB unit test infra with the the same values.

simonhaven 3y ago

I wish you well with FDW. I thought postgres_fdw seemed great and tried using it to construct cross-database queries. Unfortunately it performed like a complete dog for any kind of batch DML or mildly complex batch select statement, despite me trying every possible trick I could find online. Apparently it's a known drawback with postgres_fdw, supposedly fixed in later versions (which my organisation wasn't ready to upgrade to). Finding much pertinent information about that particular problem either in PG docs or online was a challenge in itself. It turned out to be far quicker, if less than ideal, to have a program with a connection to each database act as middleman because the queries it ran were locally optimised and could be tuned properly. All this may, of course, be a sign of a major gap in my Postgres-fu and I accept all derisory replies with appropriate humility.

debarshri 3y ago

Netezza's DWH appliance was built on top of Postgres [1]. Building datawarehouse on top of postgres is not something thats new. It is definitely lost art. [1] https://en.wikipedia.org/wiki/Netezza https://en.wikipedia.org/wiki/Netezza

osigurdson 3y ago

> This becomes a huge cost in the form of cognitive overhead for the team in addition to the time and resources it takes to set up, manage and maintain it The problem with using one system that you know well for everything is it becomes necessary to build (usually suboptimal) systems that emulate well known ones. In the end you trade one snowflake for another. That being said, there really ought to be standardized approaches to auth, backup and operations as various services today do things very differently and this does indeed increase cognitive load.

Too 3y ago
This. If it weren’t for auth and backups, “docker run prometheus” could be 100 times easier than coming up with your own schema and setting up foreign data wrappers in your already running postgres. Faster too, row vs column storage, etc. Prometheus integrates with every other tool in existence so there is very little reason to wrap it and loose those integrations. With FDW you don’t get off the hook of operating the backing data store anyway. You only get a consolidated query frontend. Kubernetes has solved a lot of the operational hassle of running multiple services. Standardized storage is still a nirvana, if you want to care about consistency, knowing if your database has a WAL or if you need to flush before snapshots, point in time, delayed replication and all that. Getting better, with many new databases connecting straight to S3 or equivalent. Standard auth was supposed to be solved but oauth2 is still a fragmented, difficult mess, often only part of the enterprise version.

infecto 3y ago

The website does a terrible job of communicating what they are. Had to click to the docs to kind of get a quick summary. In the context of their business I guess this writeup makes sense, it is afterall a marketing piece for their service. I don't agree with it though. I get the simplicity of running everything in a single db stack but I don't think any of the tools they listed were really going to be required to build something like this into another data warehouse.

rywalker 3y ago
thanks for feedback. website will get better soon, we're adding more marketing content around the product and pricing this sprint
- infecto 3y ago
  I should have been more constructive. My issue was on the top fold of the entry site it did not immediately connect to me what the service solves. Why I need the service. Hope that helps!

tayloramurphy 3y ago

The first data warehouse at Gitlab was postgres! It worked quite well until we started pulling Snowplow event data. I share some of the concerns of other commenters of having postgres do everything. Having a separation of concern around orchestration in particular seems wise. That said, getting something up and running using just pg makes sense and it's always possible to refractor.

kdamica 3y ago

I would not recommend this approach to anyone, for these reasons: - If your data is small enough that Postgres works as an analytics warehouse, then the cost of running a warehouse like BigQuery are going to be small too, and without the complexity of managing the infra yourself. The company here specializes in Postgres, so it makes sense for them to do this, but for everyone else it makes much more sense to use a warehouse so you can focus on building your product and not worry about scaling your analytics infra. - Because Postgres doesn't scale for analytics, they only keep 30 days of metrics. I don't think this would be acceptable for most businesses. BigQuery costs 2 cents per GiB per month, so storage is generally a minimal expense until you get to pretty large scale. - Tools like Fivetran and Airbyte are much simpler for maintaining these pipelines than setting up FDWs, and each support a huge number of data sources. If you're worried about cost, you can self-host Airbyte or use Stitch (which is kind of a budget player in the space). It's true that Fivetran can get very expensive as you scale, but there is enough competition now that you have options to reduce cost. Disclaimer: I run a data consulting company that specializes in zero to one data projects for startups, including setting up analytics warehouses.

lysecret 3y ago
Very good response, can I ask you for a way to find your company? I am working in the same space.
verticalscaler 3y ago
Counterpoint: - The cost of running a warehouse like BigQuery is pulling in Google Cloud, or whatever 3rd parties like the ones you mentioned, and integrating with it somehow. How you gonna get the data in there and back out? What should the ACL be? Sure might be a trivial amount of data, heck, could be 1GB. Depending on the type of data (and company) there's potentially a lot more busywork involved here than just shuttling files around and trivial scripting. "Postgres doesn't scale for analytics" and "you should not spend money on an internal guy/team to run a database, you should blow it on Google and data consulting companies instead" is a bit of a blanket statement. Seems like teams with great DBAs will go with what they are familiar and teams that are cloud-all-the-things will go with what they are familiar. But the real choice here isn't even technical in the narrow sense, it is strategic. You wouldn't recommend anybody use Postgres, really? Man, so many teams are doing it wrong I guess.

hellcow 3y ago

Data warehouse use-cases in Postgres for me generally rely on calculating counts/stats over time, and estimates are sufficient. I really like HyperLogLog for this use-case—fast performance, low storage use, and there’s a great Postgres extension that’s widely supported.

c0balt 3y ago

Fyi, The pricing page[0] is unnecessarily hidden (4-5 clicks). Please make it part of the top menu or show it with the free trial screen. [0]: https://tembo.io/docs/tembo-cloud/billing https://tembo.io/docs/tembo-cloud/billing

ramesh31 3y ago

Sounds expensive.

effnorwood 3y ago

[dead]