4 ms·

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 htt

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!
  - jconline543 2y ago
    Also, for querying both recent and historical data together, you wouldn't need to modify this tool at all. You could just add a separate periodic job (e.g. hourly/daily) that copies recent data to S3: sqlCopyCOPY (SELECT * FROM iot_data WHERE timestamp > current_date - interval '90 days') TO 's3://bucket/recent/iot_data.parquet' (FORMAT 'parquet') Then query everything together in DuckDB: sqlCopySELECT * FROM read_parquet([ 's3://bucket/year=*/month=*/iot_data_\*.parquet', -- archived data 's3://bucket/recent/iot_data.parquet' -- recent data ]) Much simpler than implementing real-time sync, and you still get a unified view of all your data for analysis (just with a small delay on recent data).

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!
- jconline543 2y ago
  Also, for querying both recent and historical data together, you wouldn't need to modify this tool at all. You could just add a separate periodic job (e.g. hourly/daily) that copies recent data to S3: sqlCopyCOPY (SELECT * FROM iot_data WHERE timestamp > current_date - interval '90 days') TO 's3://bucket/recent/iot_data.parquet' (FORMAT 'parquet') Then query everything together in DuckDB: sqlCopySELECT * FROM read_parquet([ 's3://bucket/year=*/month=*/iot_data_\*.parquet', -- archived data 's3://bucket/recent/iot_data.parquet' -- recent data ]) Much simpler than implementing real-time sync, and you still get a unified view of all your data for analysis (just with a small delay on recent data).