Questions tagged [postgres]

PostgreSQL, A.K.A Postgres, is an open source object-relational database management system (ORDBMS).

Filter by
Sorted by
Tagged with
167
votes
21answers
154k views

Stored Procedures a bad practice at one of worlds largest IT software consulting firms?

I'm working at a project in one of the world's top 3 IT consulting firms, and was told by a DBA that company best practice's state stored procedures are not a "best practice". This is so contrary to ...
99
votes
10answers
45k views

Storing a re-orderable list in a database

I'm working on a wishlist system, where users can add items to their various wishlists, and I plan to allow users to re-order the items later on. I am not really sure about the best way to go about ...
41
votes
3answers
44k views

Is there any reason to use varchar over text columns in a database?

Is varchar just a remnant from before text came around, or are there use cases where you would want to use a varchar? (Or char for that matter..) (I use Postgres and MySQL (MyISAM) daily, so those ...
17
votes
2answers
69k views

How to get real time notifications, when a database change (insert, update, delete) occurs?

I'm creating a dashboard which should monitor a database table. I have only database access (no application layer). The table is rather large (10 million rows), however not changing rapidly (100 ...
14
votes
6answers
4k views

What are the best practices around retiring obsolete database columns? [closed]

I'm designing an application which will in an early stage collect data A, B, and C from clients, but later on will instead collect data A, B, and D. A, B, C, and D are very related and right now ...
10
votes
9answers
25k views

Is there a large bank using Mysql or PostgreSQL? [closed]

I always thought the largest scale of banks use Oracle. However, there is no proof they really use Oracle instead of Mysql or PostgreSQL, nobody knows the secret. Any idea what they really use? Can ...
10
votes
5answers
1k views

Do I really need triggers for relational database, for example, PostgreSQL?

I know that triggers can be used to validate stored data to keep database consistent. However, why not perform validation of data on the application side before storing them into the database? For ...
10
votes
3answers
5k views

Storing hours a merchant is open in postgres

In postgres, I need to store the hours a merchant is open each week. Essentially, the information you'd see posted on a sign in the merchant's door: Mondays: Closed Tuesday-Friday: 9am-1pm, 2pm-6pm ...
8
votes
2answers
2k views

Making the switch from MySQL to PostgreSQL?

I've spent the last year becoming really comfortable with MySQL, but due to its increasing trendiness and my desire to homogenize my web-apps with Heroku, I'd like to start using PostgreSQL for my web ...
8
votes
4answers
8k views

What is the "correct" way to store functions in a database?

Note: Yes, I know that storing functions in databases should be punishable by law. We are developing a financial web application using PostgreSQL, Sinatra and AngularJS. As you may have guessed, a ...
7
votes
3answers
521 views

Do common relational database systems work 'out of the box'? [closed]

I'm quite new to software development and whenever I come across small projects which involve storing (relational) data I always ask myself if something like a micro-blog (or any other project with ...
6
votes
2answers
584 views

Scalability: Millions of tables vs one big master table and millions of views

We have to migrate an old (million LOC) system from SAP ADS (formerly Sybase) ISAM based (so called free tables) based system to a PostgreSQL database. The system implements kind of multitenancy ...
6
votes
1answer
144 views

Database Model description using constants

I want to create a class with static constants which are used whenever a part of an application want to access the database in any way. This way I want to remove all magic numbers and string inside my ...
6
votes
1answer
2k views

Elasticsearch and relational database combination

Imagine I have a marketplace application - where users can search for products (we concentrate on clothes). Every product has an ID, name (text), description (text), price (numeric), size (numeric), ...
6
votes
2answers
1k views

Specifying a bi-directional unique constraint on a join table in Postgres [closed]

Problem description I have a situation where I want to enforce that a relationship can only be defined once and I want to solve it on database (Postgres) level. The following situation I consider ...
5
votes
2answers
161 views

How to handle manipulation of data after a db record is written from outside my program

I have written a warehouse management web app. The application handles batch picking, warehouse routing, packing, and the final piece is handled by UPS worldship to "ship" the packages. Worldship will ...
5
votes
4answers
4k views

Is it possible to develop a web application entirely in Javascript today (client and server) including a RESTful API exposing PostgreSQL data?

Where I work we have over a dozen internal SW systems that we have developed in-house using a variety of technologies, frameworks, and languages. Those include various from Microsoft (ASP.NET Web ...
5
votes
3answers
412 views

What reading is recommended for an experienced Java/SQL Developer moving to Apache/Tomcat, Postgres, Hibernate [closed]

I am taking over management and development of a website developed in Java using Hibernate and Postgres, running on Apache/Tomcat. I am an experienced Java developer, and have experience in Ruby on ...
5
votes
1answer
2k views

Persistent storage for Apache Mesos

Recently I've discovered such a thing as a Apache Mesos. It all looks amazingly in all that demos and examples. I could easily imagine how one would run for stateless jobs - that fits to the whole ...
5
votes
1answer
993 views

Using PostgreSQL MD5 hash to calculate a 64 bit hash value for advisory lock functions?

I have a particular problem addressing the PostgreSQL advisory locking functions using the bigint variants. Basically I want to create a 64 bit bigint value from a text type obtained with the ...
4
votes
3answers
2k views

Relational vs Graph Database for (initially) moderately-sized network

We're developing an application whose data domain (or at least 90% of it) can be modeled effectively using a relational database. We've been using PostgreSQL since the beginning and have had no ...
4
votes
2answers
360 views

Database constraints for a recursive folders structure

In an application, I have a recursive folder structure (like folders in OS X or Windows file system). Each folder can contain three kind of things: Other folders (hence the recursive structure) ...
4
votes
4answers
2k views

DB Enumerations vs App Enumerations vs Lookup tables

We started a project recently, and during the design process, we have some arguments among team members. We have to deal with enumerations so we ended up with three choices. Have the enumerations as ...
4
votes
2answers
334 views

Using audit trail as time machine?

I would like to do SELECT * TABLE t (using data from 1st of march 2012) I already have a nice audit trail of all tables in the database. It basically makes a copy of all rows that change, storing ...
4
votes
1answer
1k views

Postgres roles for website users

I am setting up a PostgreSQL database with a very small number of tables for a simple website. I am looking at ways to make it as solid and auditable as possible. Is there any particular reason why ...
4
votes
0answers
347 views

Global Accounts in Multi-Tenant Authorization System

Our system is fundamentally a multi-tenant setup, where each client maintains a separate database with its own data and users. The authentication system on login issues an authentication token which ...
3
votes
2answers
2k views

Is a bad practice create database structure on the runtime?

I'm creating a crawler app that will always, in the beginning, when the Class constructor, ensure that the basic database structure exists. Is this a bad practice? What is the advantage OS creating ...
3
votes
1answer
58 views

Reorderable parent/children hierarchy in a relational database

I'm struggling on a design question for my Ruby on Rails website. I'm working on an estimation tool for construction companies for renovation projects. It orders items this way by default: Room (ex: ...
3
votes
3answers
2k views

Role based Database Schema

I am designing a database for a document management app for my office. I want to get the backend right so I don’t have issues in the future. Basically all users will be categorized into roles e.g, ...
3
votes
3answers
740 views

What are the different approaches to filtering on aggregated data?

I have a feature I'm building that aggregates data across about a dozen or so tables. This data is aggregated from a heavily joined query. This page then has a lot of filtering, sorting, counting, and ...
3
votes
0answers
776 views

how to import large amounts of data without locking sqlite database

Background Information I have a web app that provides the user with the usual CRUD functionality for "widgets" let's say. But I also have give them the option of creating widgets in bulk via ...
2
votes
3answers
3k views

What are the advantages/disadvantages of creating a new set of tables for each user?

I'm working/playing on an app where individual users' data will be completely independent from each other. Of course, there will be common tables for other parts of the app (e.g. "users"), but for the ...
2
votes
3answers
6k views

Super Fast File Storage Engine

I basically have one big gigantic table (about 1.000.000.000.000 records) in a database with these fields: id, block_id, record id is unique, block_id is not unique, it contains about 10k (max) ...
2
votes
1answer
115 views

Sharding rule updating on DB scaling

When you have sharding, horizontal partitioning, you have multiple DBMS instances, and your server app chooses which one to use by some rule that uses record IDs. Well, for example, we have ...
2
votes
2answers
560 views

Compiling vs pre-built binaries on production machine?

I referred Compiling vs using pre-built binaries performance?. The post talks about performance and opportunities for optimization. My question is about reliability. I built both python and ...
2
votes
1answer
2k views

Established coding standards for pl/pgsql code

I need to standardize coding practices for project that compromises, among others, of pl/pgsql database, that has some amount of nontrivial code. I look for: Code formatting guidelines, especially ...
2
votes
2answers
155 views

Foreign Keys and Eventual Consistency

We have a project that loads metric data from various etls and services into an AWS SQS queue, where that data is processed and inserted into a metrics database, also running on AWS RDS. When we ...
2
votes
2answers
2k views

Retry a failed REST API request - Java/Postgres

We have a REST API which calls a third-party REST API to Send Emails. The Third Party API is not super reliable and randomly fails every now and then with a 500. Our Clients do not want to retry at ...
2
votes
2answers
94 views

unique varchar(NNN) with an index in database and its overhead

I've seen the url like this domain.com/some-article-title-which-can-be-very-long many times. There's no ID embedded in it. Therefore, in a db it must be defined as "unique article_url varchar(NNN)" ...
2
votes
1answer
457 views

Scaling resolution of time series data

I built an app that display time series data. The display is very similar to a yahoo finance chart (ie https://finance.yahoo.com/quote/AMZN/). I am using Postgres to store the data because that was ...
2
votes
1answer
460 views

Breaking 1NF to model subset constraints. Does this sound sane?

My first question here. Appologize if it is in the wrong forum but this seems pretty conceptual. I am looking at doing something that goes against conventional wisdom and want to get some feedback ...
2
votes
1answer
46 views

SQL: Analytics event value that can be string or number

I'm using a PostgreSQL database and I want to create an analytics table that has a many-to-one relationship with an analytics_type table where analytics_type_id is the foreign key. The table also has ...
2
votes
2answers
572 views

Index / Unique on `deleted_at` column?

We have an Accounts table in a Postgres DB that has a unique index on user_id and product. Users can have many accounts, there's no product table. Occasionally users will create an account ...
2
votes
2answers
1k views

Ways to store objects, inherited from one superclass in a database

For my personal project I need to store 2D shapes in a Postgres database. For example Circle, Pentagon, Rectangle and so on. At first I've done it like so: all shapes are inherited from an abstract ...
2
votes
1answer
625 views

Web application with relational database - best practice?

I am currently designing a web application which is pretty similar to Twitter but with some unique features for my company. Technologies are Vue2, Spark framework and PostgreSQL. I am trying to ...
2
votes
1answer
75 views

Apply math operations over column of table

This question is about finding approaches to solve a specific problem. I have a table which has several metrics with values. [metric1] [metric2] [metric3] [metric...N] Those columns are fixed. I ...
2
votes
1answer
182 views

How to associate similar data with different tables in a database?

My application has users and ads. A user has a unique email. An ad belongs to a user (so, the ads table has a user_id FK, that is simple). Every ad can also have multiple emails and phone numbers ...
2
votes
1answer
690 views

Pulling results from postgresql with cursors

I'm writing an application in C++ using libpq as the interface to postgresql. I'm currently putting together a small library of helper functions to help deal with the conversion from C++ to C for an ...
2
votes
2answers
1k views

How to store currency ranges in a Postgres table?

I am using Postgres 9.3. I have to store range data like <$250K, $250K-$4M, etc which we will display in a dropdown. now I am making a table for all possible options that can be configured from ...
2
votes
0answers
273 views

Running a high availability PostgreSQL cluster on native AWS services only

Backstory: I am unable to use RDS, as I need to install cartridges in my PostgreSQL instances. I have been trying to pin down an architecture for PostgreSQL running on EC2 instances for a few days. ...