README.md in pgslice-0.4.7 vs README.md in pgslice-0.4.8
- old
+ new
@@ -2,21 +2,21 @@
Postgres partitioning as easy as pie. Works great for both new and existing tables, with zero downtime and minimal app changes. No need to install anything on your database server. Archive older data on a rolling basis to keep your database size under control.
:tangerine: Battle-tested at [Instacart](https://www.instacart.com/opensource)
-[](https://travis-ci.org/ankane/pgslice)
+[](https://github.com/ankane/pgslice/actions)
## Install
pgslice is a command line tool. To install, run:
```sh
gem install pgslice
```
-This will give you the `pgslice` command. If installation fails, you may need to install [dependencies](#dependencies).
+This will give you the `pgslice` command. You can also install it with [Homebrew](#homebrew). 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>`.
@@ -225,10 +225,19 @@
```sh
s3cmd put <table>_201809.dump s3://<s3-bucket>/<table>_201809.dump
```
+## Schema Updates
+
+Once a table is partitioned, make schema updates on the master table only (not partitions). This includes adding, removing, and modifying columns, as well as adding and removing indexes and foreign keys.
+
+A few exceptions are:
+
+- For Postgres 10, make index and foreign key updates on partitions only
+- For Postgres < 10, make index and foreign key updates on the master table and all partitions
+
## Additional Commands
To undo prep (which will delete partitions), use:
```sh
@@ -239,10 +248,18 @@
```sh
pgslice unswap <table>
```
+## Additional Options
+
+Set the tablespace when adding partitions
+
+```sh
+pgslice add_partitions <table> --tablespace fastspace
+```
+
## App Considerations
This set up allows you to read and write with the original table name with no knowledge it’s partitioned. However, there are a few things to be aware of.
### Reads
@@ -269,12 +286,14 @@
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
+## Frameworks
+### Rails
+
For Postgres 10+, specify the primary key for partitioned models to ensure it’s returned.
```ruby
class Visit < ApplicationRecord
self.primary_key = "id"
@@ -289,38 +308,42 @@
self.id ||= self.class.connection.select_all("SELECT nextval('#{self.class.sequence_name}')").first["nextval"]
end
end
```
+### Other Frameworks
+
+Please submit a PR if additional configuration is needed.
+
## 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
pgslice prep <table> --no-partition
pgslice fill <table> --where "id > 1000" # use any conditions
pgslice swap <table>
```
-## Schema Updates
+## Triggers
-Once a table is partitioned, here’s how to change the schema:
+Triggers aren’t copied from the original table. You can set up triggers on the intermediate table if needed. Note that Postgres doesn’t support `BEFORE / FOR EACH ROW` triggers on partitioned tables.
-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](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.
+
+## Homebrew
+
+On Mac, you can use:
+
+```sh
+brew install ankane/brew/pgslice
+```
## Dependencies
If installation fails, your system may be missing Ruby or libpq.