1

Let's say that I have this table which keeps track of every page load on my website:

CREATE TABLE "example.com page loads"
(
    id                      bigserial,
    "URL"                   text NOT NULL,
    "IP address"            inet NOT NULL,
    "user agent"            text,
    "timestamp"             timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY             (id)
)

If the same person loads 100 pages, or many other people with the same exact user-agent string load 10,000 pages, the same long "user agent" string will be stored redundantly 100/10,000 times in my poor table, massively inflating it.

This was always a huge problem to me when I used to use plaintext webserver logs, and later when I did the exact same thing as I'm describing right now (a database table in PostgreSQL).

A very obvious and immediate thought that pops up in my head is: "Why can't the user agents be automatically stored just once, internally, and then automatically 'referenced' by PostgreSQL in whatever manner it is comfortable with, while never exposing this internal optimization to me?"

That is, I don't want to have to make a separate table like this:

CREATE TABLE "example.com unique user agents"
(
    id                      bigserial,
    "user agent"            text,
    PRIMARY KEY             (id),
    UNIQUE                  ("user agent")
)

... and then be forced to do expensive and annoying manual queries to look up whether the user-agent is already present in the table of unique user agents and then have a column called "unique user agent id" referencing this table from the "page loads" table instead of a nice, simple text column.

I'm sure you understand exactly what I mean. Basically, it's such a common/obvious thing that I'm 99% sure that this must already have been solved a looong time ago, only I have just never realized it.

There is probably some simple feature to do exactly this, such as (this is just my guess):

CREATE TABLE "example.com page loads"
(
    id                      bigserial,
    "URL"                   text NOT NULL,
    "IP address"            inet NOT NULL,
    "user agent"            text OPTIMIZE_UNIQUELY_INTERNALLY,
    "timestamp"             timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY             (id)
)

That would be lovely, if there is such a "OPTIMIZE_UNIQUELY_INTERNALLY" flag that I can just apply to columns when I want this to be done "under the hood", without me having to think about it!

If there is such a thing, that would save me enormous amounts of storage and headaches.

I don't think that this is the same thing as indexes. Making the "user agent" column into an index won't make PG store each unique value only once, would it? It would only create an additional "look-up" table for quicker queries?

2
  • This is known as "dictionary based compression" in other DBMS (I think at least DB2 offers that). But Postgres does not provide such a feature. Btw: you should really avoid those dreaded quoted identifiers. They are much more trouble in the long run than they are worth it
    – user1822
    Dec 3, 2019 at 6:38
  • 1
    Getting back to basics....are you sure you want to store https log data in a database? There are so many decent, commodity logging solutions around these days (Honeycomb, LogDNA, even Loggly...the list is enormous), an RDMBS wouldn't be my first choice, without some other good reason. Dec 3, 2019 at 10:30

2 Answers 2

2

There is no magical automatism for that. You will have to create the lookup table yourself.

This is the way relational databases are designed: you spread your data over several tables. For example, if you normalize your schema, you will end up with more tables than entities. In a way, this lookup table can be seen as a kind of normalization, since the user agent doesn't feel atomic to you.

Don't worry about having more than one table: inner joins are quite simple and readable in SQL, and databases are optimized to process them efficiently.

1

Described another way, what you want to do is like so:

  • On page_load INSERT
  • Search on user_agent
  • IF you find a match, fetch and store the user_agent.id
  • ELSE INSERT the user_agent, return and store the user_agent.id

Basically, create a missing user_agent record, but only when needed, and get the matching id back into page_load. Makes sense. I figured I'd try, and I set up an example that works....but it's got some real problems. I'm posting it in hopes of getting someone who knows Postgres better to tell us the right way to do this. Like, maybe inserting through a function instead? That might be a lot easier. Okay, the obvious problems with this solution are:

  • I've got an ugly passthrough field in the user_agent field.
  • The user_agent records get rewritten over, and over, and over. Bad. Particularly in Postgres. Bad.
  • It's a [Rube Goldberg Machine][1].

Anyway, here's the code. Note that I renamed things a bit to make them Postgres-sensible.


