Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgres]

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

0
votes
1answer
38 views

Temporary users to restrict db access of query?

I am new to database managment, and I am wondering if it is a good idea to create temporary users to restrict access to only a single table. Like having an user named _logs can only (read?) and ...
1
vote
2answers
86 views

Architecture for RESTful API and a web admin

I am trying to build a RESTful API (using Flask) that will be later consumed by a mobile application. I also have a requirement to build a Web Admin. The Web Admin will have functionality that will ...
10
votes
5answers
593 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 ...
1
vote
0answers
84 views

Performance impact of JPARepository save() on a large database table with index

We have a few tables with a large amount of data and with indexes on those tables to help in faster retrieval. We are also using Spring Data JPA JpaRepository for adding data to those tables using the ...
0
votes
1answer
35 views

In a parent table - Child table relation , where to store order of children

Assume a Parent -> Child relationship in RDBMS. questionnaire table and question table. question table has a foreign key questionnaire_id to store which questionnaire it belongs to. Questionaire ...
-1
votes
1answer
43 views

How to ease the pain of lack of diffs when using database migrations?

The pain that I've often felt when creating database migration files, is best described in this Speakerdeck: Sane Database Change Management with Sqitch. Paste entire function to new "up" ...
-3
votes
2answers
296 views

Database Schema for a multiplayer quiz game

I'm trying to accomplish this scenario : There are 2 types of users, let's say Admin and Worker, and there have different roles. Admin can do a CRUD of questions, and also can create a room where ...
2
votes
2answers
78 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 ...
6
votes
2answers
470 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 ...
1
vote
1answer
42 views

Bulk Inserting Data with Duplicates or Line by Line Insertion Without

I am loading a large amount of data (2,000 files with about 30,000 rows, 30 columns) for use in a Django application. The files are based on 40 different templates. The column names are consistent ...
1
vote
1answer
150 views

Best way to model pricelist per customer for products in webshop

I am making a b2b-webshop system that will have multiple customers. Each customer will have a separate active pricelist which contains the products that they can buy and the prices that applies for ...
0
votes
1answer
55 views

Watchlist polling API NoSql vs Sql Frequent Updates

I am making a watchlist type application that will get all items being watched and then poll an api every ten minutes or so to check on its status. I am trying to design the database portion of this ...
0
votes
1answer
278 views

How to structure similar structs using Go and PostgreSQL without too much duplicate code

I am making a REST API in Go using PostgreSQL. Quick introduction: I suddenly have a case where I have different variations of the same basic entity, one of the variations have maybe 12 extra fields ...
3
votes
4answers
322 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 ...
2
votes
1answer
271 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 ...
1
vote
1answer
222 views

I need Auto-incremental like key but how?

I have a business specific pattern for storing their IDs. It's in the format of yy-mm-autoincrement. I could just store date and incremental id but there's another problem. It should be restarted ...
1
vote
0answers
41 views

Giving search on customizable fields

I have some user defined custom fields corresponding to a ticket (A ticket is an entity which has subject, status and custom fields). I need to search tickets based on its custom field values. A ...
0
votes
0answers
55 views

Edit List attribute in JPA when null?

I am using EclipseLink as my JPA implementation and Postgresql as my DB. JPA: @Struct(name="label,.....") @Entity @Table(name="\"XYZ\"", schema="\"XYZ\"") @NamedQuery(name="...
0
votes
1answer
53 views

Partitioning table by aggregate id for efficient eventsourcing in RDBMS

My events are stored in MySQL and I'm reaching a point where I have tens of millions of rows and things are starting to get sluggish when pulling an aggregate I have read about "table partitioning" ...
1
vote
1answer
71 views

In the context of nested database relations (A->B->C), what are the considerations for having an additional “shortcut” foreign key (A->C)?

I have a normalized relational database. I often find I need traverse multiple tables to get the related object of a related object and so forth. So by that I mean let's say I have related tables A->...
0
votes
2answers
128 views

Generate UUID in Application or Database level?

I created a new application and I am thinking where is the best place to generate a UUID. Generate a UUID in application level and persist it Generate a UUID in Database level I have a feeling that ...
0
votes
2answers
311 views

JSON in Database Schema Design

I'm working on a web application with constantly changing requirements. The relationships between entities in the database have mostly remained the same but the attributes of each entity type changes ...
1
vote
2answers
532 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 ...
14
votes
6answers
3k 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 ...
1
vote
1answer
72 views

Database Schema design of server running status log

I am working with 4 AWS EC2 instances (servers). Each Instance has Instance ID and Instance Name. Instance ID is unique. Each of them having multiple application servers, like on of them is running ...
2
votes
2answers
70 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)" ...
1
vote
2answers
53 views

