Swapping Out Underlying Parquet Files

[RalfNorthman]

Hi!

I'm considering using ParadeDB in a scenario where we replace multiple tables' underlying parquet files, with files exported nightly from our ERP, as a sort of brute force update. Is this feasible without causing disruptions or other problems? The database load is otherwise very low at these times.

[philippemnoel]

Hi @RalfNorthman! Thanks for opening this.

Can you share more about how you're planning to replace the underlying Parquet files? Would you simply override the .parquet files on disk and expect Postgres to pick them up properly?

Can you share a bit more about your use case and your interest in ParadeDB vs any other tool you might be considering here?

Re: your question -- @rebasedming can provide the best answer, but I would suspect that it will work properly if you follow the naming pattern of the existing files and the same path.

[RalfNorthman]

Hello!

Would you simply override the .parquet files on disk and expect Postgres to pick them up properly?

Yes, overwriting to the same file names using odbc2parquet.

Side note: one of the ERP tables uses EAV, but are exported out on a pivoted wide format to be easier to query. In this new format it has roughly 600 columns, would this pose a problem as a Postgres parquet table? (But even if not, we couldn't use Diesel with it, since Diesel only has support for up to 128 columns).

Can you share a bit more about your use case and your interest in ParadeDB vs any other tool you might be considering here?

We are using MariaDB with the CONNECT plugin to query joins between foreign ERP tables and our custom data in native tables, but we are not happy with this solution, especially when it comes to analytical work loads. Therefore, we have used Polars with exported parquet files for some queries. But, it would be much nicer to have everything in the same, Diesel-compatible database (like Postgres). We're happy to see that work on joins between parquet and heap tables is underway in #918.

Re: your question -- @rebasedming can provide the best answer, but I would suspect that it will work properly if you follow the naming pattern of the existing files and the same path.

Sounds promising, but I'm curious to hear what he has to say.

[rebasedming]

Manually copying Parquet files into the Postgres directory won't work because we use delta logs on top of the Parquet files, and those logs are generated when you write SQL DML commands.

One way to upload Parquet files into Postgres is with a Parquet FDW, and then copy over the foreign Parquet table into an actual Postgres table. I've seen one or two of these extensions that you can try. Alternatively, if you already use Polars, then you can probably convert your Parquet files into CSV and COPY them into Postgres.

[adriangb]

Or use DuckDB or pgpq to do a BINARY COPY directly from Parquet. I used the latter to build a "brute force" ETL update like what you are envisioning.

[RalfNorthman]

Okay, thanks!

If any other relevant information comes to mind I would be happy to hear it.

[philippemnoel]

Hi @RalfNorthman. I'd love to chat to better understand your use case and how we can better service you! If you join our community Slack, we can set up a Zoom call. Link here: https://join.slack.com/t/paradedbcommunity/shared_invite/zt-217mordsh-ielS6BiZf7VW3rqKBFgAlQ

Or, I'd be curious to hear what you think of the workflow @rebasedming suggested, and if there is anything you'd like improved on/you'd need to truly simplify your interaction with ParadeDB/pg_analytics.

[RalfNorthman]

Hi again!

1. Does the new pg_lakehouse extension change the answer to the original question?
2. Are joins between heap tables and pg_lakehouse tables implemented?

EDIT: I found this in the pg_analytics repo readme:

Whereas pg_analytics is designed to query over data stored within Postgres, pg_lakehouse queries over external data lakes like S3 and table formats like Iceberg, storing no data inside Postgres.

So I guess the answer to my first question is: yes, with pg_lakehouse you can overwrite your .parquet-files in the bucket without messing up your Postgres instance. :-)