--------------------------------------------------
-- Define the user_agent table
--------------------------------------------------
DROP TABLE IF EXISTS ascendco.user_agent CASCADE;
CREATE TABLE ascendco.user_agent
(
    id              int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    agent_string    text -- You might want citext here.
);


CREATE UNIQUE INDEX user_agent_unique_index
    ON ascendco.user_agent(agent_string);

ALTER TABLE ascendco.user_agent
    ADD CONSTRAINT user_agent_unique_constraint
    UNIQUE USING INDEX user_agent_unique_index;

------------------------------------------------------------
-- Create a trigger function to block pointless rewrites
------------------------------------------------------------
-- Straight from the manual:
-- https://www.postgresql.org/docs/current/functions-trigger.html
--CREATE TRIGGER z_skip_pointless_rewrite
--BEFORE UPDATE ON user_agent
--FOR EACH ROW EXECUTE FUNCTION suppress_redundant_updates_trigger();
-- NOPE! Apart from being kind of a terrible idea, this doesn't work as then
--  RETURNING clause is suppressed.

--------------------------------------------------
-- Define the page_load table
--------------------------------------------------
DROP TABLE IF EXISTS ascendco.page_load;
CREATE TABLE ascendco.page_load
(
    id                  int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- Seems to be a BIGSERIAL bug on this in my version of PG, 11.5?
    timestamp           timestamptz NOT NULL DEFAULT now(),
    ip_address          inet NOT NULL,
    user_agent_id       int,
    url                 text NOT NULL, -- You might want citext here. 
    agent_passthrough   text -- This is a agent_passthrough for the user_agent string, the data is not saved.
);

ALTER TABLE ascendco.page_load
    ADD CONSTRAINT page_load_user_agent_fk
    FOREIGN KEY (user_agent_id) 
    REFERENCES user_agent (id);

--------------------------------------------------
-- Add a view that shows the user-agent string
--------------------------------------------------
CREATE OR REPLACE VIEW ascendco.page_load_plus AS

   SELECT page_load.id,
           page_load.timestamp,
           page_load.ip_address,
           page_load.url,
           user_agent.agent_string         

      FROM page_load
 LEFT JOIN user_agent ON (user_agent.id = page_load.user_agent_id);    


--------------------------------------------------
-- Create a trigger function for page_load
--------------------------------------------------
CREATE OR REPLACE FUNCTION ascendco.get_user_agent_id()
  RETURNS pg_catalog.trigger 

AS $BODY$

DECLARE
    agent_id int;

BEGIN

    INSERT INTO user_agent (agent_string)  
    VALUES (NEW.agent_passthrough)
    ON CONFLICT (agent_string) DO UPDATE -- Do nothing seems to break the RETURNING clause.
        SET agent_string = NEW.agent_passthrough
    RETURNING user_agent.id 
    INTO agent_id;

    NEW.user_agent_id := agent_id;
    NEW.agent_passthrough  := ''; -- Clear this out of the page_load record.
    RETURN NEW;       -- important!
END;

$BODY$
    LANGUAGE plpgsql 
    VOLATILE;

--------------------------------------------------
-- Bind the trigger function to the table
--------------------------------------------------
CREATE TRIGGER page_load_upsert 
    BEFORE INSERT OR UPDATE
    ON ascendco.page_load
    FOR EACH ROW
    EXECUTE PROCEDURE ascendco.get_user_agent_id();

COMMIT;

-- Insert some sample data. You should have 3 page_load and 2 user_agent records
insert into page_load (ip_address,url,agent_passthrough) values
     ('192.168.100.128','http://www.example.com/news/index.html','Camino 1.2.3'),
     ('192.168.100.129','http://www.example.com/blog/index.html','Lynx 4.5.6'),
     ('192.168.100.130','http://www.example.com/contact/index.html','Camino 1.2.3');```


  [1]: https://en.wikipedia.org/wiki/Rube_Goldberg_machine
1
  • Yeah, that code to create the user_agent record looks really inefficient. Maybe a dedicated function that does a lookup with LIMIT 1 and INSERT otherwise. Dec 3, 2019 at 10:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.