6 ms·

Postgres and Parquet in the data lake

90 points by pramsey 4y ago

miohtama 4y ago
Please correct if I am wrong but Parquet is columnar store whereas PSQL is row based?
- vladsanchez 4y ago
  Experimental: https://github.com/adjust/parquet_fdw/ https://github.com/adjust/parquet_fdw/
- pramsey 4y ago
  Parquet is a column-based file format. PostgreSQL is a SQL query engine, and the default storage is row-oriented, but the engine can do all kinds of fun things, hence the parquet_fdw extension and the cstore extension for example.
pella 4y ago
> "Generating Parquet" It is also useful for moving data from Postgres to BigQuery! ( batch load ) https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-parquet https://cloud.google.com/bigquery/docs/loading-data-cloud-st... Thanks for the "ogr2ogr" trick! :-) I hope the next blog post will be about GeoParquet and storing complex geometries in parquet format :-) https://github.com/opengeospatial/geoparquet https://github.com/opengeospatial/geoparquet
- rockostrich 4y ago
  Cloud SQL has BigQuery connections that can be leveraged. But yea, this seems like a nice solution if you have a postgres instance outside of Cloud SQL. Another approach would be to write the CDC to a message queue and archive that to parquet.
- pramsey 4y ago
  GeoParquet just embeds the geometry as WKB, so getting PostGIS geometry is as simple as going "SELECT geom::geometry FROM my_parq_fdw_table". The spec doesn't yet have anything to say about how to get a good spatial sort or carry out a spatial filter, so it's all pretty ad hoc on that end. Thanks to ogr2ogr, generating a GeoParquet file is now pretty straightforward.
sharadov 4y ago
Thank you, this is really cool!
jaxrtech 4y ago
If you were ready to throw away a bit of archival quality, I wonder if writing Apache Arrow straight to disk and then just mmap your columns back in for a query.
- teej 4y ago
  It's certainly possible. But Parquet and Arrow are being built with each other in mind. I doubt the tradeoffs on storage, durability, and future-proofness are worth skipping the marginal overhead to load a Parquet file into memory.
- DasIch 4y ago
  Isn't that what Feather, the arrow file format, is? https://arrow.apache.org/docs/python/feather.html https://arrow.apache.org/docs/python/feather.html
