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