README.md in pgslice-0.4.5 vs README.md in pgslice-0.4.6

- old
+ new

@@ -12,11 +12,11 @@ ```sh gem install pgslice ``` -This will give you the `pgslice` command. +This will give you the `pgslice` command. If installation fails, you may need to install [dependencies](#dependencies). ## Steps 1. Ensure the table you want to partition has been created. We’ll refer to this as `<table>`. @@ -32,20 +32,18 @@ pgslice prep <table> <column> <period> ``` Period can be `day`, `month`, or `year`. - This creates a table named `<table>_intermediate` with the appropriate trigger for partitioning. + This creates a partitioned table named `<table>_intermediate`. -4. Add partitions +4. Add partitions to the intermediate table ```sh pgslice add_partitions <table> --intermediate --past 3 --future 3 ``` - This creates child tables that inherit from the intermediate table. - Use the `--past` and `--future` options to control the number of partitions. 5. *Optional, for tables with data* - Fill the partitions in batches with data from the original table ```sh @@ -78,11 +76,11 @@ 9. Back up the retired table with a tool like [pg_dump](https://www.postgresql.org/docs/current/static/app-pgdump.html) and drop it ```sql pg_dump -c -Fc -t <table>_retired $PGSLICE_URL > <table>_retired.dump - psql -c "DROP <table>_retired" $PGSLICE_URL + psql -c "DROP TABLE <table>_retired" $PGSLICE_URL ``` ## Sample Output pgslice prints the SQL commands that were executed on the server. To print without executing, use the `--dry-run` option. @@ -218,11 +216,11 @@ Back up and drop older partitions each day, month, or year. ```sh pg_dump -c -Fc -t <table>_201809 $PGSLICE_URL > <table>_201809.dump -psql -c "DROP <table>_201809" $PGSLICE_URL +psql -c "DROP TABLE <table>_201809" $PGSLICE_URL ``` If you use [Amazon S3](https://aws.amazon.com/s3/) for backups, [s3cmd](https://github.com/s3tools/s3cmd) is a nice tool. ```sh @@ -271,10 +269,30 @@ Before Postgres 10, if you use `INSERT` statements with a `RETURNING` clause (as frameworks like Rails do), you’ll no longer receive the id of the newly inserted record(s) back. If you need this, you can either: 1. Insert directly into the partition 2. Get value before the insert with `SELECT nextval('sequence_name')` (for multiple rows, append `FROM generate_series(1, n)`) +## Rails + +For Postgres 10+, specify the primary key for partitioned models to ensure it’s returned. + +```ruby +class Visit < ApplicationRecord + self.primary_key = "id" +end +``` + +Before Postgres 10, preload the value. + +```ruby +class Visit < ApplicationRecord + before_create do + self.id ||= self.class.connection.select_all("SELECT nextval('#{self.class.sequence_name}')").first["nextval"] + end +end +``` + ## One Off Tasks You can also use pgslice to reduce the size of a table without partitioning by creating a new table, filling it with a subset of records, and swapping it in. ```sh @@ -285,21 +303,42 @@ ## Schema Updates Once a table is partitioned, here’s how to change the schema: -- To add, remove, or modify a column, make the update on the master table only. -- To add or remove an index or foreign key, make the update on the master table. For Postgres < 11, make the update on all partitions as well. +To add, remove, or modify a column, make the update on the master table only. +To add or remove an index or foreign key: + +- For Postgres 11+, make the update on the master table only. +- For Postgres 10, make the update on partitions only. +- For Postgres < 10, make the update on the master table and all partitions. + ## Declarative Partitioning Postgres 10 introduces [declarative partitioning](https://www.postgresql.org/docs/10/static/ddl-partitioning.html#ddl-partitioning-declarative). A major benefit is `INSERT` statements with a `RETURNING` clause work as expected. If you prefer to use trigger-based partitioning instead (not recommended), pass the `--trigger-based` option to the `prep` command. ## Data Protection -Always make sure your connection is secure when connecting to your database over a network you don’t fully trust. Your best option is to connect over SSH or a VPN. Another option is to use `sslmode=verify-full`. If you don’t do this, your database credentials can be compromised. +Always make sure your [connection is secure](https://ankane.org/postgres-sslmode-explained) when connecting to a database over a network you don’t fully trust. Your best option is to connect over SSH or a VPN. Another option is to use `sslmode=verify-full`. If you don’t do this, your database credentials can be compromised. +## Dependencies + +If installation fails, your system may be missing Ruby or libpq. + +On Mac, run: + +```sh +brew install postgresql +``` + +On Ubuntu, run: + +```sh +sudo apt-get install ruby-dev libpq-dev build-essential +``` + ## Upgrading Run: ```sh @@ -329,10 +368,11 @@ ## Related Projects Also check out: +- [Dexter](https://github.com/ankane/dexter) - The automatic indexer for Postgres - [PgHero](https://github.com/ankane/pghero) - A performance dashboard for Postgres - [pgsync](https://github.com/ankane/pgsync) - Sync Postgres data to your local machine ## Contributing @@ -341,10 +381,10 @@ - [Report bugs](https://github.com/ankane/pgslice/issues) - Fix bugs and [submit pull requests](https://github.com/ankane/pgslice/pulls) - Write, clarify, or fix documentation - Suggest or add new features -To run tests, do: +To get started with development and testing: ```sh git clone https://github.com/ankane/pgslice.git cd pgslice bundle install