Linked Questions
47 questions linked to/from Measure the size of a PostgreSQL table row
1
vote
1
answer
7k
views
PostgreSQL, UPDATE CASE statement uses huge amount of space
I am running a CASE statement on one of my tables and it consumes a huge amount of hard drive space that is never freed up even when i run VACUUM ANALYZE. Autovacuum is ON as well.
I am using ...
- 113
5
votes
1
answer
4k
views
Schema for analytics table in Postgres
We use Postgres for analytics (star schema).
Every few seconds we get reports on ~500 metrics types.
The simplest schema would be:
timestamp metric_type value
78930890 FOO ...
- 53
5
votes
1
answer
1k
views
For alignment optimized table is bigger than original table - why?
In another question I learned that I should optimize the layout from one of my tables to save space and have better performance. I did this, but ended up with a larger table than before and ...
- 957
3
votes
1
answer
5k
views
How to reduce size of PostgreSQL table after dropping columns?
I have a PostgreSQL database. One table is very large. I want to extract a TEXT column into a separate table and see how much I can reduce the size. The problem is that the size appears to stay the ...
- 31
2
votes
1
answer
1k
views
Nullable columns or jsonb for storing user profile?
I'm deciding between using nullable fields or jsonb to store user profile. Initially, this would be used for contacts: email and phone. I anticipate that there may be other columns added later, such ...
- 749
1
vote
2
answers
3k
views
Size of data in DB is much larger than size of data dumped to disk
I have a table that contains 99.9% of the data in the database. When I query the size of the table and database, I get:
mydb=# SELECT pg_size_pretty( pg_database_size('mydb') );
pg_size_pretty
------...
- 153
2
votes
2
answers
2k
views
How to determine or predict space on disk per table row?
I'm very new to Postgres so my math could be off here ...
This is my table:
CREATE TABLE audit (
id BIGSERIAL PRIMARY KEY,
content_id VARCHAR (50) NULL,
type VARCHAR (100) NOT NULL,
...
- 123
3
votes
1
answer
1k
views
Use of integer instead of interval (of one type)
Our DB design presently has a interval column which will only be storing days (no other interval type) so it is making sense to use INT2 (smallint) instead of interval. Reference to documentation.
...
- 180
1
vote
1
answer
2k
views
UPDATE incredibly slow on small PostgreSQL query - EXPLAIN ANALYZE causes it to hang
I'm trying to debug a very slow UPDATE query in one of my tables.
The table name is beepers. It has 2486 rows and weighs 1204 MB.
It has 18 columns and two indices: one on id and the other on ...
- 11
1
vote
1
answer
1k
views
Log large queries with PGbouncer
Is there a way to ask pgbouncer to log any query that generates a result set larger than, say, 5MB?
- 825
4
votes
1
answer
1k
views
Reducing table size bytes per row in a simple PostgreSQL database
I have a pretty simple 3 column table in postgresql 11. It's timeseries data and the table can contain many billions of rows. I'm concerned about my table size and total size, and want to optimize my ...
- 53
2
votes
1
answer
2k
views
Difference between total table size and size of all pages in postgresql
Working with Postgres I noticed the following:
When I run this query I get the total table size:
SELECT pg_size_pretty( pg_table_size('tenk1') );
I get 384kb.
And when I run:
SELECT relpages FROM ...
Piet Pro
1
vote
1
answer
708
views
Database is 300 GB, but I only see small tables?
I wonder why my database is so large. I type:
SELECT pg_size_pretty(pg_database_size('defaultdb'));
and it says defaultdb is 259 GB. That is huge.
So I list my tables in psql with:
\dt
For each ...
- 113
0
votes
1
answer
661
views
Table with smaller data types seems to take more space on disk?
I have these two identical tables:
Table "public.region"
Column | Type | Collation | Nullable | Default
-------------+----------+-----------+----------+---------
...
- 235
1
vote
1
answer
189
views
how to understand the whole table tenk1 row size byte by byte
first query from Measure the size of a PostgreSQL table row
table tenk1 from https://github.com/postgres/postgres/blob/6ff2e8cdd410f70057cfa6259ad395c1119aeb32/src/test/regress/sql/test_setup.sql#L144
...
- 467