[{"_1":2,"_161":-5,"_162":-5},"loaderData",{"_3":4,"_17":18,"_25":26,"_140":141},"root",{"_5":-5,"_6":-7,"_7":8,"_9":-7,"_10":11,"_12":-7,"_13":14,"_15":16},"currentUser","primaryEmail","siteVerification","6LeIxAcTAAAAAJcZVRqyHh71UMIEGNQ_MXjiZKhI","toast","enableDev",false,"incidentStatus","theme","system","contrast","normal","routes/_layout",{"_19":-5,"_20":21,"_22":21,"_23":11,"_24":21},"username","isAskGordonEnabled",true,"isUsingNewTemplate","showMinimalLanding","showHoneypotTrap","routes/_layout.r.$namespace.$reponame",{"_27":28,"_29":-5,"_30":11,"_31":11,"_32":33,"_34":35,"_36":-5,"_37":11,"_38":39,"_88":-5,"_89":90,"_129":-5,"_130":11,"_131":21,"_132":11,"_133":-5,"_134":11,"_135":-5,"_136":137,"_138":91,"_139":-7},"canonicalUrl","https://hub.docker.com/r/kr3ssh/pgslice/tags","mcpServer","canSeeDockerfile","canSeeAutobuilds","namespace","kr3ssh","reponame","pgslice","cloudBuildSource","isAutobuildsAvailable","repo",{"_40":41,"_42":43,"_45":46,"_47":48,"_49":11,"_50":51,"_52":53,"_54":55,"_57":35,"_32":33,"_58":59,"_60":44,"_61":62,"_63":64,"_65":66,"_67":-5,"_68":62,"_69":70,"_71":11,"_72":33,"_73":74,"_79":21,"_80":81,"_85":33,"_86":87},"categories",[],"content_types",[44],"image","date_registered","2018-05-21T14:57:26.791773Z","description","Postgres partitioning as easy as pie","is_private","last_updated","2018-05-21T15:02:18.222441Z","last_modified","2024-10-16T13:48:34.145251Z","media_types",[56],"application/vnd.docker.container.image.v1+json","name","pull_count",243,"repository_type","star_count",0,"status",1,"status_description","active","source","collaborator_count","full_description","# pgslice\n\nPostgres partitioning as easy as pie. Works great for both new and existing tables, with zero downtime and minimal app changes. Archive older data on a rolling basis to keep your database size under control.\n\n:tangerine: Battle-tested at [Instacart](https://www.instacart.com/opensource)\n\n[![Build Status](https://travis-ci.org/ankane/pgslice.svg?branch=master)](https://travis-ci.org/ankane/pgslice)\n\n## Install\n\npgslice is a command line tool. To install, run:\n\n```sh\ngem install pgslice\n```\n\nThis will give you the `pgslice` command.\n\n## Steps\n\n1. Ensure the table you want to partition has been created. We’ll refer to this as ``.\n\n2. Specify your database credentials\n\n ```sh\n export PGSLICE_URL=postgres://localhost/myapp_development\n ```\n\n3. Create an intermediate table\n\n ```sh\n pgslice prep

\n ```\n\n Period can be `day` or `month`.\n\n This creates a table named `

_intermediate` with the appropriate trigger for partitioning.\n\n4. Add partitions\n\n ```sh\n pgslice add_partitions

--intermediate --past 3 --future 3\n ```\n\n This creates child tables that inherit from the intermediate table.\n\n Use the `--past` and `--future` options to control the number of partitions.\n\n5. *Optional, for tables with data* - Fill the partitions in batches with data from the original table\n\n ```sh\n pgslice fill

\n ```\n\n Use the `--batch-size` and `--sleep` options to control the speed.\n\n To sync data across different databases, check out [pgsync](https://github.com/ankane/pgsync).\n\n6. Analyze tables\n\n ```sh\n pgslice analyze

\n ```\n\n7. Swap the intermediate table with the original table\n\n ```sh\n pgslice swap

\n ```\n\n The original table is renamed `

_retired` and the intermediate table is renamed `

`.\n\n8. Fill the rest (rows inserted between the first fill and the swap)\n\n ```sh\n pgslice fill

--swapped\n ```\n\n9. Back up the retired table with a tool like [pg_dump](https://www.postgresql.org/docs/current/static/app-pgdump.html) and drop it\n\n ```sql\n pg_dump -c -Fc -t

_retired $PGSLICE_URL >

_retired.dump\n psql -c \"DROP

_retired\" $PGSLICE_URL\n ```\n\n## Sample Output\n\npgslice prints the SQL commands that were executed on the server. To print without executing, use the `--dry-run` option.\n\n```sh\npgslice prep visits created_at month\n```\n\n```sql\n-- Postgres 10\n\nBEGIN;\n\nCREATE TABLE visits_intermediate (LIKE visits INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING STORAGE INCLUDING COMMENTS) PARTITION BY RANGE (created_at);\n\nCOMMENT ON TABLE visits_intermediate is 'column:created_at,period:day';\n\nCOMMIT;\n\n-- Postgres 9.6 and below\n\nBEGIN;\n\nCREATE TABLE visits_intermediate (LIKE visits INCLUDING ALL);\n\nCREATE FUNCTION visits_insert_trigger()\n RETURNS trigger AS $$\n BEGIN\n RAISE EXCEPTION 'Create partitions first.';\n END;\n $$ LANGUAGE plpgsql;\n\nCREATE TRIGGER visits_insert_trigger\n BEFORE INSERT ON visits_intermediate\n FOR EACH ROW EXECUTE PROCEDURE visits_insert_trigger();\n\nCOMMENT ON TRIGGER visits_insert_trigger ON visits_intermediate is 'column:created_at,period:month';\n\nCOMMIT;\n```\n\n```sh\npgslice add_partitions visits --intermediate --past 1 --future 1\n```\n\n```sql\n-- Postgres 10\n\nBEGIN;\n\nCREATE TABLE visits_201608 PARTITION OF visits_intermediate FOR VALUES FROM ('2016-08-01') TO ('2016-09-01');\n\nALTER TABLE visits_201608 ADD PRIMARY KEY (id);\n\nCREATE INDEX ON visits_201608 USING btree (user_id);\n\nCREATE TABLE visits_201609 PARTITION OF visits_intermediate FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');\n\nALTER TABLE visits_201609 ADD PRIMARY KEY (id);\n\nCREATE INDEX ON visits_201609 USING btree (user_id);\n\nCREATE TABLE visits_201610 PARTITION OF visits_intermediate FOR VALUES FROM ('2016-10-01') TO ('2016-11-01');\n\nALTER TABLE visits_201610 ADD PRIMARY KEY (id);\n\nCREATE INDEX ON visits_201610 USING btree (user_id);\n\nCOMMIT;\n\n-- Postgres 9.6 and below\n\nBEGIN;\n\nCREATE TABLE visits_201608\n (CHECK (created_at >= '2016-08-01'::date AND created_at < '2016-09-01'::date))\n INHERITS (visits_intermediate);\n\nALTER TABLE visits_201608 ADD PRIMARY KEY (id);\n\nCREATE INDEX ON visits_201608 USING btree (user_id);\n\nCREATE TABLE visits_201609\n (CHECK (created_at >= '2016-09-01'::date AND created_at < '2016-10-01'::date))\n INHERITS (visits_intermediate);\n\nALTER TABLE visits_201609 ADD PRIMARY KEY (id);\n\nCREATE INDEX ON visits_201609 USING btree (user_id);\n\nCREATE TABLE visits_201610\n (CHECK (created_at >= '2016-10-01'::date AND created_at < '2016-11-01'::date))\n INHERITS (visits_intermediate);\n\nALTER TABLE visits_201610 ADD PRIMARY KEY (id);\n\nCREATE INDEX ON visits_201610 USING btree (user_id);\n\nCREATE OR REPLACE FUNCTION visits_insert_trigger()\n RETURNS trigger AS $$\n BEGIN\n IF (NEW.created_at >= '2016-09-01'::date AND NEW.created_at < '2016-10-01'::date) THEN\n INSERT INTO visits_201609 VALUES (NEW.*);\n ELSIF (NEW.created_at >= '2016-10-01'::date AND NEW.created_at < '2016-11-01'::date) THEN\n INSERT INTO visits_201610 VALUES (NEW.*);\n ELSIF (NEW.created_at >= '2016-08-01'::date AND NEW.created_at < '2016-09-01'::date) THEN\n INSERT INTO visits_201608 VALUES (NEW.*);\n ELSE\n RAISE EXCEPTION 'Date out of range. Ensure partitions are created.';\n END IF;\n RETURN NULL;\n END;\n $$ LANGUAGE plpgsql;\n\nCOMMIT;\n```\n\n```sh\npgslice fill visits\n```\n\n```sql\n/* 1 of 3 */\nINSERT INTO visits_intermediate (\"id\", \"user_id\", \"ip\", \"created_at\")\n SELECT \"id\", \"user_id\", \"ip\", \"created_at\" FROM visits\n WHERE id > 0 AND id <= 10000 AND created_at >= '2016-08-01'::date AND created_at < '2016-11-01'::date\n\n/* 2 of 3 */\nINSERT INTO visits_intermediate (\"id\", \"user_id\", \"ip\", \"created_at\")\n SELECT \"id\", \"user_id\", \"ip\", \"created_at\" FROM visits\n WHERE id > 10000 AND id <= 20000 AND created_at >= '2016-08-01'::date AND created_at < '2016-11-01'::date\n\n/* 3 of 3 */\nINSERT INTO visits_intermediate (\"id\", \"user_id\", \"ip\", \"created_at\")\n SELECT \"id\", \"user_id\", \"ip\", \"created_at\" FROM visits\n WHERE id > 20000 AND id <= 30000 AND created_at >= '2016-08-01'::date AND created_at < '2016-11-01'::date\n```\n\n```sh\npgslice analyze visits\n```\n\n```sql\nANALYZE VERBOSE visits_201608;\n\nANALYZE VERBOSE visits_201609;\n\nANALYZE VERBOSE visits_201610;\n\nANALYZE VERBOSE visits_intermediate;\n```\n\n```sh\npgslice swap visits\n```\n\n```sql\nBEGIN;\n\nSET LOCAL lock_timeout = '5s';\n\nALTER TABLE visits RENAME TO visits_retired;\n\nALTER TABLE visits_intermediate RENAME TO visits;\n\nALTER SEQUENCE visits_id_seq OWNED BY visits.id;\n\nCOMMIT;\n```\n\n## Adding Partitions\n\nTo add partitions, use:\n\n```sh\npgslice add_partitions

--future 3\n```\n\nAdd this as a cron job to create a new partition each day or month.\n\n```sh\n# day\n0 0 * * * pgslice add_partitions

--future 3 --url ...\n\n# month\n0 0 1 * * pgslice add_partitions

--future 3 --url ...\n```\n\nAdd a monitor to ensure partitions are being created.\n\n```sql\nSELECT 1 FROM\n pg_catalog.pg_class c\nINNER JOIN\n pg_catalog.pg_namespace n ON n.oid = c.relnamespace\nWHERE\n c.relkind = 'r' AND\n n.nspname = 'public' AND\n c.relname = '

_' || to_char(NOW() + INTERVAL '3 days', 'YYYYMMDD')\n -- for months, use to_char(NOW() + INTERVAL '3 months', 'YYYYMM')\n```\n\n## Archiving Partitions\n\nBack up and drop older partitions each day or month.\n\n```sh\npg_dump -c -Fc -t

_201609 $PGSLICE_URL >

_201609.dump\npsql -c \"DROP

_201609\" $PGSLICE_URL\n```\n\nIf you use [Amazon S3](https://aws.amazon.com/s3/) for backups, [s3cmd](https://github.com/s3tools/s3cmd) is a nice tool.\n\n```sh\ns3cmd put

_201609.dump s3:///

_201609.dump\n```\n\n## Additional Commands\n\nTo undo prep (which will delete partitions), use:\n\n```sh\npgslice unprep

\n```\n\nTo undo swap, use:\n\n```sh\npgslice unswap

\n```\n\n## App Considerations\n\nThis 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.\n\n### Writes\n\nBefore 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:\n\n1. Insert directly into the partition\n2. Get value before the insert with `SELECT nextval('sequence_name')` (for multiple rows, append `FROM generate_series(1, n)`)\n\n### Reads\n\nWhen possible, queries should include the column you partition on to limit the number of partitions the database needs to check. For instance, if you partition on `created_at`, try to include it in queries:\n\n```sql\nSELECT * FROM\n visits\nWHERE\n user_id = 123 AND\n -- for performance\n created_at >= '2016-09-01' AND created_at < '2016-09-02'\n```\n\nFor this to be effective, ensure `constraint_exclusion` is set to `partition` (default value) or `on`.\n\n```sql\nSHOW constraint_exclusion;\n```\n\n## One Off Tasks\n\nYou 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.\n\n```sh\npgslice prep

--no-partition\npgslice fill

--where \"id > 1000\" # use any conditions\npgslice swap

\n```\n\n## Schema Updates\n\nOnce a table is partitioned, here’s how to change the schema:\n\n- To add, remove, or modify a column, make the update on the master table only\n- To add or remove an index, make the update on the master table and all partitions\n\n## Declarative Partitioning\n\nPostgres 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.\n\n## Upgrading\n\nRun:\n\n```sh\ngem install pgslice\n```\n\nTo use master, run:\n\n```sh\ngem install specific_install\ngem specific_install https://github.com/ankane/pgslice.git\n```\n\n## Docker\n\n```sh\ndocker build -t pgslice .\nalias pgslice=\"docker run --rm -e PGSLICE_URL pgslice\"\n```\n\nThis will give you the `pgslice` command.\n\n## Reference\n\n- [PostgreSQL Manual](https://www.postgresql.org/docs/current/static/ddl-partitioning.html)\n- [PostgreSQL Wiki](https://wiki.postgresql.org/wiki/Table_partitioning)\n\n## TODO\n\n- Command to sync index changes with partitions\n- Disable indexing for faster `fill`\n- ETA for `fill`\n\n## Related Projects\n\nAlso check out:\n\n- [PgHero](https://github.com/ankane/pghero) - A performance dashboard for Postgres\n- [pgsync](https://github.com/ankane/pgsync) - Sync Postgres data to your local machine\n\n## Contributing\n\nEveryone is encouraged to help improve this project. Here are a few ways you can help:\n\n- [Report bugs](https://github.com/ankane/pgslice/issues)\n- Fix bugs and [submit pull requests](https://github.com/ankane/pgslice/pulls)\n- Write, clarify, or fix documentation\n- Suggest or add new features\n\nTo run tests, do:\n\n```sh\ngit clone https://github.com/ankane/pgslice.git\ncd pgslice\nbundle install\ncreatedb pgslice_test\nbundle exec rake\n```\n","has_starred","hub_user","immutable_tags_settings",{"_75":11,"_76":77},"enabled","rules",[78],".*","is_automated","permissions",{"_82":21,"_83":11,"_84":11},"read","write","admin","user","storage_size",346464277,"orgData","recentTags",[91],{"_92":44,"_93":94,"_95":96,"_97":98,"_99":100,"_50":118,"_119":94,"_120":33,"_121":56,"_57":122,"_123":124,"_125":109,"_126":118,"_127":66,"_128":21},"content_type","creator",63184,"full_size",346454195,"id",27719736,"images",[101],{"_102":103,"_104":105,"_106":107,"_108":109,"_110":111,"_112":113,"_114":107,"_115":-5,"_116":96,"_63":66,"_117":-5},"architecture","amd64","digest","sha256:36c539bda96733a42c59bf06453c7b944afec6b150e2260e5fd0aacc9cfc85fb","features","","last_pulled","2026-09-29T12:37:21.313718166Z","last_pushed","2018-05-21T15:02:18Z","os","linux","os_features","os_version","size","variant","2018-05-21T15:02:17.342077Z","last_updater","last_updater_username","media_type","latest","repository",5523058,"tag_last_pulled","tag_last_pushed","tag_status","v2","sbxKitMetadata","isPotentialSpam","isHubSbxKitsEnabled","showSbxLogoOverride","sbxTrademarkText","displaySecurityMetadata","tagAttestations","tagsHaveAttestations",[],"selectedTag","attestationsUrl","routes/_layout.r.$namespace.$reponame.tags",{"_38":142,"_149":150,"_136":159,"_160":11},{"_40":143,"_42":144,"_45":46,"_47":48,"_49":11,"_50":51,"_52":53,"_54":145,"_57":35,"_32":33,"_58":59,"_60":44,"_61":62,"_63":64,"_65":66,"_67":-5,"_68":62,"_69":70,"_71":11,"_72":33,"_73":146,"_79":21,"_80":148,"_85":33,"_86":87},[],[44],[56],{"_75":11,"_76":147},[78],{"_82":21,"_83":11,"_84":11},"tags",{"_151":64,"_152":-5,"_153":-5,"_154":155},"count","next","previous","results",[156],{"_92":44,"_93":94,"_95":96,"_97":98,"_99":157,"_50":118,"_119":94,"_120":33,"_121":56,"_57":122,"_123":124,"_125":109,"_126":118,"_127":66,"_128":21},[158],{"_102":103,"_104":105,"_106":107,"_108":109,"_110":111,"_112":113,"_114":107,"_115":-5,"_116":96,"_63":66,"_117":-5},[],"canDeleteTags","actionData","errors"]

kr3ssh/pgslice

Tags for kr3ssh/pgslice