Linked Questions
19 questions linked to/from What is the overhead for varchar(n)?
124
votes
5
answers
119k
views
Measure the size of a PostgreSQL table row
I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some ...
- 1,489
47
votes
2
answers
46k
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 ...
- 573
43
votes
1
answer
41k
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 ...
- 533
23
votes
4
answers
26k
views
Index performance for CHAR vs VARCHAR (Postgres)
In this answer (https://stackoverflow.com/questions/517579/strings-as-primary-keys-in-sql-database) a single remark caught my eye:
Also keep in mind that there's often a very big difference between ...
- 421
40
votes
1
answer
13k
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 ...
- 633
28
votes
2
answers
14k
views
How to store one-byte integer in PostgreSQL?
In PostgreSQL documentation, it is said that integer data types can be stored in either two-, four- or eight-byte space. One of the columns of a table in my database contains a one-byte integer value ...
- 701
10
votes
2
answers
11k
views
Using PostgreSQL what is the difference between a smallint and a bool for storing boolean?
What is the difference between the smallint type and the bool type for storing boolean values?
This question arose in the comments to a question on Geographic Information Systems Stack Exchange.
- 56.6k
9
votes
3
answers
4k
views
Lookup performance of Numeric vs String
I'm working on a project that is using the instagram key format.
TL;DR 64-bit integer ids.
They'll be used for lookups, and we also like them for sorting and batching since they will naturally sort ...
- 469
12
votes
1
answer
9k
views
Why *not* ERROR: index row size xxxx exceeds maximum 2712 for index "foo"?
We have repeatedly seen failing attempts to index columns with values exceeding a maximum size. Postgres 10 has this kind of error message for it:
ERROR: index row size xxxx exceeds maximum 2712 for ...
- 154k
9
votes
2
answers
5k
views
Shall I use enum when are too many "categories" with PostgreSQL?
Problem
I plan to load a CSV with more 10 million records into PostgreSQL v12.1, one of its columns has "categorical" values, so creating an enumerated type for it seems to be a good choice, but it ...
- 335
1
vote
2
answers
2k
views
varchar(n) size?
When I use varchar(5) in a INSERT query it means that the attribute in the table will take exactly 5 bytes in memory? (Given that one printable character takes one byte)?
- 35
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
3
votes
1
answer
2k
views
Nested Loop with Materialize resulting in very slow query on postgresql 9.5
I have an analytics database on Postgres 9.5
I'm taking advantage of constraint partitioning with table inheritance to split up events into monthly tables. The base (parent) table contains no rows.
I ...
- 975
1
vote
1
answer
1k
views
Detailed optimal column order with large text and bytea
I read this excellent answer on optimal column ordering and noticed the summation:
Generally speaking, if you put the 8-byte-types first then the 4-byte-types and the 2-byte-types last you can't go ...
user32234
0
votes
1
answer
771
views
Can I modify a column's data type against a live table in Postgres?
I have a sparse matrix table where every attribute is citext. There are some indexes built against a few of the fields.
I want to change these columns back to varchar.
Can I do this against a live ...
- 303