Hi, I need advice on which Database tool to use in the following scenario:
I work with Cesium, and I need to save and load CZML snapshot and update objects for a recording program that saves files containing several entities (along with the time of the snapshot or update). I need to be able to easily load the files according to the corresponding timeline point (for example, if the update was recorded at 13:15, I should be able to easily load the update file when I click on the 13:15 point on the timeline). I should also be able to make geo-queries relatively easily.
I am currently thinking about Elasticsearch or PostgreSQL, but I am open to suggestions. I tried looking into Time Series Databases like TimescaleDB but found that it is unnecessarily powerful than my needs since the update time is a simple variable.
Thanks for your advice in advance!
In your situation, PostgreSQL seems to be better option. Why? 1) Saving structured data is possible in both PostgreSQL and Elasticsearch. In PostgreSQL, there is JSONB column available and you can build indexes on top of it. 2) If you are able to specify the time as a primary key, both Elasticsearch and PostgreSQL are great options. 3) PostgreSQL allows you to do a lot more with your data and handle them in a relation way. You are not clear whether it's a benefit or not but let's consider extensibility to be an advantage. 4) PostgreSQL comes with PostGIS extension to work with geo data. May be useful for your situation. 5) PostgreSQL may serve for other needs of your app. Managing one database is always better than having two of them.
Thanks to JSONB column type, PostgreSQL is a sweet combination of relational and noSQL database, but there are also drawbacks coming from ACID compliancy and WAL overhead for rapid changes.