Linked Questions
37 questions linked to/from Would index lookup be noticeably faster with char vs varchar when all values are 36 chars
138
votes
5
answers
169k
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,655
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
78
votes
6
answers
153k
views
How do I efficiently get "the most recent corresponding row"?
I have a query pattern that must be very common, but I don't know how to write an efficient query for it. I want to look up the rows of a table that correspond to "the most recent date not after" the ...
- 1,619
57
votes
3
answers
53k
views
What is the optimal data type for an MD5 field?
We are designing a system that is known to be read-heavy (on the order of tens of thousands of reads per minute).
There is a table names that serves as a sort of central registry. Each row has a ...
- 765
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
30
votes
4
answers
36k
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 ...
- 513
21
votes
4
answers
24k
views
What's the most efficient UUID column type
For storing a 128 bits UUID there are multiple storage options:
a byte[16] column
two bigint/long(64 bits) columns
a CHAR(36) column - 32 hex digits + 4 dashes.
a UUID database specific column, if db ...
- 907
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
20
votes
1
answer
114k
views
Fixing table structure to avoid `Error: duplicate key value violates unique constraint`
I have a table which is created this way:
--
-- Table: #__content
--
CREATE TABLE "jos_content" (
"id" serial NOT NULL,
"asset_id" bigint DEFAULT 0 NOT NULL,
...
"xreference" varchar(50) ...
- 395
13
votes
2
answers
110k
views
How to use default value of data type as column default?
How can I specify the default value for a column in the DDL? I was pretty sure that the following method worked a while ago:
CREATE TABLE test
(
col_1 CHAR(12) NOT NULL,
col_2 INTEGER NOT NULL ...
- 149
18
votes
4
answers
59k
views
Casting an array of texts to an array of UUIDs
How can I cast an array of texts into an array of UUIDs?
I need to do a join between two tables: users and projects.
The users table has an array field named project_ids containing the project IDs ...
- 413
9
votes
1
answer
16k
views
Optimise a LATERAL JOIN query on a big table
I'm using Postgres 9.5. I have a table that records page hits from several web sites. This table contains about 32 million rows spanning from Jan 1, 2016 to June 30, 2016.
CREATE TABLE event_pg (
...
- 1,174
5
votes
1
answer
27k
views
Improve UPDATE performance on big table
I'm using Postgres 9.5 on Amazon RDS (2vCPU, 8 GB RAM).
I use pganalyze to monitor my performance.
I have around 200K records in the database.
In my Dashboard I see the following queries are taking ...
- 53
8
votes
3
answers
2k
views
Is it reasonable to mark all columns but one as primary key?
I have a table representing movies. The fields are:
id (PK), title, genre, runtime, released_in, tags, origin, downloads.
My database cannot be polluted by duplicated rows, so I want to enforce ...
- 525
9
votes
1
answer
3k
views
Do fixed-width rows improve PostgreSQL read performance?
I have a table articles:
Table "articles"
Column | Type | Modifiers ...
- 765