Linked Questions

4 votes
1 answer
3k 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 ...
21 votes
4 answers
23k 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 ...
16 votes
4 answers
54k 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 ...
0 votes
1 answer
156 views

Does a long string with "unique" constaint take a lot of space?

I've created a table "global_settings" for storing different settings of a web applications -- the ones, that I want to be able to edit on html page: global_settings: id primary key, key ...
2 votes
2 answers
283 views

Any pitfalls/benefits of creating PK with include(..) in PostgreSQL?

Application is aggressively caching data in memory and in order to support consistency (preventing persisting stale data) it is doing something like: -- typical table structure: create table t1 ( ...
2 votes
2 answers
3k views

What is more efficient: text (765) or varchar (765) to store a freeform searchable description?

EDIT : To paraphrase quesiton - at what point do I switch from varchar to text and back? A little background on the question. Let's say we have sales_orders table and we want to be able to keep a ...
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 -----------+---------...
92 votes
4 answers
91k 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: ...
54 votes
1 answer
55k 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 ...
5 votes
1 answer
2k views

Low cardinality fields: integer or string

A Ticket has the statuses: new in_progress on_hold closed I could make ticket.status a string (on_hold) or an unique int(2). It is indexed. Int pros: smallest size in index Int con: low clarity ...
13 votes
2 answers
105k 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 ...
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 -----------------+---------------+-----------+----------+----...
57 votes
3 answers
51k 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 ...
2 votes
1 answer
2k 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,...
20 votes
1 answer
113k 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) ...

15 30 50 per page