hi folks, very basic question here - I'm using

meltano run

to load a large PostgreSQL table, and queries on it are taking a long time, even simple ones (analysis of a `SELECT COUNT(*) FROM table WHERE timestamp > '2022-01-01'` for reference). from my limited knowledge of databases, I understand that setting proper indexes on the columns I'll use the most for queries will improve the performance. what would the #C01UTUSP34M be here? running a data transformation with dbt that creates a new table, essentially duplicating the data but with proper indexes? to be clear, I'm concerned that if I create an index on the table meltano created for loading, things will break. but maybe it's not the case?

Can you define "large" and "slow"? And what are the specs on your RDS instance? The first step is ensure that your expectations are correct. 😉

I'm a database guy, so I may have the wrong perspective, but this feels like a pretty standard database/qery optimization problem, not so much a meltano/ELT question

right, I should have made my concerns more explicit: that if I put an index on the table meltano created for loading, things will break. but maybe it's not the case

For better or worse whether adding an index will cause problems is probably not a meltano question... it's a question of how the target is implemented. Assuming you're using the default PG target that's actually not produced by meltano. I know I'm being a bit pedantic here but this is the double-edged sword of meltano's reliance on F/OSS components... it makes a lot of things possible but creates a lot of potential problems that meltano can't actually help you fix. 🤔

that's very helpful still @thomas_briggs, thanks a lot! I guess the only way to know is to make some experiments. my limited knowledge of PG is slowing me down here, will get better with time I hope 🙂

90 seconds to scan 1M rows definitely seems slow. My experience with Postgres is limited, unfortunately, but usually that pattern (queries that scan lots of rows and take a long time) means inadequate I/O. And of course the best solution to that is indexing. 🙂

fair enough

For fun I would try a similar query on a different column, if you can... I've seen time zone conversion cause bizarre performance issues in other DBs so it's conceivable (to me) that the problem is filtering by a timestamp. Not saying that's a likely explanation, just saying it's a possible explanation. 😉

well, I went ahead and added an index and magically those queries are blazing fast now 🔥 whether or not this will have consequences down the road, as you say, it's a problem between me and

pipelinewise-target-postgres

and not meltano. I will do the experiment with the other columns just for curiosity in any case 🙂 thanks for your help!

My pleasure. I could talk DB optimization all day so if you run into anything else let me know. 😉

try

brin

index on

timestamp

super slim I optimized the crap out of RDS postgres on AWS before moving into a real OLAP database

I'll do, thanks @alexander_butler!