Description
PgDice is a utility for creating and maintaining partitioned database tables that builds on top of the excellent gem
https://github.com/ankane/pgslice
PgDice is intended to be used by scheduled background jobs in frameworks like Sidekiq
where logging and clear exception messages are crucial.
PgDice alternatives and similar gems
Based on the "Database Tools" category.
Alternatively, view PgDice alternatives based on common mentions on social networks and blogs.
-
Database Cleaner
Strategies for cleaning databases in Ruby. Can be used to ensure a clean state for testing. -
Lol DBA
lol_dba is a small package of rake tasks that scan your application models and displays a list of columns that probably should be indexed. Also, it can generate .sql migration scripts. -
Seed Fu
Advanced seed data handling for Rails, combining the best practices of several methods together. -
Seedbank
Seedbank gives your seed data a little structure. Create seeds for each environment, share seeds between environments and specify dependencies to load your seeds in order. All nicely integrated with simple rake tasks. -
DatabaseConsistency
The tool to avoid various issues due to inconsistencies and inefficiencies between a database schema and application models. -
Polo
Polo travels through your database and creates sample snapshots so you can work with real world data in development. -
SchemaPlus
DISCONTINUED. SchemaPlus provides a collection of enhancements and extensions to ActiveRecord -
Upsert
Upsert on MySQL, PostgreSQL, and SQLite3. Transparently creates functions (UDF) for MySQL and PostgreSQL; on SQLite3, uses INSERT OR IGNORE. -
OnlineMigrations
Catch unsafe PostgreSQL migrations in development and run them easier in production (code helpers for table/column renaming, changing column type, adding columns with default, background migrations, etc). -
Ruby PG Extras
Ruby PostgreSQL database performance insights. Locks, index usage, buffer cache hit ratios, vacuum stats and more. -
PgDriveBackup
Simple solution to make encrypted with ccrypt PostgreSQL backups and storing on Google Drive API -
Slack Smart Bot
Create a Slack bot that is smart and so easy to expand, create new bots on demand, run ruby code on chat, create shortcuts... The main scope of this gem is to be used internally in the company so teams can create team channels with their own bot to help them on their daily work, almost everything is suitable to be automated!! slack-smart-bot can create bots on demand, create shortcuts, run ruby code... just on a chat channel. You can access it just from your mobile phone if you want and run those tests you forgot to run, get the results, restart a server... no limits. -
Perfect Shape
Perfect Shape is a collection of geometric algorithms that are mostly useful for GUI manipulation like checking containment of a point in popular geometric shapes such as rectangle, square, arc, circle, polygon, and paths containing lines, quadratic bézier curves, and cubic bezier curves. Also, some general math algorithms like IEEE-754 Remainder. -
ActiveRecord Setops
Union, Intersect, and Difference set operations for ActiveRecord (also, SQL's UnionAll). -
Rapidity
Simple but fast Redis-backed distributed rate limiter. Allows you to specify time interval and count within to limit distributed operations. -
PRY-BYETYPO 👋
A Pry plugin that captures exceptions that may arise from typos and deduces the correct command.
Scout Monitoring - Performance metrics and, now, Logs Management Monitoring with Scout Monitoring
* Code Quality Rankings and insights are calculated and provided by Lumnify.
They vary from L1 to L5 with "L5" being the highest.
Do you think we are missing an alternative of PgDice or a related project?
Popular Comparisons
README
PgDice
PgDice is a utility for creating and maintaining partitioned database tables that builds on top of the excellent gem https://github.com/ankane/pgslice
PgDice is intended to be used by scheduled background jobs in frameworks like Sidekiq where logging and clear exception messages are crucial.
Maintenance status
This project is stable and used daily in production.
Installation
Add this line to your application's Gemfile:
gem 'pgdice'
And then execute:
$ bundle
Or install it yourself as:
$ gem install pgdice
Usage
Configuration
You must configure PgDice
before you can use it, otherwise you won't be able to perform any manipulation actions
on tables.
This is an example config from a project using Sidekiq
require 'pgdice'
PgDice.configure do |config|
# This defaults to STDOUT if you don't specify a logger
config.logger_factory = proc { Sidekiq.logger }
# database_url *must be set*
# Rails users: see FAQ for method on how to generate this from your Rails config.
config.database_url = ENV['PGDICE_DATABASE_URL'] # postgresql://[user[:password]@][host][:port][/dbname][?param1=value1&...]
# Set a config file or build the tables manually
config.config_file = Rails.root.join('config', 'pgdice.yml') # If you are using rails, else provide the absolute path.
config.config_file = Rails.root.join('config', 'pgdice.yml') # If you are using rails, else provide the absolute path.
# and/or
config.approved_tables = PgDice::ApprovedTables.new(
PgDice::Table.new(table_name: 'comments', past: 90, future: 7, period: 'day'),
PgDice::Table.new(table_name: 'posts', past: 6, future: 2, period: 'month')
)
end
Configuration Parameters
database_url
- Required: The postgres database url to connect to.- This is required since
pgslice
requires a postgresurl
. PgDice
will throw an error if this value is not a valid postgres url.
- This is required since
logger_factory
- Optional: A factory that will return a logger to use.- Defaults to
proc { Logger.new(STDOUT) }
- Defaults to
approved_tables
- Optional: (but not really) The tables to allow modification on.- If you want to manipulate database tables with this gem you're going to need to provide this data.
- See the Approved Tables Configuration section for more.
dry_run
- Optional: Boolean value to control whether changes are executed on the database.- Defaults to
false
true
will make PgDice log out the commands but not execute them.
- Defaults to
batch_size
- Optional: Maximum number of tables you can drop in onedrop_old_partitions
call.- Defaults to 7.
- Keep in mind the size of your tables, drop operations are done in one command. Large tables will take longer to drop per table and could time out if there is activity on the parent table.
Advanced Configuration Parameters
All of the following parameters are optional and honestly you probably will never need to mess with these.
pg_connection
- This is aPG::Connection
object used for the database queries made frompgdice
.- By default it will be initialized from the
database_url
if leftnil
. - Keep in mind the dependency
pgslice
will still establish its own connection using thedatabase_url
so this feature may not be very useful if you are trying to only use one connection for this utility.
- By default it will be initialized from the
Approved Tables Configuration
In order to maintain the correct number of partitions over time you must configure a [PgDice::Table](lib/pgdice/table.rb).
An example configuration file has been provided at [config.yml](examples/config.yml) if you would rather
declare your approved_tables
in yaml.
Alternative Approved Tables Configuration
If you want to declare your [PgDice::ApprovedTables](lib/pgdice/approved_tables.rb) in your configuration block instead, you can build them like so:
require 'pgdice'
PgDice.configure do |config|
config.approved_tables = PgDice::ApprovedTables.new(
PgDice::Table.new(table_name: 'comments', # Table name for the (un)partitioned table
past: 90, # The minimum number of tables to keep before dropping older tables.
future: 7, # Number of future tables to always have.
period: 'day', # day, month, year
column_name: 'created_at', # Whatever column you'd like to partition on.
schema: 'public'), # Schema that this table belongs to.
PgDice::Table.new(table_name: 'posts') # Minimum configuration (90 past, 7 future, 'day' period).
)
end
It is possible to use both the configuration block and a file if you so choose. The block will take precedence over the values in the file.
Converting existing tables to partitioned tables
This should only be used on smallish tables and ONLY after you have tested it on a non-production copy of your production database. In fact, you should just not do this in production. Schedule downtime or something and run it a few times on a copy of your database. Then practice restoring your database some more.
This command will convert the existing comments
table into 98 partitioned tables
(90 past, 7 future, and one for today).
For more information on what's going on in the background see https://github.com/ankane/pgslice
PgDice.partition_table('comments')
Copying existing data into new partitions
If you have a table with existing data and you want that data to be split up and copied to your new partitions you can use:
PgDice.partition_table('comments', fill: true)
This will create the partitions and then insert data from the old table into the newly partitioned tables.
Notes on partition_table
- You can override values configured in the
PgDice::Table
by passing them in as a hash.- For example if you wanted to create
30
future tables instead of the configured7
for thecomments
table you could pass infuture: 30
.
- For example if you wanted to create
If you mess up, again you shouldn't use this in production, you can call:
PgDice.undo_partitioning!('comments')
This method will revert the changes made by partitioning a table. Don't rely on this in production if you mess up; you need to test everything thoroughly.
Maintaining partitioned tables
Adding more tables
If you have existing tables that need to periodically have more tables added you can run:
PgDice.add_new_partitions('comments')
Notes on add_new_partitions
- The above command would add up to
7
new tables and their associated indexes all based on theperiod
that the partitioned table was defined with.- The example
comments
table we have been using was configured to always keep7
future partitions above.
- The example
Listing droppable partitions
Sometimes you just want to know what's out there and if there are tables ready to be dropped.
To list all eligible tables for dropping you can run:
PgDice.list_droppable_partitions('comments')
If you want to know exactly which partitions will be dropped you can call:
PgDice.list_droppable_partitions_by_batch_size('comments')
This method will show partitions that are within the configured batch_size
.
Notes on list_droppable_partitions
- This method uses the
past
value from thePgDice::Table
to determine which tables are eligible for dropping. - Like most commands, if you want to override the values it will use to check just pass them in.
Dropping old tables
Dropping tables is irreversible! Do this at your own risk!!
If you want to drop old tables (after backing them up of course) you can run:
PgDice.drop_old_partitions('comments')
Notes on drop_old_partitions
- The above example command would drop partitions that exceed the configured
past
table count for thePgDice::Table
.- The example
comments
table has been configured withpast: 90
tables. So if there were 100 tables older thantoday
it would drop up tobatch_size
tables.
- The example
Validation
If you've got background jobs creating and dropping tables you're going to want to ensure they are actually working correctly.
To validate that your expected number of tables exist, you can run:
PgDice.assert_tables('comments')
An [InsufficientTablesError](lib/pgdice/error.rb) will be raised if any conditions are not met.
This will check that there are 7 future tables from now and that there are 90 past tables per our configuration above.
If you want to only assert on past
tables you could use the example below. The same goes for future
PgDice.assert_tables('comments', only: :past)
Listing approved tables
Sometimes you might need to know the tables configured for PgDice
. To list the configured tables
you can run:
PgDice.approved_tables
The [ApprovedTables](lib/pgdice/approved_tables.rb) object responds to the most common enumerable methods.
Miscellaneous Notes
All methods for PgDice
take a hash which will override whatever values would have been automatically supplied.
An example of this would be like so:
PgDice.list_droppable_partitions('comments', past: 60)
This example would use 60
instead of the configured value of 90
from the comments
table we configured above.
Examples
[Here's an example on how to use PgDice in AWS](examples/aws) and the [README](examples/aws/README.md) which will guide you through what is going on.
[Here's an example on how to write a config.yml for PgDice](examples/config.yml)
FAQ
- How do I get a postgres url if I'm running in Rails? ```ruby def build_postgres_url config = Rails.configuration.database_configuration host = config[Rails.env]["host"] database = config[Rails.env]["database"] username = config[Rails.env]["username"] password = config[Rails.env]["password"]
"postgres://#{username}:#{password}@#{host}/#{database}" end
1. I'm seeing off-by-one errors for my `assert_tables` calls?
- You should make sure your database is configured to use `UTC`.
[https://www.postgresql.org/docs/10/datatype-datetime.html](https://www.postgresql.org/docs/10/datatype-datetime.html)
## Planned Features
1. Full `PG::Connection` support (no more database URLs).
1. Non time-range based partitioning. [PgParty](https://github.com/rkrage/pg_party) might be a good option!
1. Hourly partitioning
# Development
After checking out the repo, run `bin/setup` to install dependencies. Then, run `rake test` to run the tests.
You can also run `bin/console` for an interactive prompt that will allow you to experiment.
To install this gem onto your local machine, run `bundle exec rake install`.
## Running tests
You're going to need to have postgres 10 or greater installed.
Run the following commands from your terminal. Don't run these on anything but a development machine.
1. `psql postgres -c "create role pgdice with createdb superuser login password 'password';"`
1. `createdb pgdice_test`
1. Now you can run the tests via `guard` or `rake test`
## Contributing
Bug reports and pull requests are welcome on GitHub at
[https://github.com/IlluminusLimited/pgdice](https://github.com/IlluminusLimited/pgdice). This project is intended
to be a safe, welcoming space for collaboration, and contributors are expected to adhere to
the [Contributor Covenant](http://contributor-covenant.org) code of conduct.
# License
The gem is available as open source under the terms of the [MIT License](https://opensource.org/licenses/MIT).
# Disclaimer
There are some features in this gem which allow you to drop database tables, due to the dangerous nature of
dropping database tables, please ensure you have a tested and working backup and restore strategy.
THIS SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED.
By using this software you agree that the creator, maintainers, and any affiliated parties
CANNOT BE HELD LIABLE FOR DATA LOSS OR LOSSES OF ANY KIND.
See the [LICENSE](LICENSE) for more information.
# Code of Conduct
Everyone interacting in the Pgdice project’s codebases, issue trackers, chat rooms and mailing lists is expected
to follow the [code of conduct](https://github.com/IlluminusLimited/pgdice/blob/master/CODE_OF_CONDUCT.md).
*Note that all licence references and agreements mentioned in the PgDice README section above
are relevant to that project's source code only.