6 ms·

Really cool! I have an IoT use-case where I ingest data, I want to keep like the last 3 months or so in Postgresql, and then store the old data as parquet files

by oulipo 2y ago

Really cool! I have an IoT use-case where I ingest data, I want to keep like the last 3 months or so in Postgresql, and then store the old data as parquet files on S3 I planned initially to do chunks on S3 and do the analytical queries using duckdb, I'm wondering if your tool would be a good replacement? For now I don't have that many analytical queries, I'm mostly doing visualization of the data points by querying a range (eg last 2 weeks of data for a device) Does it then make sense to use columnar storage or am I better off with "regular Postgres"? Or in my case does your approach provide "best of both worlds" in the sense that I could do some occasional analytical queries on past data stored on S3, and regularly access "last 3 months" data for visualization using the data stored in the regular Postgres?

exAspArk 2y ago
Thank you! Yes, absolutely! 1) You could use BemiDB to sync your Postgres data (e.g., partition time-series tables) to S3 in Iceberg format. Iceberg is essentially a "table" abstraction on top of columnar Parquet data files with a schema, history, etc. 2) If you don't need strong consistency and fine with delayed data (the main trade-off), you can use just BemiDB to query and visualize all data directly from S3. From a query perspective, it's like DuckDB that talks Postgres (wire protocol). Feel free to give it a try! And although it's a new project, we plan to keep building and improving it based on user feedback.
- oulipo 2y ago
  Thanks! - Can you give me more info about the strong consistency and delayed data, so I can better picture it with a few examples? - Also, is it possible to do the sync with the columnar data in "more-or-less real-time" (eg do a NOTIFY on a new write in my IoT events table, and push in the storage?) - Would your system also be suited for a kind of "audit-log" data? Eg. if I want to have some kind of audit-table of all the changes in my database, but only want to keep a few weeks worth at hand, and then push the rest on S3, or it doesn't make much sense with that kind of data?
  - exAspArk 2y ago
    For now, BemiDB supports only full Postgres table data re-sync. We plan to enable real-time data syncing from Postgres into S3 by using logical replication (CDC), which is much more reliable than PG NOTIFY. We use logical replication and this exact approach with our other project related to auditing and storing Postgres data changes https://github.com/BemiHQ/bemi https://github.com/BemiHQ/bemi. We're thinking about combining these approaches to leverage scalable and affordable separated storage layer on S3. Lmk if that makes sense or if you had any more questions!
    - oulipo 2y ago
      Really interesting thanks! I guess my use-case would rather require incremental updates Ideally it would just sync in real-time and buffer new data in the Bemi binary (with some WAL-like storage to make sure data is preserved on binary crash/reload), and when it has enough, push them on S3, etc Is this the kind of approach you're going to take?
      - exAspArk 2y ago
        Yes, we want to use the approach like you described! We'll likely wait until enough changes are accumulated by using 2 configurable thresholds: time (like 30s) and size (like 100MB)
Onavo 2y ago
Use a clickhouse FDW or something similar, clickhouse has excellent integration with postgres. They also have a great embedded Python version. Their marketing isn't as good as Duckdb but in terms of stability and performance they are so much better. Duckdb is very very buggy and full of sharp edges but because of their VC funded developer marketing, you don't really hear people talking about it.
- exAspArk 2y ago
  I agree that DuckDB may sometimes be buggy because it's being actively developed with a large surface area (a universal embeddable query engine that works with different storage layers if I were to simplify). However, DuckDB (non-profit foundation) != MotherDuck (VC funded). These are two separate organizations with different goals. I see DuckDB as a tool, not as a SaaS or a VC-funded company. My hope is that it'll be adopted by other projects and not associated with just a single for-profit company.
- thawab 2y ago
  Why are you painting Clickhouse as an underdog, with no VC funding? They raised a $250M Series B.
