13 ms·

Using PostgreSQL as a Data Warehouse

435 points by cedricd 5y ago

cedricd 5y ago
We support multiple data warehouses on our platform. We recently had to do a bit of work to get Postgres running, so we wrote a high-level post about things to consider when running analytical workloads on PG instead of normal production workloads.
- mleonhard 5y ago
  Thank you!
mattashii 5y ago
> Avoid Common Table Expressions (or upgrade to 12) Upgrading to 12 (or 13) seems like the better option here, whenever you're able to do so. The improvements are very much worth it.
- cedricd 5y ago
  Yes, that's a really great point. I should emphasize that more clearly in the blog :).
  - mattashii 5y ago
    A few more items I've encountered: - Citus data, not Citrus data. - In tables, column order matters. Order const-width non-null columns before all other columns (best so that there's no unnecessary alignment padding). Then maybe some low null fraction fixed-width columns, then ordered by query access frequency. A lot of time can be spent extracting tuple values, and you can save a lot of time using the offset caches by correctly tetris-ing your columns. Note that dropped columns are set to null, so a table rewrite (by redefining your table) may be in order for 100% performance.
    - galkk 5y ago
      Isn't #2 something that the database engine should handle by itself: const-width, non-null is written in DDL and the engine should be be able to handle it.
      - mattashii 5y ago
        yes, ish. But Postgresql has no reordering of table columns builtin, mostly due to the limitations of the internal representation of its schema (also called features) and in how the DLL is performed. Adding or removing a column in this system does not require postgres to rewrite the whole table, which means that old data can stay in the table effectively forever, as long as there are no other rewrite-required DDLs performed. Additionally, columns can be updated to SET NOT NULL / DROP NOT NULL, further complicating the whole system you're trying to optimize. Eventually postgresql might support some form of table column reordering that permanently optimizes out deleted columns, but I think it's unlikely to happen anytime soon. There are lower hanging fruits on the tree; altering existing table definitions in a backwards-incompatible manner is a lot of effort for likely very little gain. As for column packing: Maybe this can be implemented for CREATE TABLE with an option, but in the current transactional DDL framework this cannot be implemented for ADD COLUMN, because we can't reorder columns.
- wwweston 5y ago
  What situations would make people unable to upgrade to 12+?
  - cedricd 5y ago
    Maybe it doesn't get prioritized until it's important. I know PG upgrades are pretty straightforward, but sometimes people don't want to touch something running well. That said, given the performance implications, if someone wants to use PG as a warehouse upgrading to 12 is a no-brainer.
  - goatinaboat 5y ago
    What situations would make people unable to upgrade to 12+? In my personal experience: the organisation doesn't invest in a storage solution that offers snapshots/COW (e.g. ZFS or SAN or whatever). Then they wait to upgrade until their disks reach a usage that the upgrade has to be done in-place. Then they become like rabbits in the headlights and never upgrade.
  - aargh_aargh 5y ago
    One case I decided against it was when I needed to access the new postgres server (which I initially planned to be v13) from an old machine (with a legacy distro with v9.6). v13 introduces a new SCRAM-SHA-256 password hashing method by default and only libpq10 and newer supports this method. For some reason I couldn't or didn't want to neither rehash the passwords nor upgrade the client, so I remained on a lower version. Certainly not unsolvable, but I didn't have the time to spend on a proper fix.
- truculent 5y ago
  Yes, it is strange that "Avoid CTEs" made it into the TL;DR instead.
  - cedricd 5y ago
    I updated the blog post :)
- arcticfox 5y ago
  Something painful I just learned today is that even Postgres >= 12 isn't that great with planning CTEs. Querying the same CTE twice seems to force it into materializing the CTE just like Postgres <12 used to do. Fortunately there's a workaround - using the `with .. as not materialized ()` hint sped up my query 100x.
  - jeltz 5y ago
    Yeah, the only thing PosgtreSQL 12 did was to remove the optimization fence. There was no additional optimizer logic added.
