Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
1,652
questions
123
votes
3
answers
59k
views
Is a composite index also good for queries on the first field?
Let's say I have a table with fields A and B. I make regular queries on A+B, so I created a composite index on (A,B). Would queries on only A also be fully optimized by the composite index?
...
- 1,661
49
votes
2
answers
53k
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 ...
- 593
144
votes
8
answers
231k
views
Pattern matching with LIKE, SIMILAR TO or regular expressions
I had to write a simple query where I go looking for people's name that start with a B or a D:
SELECT s.name
FROM spelers s
WHERE s.name LIKE 'B%' OR s.name LIKE 'D%'
ORDER BY 1
I was wondering if ...
- 1,785
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
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
145
votes
4
answers
161k
views
Optimizing queries on a range of timestamps (two columns)
I use PostgreSQL 9.1 on Ubuntu 12.04.
I need to select records inside a range of time: my table time_limits has two timestamp fields and one integer property. There are additional columns in my ...
- 8,501
46
votes
1
answer
32k
views
SQL injection in Postgres functions vs prepared queries
In Postgres, are prepared queries and user defined functions equivalent as a mechanism for guarding against SQL injection?
Are there particular advantages in one approach over the other?
139
votes
1
answer
164k
views
Postgres UPDATE ... LIMIT 1
I have a Postgres database which contains details on clusters of servers, such as server status ('active', 'standby' etc). Active servers at any time may need to fail over to a standby, and I don't ...
- 1,555
41
votes
2
answers
42k
views
Multicolumn index and performance
I have a table with a multicolumn index, and I have doubts about the proper sorting of the indexes to get the maximum performance on the queries.
The scenario:
PostgreSQL 8.4, table with about one ...
- 711
29
votes
4
answers
6k
views
Can spatial index help a "range - order by - limit" query
Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes.
We have the following table with a tree structure (Nested Set model) of words and their frequencies:...
- 97.4k
13
votes
2
answers
43k
views
Return counts for multiple ranges in a single SELECT statement
I have a Postgres database table foo that, among other things, has a column for score that ranges from 0 - 10. I want a query to return the total number of scores, the number of scores between 0 and 3,...
- 397
13
votes
2
answers
11k
views
Select longest continuous sequence
I am trying to construct a query in PostgreSQL 9.0 that gets the longest sequence of continuous rows for a specific column.
Consider the following table:
lap_id (serial), lap_no (int), car_type (...
- 299
93
votes
5
answers
41k
views
Working of indexes in PostgreSQL
I have a couple of questions regarding working of indexes in PostgreSQL.
I have a Friends table with the following index:
Friends ( user_id1 ,user_id2)
user_id1 and user_id2 are foreign keys to ...
- 1,963
73
votes
4
answers
55k
views
Function Performance
Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company.
One of the things I ...
- 23.4k
39
votes
3
answers
41k
views
Are regular VACUUM ANALYZE still recommended under 9.1?
I'm using PostgreSQL 9.1 on Ubuntu. Are scheduled VACUUM ANALYZE still recommended, or is autovacuum enough to take care of all needs?
If the answer is "it depends", then:
I have a largish database (...
- 1,423
25
votes
3
answers
13k
views
How is LIKE implemented?
Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it?
The naive approach would be to inspect ...
- 353
38
votes
3
answers
44k
views
For absolute performance, is SUM faster or COUNT?
This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100.
I tend to prefer
COUNT(CASE WHEN invoice_amount > 100 THEN 1 END)
However, this is ...
- 4,310
134
votes
8
answers
431k
views
How to turn JSON array into Postgres array?
I have a column data of type json that holds JSON documents like this:
{
"name": "foo",
"tags": ["foo", "bar"]
}
I would like to turn the nested tags array into a concatenated string ('foo, ...
- 1,533
25
votes
1
answer
25k
views
How to preserve the original order of elements in an unnested array?
Given the string:
'I think that PostgreSQL is nifty'
I would like to operate on the individual words found within that string. Essentially, I have a separate from which I can get word details and ...
- 10.6k
25
votes
1
answer
19k
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
19
votes
2
answers
18k
views
Why is array_agg() slower than the non-aggregate ARRAY() constructor?
I was just reviewing some old code written for pre-8.4 PostgreSQL, and I saw something really nifty. I remember having a custom function do some of this back in the day, but I forgot what pre-...
- 62.1k
95
votes
3
answers
467k
views
How do I insert a row which contains a foreign key?
Using PostgreSQL v9.1. I have the following tables:
CREATE TABLE foo
(
id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
type VARCHAR(60) NOT NULL UNIQUE
);
CREATE TABLE bar
(
id BIGSERIAL ...
- 5,335
78
votes
6
answers
148k
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,559
56
votes
3
answers
48k
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 ...
- 755
34
votes
3
answers
30k
views
Is the CPU performance relevant for a database server?
This is a purely theoretical question. Let's say I have an application deployed on multiple servers.
A load balancer,
Multiple/scalable applications servers
A (single) database server (for the ...
- 451
208
votes
5
answers
553k
views
Granting access to all tables for a user
I'm new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, and DELETE privileges on a low privileged user and enable those grants to apply to all ...
- 2,185
70
votes
1
answer
87k
views
How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?
I want to migrate a fairly simple, internal, database driven application from SQLite3 to PostgreSQL 9.3 and tighten the permissions in the DB as I go.
The application currently consists of a command ...
- 755
69
votes
3
answers
106k
views
Optimizing a Postgres query with a large IN
This query gets a list of posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others.
With this style of query, the obvious optimization ...
- 1,023
9
votes
2
answers
6k
views
Developing a database for a funds transfers business where (a) people and organizations can (b) send and receive money
In the business context of relevance, both members and organizations need to have an account for funds. Funds can be transferred
from member to member,
from member to organization,
from organization ...
- 93
4
votes
2
answers
5k
views
Can Postgres use an index-only scan for this query with joined tables?
This is a follow-up to: Do covering indexes in PostgreSQL help JOIN columns?
Consider the inverse of the schema in the other question where you filter in the joined-on table:
CREATE TABLE ...
- 669
241
votes
6
answers
385k
views
Force drop db while others may be connected
I need to remove a database from a PostgreSQL DB cluster. How can I do it even if there are active connections? I need sort of a -force flag, that will drop all connections and then the DB.
How can I ...
- 2,605
155
votes
3
answers
166k
views
PostgreSQL multi-column unique constraint and NULL values
I have a table like the following:
create table my_table (
id int8 not null,
id_A int8 not null,
id_B int8 not null,
id_C int8 null,
constraint pk_my_table primary key (id),
...
- 1,661
53
votes
2
answers
85k
views
Optimizing bulk update performance in PostgreSQL
Using PG 9.1 on Ubuntu 12.04.
It currently takes up to 24h for us to run a large set of UPDATE
statements on a database, which are of the form:
UPDATE table
SET field1 = constant1, field2 = ...
- 651
36
votes
1
answer
51k
views
VACUUM returning disk space to operating system
VACUUM usually does not return disk space to operating system, except in some special cases.
From the docs:
The standard form of VACUUM removes dead row versions in tables and indexes and marks the ...
- 605
52
votes
2
answers
59k
views
Best way to populate a new column in a large table?
We have a 2.2 GB table in Postgres with 7,801,611 rows in it. We are adding a uuid/guid column to it and I am wondering what the best way to populate that column is (as we want to add a NOT NULL ...
- 745
49
votes
5
answers
53k
views
Aggressive Autovacuum on PostgreSQL
I'm trying to get PostgreSQL to aggressively auto vacuum my database. I've currently configured auto vacuum as follows:
autovacuum_vacuum_cost_delay = 0 #Turn off cost based vacuum
...
- 763
6
votes
3
answers
9k
views
Does a query with a primary key and foreign keys run faster than a query with just primary keys?
SELECT something FROM table WHERE primary_key = ?
vs.
SELECT something FROM table WHERE primary_key = ? AND other_key = ?
Say that this is a scenario where the inclusion of other_key does NOT ...
- 419
86
votes
4
answers
85k
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,199
9
votes
2
answers
1k
views
Index that is not used, yet influences query
I've got a PostgreSQL 9.3 table with some numbers and some additional data:
CREATE TABLE mytable (
myid BIGINT,
somedata BYTEA
)
This table currently has about 10M records and takes 1GB of ...
- 289
8
votes
1
answer
3k
views
Database schema for entities with two possible owner/parent types?
I'm using PostgreSQL with Sequelize as my ORM.
I have one type, User. The second type is Group, which can have any number of users associated with it through a GroupMemberships table. Users can also ...
- 183
58
votes
7
answers
168k
views
Select columns inside json_agg
I have a query like:
SELECT a.id, a.name, json_agg(b.*) as "item"
FROM a
JOIN b ON b.item_id = a.id
GROUP BY a.id, a.name;
How can I select the columns in b so I don't have b.item_id in the ...
- 1,571
24
votes
1
answer
19k
views
How do I decompose ctid into page and row numbers?
Each row in a table has a system column ctid of type tid that represents the physical location of the row:
create table t(id serial);
insert into t default values;
insert into t default values;
...
- 39.7k
22
votes
2
answers
8k
views
What does [FROM x, y] mean in Postgres?
I'm just getting started with Postgres. Reading this document I came across this query:
SELECT title, ts_rank_cd(textsearch, query) AS rank
FROM apod, to_tsquery('neutrino|(dark & matter)') query
...
- 941
18
votes
1
answer
26k
views
Get partial match from GIN indexed TSVECTOR column
I would like to get results by query this:
SELECT *
FROM (
SELECT id, subject
FROM mailboxes
WHERE tsv @@ plainto_tsquery('avail')
) AS t1
ORDER BY id DESC;
This works and return rows ...
- 437
16
votes
1
answer
40k
views
How do I generate a date series in PostgreSQL?
If you're looking to generate a time series, see this question
Let's say that I want to generate a series of dates between two dates.
I see the function generate_series provides only
Function ...
- 62.1k
15
votes
1
answer
8k
views
Enforcing constraints "two tables away"
I ran into some trouble modeling an electrical schematic in SQL.
The structure I'd like to capture is
part ←────────── pin
↑ ↑
part_inst ←───── pin_inst
where "inst" is short for ...
- 327
6
votes
2
answers
1k
views
Optimize a query with small LIMIT, predicate on one column and order by another
I'm using Postgres 9.3.4 and I have 4 queries that have very similar inputs but have vastly different response times:
Query #1
EXPLAIN ANALYZE SELECT posts.* FROM posts
WHERE posts.source_id IN (...
- 239
542
votes
5
answers
1.1m
views
List all columns for a specified table
I'm looking for a precise piece of information in a database which I have no knowledge about. The database is on a separate machine, but I can log into it, and launch a psql command line, with ...
- 8,501
80
votes
6
answers
75k
views
What is the best way to store an email address in PostgreSQL?
What would be the right datatype to store email addresses in PostgreSQL?
I can use varchar (or even text), but I wonder if there is a more specific data type for emails.
- 11.5k
14
votes
1
answer
7k
views
How do completely empty columns in a large table affect performance?
I have 400 million rows in a Postgres db, and the table has 18 columns:
id serial NOT NULL,
a integer,
b integer,
c integer,
d smallint,
e timestamp without time zone,
f smallint,
g timestamp without ...
- 243