Could you use the SQLite FDW - https://github.com/pgspider/sqlite_fdw ?
Steve On Wed, Dec 22, 2021 at 1:27 PM David Gauthier <davegauthie...@gmail.com> wrote: > Hi: I need a sanity check (brainstorming) before I jump into coding > something that might have a better solution known to a community like this > one. Here's the situation... > > To begin with... PG 11.5 on linux. > Some code (nature unimportant to this discussion) generates a lot (a lot) > of data and stuffs it in a SQLite DB which, once the process is complete, > gets zipped (compression ratio seems to be about 5). We want to keep this > data in a persistent store which others can get at should they need it. > PG seems like a great place to put this especially as the identifying > context of the SQLite already exists in our PG DB. > > So I was thinking about storing the zipped SQLite as a blob in PG. The > record it would be added to would give it all the context needed for proper > retrieval. After retrieval (in a perl script) I was thinking about writing > it out to the shell, unzipping it and then opening it using perl/DBI. The > metadata of the SQLite could be replicated in a temp table in PG and the > data content loaded into that for use. In theory, multiple SQLite DBs > could be opened like this and then loaded in the same temp tables (as long > as the metadata is the same... which it will be). > > OK, so that's the plan. What I want to ask this community about is > whether or not there's a better way to approach this. Brute force loading > the SQLite data in regular PG tables would result in billions of records > and the DB management issues that come with that. And there's really no > need to keep all that data immediately accessible like that. Is there some > sort of innate PG functionality that would allow me to store and then > compress my data for targeted retrieval/decompression (even if it's not > accessible to SQL in that form) ? > > OK, you get the picture. I'm all ears :-) And thanks in advance for any > suggestions ! >