oncethere 5y ago
It's interesting to see how PG can be configured, but why not just use a real warehouse like Snowflake? Also, do you have any numbers on how PG performs once it's configured?
- cedricd 5y ago
  Yeah, I think using Snowflake or BigQuery or something is ultimately the better move. But sometimes folks use what they know (what they're comfortable managing, tuning, deploying, whatever). In my own testing PG performed very similarly to a 'real' warehouse. It's hard to measure because I didn't have the same datasets across several warehouses. Maybe in the future I'll try running something against a few to see.
  - willvarfar 5y ago
    Snowflake and big query are cloud solutions. Some companies have a need for self hosted databases etc. And often having a homogenous database stack is a plus. If your production systems are all MySQL, then trying to get away with using MySQL for analytics too is a smart move etc. I’ve seen so many tiddly data warehouses. Most companies don’t need web scale, and they overbuild and over complicate when they could be running on a simpler homogenous stack etc.
  - fiddlerwoaroof 5y ago
    I really wanted to migrate an analytics project I was working on from Elasticsearch to Postgres: however, when we sat down and ran production-scale proofs of concepts for the change, ClickHouse handily outclassed all the Postgres-based solutions we tried. (A Real DBA might have been able to solve this for us: I did some tuning, but I’m not an expert). ClickHouse, however, worked near-optimally out of the box.
- acidbaseextract 5y ago
  I'm glad I'm not in the situation of needing to make the judgement call, but Postgres' ecosystem might be part of the answer. For example, Snowflake has basic geospatial support, but PG has insane and useful stuff in PostGIS like ST_ClusterDBSCAN: https://postgis.net/docs/ST_ClusterDBSCAN.html https://postgis.net/docs/ST_ClusterDBSCAN.html Foreign data wrappers are another thing that might be compelling — dunno if Snowflake has an equivalent. I don't have any numbers but PG has served me fine for basic pseudo-warehousing. Relative to real solutions, it's pretty bad at truly columnar workloads: scans across a small number of columns in wide tables. The "Use Fewer Columns" advice FTA is solid. This hasn't been a deal breaker though. Analytical query time has been fine if not great up to low tens of GB table size, beyond that it gets rough.
hn2017 5y ago
No columnstore option like SQL server?
- BenoitP 5y ago
  Citus, cited at the end, is a column store (single node or distributed) https://github.com/citusdata/citus https://github.com/citusdata/citus
- mattashii 5y ago
  None baked in (yet). Maybe in 15 or 16; Zedstore (a project for a PG columnstore table access method) is slowly getting more and more feature-complete, and might be committed to the main branch in one of the next few major releases.
- arcticfox 5y ago
  I'm curious what people think of Swarm64 and/or TimescaleDB on that front
willvarfar 5y ago
A new Postgres-based darling is TimescaleDB. It’s a drop-in for Postgres. It is a hybrid row column store with excellent compression and performance. It would be interesting to see how it compares if narrator would try it out. Benchmarks would be cool. One very neat feature I am enamored by is “continuous aggregates”. These are materialized views that auto-update as you change the fact table. Continuous aggregates are a great idea. InfluxDB had “continuous queries” (but the implementation of influx generally is not so neat), and firebolt has “aggregate indexes” which are much the same thing. I think all olap dbs will eventually have them as staple, and that they will trickle down into oltp too.
- cedricd 5y ago
  Would TimescaleDB be much faster for analytical queries that aren't necessarily segmented or filtered by time? My uninformed assumption is if I do a group by over all rows in a table that they may not perform better. I'll look into their continuous aggregates -- that could be one way to get around the cost of aggregating everything if it's done incrementally.
  - willvarfar 5y ago
    I’d guess any roughly sequentially keyed table ought get good insert performance. Think how common auto-increment is. And being HTAP, timescale ought do better than a classic pure column store on upserts and non-appending inserts too. Of course if your table is big and the keys are unordered you still might get excellent performance if your access pattern is read heavy. Of course you can still mix in classic Postgres row-based tables etc. Timescale just gives you a column store choice for each table.
  - gshulegaard 5y ago
    I would have to dig more into specifics of your use case but my gut reaction is yes, it would be better. I do not have specific experience with TimescaleDB, but I have some experience scaling PostgreSQL directly and with Citus (which is similar, but not the same). But depending on the nuances of your use case, I can envision a number of scaling strategies in vanilla Postgres to handle your use case. A lot of what Timescale and Citus does is abstract some of those strategies and extend them. Which is just a vague way of me saying: I think I could probably come up with a scheme in vanilla Postgres to support your use case, and since Timescale/Citus makes those strategies even easier/better I am fairly confident they would also handle that use case. As an example I currently have a table in my current Citus schema that is sharded by column hash (e.g. "type" enumerator) and further partitioned by time. The first part (hash based sharding) seems possibly sufficient for your use case. Beyond the most simple applications in that domain though, there are more exotic options available to both Timescale and Citus that could come into play. For example, I know Citus recently incorporated some of their work on the cstore_fdw into Citus "natively" to allow columnar storage tables directly: https://www.citusdata.com/blog/2021/03/05/citus-10-release-open-source-rebalancer-and-columnar-for-postgres/ https://www.citusdata.com/blog/2021/03/05/citus-10-release-o...
u678u 5y ago
What would be great is some way to codify all this advice. Eg run PG in a Data Warehouse mode. Databases are too big and too configurable and without specialist DBAs any more most people are just guessing what works best.
- cedricd 5y ago
  That's a great point. This isn't really what you're saying, but citus [1] ships a distributed Postgres. A lot of the things they improve would help massively with analytical workloads actually. 1: https://www.citusdata.com/ https://www.citusdata.com/
  - riku_iki 5y ago
    You misspelled cit<r>us, the same is in the blogpost.
    - cedricd 5y ago
      Ahh! So sorry. Fixed it.
    - cedricd 5y ago
      Also, what's your take? Do people use citus for analytical workloads as well as production at scale? I'd assume yes, but I haven't personally used you guys. I'm just aware of you and broadly how you scale Postgres.
      - riku_iki 5y ago
        Sorry, I am not familiar with citus.
        deleted 5y ago
        [deleted]
gervwyk 5y ago
Probably going to get downvoted for this, but I feel like MongoDB should get more love in these subs. We use it all the time and their aggreations can get really advanced and perform well to the level where we run most analytics on demand. Sure we're not pushing to “big data” levels, max a few 100k records, but in reality I believe that's the average size of the majority of business datasets (Just an estimate, I have nothing to back this up) Been building with it for 5 years now and it's been a breeze. Especially with Atlas. I think our team has not spent more that 3 days in total on DB dev ops. And with Atlas Lucene text search and data lakes, querying data from S3. What's not to love.
- jmchuster 5y ago
  Yes, if you're only going to have 100k records, then basically any type of store will work, and you should choose purely on ergonomics. At that size, you can get away with not even having indexes on a traditional sql database.
- ahmedelsama 5y ago
  Yeah, for this blog, it was optimizing a Postgres table with 100m records. so 1000x more and thus all these issues came to be.
- adwww 5y ago
  Mongo is nice to work with for applications, but for analytics it's a bit of a nightmare - mostly around non compatibility with external tools.
- neximo64 5y ago
  I have used Mongo and it is a total nightmare, a bit of the reasons are in your text. Its supposedly only good with Atlas which is a managed service. I haven't tried Atlas myself (and why would I? - I try and avoid lock in) but since Postgres supports json column types which has been my go to instead of Mongo & it has been an absolute breeze. Especially since it can be indexed and scanned with postgres sql.
- arcticfox 5y ago
  > Sure we're not pushing to “big data” levels, max a few 100k records, but in reality I believe that's the average size of the majority of business datasets (Just an estimate, I have nothing to back this up) Excel on a laptop is also a viable option at that scale
dgudkov 5y ago
From a quick look I didn't notice any mention of columnar storage. I would be very skeptical about any claim that a DB without columnar storage "can work extremely well as a data warehouse".
- dreyfan 5y ago
  Take a look at Kimball/Star-Schema. It's worked extremely well as a data warehouse technique for decades. That said, I think modern offerings (e.g. Clickhouse) are superior in most use cases, but it's definitely not impossible on a traditional row-oriented RDBMS.
  - haddr 5y ago
    bear in mind that Clikchouse will quickly fall short when squeezed into traditional star schema model (it's very inefficient on multiple joins, probably even can't handle more than 1 at the same time). You would really need a dabase engine that is columnar first but still operate on SQL without too much hidden pitfalls, and that is quite often challenging
    - dreyfan 5y ago
      Yes in Clickhouse you’d generally take a denormalized approach.
    - hodgesrm 5y ago
      ClickHouse can handle multiple joins just fine and has for a while. I just gave a conference talk on CH this morning that discussed this exact topic, among others. The fact is that for large datasets scans on denormalized fact tables parallelize well, which means you can (a) offer stable performance and (b) scale more efficiently. This is important for use cases like web analytics, where users play around with different dimensions and measures but still expect consistent response. Note also, dimensions for things like Year, Month, Week, and the like compress absurdly well. It is often way faster to scan these values than to join them.
richwater 5y ago
> vacuum analyze after bulk insertion Ugh, this is nightmare. I wish they would come up with a better system than forcing this on users.
- cedricd 5y ago
  Luckily Postgres' autovacuum works really well in normal workloads. If there's an even mix of inserts spread throughout time then it's probably best to just rely on it. For data warehouses inserts can happen in bulk on a regular cadence. In that case it can help to vacuum right after. I'm not sure if it has a huge impact in practice.
- ants_a 5y ago
  Nightmare is bit of an overreaction. It's just a single command at the end signifying "Ok, done for now". It's not forced on users, the system works just fine if you don't do this. It just works better if you do let the system know when is a good time to perform maintenance tasks and collect statistics.
- golergka 5y ago
  Postgresql is great for OLTP workloads out of the box. I don't think that it's easy (or even possible) to be a great database for both OLTP and OLAP workloads without any tweaking or input from the user.
CRConrad 5y ago
Slightly peculiar juxtaposition in subheading, "Differences Between Data Warehouses and Relational Databases". Most data warehouses are relational databases (like, e.g, PostgreSQL). I think you might want to use something like "Differences Between Data Warehouses and Operational Databases" in stead? Also, under "Reasons not to use indexes", #3 says: "Indexes add additional cost on every insert / update". Yes, but then data warehouses usually aren't continually updated during the workday. It's a one-time hit sometime during the night, during your ETL run. (Not a Pg DBA, but presumably you can shut of index updating during data load, and then run it separately afterwards for higher performance?)
- SigmundA 5y ago
  Usually you drop indexes before load, then recreate after.
paulrbr 5y ago
Combine these tips with those: https://tech.fretlink.com/build-your-own-data-lake-for-reporting-purposes/ https://tech.fretlink.com/build-your-own-data-lake-for-repor... (I'm the writer of that linked article) and you get a really powerful, fully open-source, extensive data-warehouse solution for many real-life use cases (when your data doesn't exceed the 10^9 order of magnitude for number of rows). Thanks Cedric for sharing your experience with using PG for data-warehousing <3
gnfargbl 5y ago
I love postgres and my business relies on it. However, at the scale the author is talking about (~100m rows), throwing all the data into BigQuery is very likely to be a better option for many people. If rows are around 1kB, then full-dataset queries over 100m rows will cost less than $0.5 each on BQ -- less if only a few columns have to be scanned. Storage costs will be negligible and, unlike pg, setup time will be pretty much nil.
- cedricd 5y ago
  Yep. Fully agree. The point of the post wasn't to say that you should use PG as a data warehouse. Just that if it's what you've got available (for various reasons) that you can.
  - open0 5y ago
    I've had well over a billion rows in multiple tables with PostgreSQL and it wasn't a problem at all.
- predictmktegirl 5y ago
  $0.5 queries?
  - Keyframe 5y ago
    Full scan. Still absurd to what we've come to to think this is tolerable.
    - phpnode 5y ago
      Reading 100Gb from disk costs $0.50? this is absolutely incredible to me, how has this become acceptable to the industry?
      - Keyframe 5y ago
        Exactly.
phibz 5y ago
Maybe I missed it but there's no mention of denormalizing strategies or data cubes, fact tables and dimension tables. Structuring your data closer to how its going to be analyzed us vital for performance. It also gives you the opportunity to cleanse, standardize, and conform your data. I ran a pg data warehouse in the 8.x and 9.x days with about 20TB of data and it performed great.
- cedricd 5y ago
  I think you're right but it's a bit out of scope. Hard to give generalizable advice around this I think. What we personally do in practice is put everything into a single time-series table with 11 columns. [1] 1: https://www.activityschema.com/ https://www.activityschema.com/
  - phibz 5y ago
    Postgres has features that help these sort of OLAP type workflows. Things like: * partitioning your fact/aggregate tables (which was mentioned) * rolling up old data reducing granularity as data ages can help with record count and overall db size * PG triggers and stored procedures can be used to manage slowly changing dimensions * the hstore and json column types are super useful for implementing quazi-nosql storage along side traditional relational storage * window functions and CTEs (in 12) are great for writing analytical style queries * Implementing incremental loads with a staging/buffer table and possibly with plpgsql can really make connecting it all easier Not saying I didn't enjoy the article. It always makes me happy to see people realizing how suited PG can be to analytical workflows especially for small to medium workloads which represents most of what people want to do.
- patman 5y ago
  One would assume a data warehouse professional would know these things. This blog post was specifically about pg features/tips for building a dw.
- winrid 5y ago
  How did you store that much data in PG? Did you have some sort of sharding mechanism?
efxhoy 5y ago
Dr. Martin Loetzsch did a great video, ETL Patterns with Postgres. He covers some really good topics: - Instead of updating tables build their replacements under a different name then rename them. This makes updating heavy-to-compute table instant. Works even for schemas: rebuild a schema as schemaname_next rename the current to schemaname_old then rename schemaname_next to schemaname. - Keep all the source data raw and disable WAL, you don't need it for ETL. - Set memory limitis high. And lots of other good tips for doing ETL/DW in postgres. It's here: https://www.youtube.com/watch?v=whwNi21jAm4 https://www.youtube.com/watch?v=whwNi21jAm4 I really appreciate having data in postgres. It's often easy to think that a specialised DW tool will solve all your problems, but that often fails to consider things like: - Developer experience. Postgres runs very easily on a local machine, more specialized solutions often don't or are tricky to setup. - Learning another tool costs time. A developer can learn postgres really well in the time it takes them to figure out how to use several more specialised tools. And many devs already know postgres because it's pretty much the default DB nowadays. - Analytics queries often don't need to run at warp speed. Bigquery might give you the answer in a second but if postgres does it in a minute and it's a weekly report, who cares? - Postgres is boring and has been around for many years now, it will probably still be here in 10 years so time spent learning it is time well spent. More niche systems will probably be superseded by fancier, faster replacements. I would go so far as to say don't necessarily need to split out your DW from your prod DB in every case. As soon as you start splitting out a DW to a separate server you need some way to keep it in sync, so you'll probably end up duplicating some business logic for a report, maintaining some ingestion app, shuffling data around S3 or whatever. Keeping your analytics in your prod DB (or just a snapshot of yesterdays DB) is often good enough and means you will be more likely to avoid gnarly business-rules going out of sync between your app and your DW.
- Guthur 5y ago
  And if say that if you are in the position where you can run DW workloads on your prod database you probably don't need a data warehouse in the first place. Data warehouse workloads tend to be very IO intensive and could be highly disruptive to a production db. ETL is hard but it's a price to pay to isolate these two very different workloads.
kthejoker2 5y ago
No mention of Greenplum? Literally a columnar DW built on top of pg. Where are my DuckDB people? (Think SQLite for OLAP workloads.) https://duckdb.org/ https://duckdb.org/
rad_gruchalski 5y ago
Is anybody aware of any serious research Yugabyte vs Postgres? Yugabyte appears to an application as essentially Postgres 11.2 with all psql features (even the row / column level security mechanisms) but, apparently, handles replication and sharding automagically (is it DHT, similar to Cassandra)? Are there any serious technical comparisons with some conclusions, without marketing bs?
- zinclozenge 5y ago
  yugabyte is multi-raft i believe
- manigandham 5y ago
  Yes, Yugabyte has been featured on Jepsen. It's basically in the class of "newsql" natively distributed relational databases. Yugabyte uses actual Postgres code for the query parsing top layer and then translates that into to operates on it's key/value store which handles replication and distributed. CockroachDB is similar but has built everything from scratch in Go. There are similar examples like TiDB and Vitesse for MySQL as well.
  - rad_gruchalski 5y ago
    The Jepsen tests do not inspire confidence but they're also pretty aged by now. 1.3.1 under test and the most recent appears to be 2.7.0. Cockroach is a no go for me because of the licensing restrictions. For example, password authentication only in the BSL licensed code. Backups in enterprise.
    - jordanlewis 5y ago
      Backups are available without a license in CockroachDB as of 20.2, FWIW. https://www.cockroachlabs.com/blog/distributed-backup-restore/ https://www.cockroachlabs.com/blog/distributed-backup-restor... https://www.cockroachlabs.com/docs/v20.2/backup.html https://www.cockroachlabs.com/docs/v20.2/backup.html
FridgeSeal 5y ago
Postgres is great, but honestly, why not use something column-oriented that’s built and optimised for this?
FridgeSeal 5y ago
> Because of this dedicated data warehouses…use column-oriented storage and don't have indexes. Well, that’s not really correct is it. ClickHouse for one definitely has them as Snowflake the last time I used it. This is a lot of work to go through to avoid using the right tool for the job. Just use something like ClickHouse or even DuckDB and reap the benefits of better performance with less caveats.
- simonw 5y ago
  I had the opposite reaction: running analytics against the database you are already using feels like a lot less work to me than adopting an additional tool and solving the problem of synchronizing your data to it.
  - mumblemumble 5y ago
    If the database stays small and the load stays low, sure. But, as things grow, you tend to run into problems with analytical loads adversely impacting, and even knocking over, the production system by locking resources and causing timeouts. Especially if you're allowing analysts to run ad-hoc analytical queries. Long story short, yes, resiliency is expensive, but it's not always more expensive than not having resiliency.
    - Footkerchief 5y ago
      Read replicas can take the brunt and have widespread availability in Postgres cloud providers.
    - marcosdumay 5y ago
      I do order the solution in "you should choose the simplest one that doesn't disrupt your environment" as: Use a single database for transational and analytical workloads. Replicate your transational database as is for analytical workloads. Remodel your data and replicate to the exact same technology stack. Remodel your data and replicate into specialized tools for analytics. I have never seen anybody that actually needs the last one. But the largest environments I've looked are government databases with a few thousands of people working on (there are bigger envs out there).
bigtimegames 5y ago
Sure why not -- I mean redshift is a cluster of Postgres databases as well. What you don't get is scale -- so its fine for a small data warehouse (in which case is it really a data warehouse...)
- gshulegaard 5y ago
  I am not sure I agree with the general idea that Postgres can't or even--albeit a bit less strongly--that it is hard to scale. Even in 2008 people were running petabyte-scale warehouses using Postgres: https://www.toolbox.com/tech/data-management/blogs/2-petabyte-postgresql-052208/ https://www.toolbox.com/tech/data-management/blogs/2-petabyt... Since 2008 improvements in parallel query execution (and numerous other improvements) in the core project plus the availability of forks/extensions which abstract and/or modify various bits for improving scalability (see Citus and Timescale) it's never been easier to scale Postgres to some truly staggering heights. While I wouldn't want to speak in absolutes, there are very few applications where I think Postgres wouldn't be a viable choice as a data warehouse. Emphasis on warehouse as I wouldn't want to suggest Postgres as an ideal candidate to be a data lake. The difference between them for me being whether or not the data is structured/processed. Similar in definition to this article: https://medium.com/@distillerytech/data-warehouse-vs-data-lake-which-is-right-for-your-enterprise-app-development-effort-fa9f046d47ca https://medium.com/@distillerytech/data-warehouse-vs-data-la... Personally, I have experience scaling core PostgreSQL (9.4) to handle ingestion of monitoring data for web servers to the tune of 2-3 terabytes a day. Not the grandest of scales, but enough to have seen a few bumps along the way...and, for what it's worth, I think it is surprisingly easy to scale. I wouldn't want to sign up to scale Postgres to handle exabyte data loads, but single digit petabytes? Sure. https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/architecting-petabyte-scale-analytics-by-scaling-out-postgres-on/ba-p/969685 https://techcommunity.microsoft.com/t5/azure-database-for-po... And at petabyte-scale, I personally think it qualifies as a data warehouse.
coreyh14444 5y ago
Beware that Google Data Studio's PostgreSQL connector is severely limited as it is not meant for this type of work. I suspect that other BI tools may have the same problem. For us, BigQuery made a lot more sense.
tbrock 5y ago
> ensure you're not I/O-bound The biggest thing holding me back from using pg as a data warehouse is RDS not having support for instances with ephemeral drives / cost for PIOPS. I need 100s of thousands of iops not thousands.
haddr 5y ago
How his compares to other columnar stores of this kind? e.g. MariaDB ColumnStore? others?
nautilus12 5y ago
I almost don't want to read this because for any company I work for its almost pointless to try convince them not to use Redshift. I'm slowly seeing the future of software engineering just us being switchboard operators for AWS.

435 points by cedricd 5y ago

We support multiple data warehouses on our platform. We recently had to do a bit of work to get Postgres running, so we wrote a high-level post about things to consider when running analytical workloads on PG instead of normal production workloads.

mleonhard 5y ago
Thank you!

mattashii 5y ago

> Avoid Common Table Expressions (or upgrade to 12) Upgrading to 12 (or 13) seems like the better option here, whenever you're able to do so. The improvements are very much worth it.

cedricd 5y ago
Yes, that's a really great point. I should emphasize that more clearly in the blog :).
- mattashii 5y ago
  A few more items I've encountered: - Citus data, not Citrus data. - In tables, column order matters. Order const-width non-null columns before all other columns (best so that there's no unnecessary alignment padding). Then maybe some low null fraction fixed-width columns, then ordered by query access frequency. A lot of time can be spent extracting tuple values, and you can save a lot of time using the offset caches by correctly tetris-ing your columns. Note that dropped columns are set to null, so a table rewrite (by redefining your table) may be in order for 100% performance.
  - galkk 5y ago
    Isn't #2 something that the database engine should handle by itself: const-width, non-null is written in DDL and the engine should be be able to handle it.
    - mattashii 5y ago
      yes, ish. But Postgresql has no reordering of table columns builtin, mostly due to the limitations of the internal representation of its schema (also called features) and in how the DLL is performed. Adding or removing a column in this system does not require postgres to rewrite the whole table, which means that old data can stay in the table effectively forever, as long as there are no other rewrite-required DDLs performed. Additionally, columns can be updated to SET NOT NULL / DROP NOT NULL, further complicating the whole system you're trying to optimize. Eventually postgresql might support some form of table column reordering that permanently optimizes out deleted columns, but I think it's unlikely to happen anytime soon. There are lower hanging fruits on the tree; altering existing table definitions in a backwards-incompatible manner is a lot of effort for likely very little gain. As for column packing: Maybe this can be implemented for CREATE TABLE with an option, but in the current transactional DDL framework this cannot be implemented for ADD COLUMN, because we can't reorder columns.
