1. How granular are the snapshots? Can we select specific tables and views from the source database?
A snapshot is created on the whole standby database, not a subset of it. This is totally different than logical replication. When a snapshot is created from the standby database, that snapshot will be a copy of the standby database at the point in time when the snapshot was created. There is no option to do this for tables or subset of the database it is the full database.
2. Are the snapshots from a standby database or a read/write database?
Yes, the snapshots are created from the standby database, meaning we place the standby database in a consistent state, take a snapshot, then place the standby back in recovery mode. The snapshot can be opened read/write or read/only. Currently, in the Snapshot Group option, the snapshots are started read/only.
3. Can we add indexes and views to the snapshot after it is created that is not on the source database?
- If you create an individual snapshot, you have the option to have this snapshot open in a read/write or read/only state. If you want to create additional indexes, views you can make use of the read/write option. Any changes made to the snapshot is only affecting the snapshot. But do keep in mind snapshots is ideally short-lived and making use of Copy-on-write technology that allows you to quickly get the snapshot, but also keeping it consistent without using a lot of disk space. But over time as the “origin or source” of the snapshot is updating the snapshot will also use more space – which means it can grow over time. Sizing the snapshot is going to be important and if it is kept for longer periods – but the ideal is that they are used short term, hours may be days, but not weeks.
- Many sites will use snapshots to create a daily snapshot of the standby database, they then run batch processes on this (like index creation or materialized views etc, then the users can query this during the day. The next day, the snapshot is removed and the process repeated. The end result is during the day a consistent environment is available that can be used for data extraction.
- The Snapshot Group option is read/only at this stage (it will have the option for read/write in the near future). This feature would be perfect for creating daily snapshots that are kept for one day as explained above two bullet points (but note the read/write option will only be there in the next update)
4. What are the use cases for Snapshot vs Snapshot Groups?
- Using the individual snapshot options is going to be something most will use. You can create and remove snapshots as you require and this can be done via the GUI (recommended) but also via the CLI (advanced users) – which can then easily be scheduled with example a Cron schedule. Individual snapshots can be opened read/write and read-only, this gives customers the flexibility to use these snapshots in a variety of use-cases – like using the snapshots for Development or Test environments which can easily be recreated on regular basis.
- The Snapshot Group option is useful for customers that want to extract data from their environment – but also allow for the snapshots to be recreated in a rotating fashion. Multiple snapshots can be used in a rotating fashion which means when a user connects to the group (using listener service), they always connect to the latest snapshot. Sessions are not moved between snapshots, if the snapshot they are connected to is removed, their session will have to reconnect – using TAF is recommended. Scheduling these snapshot group interval cannot be less than 5min.