Linked Questions
16 questions linked to/from Should I add an arbitrary length limit to VARCHAR columns?
137
votes
5
answers
155k
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,631
29
votes
4
answers
32k
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 ...
- 503
44
votes
1
answer
16k
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 ...
- 683
18
votes
1
answer
111k
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) ...
- 375
3
votes
2
answers
20k
views
Very slow execution of a simple query
I try to create a report for my data, but it is really slow on a big table.
The table structure is:
CREATE TABLE posts
(
id serial NOT NULL,
project_id integer,
moderation character varying(...
5
votes
1
answer
25k
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
1
answer
2k
views
Do datatypes of columns affect query performance?
Let's suppose I have this table:
Table "public.orders"
Column | Type | Collation | Nullable | Default
-----------------+---------------+-----------+----------+----...
- 235
1
vote
2
answers
6k
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
5
votes
2
answers
1k
views
How to make this query use my multicolumn index?
Currently, I have a view which is defined like this:
View "public.customer_list"
Column | Type | Modifiers | Storage | Description
-----------+---------...
5
votes
1
answer
5k
views
Is varchar(x) as fast as `text CHECK ( char_length(x) )`?
In an Twitter exchange, Simon West asks to Brandur,
Out of interest, why do use email TEXT CHECK (char_length(email) <= 255) rather than email VARCHAR(255)? Not a pattern I've seen before
...
- 62.2k
4
votes
2
answers
791
views
Compound Index on 43 Million PostgreSQL table
This question is related to a prior one of I asked: Order of columns in a compound index in PostgreSQL (and query order)
Rather than overload that question, I figure I can sharpen and limit my ...
- 389
3
votes
3
answers
406
views
Impact of index on a "status" field with one (guaranteed) change
Introduction
I have a PostgreSQL table setup as a queue/event-source.
I would very much like to keep the "order" of the events (even after the queue item has been processed) as a source for ...
- 33
2
votes
1
answer
1k
views
Sensible max_length for Postgres db charfields
I am using Django with a Postgres backend.
In the Django models, I have to set the max_length attribute on the Charfields, then the Django ORM deals with the database in the backend. In a lot of cases,...
1
vote
3
answers
295
views
How to map similar camelcased, pascalcased, underscored, lowerscored words to the same id?
I'm working on PostgreSQL and I have a table like this called words_table:
I need all equivalent words (like software_design and software-design) to have its map value = to its word's equivalent ...
1
vote
1
answer
121
views
RIGHT() function in postgresql 9.1 not behaving as anticipated?
The following is with regard to PostgresQL 9.1 :
I am using the right() and left() string functions to fix a few instances where data was input into my database being “off by one” .. so I’m doing ...
- 3,318