Skip to content

Instantly share code, notes, and snippets.

@piotrwlodarczyk

piotrwlodarczyk/Database structure

Created December 23, 2019 12:43
Show Gist options
  • Save piotrwlodarczyk/91cbe877c49123c64ca50b8438f8efdc to your computer and use it in GitHub Desktop.
Save piotrwlodarczyk/91cbe877c49123c64ca50b8438f8efdc to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Database structure
mytest=# \d+ book;
Partitioned table "public.book"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+--------+-----------+----------+----------------------------------+----------+--------------+-------------
id | bigint | | not null | nextval('book_id_seq'::regclass) | plain | |
id_owner | bigint | | not null | | plain | |
added | date | | not null | | plain | |
isbn | text | | | | extended | |
Partition key: RANGE (id_owner)
Indexes:
"book_id" btree (id)
Partitions: "public.book_1001-2000" FOR VALUES FROM ('1001') TO ('2000'), PARTITIONED,
"public.book_1-1000" FOR VALUES FROM ('1') TO ('1000'), PARTITIONED,
"public.book_2001-3000" FOR VALUES FROM ('2001') TO ('3000'), PARTITIONED,
"public.book_3001-4000" FOR VALUES FROM ('3001') TO ('4000'), PARTITIONED,
"public.book_4001-5000" FOR VALUES FROM ('4001') TO ('5000'), PARTITIONED,
"public.book_5001-6000" FOR VALUES FROM ('5001') TO ('6000'), PARTITIONED,
"public.book_6001-7000" FOR VALUES FROM ('6001') TO ('7000'), PARTITIONED,
"public.book_7001-8000" FOR VALUES FROM ('7001') TO ('8000'), PARTITIONED,
"public.book_8001-9000" FOR VALUES FROM ('8001') TO ('9000'), PARTITIONED,
"public.book_9001-10000" FOR VALUES FROM ('9001') TO ('10000'), PARTITIONED
mytest=# \d+ "public.book_1-1000"
Partitioned table "public.public.book_1-1000"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------+--------+-----------+----------+----------------------------------+----------+--------------+-------------
id | bigint | | not null | nextval('book_id_seq'::regclass) | plain | |
id_owner | bigint | | not null | | plain | |
added | date | | not null | | plain | |
isbn | text | | | | extended | |
Partition of: book FOR VALUES FROM ('1') TO ('1000')
Partition constraint: ((id_owner IS NOT NULL) AND (id_owner >= '1'::bigint) AND (id_owner < '1000'::bigint))
Partition key: RANGE (added)
Indexes:
"public.book_1-1000_id_idx" btree (id)
Partitions: "public.book_1-1000_1990-01-01" FOR VALUES FROM ('1990-01-01') TO ('1990-04-01'),
"public.book_1-1000_1990-04-01" FOR VALUES FROM ('1990-04-01') TO ('1990-07-01'),
"public.book_1-1000_1990-07-01" FOR VALUES FROM ('1990-07-01') TO ('1990-10-01'),
"public.book_1-1000_1990-10-01" FOR VALUES FROM ('1990-10-01') TO ('1991-01-01'),
"public.book_1-1000_1991-01-01" FOR VALUES FROM ('1991-01-01') TO ('1991-04-01'),
"public.book_1-1000_1991-04-01" FOR VALUES FROM ('1991-04-01') TO ('1991-07-01'),
"public.book_1-1000_1991-07-01" FOR VALUES FROM ('1991-07-01') TO ('1991-10-01'),
"public.book_1-1000_1991-10-01" FOR VALUES FROM ('1991-10-01') TO ('1992-01-01'),
"public.book_1-1000_1992-01-01" FOR VALUES FROM ('1992-01-01') TO ('1992-04-01'),
"public.book_1-1000_1992-04-01" FOR VALUES FROM ('1992-04-01') TO ('1992-07-01'),
"public.book_1-1000_1992-07-01" FOR VALUES FROM ('1992-07-01') TO ('1992-10-01'),
"public.book_1-1000_1992-10-01" FOR VALUES FROM ('1992-10-01') TO ('1993-01-01'),
"public.book_1-1000_1993-01-01" FOR VALUES FROM ('1993-01-01') TO ('1993-04-01'),
"public.book_1-1000_1993-04-01" FOR VALUES FROM ('1993-04-01') TO ('1993-07-01'),
"public.book_1-1000_1993-07-01" FOR VALUES FROM ('1993-07-01') TO ('1993-10-01'),
"public.book_1-1000_1993-10-01" FOR VALUES FROM ('1993-10-01') TO ('1994-01-01'),
"public.book_1-1000_1994-01-01" FOR VALUES FROM ('1994-01-01') TO ('1994-04-01'),
"public.book_1-1000_1994-04-01" FOR VALUES FROM ('1994-04-01') TO ('1994-07-01'),
"public.book_1-1000_1994-07-01" FOR VALUES FROM ('1994-07-01') TO ('1994-10-01'),
"public.book_1-1000_1994-10-01" FOR VALUES FROM ('1994-10-01') TO ('1995-01-01'),
"public.book_1-1000_1995-01-01" FOR VALUES FROM ('1995-01-01') TO ('1995-04-01'),
"public.book_1-1000_1995-04-01" FOR VALUES FROM ('1995-04-01') TO ('1995-07-01'),
"public.book_1-1000_1995-07-01" FOR VALUES FROM ('1995-07-01') TO ('1995-10-01'),
"public.book_1-1000_1995-10-01" FOR VALUES FROM ('1995-10-01') TO ('1996-01-01'),
"public.book_1-1000_1996-01-01" FOR VALUES FROM ('1996-01-01') TO ('1996-04-01'),
"public.book_1-1000_1996-04-01" FOR VALUES FROM ('1996-04-01') TO ('1996-07-01'),
"public.book_1-1000_1996-07-01" FOR VALUES FROM ('1996-07-01') TO ('1996-10-01'),
"public.book_1-1000_1996-10-01" FOR VALUES FROM ('1996-10-01') TO ('1997-01-01'),
"public.book_1-1000_1997-01-01" FOR VALUES FROM ('1997-01-01') TO ('1997-04-01'),
"public.book_1-1000_1997-04-01" FOR VALUES FROM ('1997-04-01') TO ('1997-07-01'),
"public.book_1-1000_1997-07-01" FOR VALUES FROM ('1997-07-01') TO ('1997-10-01'),
"public.book_1-1000_1997-10-01" FOR VALUES FROM ('1997-10-01') TO ('1998-01-01'),
"public.book_1-1000_1998-01-01" FOR VALUES FROM ('1998-01-01') TO ('1998-04-01'),
"public.book_1-1000_1998-04-01" FOR VALUES FROM ('1998-04-01') TO ('1998-07-01'),
"public.book_1-1000_1998-07-01" FOR VALUES FROM ('1998-07-01') TO ('1998-10-01'),
"public.book_1-1000_1998-10-01" FOR VALUES FROM ('1998-10-01') TO ('1999-01-01'),
"public.book_1-1000_1999-01-01" FOR VALUES FROM ('1999-01-01') TO ('1999-04-01'),
"public.book_1-1000_1999-04-01" FOR VALUES FROM ('1999-04-01') TO ('1999-07-01'),
"public.book_1-1000_1999-07-01" FOR VALUES FROM ('1999-07-01') TO ('1999-10-01'),
"public.book_1-1000_1999-10-01" FOR VALUES FROM ('1999-10-01') TO ('2000-01-01'),
"public.book_1-1000_2000-01-01" FOR VALUES FROM ('2000-01-01') TO ('2000-04-01'),
"public.book_1-1000_2000-04-01" FOR VALUES FROM ('2000-04-01') TO ('2000-07-01'),
"public.book_1-1000_2000-07-01" FOR VALUES FROM ('2000-07-01') TO ('2000-10-01'),
"public.book_1-1000_2000-10-01" FOR VALUES FROM ('2000-10-01') TO ('2001-01-01'),
"public.book_1-1000_2001-01-01" FOR VALUES FROM ('2001-01-01') TO ('2001-04-01'),
"public.book_1-1000_2001-04-01" FOR VALUES FROM ('2001-04-01') TO ('2001-07-01'),
"public.book_1-1000_2001-07-01" FOR VALUES FROM ('2001-07-01') TO ('2001-10-01'),
"public.book_1-1000_2001-10-01" FOR VALUES FROM ('2001-10-01') TO ('2002-01-01'),
"public.book_1-1000_2002-01-01" FOR VALUES FROM ('2002-01-01') TO ('2002-04-01'),
"public.book_1-1000_2002-04-01" FOR VALUES FROM ('2002-04-01') TO ('2002-07-01'),
"public.book_1-1000_2002-07-01" FOR VALUES FROM ('2002-07-01') TO ('2002-10-01'),
"public.book_1-1000_2002-10-01" FOR VALUES FROM ('2002-10-01') TO ('2003-01-01'),
"public.book_1-1000_2003-01-01" FOR VALUES FROM ('2003-01-01') TO ('2003-04-01'),
"public.book_1-1000_2003-04-01" FOR VALUES FROM ('2003-04-01') TO ('2003-07-01'),
"public.book_1-1000_2003-07-01" FOR VALUES FROM ('2003-07-01') TO ('2003-10-01'),
"public.book_1-1000_2003-10-01" FOR VALUES FROM ('2003-10-01') TO ('2004-01-01'),
"public.book_1-1000_2004-01-01" FOR VALUES FROM ('2004-01-01') TO ('2004-04-01'),
"public.book_1-1000_2004-04-01" FOR VALUES FROM ('2004-04-01') TO ('2004-07-01'),
"public.book_1-1000_2004-07-01" FOR VALUES FROM ('2004-07-01') TO ('2004-10-01'),
"public.book_1-1000_2004-10-01" FOR VALUES FROM ('2004-10-01') TO ('2005-01-01'),
"public.book_1-1000_2005-01-01" FOR VALUES FROM ('2005-01-01') TO ('2005-04-01'),
"public.book_1-1000_2005-04-01" FOR VALUES FROM ('2005-04-01') TO ('2005-07-01'),
"public.book_1-1000_2005-07-01" FOR VALUES FROM ('2005-07-01') TO ('2005-10-01'),
"public.book_1-1000_2005-10-01" FOR VALUES FROM ('2005-10-01') TO ('2006-01-01'),
"public.book_1-1000_2006-01-01" FOR VALUES FROM ('2006-01-01') TO ('2006-04-01'),
"public.book_1-1000_2006-04-01" FOR VALUES FROM ('2006-04-01') TO ('2006-07-01'),
"public.book_1-1000_2006-07-01" FOR VALUES FROM ('2006-07-01') TO ('2006-10-01'),
"public.book_1-1000_2006-10-01" FOR VALUES FROM ('2006-10-01') TO ('2007-01-01'),
"public.book_1-1000_2007-01-01" FOR VALUES FROM ('2007-01-01') TO ('2007-04-01'),
"public.book_1-1000_2007-04-01" FOR VALUES FROM ('2007-04-01') TO ('2007-07-01'),
"public.book_1-1000_2007-07-01" FOR VALUES FROM ('2007-07-01') TO ('2007-10-01'),
"public.book_1-1000_2007-10-01" FOR VALUES FROM ('2007-10-01') TO ('2008-01-01'),
"public.book_1-1000_2008-01-01" FOR VALUES FROM ('2008-01-01') TO ('2008-04-01'),
"public.book_1-1000_2008-04-01" FOR VALUES FROM ('2008-04-01') TO ('2008-07-01'),
"public.book_1-1000_2008-07-01" FOR VALUES FROM ('2008-07-01') TO ('2008-10-01'),
"public.book_1-1000_2008-10-01" FOR VALUES FROM ('2008-10-01') TO ('2009-01-01'),
"public.book_1-1000_2009-01-01" FOR VALUES FROM ('2009-01-01') TO ('2009-04-01'),
"public.book_1-1000_2009-04-01" FOR VALUES FROM ('2009-04-01') TO ('2009-07-01'),
"public.book_1-1000_2009-07-01" FOR VALUES FROM ('2009-07-01') TO ('2009-10-01'),
"public.book_1-1000_2009-10-01" FOR VALUES FROM ('2009-10-01') TO ('2010-01-01'),
"public.book_1-1000_2010-01-01" FOR VALUES FROM ('2010-01-01') TO ('2010-04-01'),
"public.book_1-1000_2010-04-01" FOR VALUES FROM ('2010-04-01') TO ('2010-07-01'),
"public.book_1-1000_2010-07-01" FOR VALUES FROM ('2010-07-01') TO ('2010-10-01'),
"public.book_1-1000_2010-10-01" FOR VALUES FROM ('2010-10-01') TO ('2011-01-01'),
"public.book_1-1000_2011-01-01" FOR VALUES FROM ('2011-01-01') TO ('2011-04-01'),
"public.book_1-1000_2011-04-01" FOR VALUES FROM ('2011-04-01') TO ('2011-07-01'),
"public.book_1-1000_2011-07-01" FOR VALUES FROM ('2011-07-01') TO ('2011-10-01'),
"public.book_1-1000_2011-10-01" FOR VALUES FROM ('2011-10-01') TO ('2012-01-01'),
"public.book_1-1000_2012-01-01" FOR VALUES FROM ('2012-01-01') TO ('2012-04-01'),
"public.book_1-1000_2012-04-01" FOR VALUES FROM ('2012-04-01') TO ('2012-07-01'),
"public.book_1-1000_2012-07-01" FOR VALUES FROM ('2012-07-01') TO ('2012-10-01'),
"public.book_1-1000_2012-10-01" FOR VALUES FROM ('2012-10-01') TO ('2013-01-01'),
"public.book_1-1000_2013-01-01" FOR VALUES FROM ('2013-01-01') TO ('2013-04-01'),
"public.book_1-1000_2013-04-01" FOR VALUES FROM ('2013-04-01') TO ('2013-07-01'),
"public.book_1-1000_2013-07-01" FOR VALUES FROM ('2013-07-01') TO ('2013-10-01'),
"public.book_1-1000_2013-10-01" FOR VALUES FROM ('2013-10-01') TO ('2014-01-01'),
"public.book_1-1000_2014-01-01" FOR VALUES FROM ('2014-01-01') TO ('2014-04-01'),
"public.book_1-1000_2014-04-01" FOR VALUES FROM ('2014-04-01') TO ('2014-07-01'),
"public.book_1-1000_2014-07-01" FOR VALUES FROM ('2014-07-01') TO ('2014-10-01'),
"public.book_1-1000_2014-10-01" FOR VALUES FROM ('2014-10-01') TO ('2015-01-01'),
"public.book_1-1000_2015-01-01" FOR VALUES FROM ('2015-01-01') TO ('2015-04-01'),
"public.book_1-1000_2015-04-01" FOR VALUES FROM ('2015-04-01') TO ('2015-07-01'),
"public.book_1-1000_2015-07-01" FOR VALUES FROM ('2015-07-01') TO ('2015-10-01'),
"public.book_1-1000_2015-10-01" FOR VALUES FROM ('2015-10-01') TO ('2016-01-01'),
"public.book_1-1000_2016-01-01" FOR VALUES FROM ('2016-01-01') TO ('2016-04-01'),
"public.book_1-1000_2016-04-01" FOR VALUES FROM ('2016-04-01') TO ('2016-07-01'),
"public.book_1-1000_2016-07-01" FOR VALUES FROM ('2016-07-01') TO ('2016-10-01'),
"public.book_1-1000_2016-10-01" FOR VALUES FROM ('2016-10-01') TO ('2017-01-01'),
"public.book_1-1000_2017-01-01" FOR VALUES FROM ('2017-01-01') TO ('2017-04-01'),
"public.book_1-1000_2017-04-01" FOR VALUES FROM ('2017-04-01') TO ('2017-07-01'),
"public.book_1-1000_2017-07-01" FOR VALUES FROM ('2017-07-01') TO ('2017-10-01'),
"public.book_1-1000_2017-10-01" FOR VALUES FROM ('2017-10-01') TO ('2018-01-01'),
"public.book_1-1000_2018-01-01" FOR VALUES FROM ('2018-01-01') TO ('2018-04-01'),
"public.book_1-1000_2018-04-01" FOR VALUES FROM ('2018-04-01') TO ('2018-07-01'),
"public.book_1-1000_2018-07-01" FOR VALUES FROM ('2018-07-01') TO ('2018-10-01'),
"public.book_1-1000_2018-10-01" FOR VALUES FROM ('2018-10-01') TO ('2019-01-01'),
"public.book_1-1000_2019-01-01" FOR VALUES FROM ('2019-01-01') TO ('2019-04-01'),
"public.book_1-1000_2019-04-01" FOR VALUES FROM ('2019-04-01') TO ('2019-07-01'),
"public.book_1-1000_2019-07-01" FOR VALUES FROM ('2019-07-01') TO ('2019-10-01')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment