Comment by cjonas
2 years ago
What's the workflow for leveraging this extension in real-time for an existing database?
Say I wanted to use this to create a high performance "aggregation" API of my existing "write heavy" tables.
Is there a way to keep a `heap` & `columnar` table in sync?
(relative Postgres noob here)
You could use ETL tools like peerdb.io. This isn't "real time" but instead some refresh interval. ZomboDB uses ElasticSearch as an index that is transactionally consistent with Postgres. It gives hope that in the future we will see consistent columnar tables or indexes. SQL Server supports columnar indexes on non-columnar tables.
there are a couple of ways to do it, and none of them that I'm able to think of are great - maybe some others will be able to answer better than I am, but ...
if the data is append-only, an insert trigger could work. if it gets updated and deleted, then insert, update, and delete triggers could be added. of course if the table is very active, this could get bad, fast.
alternately, you can do an insert every hour or so, like insert into table_columnar where created_at > DATE_TRUNC('hour', created_at)
or, even truncate the columnar table daily and re-insert all of the data.
likely none of these is the _best_ solution, but they could help you find what might be the best solution for you.
alternately, if the query patterns work well, you can simply convert the table to columnar, but that's not a panacea.