Schedule - PGConf APAC 2019

Dealing with Gigantic Tables

Date: 2019-03-20
Time: 11:00–11:50
Room: Conference Room
Level: Intermediate
Feedback: Leave feedback

One of the databases that I'm working on belongs to an academic project, and academia is notorious for their dislike of deleting data - in their eyes, every single byte has "future research potential" and so nothing is to be purged at any cost. Thus, research datasets have a tendency to grow to colossal sizes, and normal database management practices no longer apply - one has to put their own DBA strategy using scraps of information on mailing lists, RhodiumToad's IRC logs, and creative hacks and tricks of varying nastiness that one has thought of in a shower.

In this talk, I present my own stash of tricks of dealing with large (1+ TB) tables:

  1. Real and imaginary reasons to partition large tables
  2. Gradually partitioning a large table without any downtime
  3. Partitions and query planner
  4. Continuously backing up large tables using volume snapshots
  5. Adding new columns with DEFAULT values to large tables
  6. Large indexes, and how to make them smaller
  7. Dumping large tables
  8. Replicating huge datasets


Linas Valiukas