Stack Exchange Network

Stack Exchange network consists of 174 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]

The tag has no usage guidance.

1
vote
2answers
51 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
426 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
41 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
44 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
33 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
258 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
127 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
123 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
127 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
33 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
47 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
70 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
101 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
240 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 ...
0
votes
0answers
44 views

Strategies to migrate a SAP ADS database to postgres

I am looking for a good strategy to migrate an old and big SAP ADS (formerly sybase) database system to a modern RDBMS (most likely postgres). The first and foremost problem I am facing is, that the ...
0
votes
2answers
359 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
70 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 ...
1
vote
2answers
67 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
201 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
103 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 ...
0
votes
0answers
448 views

Building up a JSON object in a stored procedure

My apologies if this isn't the right forum to ask this question. If it is, please let me know and I'll move it over to stack exchange. After discovering an unfortunate limitation of our PostgreSQL ...
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
270 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
97 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
133 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 ...
0
votes
0answers
47 views

PostgreSQL tables or JSONB for permissions

I'm currently in the process of adding permissions functionality to my app. I'm trying to decide which route to take between implementing this functionality with JSONB or tables. As a side note the ...
2
votes
1answer
215 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
2k 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 ...
3
votes
3answers
689 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
43 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
71 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
139 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 ...
7
votes
1answer
101 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 ...
4
votes
3answers
385 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
30 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
463 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
178 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
71 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
786 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
130 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
34 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 ...
6
votes
3answers
2k 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 ...
3
votes
3answers
1k 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
1answer
774 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 ...
0
votes
1answer
138 views

Is it advisable to create a single API to access the database? [closed]

Got an organization that its core work is acquiring various sources of data, validating it and integrating it to existing mostly financial data sets and then availing that data to many different types ...