Videos Watched
- Scaling Postgres Episode 6 | Terabyte Scale | Permissions | Fast Column Adds | pgBouncerScaling Scaling
- Postgres Episode 10 | ANSI Schmansi | Split-Brain | Performance Scenarios | ParallelismScaling
- Postgres Episode 20 | Declarative Partitioning | Permissions | Trigger SpeedScaling
- Postgres Episode 25 | generate_series | PG11 Partitioning | GPU QueriesScaling
- Postgres Episode 36 | Sharding | Giving Back | SCRAM | Partitioning Evolution
Reference
Blog Post Read
- PostgreSQL 10 – How to create a partitioned table based on JSONB column field value
- PostgreSQL 11: Partitioning Evolution from Postgres 9.6 to 11
- Talk slides: Partitioning Improvements in PostgreSQL 11
- Declarative data partitioning with PostgreSQL
- Sharding Your Data With PostgreSQL 11
Declarative Partitioning
CREATE TABLE test04 (data jsonb) partition by range ((data->’id’));
CREATE TABLE test04_01 PARTITION OF test04
FOR VALUES FROM (‘1’) TO (‘2500000’);
CREATE TABLE test04_02 PARTITION OF test04
FOR VALUES FROM (‘2500001’) TO (‘5000000’);
CREATE TABLE test04_03 PARTITION OF test04
FOR VALUES FROM (‘5000001’) TO (‘7500000’);
CREATE TABLE test04_04 PARTITION OF test04
FOR VALUES FROM (‘7500001’) TO (‘11000000’);
CREATE INDEX ON test04_01 USING BTREE ((data->’id’));
CREATE INDEX ON test04_02 USING BTREE ((data->’id’));
CREATE INDEX ON test04_03 USING BTREE ((data->’id’));
CREATE INDEX ON test04_04 USING BTREE ((data->’id’));
Vagrant Installation
1. git clone https://github.com/stuartshay/Vagrant
2. Create multiple directories under home home user dir. Only /home/ms4446/vagrant-testVagrant was created from github /home/ms4446/vagrant-test/
· /home/ms4446/vagrant-test/conf
· /home/ms4446/vagrant-test/postgres
· /home/ms4446/vagrant-test/scripts
· /home/ms4446/vagrant-test/provision
1. Edited Vagrantfile
2. Ensure pg_hba.conf postgresql.conf exist in /home/ms4446/vagrant-test/conf
3. Ensure database_provision.sh vagrant_provision.s /home/ms4446/vagrant-test/provision
4. Run `vagrant init ubuntu/trusty64`
5. cd postgres
6. vagrant up
1) Get Docker to Run to the Vagrant VM
Git Editing
https://github.com/stuartshay/Vagrant/blob/master/ubuntu/provision/vagrant_provision.sh
Uncomment the Docker Part
2) Save the Changes you did to Github
git add .
git commit -m “Updated File”
git push
Machine Learning
One comment