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
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
6
votes
1
answer
6k
views
Conditional INSERT with a nested CTE?
I am trying to figure out if there is a way I can make nested CTEs work for this particular case.
Consider the following (highly contrived) scenario which is based on the actual application: there's ...
- 823
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 ...
- 145
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
-----------+---------...
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 ...
- 1,174
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 ...
- 307
1
vote
1
answer
3k
views
Why does ENCODE on BYTEA not seem to have any performance impact?
I am working on a scenario to store strings in hex format of fixed length 64 to database. Obviously the choices are BYTEA and CHAR(64).
Initial thought was to enforce a valid hex string storing it ...
- 175
0
votes
2
answers
1k
views
Query that was working on MySQL is not working on PostgreSQL
I had the following query (with bin_number being a string) which was working on MySQL 5.5:
Postgres - was working on MySQL and doesn't work on PostgreSQL
SELECT "menu_items".*
FROM "menu_items"
...
- 657
2
votes
1
answer
3k
views
Poor query performance due to recheck condition on index scan
We are testing the performance of metadata when stored in JSONB compared to a traditional EAV catalogue (commonly used in biomedical scenarios), using a PostgreSQL 9.4 server.
I get a problem when ...
- 23
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,...
1
vote
1
answer
2k
views
Index by subkey in jsonb
I have a table payment with a jsonb column application. It contains payments and the total amount for one or more tours. For example, a parent can pay 1000 $ for all his family, e.g. 4 children and ...
- 201
5
votes
1
answer
854
views
Index not being used in SELECT query
I have a table of about 3.25M rows with the follow format in Postgres 9.4.1
CREATE TABLE stats
(
id serial NOT NULL,
type character varying(255) NOT NULL,
"references" jsonb NOT NULL,
...
- 285
2
votes
1
answer
2k
views
EXECUTE within function not using index?
I have an accounts table with ~200k rows and an index for these columns:
account_type_id BIGINT
, member_id BIGINT
, external_id VARCHAR(64)
CREATE INDEX account_full_qualifiers_idx
ON ...
- 3,428
4
votes
2
answers
395
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
2
answers
1k
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 ...
- 203
2
votes
2
answers
295
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 (
...
1
vote
3
answers
430
views
Not able to search on zipcode column a table in postgresql
Hello I have a postgresql question about searching on zip codes. Basicly, if I do a:
select * from my_table where zip_ = '90210';
I am returned 0 rows. I know for a fact that there is thousands ...
- 11
1
vote
1
answer
374
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 ...
- 459
0
votes
1
answer
284
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 ...
- 45
1
vote
1
answer
108
views
Finding the current prices for n Fuelstations at a certain point in time
I have a Table where price information is stored in with approx 13 million rows stored in a PostgreSQL 9.5 database.
CREATE TABLE public.de_tt_priceinfo (
id integer NOT NULL DEFAULT nextval('...
- 298
1
vote
2
answers
124
views
3 additional varchar(32) keys and also updating them -- too bad for performance?
I have a table in mind which basicaly would look like this:
table1
--------
id bigserial autoincrement primary key,
token1 varchar(32) unique not null,
token2 varchar(32) unique not null,
...
- 13
1
vote
2
answers
84
views
Find the greatest difference between each unique record with different timestamps
I have a PostgreSQL table with the following schema and data:
CREATE TABLE IF NOT EXISTS T(
id uuid PRIMARY KEY,
username varchar(15),
person varchar(10),
tweets int,
...
- 13