Not directly linked to migration (although I'd love to know the best way to handle those when you have a kind of Datalake with big number of past tables that have been archived on S3)
I'm trying to move my database to Postgres, there is a part which is "describing all the objects" (object id, properties, etc), and a huge table which is a log of events, that I'm storing in case I want to data-mine it later.
Of course this last table is:
huge (or should become huge at some point)
better suited by columnar storage
might be archived from time to time on S3
My initial thinking was to store it in Postgres "natively" or as a "duckdb/clickhouse" extension with postgres-querying capabilities, keep the last 90 days of data in the database, and regularly have a script to export the rest as Parquet files on S3
does this seem reasonable? is there a "best practice" to do this?
I also want to do the same with "audit logs" of everything going in the system (modifications to the fields, actions taken by users on the dashboard, etc)
I'm trying to move my database to Postgres, there is a part which is "describing all the objects" (object id, properties, etc), and a huge table which is a log of events, that I'm storing in case I want to data-mine it later.
Of course this last table is:
huge (or should become huge at some point) better suited by columnar storage might be archived from time to time on S3 My initial thinking was to store it in Postgres "natively" or as a "duckdb/clickhouse" extension with postgres-querying capabilities, keep the last 90 days of data in the database, and regularly have a script to export the rest as Parquet files on S3
does this seem reasonable? is there a "best practice" to do this?
I also want to do the same with "audit logs" of everything going in the system (modifications to the fields, actions taken by users on the dashboard, etc)
what would you recommend?