jconline543 2y ago
If you just need the Postgres -> S3 archival pattern you described, I built a simpler focused tool: pg-archiver (https://github.com/johnonline35/pg-archiver https://github.com/johnonline35/pg-archiver) It: - Auto-archives old Postgres data to Parquet files on S3 - Keeps recent data (default 90 days) in Postgres for fast viz queries - Uses year/month partitioning in S3 for basic analytical queries - Configures with just PG connection string and S3 bucket Currently batch-only archival (no real-time sync yet). Much lighter than running a full analytical DB if you mainly need timeseries visualization with occasional historical analysis. Let me know if you try it out!
- oulipo 2y ago
  Really cool! I'll take a look! - can you then easily query it with duckdb / clickhouse / something else? What do you use yourself? do you have some tutorial / toy example to check? - would it be complicated to have the real-time data be also stored somehow on S3 so it would be "transparent" to do query on historical data which includes day data? - what typical "batch data" size makes sense, I guess doing "day batches" might be a bit small and will incurr too many "read" operations (if I have moderate amount of day data), rather than "week batches"? but then the "timelag" increases?
  - jconline543 2y ago
    Thanks for your interest! Let me address your questions: Querying the data: Yes, you can easily query the Parquet files with DuckDB. The files are stored in a year/month partitioned structure (e.g., year=2024/month=03/iot_data_20240315_143022.parquet), which makes it efficient to query specific time ranges. I personally use DuckDB for ad-hoc analysis since it works great with Parquet. Here's a quick example: sqlCopySELECT * FROM read_parquet('s3://my-iot-archive/year=/month=/iot_data_*.parquet') WHERE timestamp BETWEEN '2023-01-01' AND '2023-12-31' Real-time data on S3: Currently, the tool is batch-focused. Adding real-time sync would require some architectural changes - either using CDC (Change Data Capture) or implementing a dual-write pattern. I kept it simple for now since most IoT visualization use cases I've seen focus on recent data in Postgres. If you need this feature, I'd be happy to take a look at what you want. For data processing the tool works like this: It identifies records older than 90 days (configurable retention period) Processes these records in batches of 100 (also configurable) to manage memory usage Creates Parquet files partitioned by year/month in S3 Deletes the archived records from Postgres The key is that you always have your recent 90 days in Postgres for fast querying, while maintaining older data in a cost-effective S3 storage that you can still query when needed. You can adjust both the retention period and batch size based on your specific needs. Let me know if you'd like me to clarify anything or if you have other questions!

by oulipo 2y ago

exAspArk 2y ago

Thank you! Yes, absolutely! 1) You could use BemiDB to sync your Postgres data (e.g., partition time-series tables) to S3 in Iceberg format. Iceberg is essentially a "table" abstraction on top of columnar Parquet data files with a schema, history, etc. 2) If you don't need strong consistency and fine with delayed data (the main trade-off), you can use just BemiDB to query and visualize all data directly from S3. From a query perspective, it's like DuckDB that talks Postgres (wire protocol). Feel free to give it a try! And although it's a new project, we plan to keep building and improving it based on user feedback.

oulipo 2y ago
Thanks! - Can you give me more info about the strong consistency and delayed data, so I can better picture it with a few examples? - Also, is it possible to do the sync with the columnar data in "more-or-less real-time" (eg do a NOTIFY on a new write in my IoT events table, and push in the storage?) - Would your system also be suited for a kind of "audit-log" data? Eg. if I want to have some kind of audit-table of all the changes in my database, but only want to keep a few weeks worth at hand, and then push the rest on S3, or it doesn't make much sense with that kind of data?
- exAspArk 2y ago
  For now, BemiDB supports only full Postgres table data re-sync. We plan to enable real-time data syncing from Postgres into S3 by using logical replication (CDC), which is much more reliable than PG NOTIFY. We use logical replication and this exact approach with our other project related to auditing and storing Postgres data changes https://github.com/BemiHQ/bemi https://github.com/BemiHQ/bemi. We're thinking about combining these approaches to leverage scalable and affordable separated storage layer on S3. Lmk if that makes sense or if you had any more questions!
  - oulipo 2y ago
    Really interesting thanks! I guess my use-case would rather require incremental updates Ideally it would just sync in real-time and buffer new data in the Bemi binary (with some WAL-like storage to make sure data is preserved on binary crash/reload), and when it has enough, push them on S3, etc Is this the kind of approach you're going to take?
    - exAspArk 2y ago
      Yes, we want to use the approach like you described! We'll likely wait until enough changes are accumulated by using 2 configurable thresholds: time (like 30s) and size (like 100MB)