wwweston 5y ago
What situations would make people unable to upgrade to 12+?
- cedricd 5y ago
  Maybe it doesn't get prioritized until it's important. I know PG upgrades are pretty straightforward, but sometimes people don't want to touch something running well. That said, given the performance implications, if someone wants to use PG as a warehouse upgrading to 12 is a no-brainer.
- goatinaboat 5y ago
  What situations would make people unable to upgrade to 12+? In my personal experience: the organisation doesn't invest in a storage solution that offers snapshots/COW (e.g. ZFS or SAN or whatever). Then they wait to upgrade until their disks reach a usage that the upgrade has to be done in-place. Then they become like rabbits in the headlights and never upgrade.
- aargh_aargh 5y ago
  One case I decided against it was when I needed to access the new postgres server (which I initially planned to be v13) from an old machine (with a legacy distro with v9.6). v13 introduces a new SCRAM-SHA-256 password hashing method by default and only libpq10 and newer supports this method. For some reason I couldn't or didn't want to neither rehash the passwords nor upgrade the client, so I remained on a lower version. Certainly not unsolvable, but I didn't have the time to spend on a proper fix.
truculent 5y ago
Yes, it is strange that "Avoid CTEs" made it into the TL;DR instead.
- cedricd 5y ago
  I updated the blog post :)
