This simple SQL "Initializes" the EBS volume linked to an RDS Instance, something which isn't possible to do without sending workload (and experience high Latency in the first run).
Key scenarios, where this is really helpful are:
- Create a Read-Replica (or Hot Standby in Postgres terms)
- Restore a new RDS Instance from a Snapshot
Update: The Script, now also does the following:
Limitations that still exist:
TOAST tables are still directly inaccessible in RDSIndexes for TOAST columns also fall under this categoryTrying hard to see if this last hurdle can be worked aroundAnyone with any ideas?!- Script needs to be run once per Database Owner
- Not sure if there is any magic around this
- Object ownership is a Postgres property
- RDS Postgres does not give Superuser access
- I'll try to ease this in the future
- By creating a script to list the Users that this needs to run as
- The other possibility is to use DBLink to run this for separate Users in a single run
I'll update here, in case I make any significant changes.
Sample Run
-[ RECORD 1 ]-------+------------------------------
clock_timestamp | 2017-11-19 15:40:08.291891-05
table_size | 13 GB
freespace_map_size | 3240 kB
visibility_map_size | 408 kB
blocks_prefetched | 1639801
current_database | pgbench
schema_name | public
table_name | pgbench_accounts
-[ RECORD 2 ]-------+------------------------------
clock_timestamp | 2017-11-19 15:43:37.703711-05
table_size | 2142 MB
freespace_map_size | 0 bytes
visibility_map_size | 0 bytes
blocks_prefetched | 274194
current_database | pgbench
schema_name | public
table_name | pgbench_accounts_pkey
-[ RECORD 3 ]-------+------------------------------
clock_timestamp | 2017-11-19 15:44:12.899115-05
table_size | 440 kB
freespace_map_size | 24 kB
visibility_map_size | 8192 bytes
blocks_prefetched | 59
current_database | pgbench
schema_name | public
table_name | pgbench_tellers
-[ RECORD 4 ]-------+------------------------------
clock_timestamp | 2017-11-19 15:44:12.901088-05
table_size | 240 kB
freespace_map_size | 0 bytes
visibility_map_size | 0 bytes
blocks_prefetched | 30
current_database | pgbench
schema_name | public
table_name | pgbench_tellers_pkey
-[ RECORD 5 ]-------+------------------------------
clock_timestamp | 2017-11-19 15:44:12.905107-05
table_size | 40 kB
freespace_map_size | 0 bytes
visibility_map_size | 0 bytes
blocks_prefetched | 5
current_database | pgbench
schema_name | public
table_name | pgbench_branches_pkey
-[ RECORD 6 ]-------+------------------------------
clock_timestamp | 2017-11-19 15:44:12.907089-05
table_size | 40 kB
freespace_map_size | 24 kB
visibility_map_size | 8192 bytes
blocks_prefetched | 9
current_database | pgbench
schema_name | public
table_name | pgbench_branches
-[ RECORD 7 ]-------+------------------------------
clock_timestamp | 2017-11-19 15:44:12.907142-05
table_size | 0 bytes
freespace_map_size | 0 bytes
visibility_map_size | 0 bytes
blocks_prefetched | 0
current_database | pgbench
schema_name | public
table_name | pgbench_history