Linked Questions

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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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.
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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)?
user avatar
  • 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), ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 303

15 30 50 per page