Software Engineering

Questions tagged [postgres]

Ask Question

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

115 questions
Newest
Active
Bountied
Unanswered
Filter by
Sorted by
Tagged with
-1
votes
3answers
104 views

Should I denomralize my DB schema for a multi tenant application

I am working on a multi-tenant application that's using a shared Postgres db for all tenants. All entities in the DB are connected (directly or indirectly) to a tenant, for example: Tenant --has many--...
0
votes
1answer
79 views

Race conditions in API calls within Golang microservices

I have a microservice architecture running on Heroku. I am having some problems handling race conditions. The problem is, that service A: Needs to fetch the user's balance through an API call to ...
-2
votes
2answers
87 views

Caching expensive sql queries on first page load

I have a web application with an express/node backend using typeorm and PostgreSQL. The home page in my app is a query with lots of inner joins that shows the user a complex report. This query takes ...
-1
votes
2answers
108 views

Is it true that ORM allows for changing the type of SQL that you use easily?

Hey so I have been looking into ORM since my last question and I am wondering. Instead of worrying alot right now about whether I will use mysql, postgre, or sqlite, would it be better to use ORM ...
-5
votes
1answer
39 views

Sync local database with remote

My client has a business which work mostly in remote areas where internet felicity is limited, We have a central database and the branches in remote areas need to connect to the central database. We ...
0
votes
0answers
34 views

Xml data type and Hstore to create and query dynamic fields on a SQL database

I'm developing a traceability platform for the agro industry(a web and Android application) where the user can define a series of processes where the raw material becomes a final product. Each process ...
0
votes
1answer
97 views

Doing data manipulation on server side vs client side

Im building a project that stores time series data on a per user basis. On the dashboard of the user it'll show some simple statistical analysis like averages but more importantly, it'll create charts ...
-2
votes
1answer
60 views

Adding unstructured data to a relational database (PostgreSQL)

I'm building a project and I only know the MERN stack. Everywhere I see the general consensus is that Mongo is not good and should only be used in very specific use cases. More importantly, I also ...
1
vote
1answer
78 views

Should I perform some minor denormalization to save myself several queries in the future?

I'm in charge of designing the entire backend for the REST API of an application that works more or less like an online browser game (think OGAME, Travian, and the likes). In this game, players are ...
1
vote
1answer
29 views

Data integrity on polymorphic association to two different table

Let's say I have a very large student list want to filter if each of these students is satisfying some specific classroom school and classroom limitations. So, in my data model, I have School and ...
0
votes
2answers
71 views

How to use strict schema with seemingly fluid data type

Our company is trying to find a good generic way to have Many-to-One data for an entity. For example, a user might have 1 primary email, but many other emails also attached to their account. So we ...
0
votes
0answers
34 views

Recommendations for data clustering in an application

I am making a social media app. I have a use case in which i have to cluster similar type of data. For example, take Instagram. When a user likes a post, we get a notification that 'x likes your post'...
3
votes
1answer
402 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 ...
2
votes
0answers
112 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. ...
-1
votes
1answer
293 views

Elasticsearch and PostgreSQL combination

I have a marketplace application and I stored data in a PostgreSQL. I have performance problem for product search, I know I can improve search performance if I use Elasticsearch instead of PostgreSQL ...
0
votes
0answers
14 views

Using custom password to create RDS Aurora PG instance and use it to connect to PgAdmin

Background/Use-Case: I am currently working on a requirement that includes creation of an RDS Aurora PG instance, to which the data will be written through a Lambda. I am creating the RDS using a ...
1
vote
1answer
213 views

Optimizing inserting/storing Large amount of data in database

We have a POST API that takes data from the Client and send it to different applications (Payment, Email, SMS). We also store all the data sent by the Client in a database for future analytics ...
1
vote
0answers
68 views

Architecture recommendation for dynamic integration of algorithms/scripts

I am looking for a simple software architecture that is able to evaluate user data using different versions of algorithms. According to my concept, the user is able to upload tabular data via a Web ...
1
vote
2answers
835 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 ...
0
votes
0answers
23 views

Building a Feed Handler and Persisting JSON Data to Postgresql

I'm starting a pet project and would like some advice. I need to perform the following: Scrape JSON Data from a REST API Save the Data into my Postgresql table Paginate through the rest API and ...
-1
votes
1answer
66 views

Which is better for my use case? A hybrid customer data model or a document store customer data model?

I am trying to get some preliminary insight into the pros and cons of using either a hybrid model (jsonb document-relational) or an all jsonb document store model for customer data in PostgreSQL. Both ...
-1
votes
2answers
275 views

When self-referencing in a table, do you prefer `parent_id = null` or `parent_id = id` for a row referencing to itself?

For the discussion, I will provide my example. But I am interested in the broad guidelines. I save in my postgres DB legislation (law texts) composed of a single header (H), paragraphs (P), sub-...
1
vote
0answers
39 views

Building a schedule app - best design pattern for collection on Rails API

I’m building a web app using Rails API on server side. The goal is to make a planning manager. I want my users to be able to plan ‘daily’ events, which will display at some specific weekdays, with ...
0
votes
1answer
43 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
255 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
947 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
377 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
42 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
51 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
477 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
302 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
535 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
55 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
304 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 ...
1
vote
1answer
90 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
337 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 ...
4
votes
4answers
1k 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
540 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
348 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
43 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
1answer
76 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
73 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->...
1
vote
2answers
509 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
494 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 ...
2
votes
2answers
817 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
79 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
85 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
55 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
328 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 ...

1
2 3