This section describes the database for the "hello world" phase of the OSS project.
The database stores observational data sent from the VLA and GBT sites.
The OSS API software is the only software interacting with the database and, thus, is responsible for loading and retrieving all data into/from the database.
A PostgreSQL server running on the test computer c3po is hosting the database. The computer is located at the DSOC.
After the development and test phase, the database will move to the production computer (TBD). (chewbacca, turing?)
The database is named oss. *
It has two usernames: oss, which has read-write permissions, and oss_ro, which has read-only permission.
*A script named create-database, developed and maintained by the SSA group to standardize NRAO databases, was used to initialize and create the database and usernames.
Currently, the database uses a single table named oss_data for storing data. A second table for storing site data is under consideration.
The table has three columns: id, created_utc, and site_data.
The data stored in the site_data column are JSON documents sent from the VLA and GBT sites. Here's a sample from the VLA:
{"notes": "inAdv:True", "obs_id": "23A-214.sb44109940", "src_id": "J1415+1320", "src_ra": 213.99507291249998, "site_id": "vla", "src_dec": 13.339920166666666, "site_lat": 34.07874917, "site_lon": -107.6177275, "site_elev": 2124.0, "freq_lower": 4000000000.0, "freq_upper": 8000000000.0, "src_end_utc": "2023-07-21T05:22:56.000393", "trk_rate_ra": 0.0, "trk_rate_dec": 0.0, "src_start_utc": "2023-07-21T05:21:16.000939"}
Index on the JSON key site_data->>src_end_utc:
Using EXPLAIN ANALYZE shows the improvement in execution time gained by using the index.
Query (without index): SELECT * from oss_data WHERE (site_data->>'src_end_utc')::timestamp > now();
Query (with index): SELECT * FROM oss_data WHERE text_to_timestamp(site_data->>'src_end_utc') > now();
Index Scan using oss_data_text_to_timestamp_idx on oss_data (cost=0.29..8.35 rows=3 width=453) (actual time=0.044..0.046 rows=6 loops=1)
Index Cond: (text_to_timestamp((site_data ->> 'src_end_utc'::text)) > now())
Planning time: 0.512 ms
Execution time: 0.078 ms
(TBD)
To be addressed after the "hello world" phase.