Linked Questions

0
votes
1answer
837 views

Is there any significant performance difference when using string vs bigint as a primary key in Postgres (Aurora)? [duplicate]

We can use same logic for both types (string/bigint) to generate consecutive ids (e.g. like https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c). This way both are naturally ...
122
votes
5answers
107k 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 ...
48
votes
2answers
43k 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 ...
39
votes
1answer
12k 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 ...
20
votes
1answer
11k 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 ...
2
votes
4answers
3k views

When do I use another type index than integer? (eg string) [integer vs string index]

I was reading about relational databases like sqlite and I always see the schema for user storage like: UserID (index) | Username | Password but wouldn't it be actually much faster to use Username ...
4
votes
2answers
321 views

Optimize query to find top N users who commented on a post

Problem Trying to find the most efficient query to retrieve the top N (5 in the examples) users who have commented on a post, where a user is considered 'top' if they have the most followers. The ...
1
vote
1answer
222 views

Is normalizing further to index on INT FK rather than VARCHAR worth it?

I asked a question about whether or not I should normalize some static data and the general consensus was that it is not necessary since the data is unchanging, thus it is not redundant and will not ...
1
vote
2answers
157 views

Does it matter to convert integer primary key into equivalent string?

In a PostgreSQL DB, I have a sortable bigint primary key, which is a bit verbose in terms of readability. I want to encode it as base36 or base64, like using char(n). In theory the number of bytes ...
1
vote
1answer
41 views

PostgreSQL chooses a wrong order of Hash Join

EXPLAIN shows that PostgreSQL chooses a bad plan for hash join. it should have first iterate by tags_name_value_height2 in the table tags and use it to find corresponding transactions.id, but instead ...