Tagged Questions
0
votes
0answers
23 views
Postgresql Retrieve / Find Primary Key Serial Autonumber field for Audit logs
I need to find the Primary Key field name in each of a number (approx 70) of tables to create audit logs.
All the primary keys are type serial (auto incrementing integer) and all have the name in the ...
1
vote
4answers
123 views
Database design - People and Organisations
Databases are not my primary skill and I am finding I need some assistance please.
The software we are building has "Customers".
The Customer could either be a Person or an Organisation.
I really ...
4
votes
2answers
217 views
Is it ever a good idea to denormalize for integrity?
I'm using Postgres 9.2 to develop a quiz app, in which I present the user with a series of problems and record their answers.
These problems can take a variety of forms - they might be multiple ...
1
vote
1answer
55 views
Table for optional parent/child relationship
Assuming we have the following table: Item, Parent, Child and Parent is the parent of child.
The item can either belong to a parent or child and not both.
I have other tables that are similar to ...
2
votes
2answers
84 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
87 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
73 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 ...
2
votes
0answers
56 views
How to execute a non-table-locking update operation on PostgreSQL? [closed]
Looking for a good way to update the value on a column for all the rows in a database (not huge, but big enough - about 10M records), without locking the whole table, so operations can continue while ...
4
votes
1answer
65 views
How does PostgreSQL physically order new records on disk (after a cluster on primary key)?
Need to know how PostgreSQL orders records on disk. In this case, I would like to take advantage of index combination as stated in the docs, which as I understand uses bitmaps to get matching rows ...
2
votes
1answer
96 views
Will table inheritance in PostgreSql suit this situation?
Let's start off by saying, I'm new to PSQL and coming from a MSSQL background. I'm starting to design a DB in PSQL.
So my main aim is to have a Main schema in this DB, which I am going to use to ...
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 ...
3
votes
2answers
1k views
How can I specify the position for a new column in PostgreSQL?
If I have a table with the columns:
id | name | created_date
and would like to add a column, I use:
alter table my_table add column email varchar(255)
Then the column is added after the ...
6
votes
1answer
84 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
0answers
67 views
Does PgBouncer create a separate pool for every database config entry?
If I set up a pgbouncer.ini config file with several database entries, does pgBouncer create a separate pool for each one? I would think so, but have not been able to confirm this.
For example, ...
4
votes
2answers
83 views
How to properly configure PostgreSQL RAM usage?
Using PostgreSQL, we have several hundred concurrent processes doing regular database access. We have our data spread out through several database servers, each of which houses a connection pool with ...
2
votes
0answers
72 views
How to properly configure pgBouncer transaction level pooling?
What is a good way to determine the number of available transaction connections when using pgBouncer to pool database connections? Also, how should I determine the number of max_connections and ...
0
votes
2answers
316 views
Open source alternative to SQLMaestro for PostgreSQL
I'm looking for oen source alternative to SQLMaestro for PostgreSQL because it has visual database designer which not avaliable in pgAdmin which make database desinging so easy.
PS I'm not sure if ...
5
votes
1answer
88 views
To Foreign Key or not to Foreign Key… (inet -> cidr)
Below is a very abridged version of a project I am working on.
CREATE TABLE cidr_block (
id serial not null unique,
block_def cidr primary key,
dhcp_server_id int not null references ...
6
votes
3answers
188 views
Store a formula in a table and use the formula in a function
I have a PostgreSQL 9.1 database where part of it handles agent commissions. Each agent has his/her own formula of calculation how much commission they get. I have a function to generate the amount of ...
0
votes
1answer
174 views
one trigger for multiple tables
i have a trigger in PostgreSql 9.1 that i want to fire on inserts of several tables. is it possible to have it affect all these tables instead of creating the trigger for all these tables?
i have 58 ...
2
votes
2answers
87 views
What is the way to paginate last updated rows from multiple tables?
I want to have something like a newsfeed from multiple objects sort by most_recent. However I need to paginate (load more/offset) the results.
Row1 = Post's - text, username
Row2 = Photo's - text, ...
0
votes
1answer
116 views
Use data in a tablespace to populate another postgres database instance without actually inserting
I have a postgres database (version 8.4) with many tables. Some tables are huge in size, but the data is static (common for any instance). I have stored such tables in a separate tablespace. Is there ...
1
vote
2answers
188 views
Database Design - One table from many source(One to One)
I have a 'Text' table, the text table may came from different source, like Tweet, SMS, Email, etc. and each source have their own log, and Text is came from the source. so i came with some solution, ...
3
votes
2answers
792 views
Tool for Reverse Engineering of PostgreSQL Database on Linux (Ubuntu)
Please suggest a tool to reverse engineer a PostgreSQL DB into an entity-relationship-diagram (ERD) or equivalent ??
Ideally, I'm looking for a simple ERD or UML class diagram in a format that is ...
7
votes
2answers
519 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?
...
4
votes
2answers
126 views
What happens to the index of a primary key after a DROP CONSTRAINT?
I am running PostgreSQL 9.1.4.
I have a table with many existing rows, and a bunch of other tables with foreign keys pointing to it, for which I am trying to :
1 - Remove the pkey constraint on the ...
1
vote
3answers
237 views
Many-to-one Subselection in single query
I have two tables with a foreign key from T1->T2, in a one-to-many relationship. That is, 1 tuple in table T1 is associated with 0..N tuples in T2.
To create a simple example, lets say T1 is Cars, ...
2
votes
1answer
417 views
PostgreSQL versus MySQL for EAV structures storage
Is there any practical difference or what are the advantages of using Postgres 9.x versus MySQL 5.x for storing EAV (Entity-Attribute-Value) structures, especially in terms of read performance?
3
votes
1answer
136 views
Model with variable number of “properties” of different types.
I would like to have a table of microscope slides, where on top of the basic id, name,date_created would each slide also have a number of editable "properties".
Imagine one slide having
...
7
votes
1answer
287 views
Choice of authentication approach for financial app on PostgreSQL
First some background.
The LedgerSMB project is an open source financial accounting software project that runs on PostgreSQL. We implement a very large amount of business logic in user defined ...
