A naukri.com initiative
Dbi-Services
1M
55
PostgreSQL: Partitioning an existing table (almost) online using table inheritance
- The PostgreSQL mailing list archives are a wonderful place to get new ideas just by reading through random posts. This time I found an interesting idea on how to partition an existing table almost online without dumping and reloading the data in some way.
- For those who remember: Before PostgreSQL 10 the only way to partition a table in PostgreSQL was to use table inheritance.
- create table t_aaa ( a int, b text check (b in ('aaa')) ) inherits ( t );create table t_bbb ( a int, b text check (b in ('bbb')) ) inherits ( t );
- Having that confirmed we can delete the data from the parent table and insert into the child tables (maybe in multiple batches).We do that until the parent table is empty and all data is in the child tables.
- The final step is to destroy the inheritance and attach the child tables as new partitions to a newly created partitioned table, and finally rename the old one and the new one.
- Because the check constraint matches the partition key, PostgreSQL can just attach the tables as new partitions without scanning the tables, so this is a very fast operation with a very short lock.
- This is a really simple test case, of course this works well. In busy systems this might get more tricky. Routing data with a trigger might introduce some performance degradation.
- You somehow have to deal with data coming in while you go from the old to the new structure, or stop data from coming in during the last step.
- The PostgreSQL community believes strongly in helping each other learn and improve. Every year, they hold PostgreSQL conferences to help people gain a better understanding of PostgreSQL.
- Some popular PostgreSQL conferences include PGCon, PostgreSQL Conference East, and PostgreSQL Conference West.
Read Full Article
3 Likes
For uninterrupted reading, download the app