1

The size of postgres data folder is 302 GB. But, when I try to view individual table size in psql using \d+ , the size returned for the tables is unrealistic

                                                         List of relations
 Schema |                  Name                | Type  |  Owner   | Persistence | Access method |    Size    | Description
--------+--------------------------------------+-------+----------+-------------+---------------+------------+-------------
 public | TimeSeriesData1                      | table | postgres | permanent   | heap          | 296 MB     |
 public | TimeSeriesData2                      | table | postgres | permanent   | heap          | 8192 bytes |
 public | TimeSeriesDataPoints                 | table | postgres | permanent   | heap          | 8192 bytes |
 public | TimeSeriesDataPoints_NEW             | table | postgres | permanent   | heap          | 8192 bytes |
 public | TimeSeriesDataPoints_NEW1            | table | postgres | permanent   | heap          | 8192 bytes |
 public | TimeSeriesDataPoints_custom          | table | postgres | permanent   | heap          | 8192 bytes |
 public | TimeSeriesDataPoints_custom1         | table | postgres | permanent   | heap          | 8192 bytes |
 public | TimeSeriesDataPoints_jsonb           | table | postgres | permanent   | heap          | 128 kB     |
 public | TimeSeriesDataPoints_jsonb1          | table | postgres | permanent   | heap          | 8192 bytes |
 public | TimeSeriesDataPoints_jsonb2          | table | postgres | permanent   | heap          | 8192 bytes |
 public | TimeSeriesData3                      | table | postgres | permanent   | heap          | 198 MB     |
 public | TimeSeriesData4                      | table | postgres | permanent   | heap          | 8192 bytes |
 public | TimeSeriesData5                      | table | postgres | permanent   | heap          | 8192 bytes |
 public | samplesTimeseries                    | table | postgres | permanent   | heap          | 4400 kB    |
 public | chunk_TimeSeriesData                 | table | postgres | permanent   | heap          | 8192 bytes |

TimeSeriesDataPoints table is huge and has more than 1 billion records. But, it is showing as 8192 bytes. I tried reindex and vacuum commands. But, unable to get the realistic table sizes. Could you please help

Ann
  • 137
  • 1
  • 11
  • 1
    There might be other databases, there might be other schemas, there might be indexes, there might be materialized views, there might be lots of WAL segments in `pg_wal` that you must not delete. My crystal ball suggests the latter. – Laurenz Albe Jul 14 '22 at 10:44
  • 1
    Please try to avoid using words like *crore* that are not globally understood. – James Z Jul 14 '22 at 11:14
  • To your question add 1) Postgres version. 2) Are you sure you are pointing at correct database when looking at table sizes? 3) Are *DataPoints tables partitions? 4) What do you get when you do `select count(*) from "TimeSeriesDataPoints"` or any of the other *DataPoints tables? – Adrian Klaver Jul 14 '22 at 14:56
  • 3
    Also, there could be child tables. \d+ does not evaluate child tables for partitioned tables. Check out [this](https://stackoverflow.com/questions/54920864/get-table-size-of-partitioned-table-postgres-10). Also, there are some cases when you drop table, it still stuck on the file system. Better to check "lsof| grep deleted" if it returns something that contains related to your data directory. – Umut TEKİN Jul 14 '22 at 15:00
  • @jjanes timescaledb version: 2.7.0 – Ann Jul 15 '22 at 07:54
  • @AdrianKlaver the count of TimeSeriesDataPoints table is 1,000,230,000. – Ann Jul 15 '22 at 07:56

1 Answers1

1

\d+ only shows things in your search_path. timescaledb puts its data into its own schema, which is usually not listed in your search_path. You could add '_timescaledb_internal' to your seach_path, or just give a one-time command to show those tables with \d+ _timescaledb_internal.*

jjanes
  • 37,812
  • 5
  • 27
  • 34