Tagged Questions
1
vote
4answers
132 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 ...
1
vote
1answer
59 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 ...
4
votes
2answers
233 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 ...
2
votes
2answers
90 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
96 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
80 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
57 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
67 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 ...
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 ...
2
votes
1answer
101 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 ...
6
votes
1answer
86 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
69 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
86 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
0answers
32 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 ...
6
votes
3answers
195 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 ...
5
votes
1answer
90 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 ...
0
votes
1answer
182 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 ...
0
votes
2answers
326 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 ...
2
votes
2answers
88 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, ...
1
vote
2answers
189 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, ...
0
votes
1answer
119 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 ...
4
votes
2answers
131 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
245 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, ...
7
votes
2answers
548 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?
...
3
votes
1answer
137 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
303 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 ...
4
votes
3answers
346 views
Lookup table for a one-to-many relationship
I have a tickets table with an id that I need to associate to a lookup table where the counterpart of that data is another id that is controlled from an outside source.
tickets
- id
- sutff
lookup
- ...
3
votes
2answers
272 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
1answer
105 views
Unique foreign key constraint between three tables
I'd like to track appointment attendances. Visitors can have appointments, or they can just turn up. If they have an appointment, and miss it, in which case I'd like to record a reason for the missed ...
8
votes
2answers
239 views
Modelling constraints on subset aggregates?
This is a cross-post from Stack Overflow. I figured I might get more interesting thoughts here. I am using PostgreSQL but I figure most of the top-end db's must have some similar capabilities, and ...
-1
votes
1answer
219 views
Database for an application similar to Chatroulette
I am making an app that is similar to Chatroulette.
When a user starts the app it finds someone that is not currently talking to anyone and have them talk to each other. At any point a user could ...
0
votes
2answers
233 views
Is there data visualisation tool for postgresql which is capable of displaying inter schema relations as well?
Operating system used is linux. I have tried Navicat and SQL Power Architect. They did display relations between tables in the same schema. I have some foreign key constraints which reference tables ...
1
vote
1answer
325 views
Is PostgreSQL 9.2 autovacuum sufficient for a busy table?
I have one table on a PostgreSQL 9.2/PostGIS 2.0.1 database:
CREATE TABLE ch02.markers
(
ff_id SERIAL PRIMARY KEY,
col1 boolean,
col2 smallint,
created_at timestamp,
lat double precision,
...
3
votes
2answers
813 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
3answers
457 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 ...
3
votes
2answers
397 views
How do I design a database for continents, countries, regions, cities and POIs?
I am not a database designer so I'm having trouble designing database with GIS information. The goal is to create a system with continents, countries, regions (including states, sub-regions, ...
2
votes
1answer
418 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?
1
vote
1answer
83 views
Impact of changing schema name
I have a server with two databases. Each database has a different schema name despite the fact that DB2 is a subset of DB1 (DB2 has a subset of the tables in DB1).
Not withstanding any code changes ...
2
votes
2answers
202 views
Joining based on date (or date range)
I'm not sure if I've completely fudged the design of my small hobby database (I'm not a DBA by any means), but I have a table like this (primary key is (staffid, effectivefrom)):
staffid | target | ...
6
votes
2answers
601 views
Database for building a realtime analytics system
I want to build a system similar to Google Analytics (only used for internal use, less traffic and less feature), and mainly focus on
Real time counting of unique URI visit/PV by different ...
2
votes
1answer
373 views
Table structure/schema for spreadsheet-like web app (ex: Google Docs)?
I'm a front-end developer/designer who is trying to branch out and learn more about the back-end world. I've chosen Python, Django, and Postgres as my starting place. My goal is to build a personal ...
4
votes
1answer
54 views
Want to add a “variable” to a table
I'm using PostgreSQL 8.4 and I have a table that is cleared out and refilled with new data every so often. I want to be able to store the date that the table was last filled, but I'm not sure where to ...
4
votes
3answers
2k views
PostgreSQL designing tool
I am about to design my first database which is going to run with PostgreSQL. I am used to the magnificant tool called MySQL Workbench. It is useful and it looks good which I kind of expect from a ...
6
votes
1answer
361 views
Concept of Schema in PostgreSQL
I am not able to understand concept and usage of schema in PostgreSQL. I have no idea how it can affect my database design.
Why should I use it?
Can it affect me in future if I decide to not think ...
2
votes
2answers
312 views
Database Transfer from PostgreSQL to MySQL
Is there any method to tranfer the data and schema from a postgresql database to mysql?
3
votes
2answers
585 views
Removing Archive Logs after PostgreSQL PITR online-recovery
I'm using pgpool-II + PostgreSQL 8.4 on three servers: primary + standby1 + standby2.
Replication mode is "on"
Load balance mode is "on" (between primary and standby1)
I followed the official ...
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 ...
5
votes
4answers
2k views
Finding empty columns of a table in PostgreSQL
What query would return the name of the columns of a table where all rows are NULL?
6
votes
3answers
636 views
Where does the magic column “name” come from?
I got this by accident:
db=> select name from site;
ERROR: column "name" does not exist
LINE 1: select name from site;
^
db=> select site.name from site;
name
...
