Tagged Questions
1
vote
0answers
27 views
Performance of database with transactional data
There are lots of row modifications (several thousands of rows per minute) in our PostgreSQL database that stores transactional data. We have a problem because PostgreSQL runs vacuuming process and ...
1
vote
1answer
22 views
How un-clustered is a CLUSTER USING table
I have some tables which benefit greatly from CLUSTER ON/CLUSTER USING in Postgres. Data accessed at the same time is "defragmented" into a small number of disk blocks:
# CLUSTER table USING ...
1
vote
0answers
49 views
Configuring PostgreSQL for read performance
Our system write a lots of data (kind of Big Data system). The write performance is good enough for our needs but the read performance is really too slow.
The primary key (constraint) structure is ...
3
votes
1answer
36 views
Why do psql queries take longer when inserting many rows. Why is it non linear
So if I insert a single row into a postgres database it takes 18 ms. If I do this in a loop like this:
INSERT INTO contacts (numbers)
SELECT distinct array[
(random() * 99999999)::integer,
...
2
votes
2answers
93 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 = ...
2
votes
1answer
59 views
PostgreSQL concurrent increment of counter
I need to maintain a statistical table for a project, composed by a list of items and their usage (Think about something like a website in which you would like to count pageviews). Each time an item ...
2
votes
2answers
87 views
How to implement posts with “seen by” like facebook?
Inside a Facebook group there is a feature when every time see the group newsfeed it mark all posts as seen by me.
And if I see a specific post inside a group thought a url ...
4
votes
1answer
89 views
How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL?
According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause
some ...
3
votes
1answer
74 views
Could too many idle connections affect PostgreSQL 9.2 performance?
Some queries on my database server seem to take a long time to respond, and I believe the CPU usage is high. When running ps aux, I see ~250 "idle" connections (which I believe to be too many). I ...
3
votes
0answers
23 views
Does avg_query for PgBouncer include update and inserts?
Trying to tune our application and would like to know if the stats data that pgBouncer shows as avg_query, according to the PgBouncer docs on usage:
avg_query
Average query duration in ...
1
vote
1answer
152 views
Very slow DELETE in PostgreSQL, workaround?
I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign ...
6
votes
1answer
85 views
What is a good way to determine the performance I should expect from a PostgreSQL server?
Trying to figure out how I should expect my database to perform. Basically, I need to determine when the server I have set up is reaching its performance limits - which would help me plan server ...
4
votes
1answer
78 views
After Rackspace server creation, PostgreSQL query planner doesn't work as expected
We created an image of one of our database servers in Rackspace. Then, we created a new server using that image, expecting things to work. However, the index performance we have seen seems to be ...
1
vote
0answers
31 views
postgresql 8.3 possible checkpoint and memory misconfiguration
I am using postgresql 8.3 in a read/write heavy application where data are being written to the database from a background process while they are also being read/written from a web application.
At ...
1
vote
2answers
168 views
How should I tune Postgresql for 20 GB of RAM?
I've been fortunate enough to have the use of a 20GB Linode instance running Ubuntu 64 bit. I want to try to optimize PostGres for this service, but I don't know what I should prioritize changing.
I ...
1
vote
1answer
151 views
Tuning Postgres config for new Database Server
I have finally gotten a Purpose Built Database machine for a project i'm working on.
The server is a 4 Core Xeon with 64GB of Ram and a Raid10 of 10krpm drives.
I have just got the database moved ...
1
vote
0answers
130 views
How to calculate running total based on dynamic values?
I need to calculate the running total in which the target column is changing along with the time. Its something which aws does. Charging based for only what you use. So how do I aggregate sum of ...
4
votes
1answer
137 views
PostgreSQL: Recommendation size for shared_buffers
I am rather new to PostgreSQL. I started migrating some MySQL databases to PostgreSQL one month ago.
I am looking for help about the proper memory values for the configuration file.
Currently, I ...
2
votes
1answer
128 views
Performance impact of setting PostgreSQL to log all statements
PostgreSQL 9.2.2 on Windows 7 64-bit.
The setting log_statement in the postgresql.conf file specifies what statements to log (none, DDL, data-modifying, or all). To what extent does setting ...
2
votes
3answers
420 views
Slow query on large table with GROUP BY and ORDER BY
I have a table with 7.2 million tuples which looks like this:
table public.methods
column | type | attributes
...
1
vote
0answers
157 views
Finding the bottleneck by analyzing Munin graphs
We’ve been experiencing some issues with our database server during peak hours. In our Nginx error-log we can see that a lot of requests get timed out. The requests are also very slow. We have had ...
0
votes
0answers
48 views
Long disk queue when no traffic
I got dedicated disk (RAID 10 - 4 SAS disks) for Postgres. I am bit worried as recently, I see under Windows Resource Manager disk queues like 3+ in the mornings (low traffic hours), like every 30-60 ...
7
votes
1answer
724 views
PostgreSQL Index optimization with dates
I have a large table of objects (15M rows +), for which I want to query for outdated field. PostgreSQL (9.0.8)
I want to divide the query by millions, for scalability & concurrency purposes, and ...
3
votes
1answer
70 views
query discrepancies on very large postgres table
I have a fairly large table (25m entries) which stores tracking data, I am not a postgres admin by trade so this may be a simple problem.
these two queries although similar are taking massively ...
2
votes
2answers
281 views
PostgreSQL performance degradation over time on a write intensive db
I have observed a weird situation that over time the performance of a query (a combination of queries explained below) degrades, meaning at the start of testing (for a few minutes) the time of the ...
7
votes
2answers
530 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?
...
2
votes
1answer
65 views
Does updating a partially indexed field add or remove it from an index in Postgres
We can create a partial index for a field in Postgres.
I am looking at using this to stop rows which are archived from being in an index on a created_at field:
First we create the index:
test=# ...
6
votes
2answers
307 views
PostgreSQL Index Caching
I'm having difficulty finding 'lay' explanations of how indexes are cached in PostgreSQL, so I'd like a reality check on any or all of these assumptions:
PostgreSQL indexes, like rows, live on disk ...
3
votes
2answers
248 views
Is the speed of a PostgreSQL SELECT adversely affected by too many indexes on the table?
I have read that when having a lot of indexes on a database It can seriously hurt the performance but in the PostgreSQL doc I can't find anything about it.
I have a very big table with something like ...
4
votes
1answer
120 views
How should we handle rows which won't be queried once they are old in PostgreSQL?
We have a table in a PostgreSQL database which is growing on the order of millions of rows per day.
Each row consists of:
ID
Foreign user ID
Date and time
Other data
The date and time aren't ...
3
votes
2answers
264 views
Index max row size error
Is there a upper bound for an array column?
I am getting this error when inserting into the array field -
PG::Error: ERROR: index row size 3480 exceeds maximum 2712 for index "ix_data"
Here's my ...
2
votes
2answers
103 views
Which performance can I expect?
I'm running a Postgres 9.1 database on a Debian Squeeze server. It's a paravirtualized XEN guest system with 4G RAM and 1 CPU core, based on a i7-2600 host system with Linux software-RAID1.
I'm ...
2
votes
1answer
227 views
How to stabilize performance on frequently updated table in PostgreSQL
I'm trying to find-out a way how to prevent performance degradation in my PostgreSQL database.
The table is updated every minute with 100,000 - 200,000 INSERTs. Every 5 minutes the table is updated ...
2
votes
2answers
237 views
Is it a good idea to move high-volume and highly-accessed tables to a separate database?
Ours is a web-based application (built on multi-tenant architecture) running PostgreSQL v9.1.3. There are about 450 tables in our application, out of which 2-3 tables, specific to a module in the ...
6
votes
3answers
2k 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 ...
8
votes
2answers
161 views
Postgres query plan of a UDF invocation written in pgpsql
It's possible when using the pgadmin or plsql to get a hold of a query plan for a sql statement executed via a UDF (using EXPLAIN). So, how do I get a hold of the query plan for a particular ...
2
votes
1answer
115 views
How do you figure out what planner cost constants to use in Postgres?
I'm using Postgres 8.4 right now. Performance has started to become an issue as our tables have grown in size and our queries in complexity, so I've started to look into some performance tuning, but I ...
2
votes
1answer
301 views
Tune postgres 9.1 to speedup deletes
I need to tune my postgres db to speed up delete queries. I cant't optimize my db structure yet, so I'm looking for options available on server level for postgres 9.1.
I've seen in older posts the ...
5
votes
4answers
2k 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
...
2
votes
1answer
845 views
PostgreSQL tree structure and recursive CTE optimization
I'm trying to represent a tree structure in PostgreSQL (8.4) to be able to query the path from the root to a given node or to find all the nodes within a sub-branch.
Here is a test table:
CREATE ...
2
votes
3answers
627 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?
0
votes
1answer
148 views
Fill factor for later bigserial + index
I'm planning to import and update a huge table (3 Bil. rows) in the following step:
create table with fill factor x
import the data from csv files (6 files, ~21GB each)
add a BIGSERIAL column
create ...
2
votes
2answers
777 views
Most efficient way to add a serial column to a huge table
What's the fastest way to add a BIGSERIAL column to a huge table (~3 Bil. rows, ~ 174Gb)?
EDIT:
I want the column to be incremented values for existing rows (NOT NULL).
I didn't set a fillfactor ...
3
votes
2answers
300 views
Slow ORDER BY with LIMIT
I have this query:
SELECT *
FROM location
WHERE to_tsvector('simple',unaccent2("city"))
@@ to_tsquery('simple',unaccent2('wroclaw'))
order by displaycount
I'm happy with it:
"Sort ...
1
vote
2answers
149 views
Perf of query of large table by date?
I have a table with some sensor data in it which grows every minute with a new measurement for that specific sensor. Something like:
entry_id (pk), sensor_id (fk), measurement_value, created_at
I ...
8
votes
3answers
277 views
What is retrieved from disk during a query?
Fairly simple question, probably answered somewhere, but I can't seem to form the right search question for Google...
Do the number of columns in a particular table affect the performance of a query, ...
2
votes
0answers
33 views
Splitting a wide table [duplicate]
Possible Duplicate:
Database Design: New Table versus New Columns
I'm in the process of building a DB schema that contains information about something we call "blocks" (not very important ...
3
votes
1answer
231 views
PostgreSQL memory spike upgrade from 8.2 to 9.1
We recently upgraded a few of our development computers from PostgreSQL 8.2 to 9.1, with plans on eventually upgrading our production servers. Everything seems well with the restore of the databases, ...
1
vote
4answers
338 views
Performance degradation while updating tables having 10s of millions of records
I want to update tables ( my be 20-30 ) having 10s of millions of records each.
The problem is that it is taking too much time for the update process and also at that time CPU usage also goes very ...
1
vote
1answer
376 views
PostgreSQL vs MySQL benchmarks when a 2nd level cache is included
Has anyone been able to find any benchmarks comparing PostgreSQL and MySQL performance when they are used by a system through Hibernate (or another comparable ORM) and the 2nd level cache (backed by ...
