Linked Questions
47 questions linked to/from Measure the size of a PostgreSQL table row
4
votes
1
answer
12k
views
Calculate storage size of rows? [duplicate]
In a multi-tenant only one DB, one column is used to isolate customers data, that is customer_id.
Is it possible to get the storage size of a certain customer in all tables?
- 301
8
votes
0
answers
150
views
How to do Statistics in Partial Indexes of PostgreSQL efficiently? [duplicate]
This answer motivated think again my following tables but I am still uncertain how to store variables/results from data analysis for quick selection in post-processing.
If stored, I think it is well ...
0
votes
0
answers
77
views
Calculating the row size of a table [duplicate]
Is there a way to calculate the row size (in bytes) of a particular table in postgresql / pgadmin3?
- 999
93
votes
4
answers
98k
views
Should I add an arbitrary length limit to VARCHAR columns?
According to PostgreSQL's docs, there's no performance difference between VARCHAR, VARCHAR(n) and TEXT.
Should I add an arbitrary length limit to a name or address column?
Edit: Not a dupe of:
...
- 1,275
129
votes
3
answers
66k
views
Is a composite index also good for queries on the first field?
Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index?
...
- 1,741
53
votes
2
answers
63k
views
Best way to populate a new column in a large table?
We have a 2.2 GB table in Postgres with 7,801,611 rows in it. We are adding a uuid/guid column to it and I am wondering what the best way to populate that column is (as we want to add a NOT NULL ...
- 755
49
votes
2
answers
57k
views
Configuring PostgreSQL for read performance
Our system writes a lots of data (kind of Big Data system). Write performance is good enough for our needs but read performance is really too slow.
The primary key (constraint) structure is similar ...
- 593
28
votes
5
answers
90k
views
SELECT DISTINCT on multiple columns
Supposing we have a table with four columns (a,b,c,d) of the same data type.
Is it possible to select all distinct values within the data in the columns and return them as a single column or do I ...
- 1,956
54
votes
1
answer
59k
views
Indexes: integer vs string performance if the number of nodes is the same
I am developing an application in Ruby on Rails with the PostgreSQL (9.4) database. For my use case, columns in tables will be looked up very frequently, as the whole point of the application is ...
- 643
46
votes
1
answer
18k
views
Would index lookup be noticeably faster with char vs varchar when all values are 36 chars
I have a legacy schema (disclaimer!) that uses a hash-based generated id for the primary key for all tables (there are many). An example of such an id is:
922475bb-ad93-43ee-9487-d2671b886479
There ...
- 713
37
votes
1
answer
57k
views
VACUUM returning disk space to operating system
VACUUM usually does not return disk space to operating system, except in some special cases.
From the docs:
The standard form of VACUUM removes dead row versions in tables and indexes and marks the ...
- 625
24
votes
2
answers
78k
views
PostgreSQL "size of temporary files"
I've imported data into a new database (about 600m rows of timestamp, integer, double).
I then created some indexes and tried to alter some columns (got some out of space issues), the database is ...
- 821
25
votes
1
answer
22k
views
What is the overhead for varchar(n)?
I wanted to ask for the meaning of this fragment from Postgres doc regarding varchar(n) type:
The storage requirement for a short string (up to 126 bytes) is 1 byte
plus the actual string, which ...
- 353
19
votes
1
answer
15k
views
Postgres: check disk space taken by materialized view?
I know how to check the size of indexes and tables in Postgres (I'm using version 9.4):
SELECT
relname AS objectname,
relkind AS objecttype,
reltuples AS "#entries", pg_size_pretty(relpages::...
- 333
14
votes
1
answer
8k
views
How do completely empty columns in a large table affect performance?
I have 400 million rows in a Postgres db, and the table has 18 columns:
id serial NOT NULL,
a integer,
b integer,
c integer,
d smallint,
e timestamp without time zone,
f smallint,
g timestamp without ...
- 243
7
votes
5
answers
16k
views
Is it bad practice to add columns to an existing table?
Conversely: Is it better to get all the columns created at the time when a new table is created?
I'm working on a new system and new requirements are coming up all the time. The latest requirement ...
- 623
12
votes
2
answers
23k
views
Proper storage size estimation technique for PostgreSQL
We are preparing a PostgreSQL database for production usage and we need to estimate the storage size for this database. We are a team of developers with low expertise on database administration, so we ...
- 229
11
votes
2
answers
2k
views
What fillfactor for caching table?
I have heavily updated / accessed table where I store serialized java objects. They are in the table for 2-3 hours (also are being updated during that period) and then removed. Size of table is around ...
- 163
9
votes
3
answers
6k
views
Speed up creation of Postgres partial index
I am trying to create partial indexes for a large (1.2TB), static table in Postgres 9.4.
My data is completely static, so I am able to insert all data, then create all indexes.
In this 1.2TB table, ...
- 285
6
votes
3
answers
6k
views
Advantage of using INCLUDE as against adding the column in INDEX for covering index
Postgres docs state the following about Index-Only Scans and Covering-Indexes:
if you commonly run queries like
SELECT y FROM tab WHERE x = 'key';
the traditional approach to speeding up such queries ...
- 1,243
6
votes
1
answer
9k
views
Postgres partial index on IS NULL not working
Postgres version
Using PostgreSQL 10.3.
Table definition
CREATE TABLE tickets (
id bigserial primary key,
state character varying,
closed timestamp
);
CREATE INDEX "state_index" ON "tickets"...
- 115
7
votes
2
answers
11k
views
What is the most efficient way to count the number of rows in a table?
I am using Postgres with the following query:
select count(*) from image;
The primary key on this table is non-incrementing; it's a unique serial number for the images stored in the table. Our app ...
- 661
6
votes
2
answers
5k
views
Exists subselect vs inner join?
I'm moving up to the next level of my mystery query. It looks like there's a subselect inside of an exists, but on the same table. I think this could probably be simplified with an INNER JOIN higher ...
- 113
4
votes
2
answers
5k
views
Use column values in ORDER BY and LIMIT clauses
I have this table in a Postgres 9.3 database:
I need to use the content of columns order and limit to sort and filter this table. The table will be sorted by column first_name.
The final result will ...
- 233
9
votes
2
answers
9k
views
Optimizing queries on a range of timestamps (one column)
I am using Postgres 9.3 through Heroku.
I have a table, "traffic", with 1M+ records that has many inserts and updates every day. I need to perform SUM operations across this table over different time ...
- 1,193
4
votes
1
answer
16k
views
Find bloated tables and indexes in PostgreSQL without extensions
I have tables that change a lot during the day, lots of data is deleted, modified and inserted.
I suspect that the tables and indexes on those tables might be bloated.
I've seen that there are ...
- 428
3
votes
2
answers
9k
views
Real time much greater than `EXPLAIN ANALYZE`'s execution time (index scan)
I want to fetch up to 100 rows based on their id. The id is the primary key of the table.
The query that I had written looks like this:
select * from table where id = any ($1);
where $1 is ...
- 525
5
votes
2
answers
3k
views
Good layout of 3d point data for spatial queries in Postgres?
Like shown in another question, I deal with a lot (>10,000,000) entries of points in a 3D space. These points are defined like this:
CREATE TYPE float3d AS (
x real,
y real,
z real);
If I am ...
- 957
2
votes
1
answer
2k
views
Using pg_prewarm to load X number of latest rows into cache
We have a large query that is extra slow when customers "run it the first time, early in the morning..."
So, I found pg_prewarm that I would like to use load into PG's buffer cache
a certain amount ...
- 471
3
votes
1
answer
2k
views
Why is my database 12 times bigger than inserted data?
I have a short question concerning my database size. I need to insert data in a database. Before the insert, some calculations need to be done.
The point is: from 50 mb plain data (~700,000 lines), ...
- 45