arcticfox 5y ago
Something painful I just learned today is that even Postgres >= 12 isn't that great with planning CTEs. Querying the same CTE twice seems to force it into materializing the CTE just like Postgres <12 used to do. Fortunately there's a workaround - using the `with .. as not materialized ()` hint sped up my query 100x.
- jeltz 5y ago
  Yeah, the only thing PosgtreSQL 12 did was to remove the optimization fence. There was no additional optimizer logic added.

oncethere 5y ago

It's interesting to see how PG can be configured, but why not just use a real warehouse like Snowflake? Also, do you have any numbers on how PG performs once it's configured?

cedricd 5y ago
Yeah, I think using Snowflake or BigQuery or something is ultimately the better move. But sometimes folks use what they know (what they're comfortable managing, tuning, deploying, whatever). In my own testing PG performed very similarly to a 'real' warehouse. It's hard to measure because I didn't have the same datasets across several warehouses. Maybe in the future I'll try running something against a few to see.
- willvarfar 5y ago
  Snowflake and big query are cloud solutions. Some companies have a need for self hosted databases etc. And often having a homogenous database stack is a plus. If your production systems are all MySQL, then trying to get away with using MySQL for analytics too is a smart move etc. I’ve seen so many tiddly data warehouses. Most companies don’t need web scale, and they overbuild and over complicate when they could be running on a simpler homogenous stack etc.
- fiddlerwoaroof 5y ago
  I really wanted to migrate an analytics project I was working on from Elasticsearch to Postgres: however, when we sat down and ran production-scale proofs of concepts for the change, ClickHouse handily outclassed all the Postgres-based solutions we tried. (A Real DBA might have been able to solve this for us: I did some tuning, but I’m not an expert). ClickHouse, however, worked near-optimally out of the box.
acidbaseextract 5y ago
I'm glad I'm not in the situation of needing to make the judgement call, but Postgres' ecosystem might be part of the answer. For example, Snowflake has basic geospatial support, but PG has insane and useful stuff in PostGIS like ST_ClusterDBSCAN: https://postgis.net/docs/ST_ClusterDBSCAN.html https://postgis.net/docs/ST_ClusterDBSCAN.html Foreign data wrappers are another thing that might be compelling — dunno if Snowflake has an equivalent. I don't have any numbers but PG has served me fine for basic pseudo-warehousing. Relative to real solutions, it's pretty bad at truly columnar workloads: scans across a small number of columns in wide tables. The "Use Fewer Columns" advice FTA is solid. This hasn't been a deal breaker though. Analytical query time has been fine if not great up to low tens of GB table size, beyond that it gets rough.

hn2017 5y ago

No columnstore option like SQL server?

BenoitP 5y ago
Citus, cited at the end, is a column store (single node or distributed) https://github.com/citusdata/citus https://github.com/citusdata/citus
mattashii 5y ago
None baked in (yet). Maybe in 15 or 16; Zedstore (a project for a PG columnstore table access method) is slowly getting more and more feature-complete, and might be committed to the main branch in one of the next few major releases.
arcticfox 5y ago
I'm curious what people think of Swarm64 and/or TimescaleDB on that front

willvarfar 5y ago

A new Postgres-based darling is TimescaleDB. It’s a drop-in for Postgres. It is a hybrid row column store with excellent compression and performance. It would be interesting to see how it compares if narrator would try it out. Benchmarks would be cool. One very neat feature I am enamored by is “continuous aggregates”. These are materialized views that auto-update as you change the fact table. Continuous aggregates are a great idea. InfluxDB had “continuous queries” (but the implementation of influx generally is not so neat), and firebolt has “aggregate indexes” which are much the same thing. I think all olap dbs will eventually have them as staple, and that they will trickle down into oltp too.

cedricd 5y ago
Would TimescaleDB be much faster for analytical queries that aren't necessarily segmented or filtered by time? My uninformed assumption is if I do a group by over all rows in a table that they may not perform better. I'll look into their continuous aggregates -- that could be one way to get around the cost of aggregating everything if it's done incrementally.
- willvarfar 5y ago
  I’d guess any roughly sequentially keyed table ought get good insert performance. Think how common auto-increment is. And being HTAP, timescale ought do better than a classic pure column store on upserts and non-appending inserts too. Of course if your table is big and the keys are unordered you still might get excellent performance if your access pattern is read heavy. Of course you can still mix in classic Postgres row-based tables etc. Timescale just gives you a column store choice for each table.
- gshulegaard 5y ago
  I would have to dig more into specifics of your use case but my gut reaction is yes, it would be better. I do not have specific experience with TimescaleDB, but I have some experience scaling PostgreSQL directly and with Citus (which is similar, but not the same). But depending on the nuances of your use case, I can envision a number of scaling strategies in vanilla Postgres to handle your use case. A lot of what Timescale and Citus does is abstract some of those strategies and extend them. Which is just a vague way of me saying: I think I could probably come up with a scheme in vanilla Postgres to support your use case, and since Timescale/Citus makes those strategies even easier/better I am fairly confident they would also handle that use case. As an example I currently have a table in my current Citus schema that is sharded by column hash (e.g. "type" enumerator) and further partitioned by time. The first part (hash based sharding) seems possibly sufficient for your use case. Beyond the most simple applications in that domain though, there are more exotic options available to both Timescale and Citus that could come into play. For example, I know Citus recently incorporated some of their work on the cstore_fdw into Citus "natively" to allow columnar storage tables directly: https://www.citusdata.com/blog/2021/03/05/citus-10-release-open-source-rebalancer-and-columnar-for-postgres/ https://www.citusdata.com/blog/2021/03/05/citus-10-release-o...

u678u 5y ago

What would be great is some way to codify all this advice. Eg run PG in a Data Warehouse mode. Databases are too big and too configurable and without specialist DBAs any more most people are just guessing what works best.

cedricd 5y ago
That's a great point. This isn't really what you're saying, but citus [1] ships a distributed Postgres. A lot of the things they improve would help massively with analytical workloads actually. 1: https://www.citusdata.com/ https://www.citusdata.com/
- riku_iki 5y ago
  You misspelled cit<r>us, the same is in the blogpost.
  - cedricd 5y ago
    Ahh! So sorry. Fixed it.
  - cedricd 5y ago
    Also, what's your take? Do people use citus for analytical workloads as well as production at scale? I'd assume yes, but I haven't personally used you guys. I'm just aware of you and broadly how you scale Postgres.
    - riku_iki 5y ago
      Sorry, I am not familiar with citus.
      - deleted 5y ago
        [deleted]

gervwyk 5y ago

Probably going to get downvoted for this, but I feel like MongoDB should get more love in these subs. We use it all the time and their aggreations can get really advanced and perform well to the level where we run most analytics on demand. Sure we're not pushing to “big data” levels, max a few 100k records, but in reality I believe that's the average size of the majority of business datasets (Just an estimate, I have nothing to back this up) Been building with it for 5 years now and it's been a breeze. Especially with Atlas. I think our team has not spent more that 3 days in total on DB dev ops. And with Atlas Lucene text search and data lakes, querying data from S3. What's not to love.

jmchuster 5y ago
Yes, if you're only going to have 100k records, then basically any type of store will work, and you should choose purely on ergonomics. At that size, you can get away with not even having indexes on a traditional sql database.
ahmedelsama 5y ago
Yeah, for this blog, it was optimizing a Postgres table with 100m records. so 1000x more and thus all these issues came to be.
adwww 5y ago
Mongo is nice to work with for applications, but for analytics it's a bit of a nightmare - mostly around non compatibility with external tools.
neximo64 5y ago
I have used Mongo and it is a total nightmare, a bit of the reasons are in your text. Its supposedly only good with Atlas which is a managed service. I haven't tried Atlas myself (and why would I? - I try and avoid lock in) but since Postgres supports json column types which has been my go to instead of Mongo & it has been an absolute breeze. Especially since it can be indexed and scanned with postgres sql.
arcticfox 5y ago
> Sure we're not pushing to “big data” levels, max a few 100k records, but in reality I believe that's the average size of the majority of business datasets (Just an estimate, I have nothing to back this up) Excel on a laptop is also a viable option at that scale

dgudkov 5y ago

From a quick look I didn't notice any mention of columnar storage. I would be very skeptical about any claim that a DB without columnar storage "can work extremely well as a data warehouse".

dreyfan 5y ago
Take a look at Kimball/Star-Schema. It's worked extremely well as a data warehouse technique for decades. That said, I think modern offerings (e.g. Clickhouse) are superior in most use cases, but it's definitely not impossible on a traditional row-oriented RDBMS.
- haddr 5y ago
  bear in mind that Clikchouse will quickly fall short when squeezed into traditional star schema model (it's very inefficient on multiple joins, probably even can't handle more than 1 at the same time). You would really need a dabase engine that is columnar first but still operate on SQL without too much hidden pitfalls, and that is quite often challenging
  - dreyfan 5y ago
    Yes in Clickhouse you’d generally take a denormalized approach.
  - hodgesrm 5y ago
    ClickHouse can handle multiple joins just fine and has for a while. I just gave a conference talk on CH this morning that discussed this exact topic, among others. The fact is that for large datasets scans on denormalized fact tables parallelize well, which means you can (a) offer stable performance and (b) scale more efficiently. This is important for use cases like web analytics, where users play around with different dimensions and measures but still expect consistent response. Note also, dimensions for things like Year, Month, Week, and the like compress absurdly well. It is often way faster to scan these values than to join them.

richwater 5y ago

> vacuum analyze after bulk insertion Ugh, this is nightmare. I wish they would come up with a better system than forcing this on users.

cedricd 5y ago
Luckily Postgres' autovacuum works really well in normal workloads. If there's an even mix of inserts spread throughout time then it's probably best to just rely on it. For data warehouses inserts can happen in bulk on a regular cadence. In that case it can help to vacuum right after. I'm not sure if it has a huge impact in practice.
ants_a 5y ago
Nightmare is bit of an overreaction. It's just a single command at the end signifying "Ok, done for now". It's not forced on users, the system works just fine if you don't do this. It just works better if you do let the system know when is a good time to perform maintenance tasks and collect statistics.
golergka 5y ago
Postgresql is great for OLTP workloads out of the box. I don't think that it's easy (or even possible) to be a great database for both OLTP and OLAP workloads without any tweaking or input from the user.

CRConrad 5y ago

Slightly peculiar juxtaposition in subheading, "Differences Between Data Warehouses and Relational Databases". Most data warehouses are relational databases (like, e.g, PostgreSQL). I think you might want to use something like "Differences Between Data Warehouses and Operational Databases" in stead? Also, under "Reasons not to use indexes", #3 says: "Indexes add additional cost on every insert / update". Yes, but then data warehouses usually aren't continually updated during the workday. It's a one-time hit sometime during the night, during your ETL run. (Not a Pg DBA, but presumably you can shut of index updating during data load, and then run it separately afterwards for higher performance?)

SigmundA 5y ago
Usually you drop indexes before load, then recreate after.

paulrbr 5y ago

Combine these tips with those: https://tech.fretlink.com/build-your-own-data-lake-for-reporting-purposes/ https://tech.fretlink.com/build-your-own-data-lake-for-repor... (I'm the writer of that linked article) and you get a really powerful, fully open-source, extensive data-warehouse solution for many real-life use cases (when your data doesn't exceed the 10^9 order of magnitude for number of rows). Thanks Cedric for sharing your experience with using PG for data-warehousing <3

gnfargbl 5y ago

I love postgres and my business relies on it. However, at the scale the author is talking about (~100m rows), throwing all the data into BigQuery is very likely to be a better option for many people. If rows are around 1kB, then full-dataset queries over 100m rows will cost less than $0.5 each on BQ -- less if only a few columns have to be scanned. Storage costs will be negligible and, unlike pg, setup time will be pretty much nil.

cedricd 5y ago
Yep. Fully agree. The point of the post wasn't to say that you should use PG as a data warehouse. Just that if it's what you've got available (for various reasons) that you can.
- open0 5y ago
  I've had well over a billion rows in multiple tables with PostgreSQL and it wasn't a problem at all.
predictmktegirl 5y ago
$0.5 queries?
- Keyframe 5y ago
  Full scan. Still absurd to what we've come to to think this is tolerable.
  - phpnode 5y ago
    Reading 100Gb from disk costs $0.50? this is absolutely incredible to me, how has this become acceptable to the industry?
    - Keyframe 5y ago
      Exactly.

phibz 5y ago

Maybe I missed it but there's no mention of denormalizing strategies or data cubes, fact tables and dimension tables. Structuring your data closer to how its going to be analyzed us vital for performance. It also gives you the opportunity to cleanse, standardize, and conform your data. I ran a pg data warehouse in the 8.x and 9.x days with about 20TB of data and it performed great.

cedricd 5y ago
I think you're right but it's a bit out of scope. Hard to give generalizable advice around this I think. What we personally do in practice is put everything into a single time-series table with 11 columns. [1] 1: https://www.activityschema.com/ https://www.activityschema.com/
- phibz 5y ago
  Postgres has features that help these sort of OLAP type workflows. Things like: * partitioning your fact/aggregate tables (which was mentioned) * rolling up old data reducing granularity as data ages can help with record count and overall db size * PG triggers and stored procedures can be used to manage slowly changing dimensions * the hstore and json column types are super useful for implementing quazi-nosql storage along side traditional relational storage * window functions and CTEs (in 12) are great for writing analytical style queries * Implementing incremental loads with a staging/buffer table and possibly with plpgsql can really make connecting it all easier Not saying I didn't enjoy the article. It always makes me happy to see people realizing how suited PG can be to analytical workflows especially for small to medium workloads which represents most of what people want to do.
patman 5y ago
One would assume a data warehouse professional would know these things. This blog post was specifically about pg features/tips for building a dw.
winrid 5y ago
How did you store that much data in PG? Did you have some sort of sharding mechanism?

efxhoy 5y ago

Dr. Martin Loetzsch did a great video, ETL Patterns with Postgres. He covers some really good topics: - Instead of updating tables build their replacements under a different name then rename them. This makes updating heavy-to-compute table instant. Works even for schemas: rebuild a schema as schemaname_next rename the current to schemaname_old then rename schemaname_next to schemaname. - Keep all the source data raw and disable WAL, you don't need it for ETL. - Set memory limitis high. And lots of other good tips for doing ETL/DW in postgres. It's here: https://www.youtube.com/watch?v=whwNi21jAm4 https://www.youtube.com/watch?v=whwNi21jAm4 I really appreciate having data in postgres. It's often easy to think that a specialised DW tool will solve all your problems, but that often fails to consider things like: - Developer experience. Postgres runs very easily on a local machine, more specialized solutions often don't or are tricky to setup. - Learning another tool costs time. A developer can learn postgres really well in the time it takes them to figure out how to use several more specialised tools. And many devs already know postgres because it's pretty much the default DB nowadays. - Analytics queries often don't need to run at warp speed. Bigquery might give you the answer in a second but if postgres does it in a minute and it's a weekly report, who cares? - Postgres is boring and has been around for many years now, it will probably still be here in 10 years so time spent learning it is time well spent. More niche systems will probably be superseded by fancier, faster replacements. I would go so far as to say don't necessarily need to split out your DW from your prod DB in every case. As soon as you start splitting out a DW to a separate server you need some way to keep it in sync, so you'll probably end up duplicating some business logic for a report, maintaining some ingestion app, shuffling data around S3 or whatever. Keeping your analytics in your prod DB (or just a snapshot of yesterdays DB) is often good enough and means you will be more likely to avoid gnarly business-rules going out of sync between your app and your DW.

Guthur 5y ago
And if say that if you are in the position where you can run DW workloads on your prod database you probably don't need a data warehouse in the first place. Data warehouse workloads tend to be very IO intensive and could be highly disruptive to a production db. ETL is hard but it's a price to pay to isolate these two very different workloads.

kthejoker2 5y ago

No mention of Greenplum? Literally a columnar DW built on top of pg. Where are my DuckDB people? (Think SQLite for OLAP workloads.) https://duckdb.org/ https://duckdb.org/

rad_gruchalski 5y ago

Is anybody aware of any serious research Yugabyte vs Postgres? Yugabyte appears to an application as essentially Postgres 11.2 with all psql features (even the row / column level security mechanisms) but, apparently, handles replication and sharding automagically (is it DHT, similar to Cassandra)? Are there any serious technical comparisons with some conclusions, without marketing bs?

zinclozenge 5y ago
yugabyte is multi-raft i believe
manigandham 5y ago
Yes, Yugabyte has been featured on Jepsen. It's basically in the class of "newsql" natively distributed relational databases. Yugabyte uses actual Postgres code for the query parsing top layer and then translates that into to operates on it's key/value store which handles replication and distributed. CockroachDB is similar but has built everything from scratch in Go. There are similar examples like TiDB and Vitesse for MySQL as well.
- rad_gruchalski 5y ago
  The Jepsen tests do not inspire confidence but they're also pretty aged by now. 1.3.1 under test and the most recent appears to be 2.7.0. Cockroach is a no go for me because of the licensing restrictions. For example, password authentication only in the BSL licensed code. Backups in enterprise.
  - jordanlewis 5y ago
    Backups are available without a license in CockroachDB as of 20.2, FWIW. https://www.cockroachlabs.com/blog/distributed-backup-restore/ https://www.cockroachlabs.com/blog/distributed-backup-restor... https://www.cockroachlabs.com/docs/v20.2/backup.html https://www.cockroachlabs.com/docs/v20.2/backup.html

FridgeSeal 5y ago

Postgres is great, but honestly, why not use something column-oriented that’s built and optimised for this?

FridgeSeal 5y ago

> Because of this dedicated data warehouses…use column-oriented storage and don't have indexes. Well, that’s not really correct is it. ClickHouse for one definitely has them as Snowflake the last time I used it. This is a lot of work to go through to avoid using the right tool for the job. Just use something like ClickHouse or even DuckDB and reap the benefits of better performance with less caveats.

simonw 5y ago
I had the opposite reaction: running analytics against the database you are already using feels like a lot less work to me than adopting an additional tool and solving the problem of synchronizing your data to it.
- mumblemumble 5y ago
  If the database stays small and the load stays low, sure. But, as things grow, you tend to run into problems with analytical loads adversely impacting, and even knocking over, the production system by locking resources and causing timeouts. Especially if you're allowing analysts to run ad-hoc analytical queries. Long story short, yes, resiliency is expensive, but it's not always more expensive than not having resiliency.
  - Footkerchief 5y ago
    Read replicas can take the brunt and have widespread availability in Postgres cloud providers.
  - marcosdumay 5y ago
    I do order the solution in "you should choose the simplest one that doesn't disrupt your environment" as: Use a single database for transational and analytical workloads. Replicate your transational database as is for analytical workloads. Remodel your data and replicate to the exact same technology stack. Remodel your data and replicate into specialized tools for analytics. I have never seen anybody that actually needs the last one. But the largest environments I've looked are government databases with a few thousands of people working on (there are bigger envs out there).

bigtimegames 5y ago

Sure why not -- I mean redshift is a cluster of Postgres databases as well. What you don't get is scale -- so its fine for a small data warehouse (in which case is it really a data warehouse...)

gshulegaard 5y ago
I am not sure I agree with the general idea that Postgres can't or even--albeit a bit less strongly--that it is hard to scale. Even in 2008 people were running petabyte-scale warehouses using Postgres: https://www.toolbox.com/tech/data-management/blogs/2-petabyte-postgresql-052208/ https://www.toolbox.com/tech/data-management/blogs/2-petabyt... Since 2008 improvements in parallel query execution (and numerous other improvements) in the core project plus the availability of forks/extensions which abstract and/or modify various bits for improving scalability (see Citus and Timescale) it's never been easier to scale Postgres to some truly staggering heights. While I wouldn't want to speak in absolutes, there are very few applications where I think Postgres wouldn't be a viable choice as a data warehouse. Emphasis on warehouse as I wouldn't want to suggest Postgres as an ideal candidate to be a data lake. The difference between them for me being whether or not the data is structured/processed. Similar in definition to this article: https://medium.com/@distillerytech/data-warehouse-vs-data-lake-which-is-right-for-your-enterprise-app-development-effort-fa9f046d47ca https://medium.com/@distillerytech/data-warehouse-vs-data-la... Personally, I have experience scaling core PostgreSQL (9.4) to handle ingestion of monitoring data for web servers to the tune of 2-3 terabytes a day. Not the grandest of scales, but enough to have seen a few bumps along the way...and, for what it's worth, I think it is surprisingly easy to scale. I wouldn't want to sign up to scale Postgres to handle exabyte data loads, but single digit petabytes? Sure. https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/architecting-petabyte-scale-analytics-by-scaling-out-postgres-on/ba-p/969685 https://techcommunity.microsoft.com/t5/azure-database-for-po... And at petabyte-scale, I personally think it qualifies as a data warehouse.

coreyh14444 5y ago

Beware that Google Data Studio's PostgreSQL connector is severely limited as it is not meant for this type of work. I suspect that other BI tools may have the same problem. For us, BigQuery made a lot more sense.

tbrock 5y ago

> ensure you're not I/O-bound The biggest thing holding me back from using pg as a data warehouse is RDS not having support for instances with ephemeral drives / cost for PIOPS. I need 100s of thousands of iops not thousands.

haddr 5y ago

How his compares to other columnar stores of this kind? e.g. MariaDB ColumnStore? others?

nautilus12 5y ago

I almost don't want to read this because for any company I work for its almost pointless to try convince them not to use Redshift. I'm slowly seeing the future of software engineering just us being switchboard operators for AWS.