deleted 4y ago
[deleted]
alexott 4y ago
I would suggest to look onto Delta Lake (https://delta.io/ https://delta.io/) - it's built on top of the Parquet, but has advantages over the plain parquet: - transactions - you don't get a garbage in your table if your write failed - supports update/delete/merge operations (in some implementations) - metadata allows faster discovery of data, for example, if you have a lot of partitions on the cloud storage. - metadata also allow to support features like data skipping, when you can filter out files that doesn't contain necessary data - time travel - you can get back to previous versions of the tables
sunng 4y ago
While using data lake for this case is a good idea. I'm not sure if parquet is the particular good storage format for data like event log, audio log. The data is often row oriented and does not take full advantage of a column based format.
jhoechtl 4y ago
Wouldn't it be nice if Postgresql could gain a native new storage engine which serializes columnar data as parquet files? Does this even make sense? DuckDB already has fine Parquet support and I find it really helpful.
- skeletal88 4y ago
  No need to change the database itself, these kind of features belong in foreign data wrappers. You can already treat csv files as tables or access tables in other databases(mysql, oracle etc)
  - eatonphil 4y ago
    With a massive performance impact (for reading csvs anyway). It's a matter of how much you want to keep using postgres versus if you want interactive-level performance. I'm not saying one is better or worse than the other but they are definitely not the same.
- tomnipotent 4y ago
  > Does this even make sense? Yes, but may not be practical. A non-trivial amount of code makes assumptions about the underlying data layout, so things like the cost-based optimizer and MVCC would basically need to be rewritten.
- masklinn 4y ago
  > Wouldn't it be nice if Postgresql could gain a native new storage engine which serializes columnar data as parquet files? Does this even make sense? Surely that's exactly what parquet_fdw is?
- pramsey 4y ago
  There is the cstore extension, which is pretty much "native" (at some point these terms start to lose meaning... it's running in the same process space and transaction aware, etc.)
bjackman 4y ago
For [Ex-]Googlers: I thought this sounded a bit like Capacitor and indeed it is mentioned in this page: https://cloud.google.com/blog/products/bigquery/inside-capacitor-bigquerys-next-generation-columnar-storage-format https://cloud.google.com/blog/products/bigquery/inside-capac... (I don't think there's a direct analogue to Postgres though? Maybe people sometimes take data out of Spanner and put it in a Capacitor file instead sometimes?)
- flakiness 4y ago
  And for Non-Googlers: BigQuery does support Parquet in the lake. If you don't need PQ compatibility this is an easy option. https://cloud.google.com/bigquery/external-data-cloud-storage https://cloud.google.com/bigquery/external-data-cloud-storag... People dump data from the main OLTP to the "lake" or some OLAP database and call it data-warehouse vs. Internet-oriented companies like Google tend to focus more on querying the log and its aggregated data.
iknownothow 4y ago
After reading a few comments here, here's the experiment I'm going to run this weekend to couple Postgres (AWS rds) and DuckDB There's already an FDW for DuckDB [1]! But it's not available from within AWS RDS because they only allow a few extensions. So, I would launch an EC2 instance and launch a postgres instance on it. Then I would install the DuckDB FDW on this postgres instance. Then, on the AWS RDS postgres instance, I would use the postgres_fdw extension to connect to the postgres instance running on EC2 and indirectly gain access to it's DuckDB FDW. I'm not sure if this chained FDW strategy will work out. But here's why I think it's a great idea: 1. Any data that I am willing to put into DuckDB is guaranteed to have backups and/or an ETL already setup to sync data. Therefore I don't need this EC2 setup to be "reliable". If it goes down, I'll just rerun the ETL. 2. I need to pay a small DevOps cost to have some performance gain but the analytics complexity remains unchanged because at the end of the day it's just Postgres. I get to continue to use DBT against the original AWS RDS. The only thing I need to see is if there are any performance gains to be had using this setup. I won't take that for granted until it's tested. [1] https://github.com/alitrack/duckdb_fdw https://github.com/alitrack/duckdb_fdw
- lysecret 4y ago
  How did it go? It sounds super interesting.
mildbyte 4y ago
Another similar option is cstore_fdw [0] -- it's now part of Citus but can still be used standalone as a foreign data wrapper. We use it at my startup to do OLAP on Postgres. It has some advantages on parquet_fdw: * Supports writes (actually generating Parquet files was also difficult in my testing: I used odbc2parquet [1] but thanks for the ogr2ogr tip!) so you can write directly to the foreign table by running INSERT INTO ... SELECT FROM ... * Supports all PG datatypes (including types from extensions, like PostGIS) * Performance was basically comparable in my limited testing (faster for a single-row SELECT with cstore_fdw in our case since we do partition pruning, same for a full-table scan-and-aggregation). Re: performance overhead, with FDWs we have to re-munge the data into PostgreSQL's internal row-oriented TupleSlot format again. Postgres also doesn't run aggregations that can take advantage of the columnar format (e.g. CPU vectorization). Citus had some experimental code to get that working [2], but that was before FDWs supported aggregation pushdown. Nowadays it might be possible to basically have an FDW that hooks into the GROUP BY execution and runs a faster version of the aggregation that's optimized for columnar storage. We have a blog post series [3] about how we added agg pushdown support to Multicorn -- similar idea. There's also DuckDB which obliterates both of these options when it comes to performance. In my (again limited, not very scientific) benchmarking of on a customer's 3M row table [4] (278MB in cstore_fdw, 140MB in Parquet), I see a 10-20x (1/2s -> 0.1/0.2s) speedup on some basic aggregation queries when querying a Parquet file with DuckDB as opposed to using cstore_fdw/parquet_fdw. I think the dream is being able to use DuckDB from within a FDW as an OLAP query engine for PostgreSQL. duckdb_fdw [5] exists, but it basically took sqlite_fdw and connected it to DuckDB's SQLite interface, which means that a lot of operations get lost in translation and aren't pushed down to DuckDB, so it's not much better than plain parquet_fdw. I had a complex query in the PG dialect generated with dbt that used joins, CTEs and window functions. I don't remember the exact timings, but it was even slower on duckdb_fdw than with cstore_fdw, whereas I could take the same query and run it on DuckDB verbatim, only replacing the foreign table name with the Parquet filename. This comment is already getting too long, but FDWs can indeed participate in partitions! There's this blog post that I keep meaning to implement where the setup is, a "coordinator" PG instance has a partitioned table, where each partition is a postgres_fdw foreign table that proxies to a "data" PG instance. The "coordinator" node doesn't store any data and only gathers execution results from the "data" nodes. In the article, the "data" nodes store plain old PG tables, but I don't think there's anything preventing them from being parquet_fdw/cstore_fdw tables instead. [0] https://github.com/citusdata/cstore_fdw https://github.com/citusdata/cstore_fdw [1] https://github.com/pacman82/odbc2parquet https://github.com/pacman82/odbc2parquet [2] https://github.com/citusdata/postgres_vectorization_test https://github.com/citusdata/postgres_vectorization_test [3] https://www.splitgraph.com/blog/postgresql-fdw-aggregation-pushdown-multicorn-part-1 https://www.splitgraph.com/blog/postgresql-fdw-aggregation-p... [4] https://www.splitgraph.com/trase/supply-chains https://www.splitgraph.com/trase/supply-chains [5] https://github.com/alitrack/duckdb_fdw https://github.com/alitrack/duckdb_fdw [6] https://swarm64.com/post/scaling-elastic-postgres-cluster/ https://swarm64.com/post/scaling-elastic-postgres-cluster/
eatonphil 4y ago
This is a great post demonstrating the wide variety of things you can do with postgres foreign data wrappers. But I've worked on analytics systems built on this and it should just be clear that when you use this kind of system you're accepting ease of operation/ease of integration for actual interactive-level performance. At some point if you want interactive-level performance on this historic data (which is very much possible) you'll have to start putting this data in clickhouse or elasticsearch and some system on top of postgres with a better sharing scheme. If you wanted better performance without giving up the postgres frontend it would (to me) make more sense for folks to invest in building FDWs for elastic or clickhouse rather than over parquet or csv. But that's just a theory.

90 points by pramsey 4y ago

miohtama 4y ago

Please correct if I am wrong but Parquet is columnar store whereas PSQL is row based?

vladsanchez 4y ago
Experimental: https://github.com/adjust/parquet_fdw/ https://github.com/adjust/parquet_fdw/
pramsey 4y ago
Parquet is a column-based file format. PostgreSQL is a SQL query engine, and the default storage is row-oriented, but the engine can do all kinds of fun things, hence the parquet_fdw extension and the cstore extension for example.

pella 4y ago

> "Generating Parquet" It is also useful for moving data from Postgres to BigQuery! ( batch load ) https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-parquet https://cloud.google.com/bigquery/docs/loading-data-cloud-st... Thanks for the "ogr2ogr" trick! :-) I hope the next blog post will be about GeoParquet and storing complex geometries in parquet format :-) https://github.com/opengeospatial/geoparquet https://github.com/opengeospatial/geoparquet

rockostrich 4y ago
Cloud SQL has BigQuery connections that can be leveraged. But yea, this seems like a nice solution if you have a postgres instance outside of Cloud SQL. Another approach would be to write the CDC to a message queue and archive that to parquet.
pramsey 4y ago
GeoParquet just embeds the geometry as WKB, so getting PostGIS geometry is as simple as going "SELECT geom::geometry FROM my_parq_fdw_table". The spec doesn't yet have anything to say about how to get a good spatial sort or carry out a spatial filter, so it's all pretty ad hoc on that end. Thanks to ogr2ogr, generating a GeoParquet file is now pretty straightforward.

sharadov 4y ago

Thank you, this is really cool!

jaxrtech 4y ago

If you were ready to throw away a bit of archival quality, I wonder if writing Apache Arrow straight to disk and then just mmap your columns back in for a query.

teej 4y ago
It's certainly possible. But Parquet and Arrow are being built with each other in mind. I doubt the tradeoffs on storage, durability, and future-proofness are worth skipping the marginal overhead to load a Parquet file into memory.
DasIch 4y ago
Isn't that what Feather, the arrow file format, is? https://arrow.apache.org/docs/python/feather.html https://arrow.apache.org/docs/python/feather.html

deleted 4y ago

[deleted]

alexott 4y ago

I would suggest to look onto Delta Lake (https://delta.io/ https://delta.io/) - it's built on top of the Parquet, but has advantages over the plain parquet: - transactions - you don't get a garbage in your table if your write failed - supports update/delete/merge operations (in some implementations) - metadata allows faster discovery of data, for example, if you have a lot of partitions on the cloud storage. - metadata also allow to support features like data skipping, when you can filter out files that doesn't contain necessary data - time travel - you can get back to previous versions of the tables

sunng 4y ago

While using data lake for this case is a good idea. I'm not sure if parquet is the particular good storage format for data like event log, audio log. The data is often row oriented and does not take full advantage of a column based format.

jhoechtl 4y ago

Wouldn't it be nice if Postgresql could gain a native new storage engine which serializes columnar data as parquet files? Does this even make sense? DuckDB already has fine Parquet support and I find it really helpful.

skeletal88 4y ago
No need to change the database itself, these kind of features belong in foreign data wrappers. You can already treat csv files as tables or access tables in other databases(mysql, oracle etc)
- eatonphil 4y ago
  With a massive performance impact (for reading csvs anyway). It's a matter of how much you want to keep using postgres versus if you want interactive-level performance. I'm not saying one is better or worse than the other but they are definitely not the same.
tomnipotent 4y ago
> Does this even make sense? Yes, but may not be practical. A non-trivial amount of code makes assumptions about the underlying data layout, so things like the cost-based optimizer and MVCC would basically need to be rewritten.
masklinn 4y ago
> Wouldn't it be nice if Postgresql could gain a native new storage engine which serializes columnar data as parquet files? Does this even make sense? Surely that's exactly what parquet_fdw is?
pramsey 4y ago
There is the cstore extension, which is pretty much "native" (at some point these terms start to lose meaning... it's running in the same process space and transaction aware, etc.)

bjackman 4y ago

For [Ex-]Googlers: I thought this sounded a bit like Capacitor and indeed it is mentioned in this page: https://cloud.google.com/blog/products/bigquery/inside-capacitor-bigquerys-next-generation-columnar-storage-format https://cloud.google.com/blog/products/bigquery/inside-capac... (I don't think there's a direct analogue to Postgres though? Maybe people sometimes take data out of Spanner and put it in a Capacitor file instead sometimes?)

flakiness 4y ago
And for Non-Googlers: BigQuery does support Parquet in the lake. If you don't need PQ compatibility this is an easy option. https://cloud.google.com/bigquery/external-data-cloud-storage https://cloud.google.com/bigquery/external-data-cloud-storag... People dump data from the main OLTP to the "lake" or some OLAP database and call it data-warehouse vs. Internet-oriented companies like Google tend to focus more on querying the log and its aggregated data.

iknownothow 4y ago

After reading a few comments here, here's the experiment I'm going to run this weekend to couple Postgres (AWS rds) and DuckDB There's already an FDW for DuckDB [1]! But it's not available from within AWS RDS because they only allow a few extensions. So, I would launch an EC2 instance and launch a postgres instance on it. Then I would install the DuckDB FDW on this postgres instance. Then, on the AWS RDS postgres instance, I would use the postgres_fdw extension to connect to the postgres instance running on EC2 and indirectly gain access to it's DuckDB FDW. I'm not sure if this chained FDW strategy will work out. But here's why I think it's a great idea: 1. Any data that I am willing to put into DuckDB is guaranteed to have backups and/or an ETL already setup to sync data. Therefore I don't need this EC2 setup to be "reliable". If it goes down, I'll just rerun the ETL. 2. I need to pay a small DevOps cost to have some performance gain but the analytics complexity remains unchanged because at the end of the day it's just Postgres. I get to continue to use DBT against the original AWS RDS. The only thing I need to see is if there are any performance gains to be had using this setup. I won't take that for granted until it's tested. [1] https://github.com/alitrack/duckdb_fdw https://github.com/alitrack/duckdb_fdw

lysecret 4y ago
How did it go? It sounds super interesting.

mildbyte 4y ago

Another similar option is cstore_fdw [0] -- it's now part of Citus but can still be used standalone as a foreign data wrapper. We use it at my startup to do OLAP on Postgres. It has some advantages on parquet_fdw: * Supports writes (actually generating Parquet files was also difficult in my testing: I used odbc2parquet [1] but thanks for the ogr2ogr tip!) so you can write directly to the foreign table by running INSERT INTO ... SELECT FROM ... * Supports all PG datatypes (including types from extensions, like PostGIS) * Performance was basically comparable in my limited testing (faster for a single-row SELECT with cstore_fdw in our case since we do partition pruning, same for a full-table scan-and-aggregation). Re: performance overhead, with FDWs we have to re-munge the data into PostgreSQL's internal row-oriented TupleSlot format again. Postgres also doesn't run aggregations that can take advantage of the columnar format (e.g. CPU vectorization). Citus had some experimental code to get that working [2], but that was before FDWs supported aggregation pushdown. Nowadays it might be possible to basically have an FDW that hooks into the GROUP BY execution and runs a faster version of the aggregation that's optimized for columnar storage. We have a blog post series [3] about how we added agg pushdown support to Multicorn -- similar idea. There's also DuckDB which obliterates both of these options when it comes to performance. In my (again limited, not very scientific) benchmarking of on a customer's 3M row table [4] (278MB in cstore_fdw, 140MB in Parquet), I see a 10-20x (1/2s -> 0.1/0.2s) speedup on some basic aggregation queries when querying a Parquet file with DuckDB as opposed to using cstore_fdw/parquet_fdw. I think the dream is being able to use DuckDB from within a FDW as an OLAP query engine for PostgreSQL. duckdb_fdw [5] exists, but it basically took sqlite_fdw and connected it to DuckDB's SQLite interface, which means that a lot of operations get lost in translation and aren't pushed down to DuckDB, so it's not much better than plain parquet_fdw. I had a complex query in the PG dialect generated with dbt that used joins, CTEs and window functions. I don't remember the exact timings, but it was even slower on duckdb_fdw than with cstore_fdw, whereas I could take the same query and run it on DuckDB verbatim, only replacing the foreign table name with the Parquet filename. This comment is already getting too long, but FDWs can indeed participate in partitions! There's this blog post that I keep meaning to implement where the setup is, a "coordinator" PG instance has a partitioned table, where each partition is a postgres_fdw foreign table that proxies to a "data" PG instance. The "coordinator" node doesn't store any data and only gathers execution results from the "data" nodes. In the article, the "data" nodes store plain old PG tables, but I don't think there's anything preventing them from being parquet_fdw/cstore_fdw tables instead. [0] https://github.com/citusdata/cstore_fdw https://github.com/citusdata/cstore_fdw [1] https://github.com/pacman82/odbc2parquet https://github.com/pacman82/odbc2parquet [2] https://github.com/citusdata/postgres_vectorization_test https://github.com/citusdata/postgres_vectorization_test [3] https://www.splitgraph.com/blog/postgresql-fdw-aggregation-pushdown-multicorn-part-1 https://www.splitgraph.com/blog/postgresql-fdw-aggregation-p... [4] https://www.splitgraph.com/trase/supply-chains https://www.splitgraph.com/trase/supply-chains [5] https://github.com/alitrack/duckdb_fdw https://github.com/alitrack/duckdb_fdw [6] https://swarm64.com/post/scaling-elastic-postgres-cluster/ https://swarm64.com/post/scaling-elastic-postgres-cluster/

eatonphil 4y ago

This is a great post demonstrating the wide variety of things you can do with postgres foreign data wrappers. But I've worked on analytics systems built on this and it should just be clear that when you use this kind of system you're accepting ease of operation/ease of integration for actual interactive-level performance. At some point if you want interactive-level performance on this historic data (which is very much possible) you'll have to start putting this data in clickhouse or elasticsearch and some system on top of postgres with a better sharing scheme. If you wanted better performance without giving up the postgres frontend it would (to me) make more sense for folks to invest in building FDWs for elastic or clickhouse rather than over parquet or csv. But that's just a theory.