Onavo 2y ago

Use a clickhouse FDW or something similar, clickhouse has excellent integration with postgres. They also have a great embedded Python version. Their marketing isn't as good as Duckdb but in terms of stability and performance they are so much better. Duckdb is very very buggy and full of sharp edges but because of their VC funded developer marketing, you don't really hear people talking about it.

exAspArk 2y ago
I agree that DuckDB may sometimes be buggy because it's being actively developed with a large surface area (a universal embeddable query engine that works with different storage layers if I were to simplify). However, DuckDB (non-profit foundation) != MotherDuck (VC funded). These are two separate organizations with different goals. I see DuckDB as a tool, not as a SaaS or a VC-funded company. My hope is that it'll be adopted by other projects and not associated with just a single for-profit company.
thawab 2y ago
Why are you painting Clickhouse as an underdog, with no VC funding? They raised a $250M Series B.

jconline543 2y ago

If you just need the Postgres -> S3 archival pattern you described, I built a simpler focused tool: pg-archiver (https://github.com/johnonline35/pg-archiver https://github.com/johnonline35/pg-archiver) It: - Auto-archives old Postgres data to Parquet files on S3 - Keeps recent data (default 90 days) in Postgres for fast viz queries - Uses year/month partitioning in S3 for basic analytical queries - Configures with just PG connection string and S3 bucket Currently batch-only archival (no real-time sync yet). Much lighter than running a full analytical DB if you mainly need timeseries visualization with occasional historical analysis. Let me know if you try it out!

oulipo 2y ago
Really cool! I'll take a look! - can you then easily query it with duckdb / clickhouse / something else? What do you use yourself? do you have some tutorial / toy example to check? - would it be complicated to have the real-time data be also stored somehow on S3 so it would be "transparent" to do query on historical data which includes day data? - what typical "batch data" size makes sense, I guess doing "day batches" might be a bit small and will incurr too many "read" operations (if I have moderate amount of day data), rather than "week batches"? but then the "timelag" increases?
- jconline543 2y ago
  Thanks for your interest! Let me address your questions: Querying the data: Yes, you can easily query the Parquet files with DuckDB. The files are stored in a year/month partitioned structure (e.g., year=2024/month=03/iot_data_20240315_143022.parquet), which makes it efficient to query specific time ranges. I personally use DuckDB for ad-hoc analysis since it works great with Parquet. Here's a quick example: sqlCopySELECT * FROM read_parquet('s3://my-iot-archive/year=/month=/iot_data_*.parquet') WHERE timestamp BETWEEN '2023-01-01' AND '2023-12-31' Real-time data on S3: Currently, the tool is batch-focused. Adding real-time sync would require some architectural changes - either using CDC (Change Data Capture) or implementing a dual-write pattern. I kept it simple for now since most IoT visualization use cases I've seen focus on recent data in Postgres. If you need this feature, I'd be happy to take a look at what you want. For data processing the tool works like this: It identifies records older than 90 days (configurable retention period) Processes these records in batches of 100 (also configurable) to manage memory usage Creates Parquet files partitioned by year/month in S3 Deletes the archived records from Postgres The key is that you always have your recent 90 days in Postgres for fast querying, while maintaining older data in a cost-effective S3 storage that you can still query when needed. You can adjust both the retention period and batch size based on your specific needs. Let me know if you'd like me to clarify anything or if you have other questions!