Proper way to track ranking evolution of some db records

I will have a database of products where each record will have a number associated with it, let's say, the number of "sales". I will periodically update the database so each "sales" field for each ...
1
vote
3answers
239 views

Django web app: how to save thousands of variables per user

There is a website driven by Django where the user logs in and can answer thousands of questions. I want to persistently save whether a given user has answered a given question or not. My approach so ...
0
votes
1answer
121 views

Can a database wrapper be implemented in Scala using Traits? Is there a better way?

I've decided to implement a database wrapper to provide data operations of a web service using postgresql dbms. Models in my database are somelike complex and frameworks like slick do provide more ...
1
vote
0answers
1k views

Using import.sql vs persisting JPA entities

In Hibernate ORM & Spring Boot, during the SessionFactory creation, SQL DML statements can be executed to create tables and/or insert data via files such as import.sql defined in the javax....
0
votes
1answer
474 views

Normalisation Vs Join , trade-off

In RDBMS, goals of Normalization: Free the database of modification anomalies Minimize re-design when extending Avoid bias toward any particular access pattern First step involves avoiding ...
0
votes
1answer
111 views

Automatically push content to CDN

Here's the thing: we have a website which has some i18n. Translations are maintained in DB and manually exported into JSON files which are consumed by our SPA one time on load. Now I have the ...
0
votes
1answer
149 views

Is it possible to store and query combinations of boolean-like information in an integer?

With one boolean, it is simply 0 and 1, and it's straight-forward to query. With two booleans, it becomes necessary to specify what each number means, ex: 0 F-F 1 F-T 2 T-F 3 T-T Here it's easy to ...
2
votes
1answer
269 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 ...
-1
votes
2answers
3k views

How to store product and pricing history in a product database?

What is the best way to store historical information about products and pricing in a database? Our suppliers provide us with catalogues in various formats that usually contain manufacturer's ID of ...
2
votes
3answers
926 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, ...
0
votes
1answer
44 views

design implementation for a postgres database in a not connected environment

I have several postgres DB, on distant sites, two in China, 1 in India , one in Korea, one in Germany, one in France and one in Mexico. All dbs, regardless of their site, have one table that is part ...
2
votes
1answer
73 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 ...
0
votes
2answers
143 views

Compile analytics from millions of rows in PostgreSQL

How do we compile analytics from millions of rows in a PostgreSQL table? We pull order data from multiple CRM's and need to compile the data for reporting and each CRM has it's own orders table. We ...
0
votes
1answer
1k views

Database Architecture for SaaS application [closed]

I am designing a SaaS application where thousands of users will be using this application. This application will do lots of data crunching and analytics. I am considering creating a main database ...
6
votes
1answer
114 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 ...
3
votes
3answers
434 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 ...
0
votes
0answers
37 views

Should I implement an intermediate Profile table now, or wait until if/when I need it?

Traditionally the schema is very simple: User <- Post I have written the code for a Profile model to be an intermediate: User <- ProfileUser -> Profile <- Post When registering for the ...
0
votes
1answer
32 views

How should I balance memory with network latency on a paginated table?

A web application I'm working on has a tool that queries a database and returns up to several thousand rows. When considering pagination, at what point does it make more sense to get the entire result ...
3
votes
1answer
553 views

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

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 ...
-3
votes
1answer
219 views

Is SQL inefficient since it has to parse through everything?

As you know SQL doesn't have arrays. So if you make a table "article" and wish to have comments on your articles I presume you would make another table "comment". Inside "comment" table you have the ...
1
vote
1answer
78 views

How perform automated tests over code that changes a DBMS (throught DML or DDL)?

I'm written a software as an exercise to improve my skills and I ask my self about how to test code that changes a database. Let the code in my github: https://github.com/daniloanp/Ensaios/blob/...
0
votes
1answer
859 views

Most elegant way to execute a AWS lambda function with preloaded parameters on a specific time

I am looking for the following workflow: Add an entry to a table of my Postgres database with a end_date and state (with the following values: active or done) columns. Execute a lambda at the exact ...
2
votes
1answer
138 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 ...
1
vote
0answers
35 views

Split long running work amongst multiple workers

I have a large number of rows in a database each containing a user specific token. For each token I need to maintain an open websocket connection. To scale this up I would like to run multiple ...