Final specs for the server is just an ordinary desktop fitted w/ 3
7200rpm 500GB drives & 1 7200 80GB drive / 1 GB ram / 2G processor
(single core)
number of records will be between 3 to 30 million rows.
Currently the process is
1. pull from mssql
2. \copy into PG temp table
3. insert into final table.
current tables are distributed via tablespaces. (current test server is
my laptop w/ 2 5400rpm drives hda & hdc.) and I'm already seeing the
strain of the concurrent select/delete/insert/update and the additional
"client" pull.
So, I wanted to ask the list for advice on how to tread w/ regard to the
server.
Scenario 1.
1. temp table in it's own tablespace
2. final tables in it's own tablespace
3. pgxlog in the OS tablespace
scenario 2
1. temp table in it's own tablespace
2. final tables in it's own tablespace (Read Only Copy)
3. final tables in it's own tablespace (Read write Copy)
4. pgxlog in the OS tablespace
the idea of read and read/write copy is obtained from some
presentation/article I read whereby, all updates of new data is inserted
into the read/write copy up until 1 point (say lunch time / 5pm etc)
whereby the read-write copy[3] will be "renamed" and made into a
read-only copy and the previous read-only copy[2] will be made into a
read-write copy.
The only thing I can't wrap my head around is how to keep these 2 copies
in sync. eg: when everything is being updated to [3] and users are
querying [2] and then at the switch over instance, how will the new data
be inputted to the read only copy? because if I switch back and forth
like this, there's bound to be gaps in the data.
Any comments appreciated.